Help needed in Date Logic

Question

Post reply

Help needed in Date Logic

KGJ-Dev

SSCertifiable

Points: 5780
More actions
September 29, 2014 at 5:34 pm

#290443

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

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

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 3

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

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

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

SELECT

SUBSTRING(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;

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

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.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 6

Note today is 1st of October.

If you need data as it's still September, set @now as needed.