|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
DATEADD - Count events for LAST MONTH, Count events for NEXT MONTH
Okay, thanks to help and pulling out hair, I able to COUNT specific caterories of events for the CURRENT MONTH from my calendar. This is the SQL Query that is working:
SELECT COUNT(ItemID) AS [Total Closings for Current Month] FROM CalendarEvents inner join CalendarCategories on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID WHERE CalendarEvents.CalCategoryID IN (1, 2, 3, 4, 20) AND month(StartDate)=month(getdate()) AND year(StartDate)=year(getdate()) AND CalendarEvents.ModuleID = 360 Now, I'm trying to COUNT the events with specific categories from the NEXT MONTH and from the PREVIOUS MONTH. This is what I tried for events from LAST MONTH but it returns an incorrect quantity: SELECT COUNT(ItemID) AS [Total Closings for LAST Month] FROM CalendarEvents INNER JOIN CalendarCategories ON CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID WHERE CalendarEvents.CalCategoryID IN (1, 2, 3, 4, 20) AND (StartDate <= DATEADD(MONTH, -1, (CONVERT(char(10), GETDATE(), 101))) AND StartDate > DATEADD(MONTH, -2, (CONVERT(char(10), GETDATE(), 101)))) AND CalendarEvents.ModuleID = 360 This is the INCORRECT quantity for NEXT MONTH'S events: SELECT COUNT(ItemID) AS [Total Closings for NEXT Month] FROM CalendarEvents INNER JOIN CalendarCategories ON CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID WHERE CalendarEvents.CalCategoryID IN (1, 2, 3, 4, 20) AND (StartDate >= DATEADD(MONTH, 1, (CONVERT(char(10), GETDATE(), 101))) AND StartDate < DATEADD(MONTH, 2, (CONVERT(char(10), GETDATE(), 101)))) AND CalendarEvents.ModuleID = 360 HELP PLEASE |
|
#2
|
|||
|
|||
|
first of all your not doing the same query for the current month and the last/next month...try to put the month(...) and year(...) like the current query into your other query
P.s Next time try to think about writting one query or SP with different param, instead of writting 3(just a hint...I know that sometime you really got no choice.) |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > DATEADD - Count events for LAST MONTH, Count events for NEXT MONTH |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|