May 14, 2009 at 7:50 am
I'm trying to create a view of item quantities with due dates. I want to total each item by month. I'm using date part as below. When I get out to +9, the next year (2010) it will return 0. How do I account for the next year? Thanks
SUM(CASE WHEN DATEPART(MONTH, scheddate) = DATEPART(MONTH, GetDate()) THEN qty ELSE 0 END) AS month_current, SUM(CASE WHEN DATEPART(MONTH, scheddate) = DATEPART(MONTH, GetDate()) + 1 THEN qty ELSE 0 END) AS month_2
May 14, 2009 at 8:01 am
Really need more information to help you. It would help if you would take the time read and follow the guidelines in the first article I reference below in my signature block. If you would like to know more as to why, go to my blog (SQL Musing from the Desert) and read the entry on "The Flip Side" (that is just part of the title).
May 14, 2009 at 8:49 am
you need to use the dateadd with convert functions
select
SUM(CASE WHEN DATEPART(MONTH, scheddate) = DATEPART(MONTH, GetDate()) THEN qty ELSE 0 END) AS month_current,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 1, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_2,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 2, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_3,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 3, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_4,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 4, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_5,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 5, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_6,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 6, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_7,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 7, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_8,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 8, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_9,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 9, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_10,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 10, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_11,
SUM(CASE WHEN left(convert(varchar, scheddate, 112), 6) = left(convert(varchar, dateadd(month, 11, GetDate()), 112), 6) THEN qty ELSE 0 END) AS month_12
from (
select getdate() as scheddate, 1 as qty
union all
select '20090605' as scheddate, 1 as qty
union all
select '20090605' as scheddate, 1 as qty
union all
select '20091105' as scheddate, 1 as qty
union all
select '20100205' as scheddate, 1 as qty
) T1
May 14, 2009 at 9:41 am
Samuel,
This is exactly what I needed, works great! thank you very much.
May 14, 2009 at 9:55 am
mike (5/14/2009)
Samuel,This is exactly what I needed, works great! thank you very much.
No probs 😀
Date comparisons can throw up a few of these gotchas for Newbies!
Have a careful look at the date functions and the convert function in books online. When working with any business data, being able to acurately deal with dates is a must.
May 14, 2009 at 1:30 pm
Thanks for this post Samuel! This is something I can use too...
Michelle 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply