Help needed in Date Logic
-
Hi I am working in sqlserver 2008 R2 and below is my sample research query
i am trying to get previous 6 months data.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
,(2)
,(3)
,(4)
,(5)
,(6)
) x(N)
WHERE N <= 6
)
SELECT month
,SUM(isnull(perks.amount,0)) AS PerkAmount
FROM CutomMonths
LEFT JOIN (
select 10.00 as amount,'2014-04-03' as StartDate,'2015-04-03' as EndDate union all
select 10.00 as amount,'2014-04-03' as StartDate,'2015-04-03' as EndDate
) perks ON
CutomMonths.startdate >= perks.StartDate
AND CutomMonths.startdate < perks.EndDate
GROUP BY CutomMonths.Month
,CutomMonths.startdate
ORDER BY CutomMonths.startdate ASC
current output what i am getting:
Expected Output:
I found why the April month i din't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output. please help me on this as i am struggling on my logic
-
Here is the only way of doing it which i know, is there any other way of doing this.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
,(2)
,(3)
,(4)
,(5)
,(6)
) x(N)
WHERE N <= 6
)
SELECT month
,SUM(isnull(perks.amount,0)) AS PerkAmount
FROM CutomMonths
LEFT JOIN (
select 10.00 as amount,'2014-04-03' as StartDate,'2015-04-03' as EndDate union all
select 10.00 as amount,'2014-04-03' as StartDate,'2015-04-03' as EndDate
) perks ON
CutomMonths.startdate >= DATEADD(mm, DATEDIFF(mm, 0, perks.StartDate), 0)
AND CutomMonths.startdate < DATEADD(mm, DATEDIFF(mm, 0, perks.EndDate), 0)
GROUP BY CutomMonths.Month
,CutomMonths.startdate
ORDER BY CutomMonths.startdate ASC
-
Quick suggestion for simplification
😎
USE tempdb;GO
SET NOCOUNT ON;
DECLARE @REPORT_MONTHS INT = 6;
DECLARE @NOW DATE = GETDATE();
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@REPORT_MONTHS) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7)
,SAMPLE_DATA AS
(
select 10.00 as amount,'2014-04-03' as StartDate,'2015-04-03' as EndDate union all
select 10.00 as amount,'2014-04-03' as StartDate,'2015-04-03' as EndDate
)
SELECT
SUBSTRING(UPPER(DATENAME(MONTH,DATEADD(MONTH,-NM.N,@NOW))),1,3) AS [Month]
,SUM(SD.amount) AS [PerkAmount]
FROM SAMPLE_DATA SD
OUTER APPLY NUMS NM
WHERE DATEADD(MONTH,-NM.N,@NOW) BETWEEN SD.StartDate AND SD.EndDate
GROUP BY NM.N
ORDER BY NM.N ASC;
Results
Month PerkAmount----- -----------
AUG 20.00
JUL 20.00
JUN 20.00
MAY 20.00
APR 20.00
-
Hi Erik,
thanks for your time on this post and reply. I like the idea but i need to show 0.00 for march as i need to display last 6 months. the way you queried skips the month if it doesn't falls the start date and end date. I need to show 0.00 if it doesn't meets the condition
any suggestions please
-
Then just reverse join order. You may also need to ajust BETWEEN to exact month's first / last days as you do it in your original query. See below
SELECTSUBSTRING(UPPER(DATENAME(MONTH,DATEADD(MONTH,-NM.N,@NOW))),1,3) AS [Month]
,isnull(SUM(SD.amount),0) AS [PerkAmount]
FROM NUMS NM
LEFT JOIN SAMPLE_DATA SD
ON DATEADD(MONTH,-NM.N,@NOW)
BETWEEN DATEADD(MM, DATEDIFF(MM, 0, SD.StartDate), 0)
AND DATEADD(MM, DATEDIFF(MM, 0, SD.EndDate)+1,0)-1
GROUP BY NM.N
ORDER BY NM.N ASC;
-
Hi Serg,
Thanks for your reply and as per my required output, i need to get for Mar the value should be 0.00 and SEP should not come into this picture because this is previous month data
i will try to tweak your query.
-
Note today is 1st of October.
If you need data as it's still September, set @now as needed.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply