Syntax 1

[NotSQL]

hi i wonder if someone could help with the correct syntax. I have the following sql statement


select case when DueDate = dateadd(mm,0,DueDate) then sum(Qty) else 0 end as month1, case when DueDate = dateadd(mm,-1,DueDate) then sum(Qty) else 0 end as month2
From dbRD_ITEMS

i want to sum the qty for everything where the due date is last month and the month before and so on for 12 months..
Can someone explain what im doing wrong.

Thanks in advance

 

[gmmastros]

Try this:

select Sum(case when DueDate = dateadd(mm,0,DueDate) 
            then Qty
            else 0 end) as month1, 
       Sum(case when DueDate = dateadd(mm,-1,DueDate) 
            then Qty else 0 end) as month2
From dbo.ORD_ITEMS

Notice the slight difference between your query and mine?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NotSQL]

Thanks, however this doesn’t give me the expected results.

Not sure if my sql is right but what im trying to achieve is: When the duedate equals last month sum the qty, when the duedate equals the month before sum the qty and so on...

 

[gmmastros]

Maybe this:

Select Sum(Case When DateDiff(Month, GetDate(), DueDate) = 0
                Then Qty End) As Month1,
       Sum(Case When DateDiff(Month, GetDate(), DueDate) = -1
                Then Qty End) As Month2
From   dbo.ORD_ITEMS

If this works, and you would like me to explain it, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I think we need to test due date in intervals.

The brute force approach would be

declare @nCurYear int
set @nCurYear = year(getdate())

select sum(case when month(DueDate) = 1 and year(DueDate) = @nCurYear then Qty else 0 end) as JanTotal, etc.