如何根据开始日期和结束日期参数值获取前三个月的日期

Question

我们必须根据最近三个月的参数来比较给定日期的销售额。

Create table Net_sales 
(terminalid varchar(14),
Region varchar(20),
City varchar(50),
ICC_TRNS_COUNT int,
ICC_AMount money,
Trns_Date datetime)

--SELECT * FROM Net_sales
insert into Net_sales values ('INMAA031000000','SOUTH','CHENNAI',1,5000,'08/01/2019')
insert into Net_sales values ('INMAA031000000','SOUTH','CHENNAI',1,4000,'08/02/2019')
insert into Net_sales values ('INMAA031000000','SOUTH','CHENNAI',1,200,'08/04/2019')

insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,52000,'08/01/2019')
insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,40700,'08/02/2019')
insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,2200,'08/04/2019')

insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,52000,'09/01/2019')
insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,47000,'09/02/2019')
insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,2200,'09/10/2019')
insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,52000,'10/01/2019')
insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,70000,'10/02/2019')
insert into Net_sales values ('INAMD03900030G','WEST','Gujarat',1,3200,'10/10/2019')

-------------------
DECLARE @FROMDATE DATETIME
DECLARE @TODAY DATETIME 
SET @FROMDATE='08/01/2019'
SET @TODAY='10/10/2019'
SELECT 
TERMINALID ,
CITY,
REGION,
SUM(ICC_AMOUNT) ICASHDMRAMOUNT
FROM NET_SALES WHERE CONVERT(VARCHAR,TRNS_DATE,101) BETWEEN CONVERT(VARCHAR,@FROMDATE,101) AND CONVERT(VARCHAR,@TODAY,101)
GROUP BY TERMINALID ,
CITY,
REGION 
ORDER  BY TERMINALID

我们需要预期和结果预期结果

TERMINALID  CITY    REGION  ICASHDMRAMOUNT_AUG  ICASHDMRAMOUNT_SEP  ICASHDMRAMOUNT_OCT
INAMD030001024  Gujarat WEST    94200   94200   94200
INAMD03900030G  Gujarat WEST    94900   101200  125200
INMAA031000000  CHENNAI SOUTH   5000    0   0

Answer 1

一些事情。

TerminalIDINAMD030001024不存在于样本数据中，因此不在样本结果集中。

你的日期存储为日期，所以保持这种状态。当您将它们转换为字符串时，日期函数将无法在它们上正常工作。此外，最好使用ANSI/ISO标准的日期格式，即'YYYY-MM-DD'。根据服务器上的区域设置，使用'MM/DD/YYYY'可能会被误解为'DD/MM/YYY'。服务器将始终正确解释标准表单。

话虽如此，您的查询只需要一些条件聚合：

SELECT 
TERMINALID,
CITY,
REGION,
SUM(CASE WHEN MONTH(TRNS_DATE) = 8 THEN ICC_AMOUNT ELSE 0 END) AS ICASHDMRAMOUNT_AUG,
SUM(CASE WHEN MONTH(TRNS_DATE) = 9 THEN ICC_AMOUNT ELSE 0 END) AS ICASHDMRAMOUNT_SEP,
SUM(CASE WHEN MONTH(TRNS_DATE) = 10 THEN ICC_AMOUNT ELSE 0 END) AS ICASHDMRAMOUNT_OCT
FROM 
NET_SALES WHERE TRNS_DATE BETWEEN @FROMDATE AND @TODAY
GROUP BY 
TERMINALID ,
CITY,
REGION 
ORDER BY 
TERMINALID;

结果：

TERMINALID  CITY    REGION  ICASHDMRAMOUNT_AUG  ICASHDMRAMOUNT_SEP  ICASHDMRAMOUNT_OCT
INAMD03900030G  Gujarat WEST    94900   101200  125200
INMAA031000000  CHENNAI SOUTH   9200    0   0

SQL Fiddle：http://sqlfiddle.com/#！18/42bfbc/3/0

如果您编辑Fiddle数据以包括丢失的TerminalID，则应该可以获得您想要的精确结果。

EDIT:如果需要滚动三个月，请创建一个变量，可能是@FinalMonth int，将其设置为MONTH(@TODAY)的值，然后在CASE表达式中使用@FinalMonth-2, @FinalMonth-1, @FinalMonth，而不是上面的硬编码值。如果不执行一些动态SQL，您的列名中就不会有月份名称，但_CurrMnth, _PrevMnth, _2ndPrevMnth或类似的东西会让您明白这一点。