Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DatePart Expand / Collapse
Author
Message
Posted Thursday, May 14, 2009 7:50 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 10:38 AM
Points: 7, Visits: 75
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
Post #716952
Posted Thursday, May 14, 2009 8:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 22,491, Visits: 30,177
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).




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #716970
Posted Thursday, May 14, 2009 8:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 335, Visits: 1,956
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

Post #717032
Posted Thursday, May 14, 2009 9:41 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 10:38 AM
Points: 7, Visits: 75
Samuel,

This is exactly what I needed, works great! thank you very much.
Post #717089
Posted Thursday, May 14, 2009 9:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 335, Visits: 1,956
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.
Post #717111
Posted Thursday, May 14, 2009 1:30 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 06, 2009 4:10 PM
Points: 85, Visits: 182
Thanks for this post Samuel! This is something I can use too...

Michelle
Post #717331
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse