How To Get a row(date) for each month

Question

Post reply

How To Get a row(date) for each month

Teee-SQL

SSCrazy

Points: 2218
More actions
June 13, 2013 at 5:38 am

#278567

Hi All,
I need to get a minimum month and year from a table and then a row for every month/year up to today.
How to I achieve that, here's the query I'm using below:
Select
MIN(DATENAME(MONTH, dtePostedToWebsiteDate))as MinMonth
,MIN(Year(dtePostedToWebsiteDate))as MinYear
,''as [RowForEachMonth] --??
From dtlVacancyPostAudit
Where bitPostToWebSite = 1
Please help.
Thanks
Teee

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

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

I'm not sure it this is what you want.

Select DISTINCT

DATENAME(MONTH, dtePostedToWebsiteDate) as Month

,Year(dtePostedToWebsiteDate) as Year

From dtlVacancyPostAudit

Where bitPostToWebSite = 1

GROUP BY dtePostedToWebsiteDate

ORDER BY 2, 1

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

Something like this?

DECLARE@min-2 DATETIME

DECLARE@max-2 DATETIME

SELECT@min-2 = MIN(dtePostedToWebsiteDate)

FROMdtlVacancyPostAudit

WHEREbitPostToWebSite = 1

SET@max-2 = DATEADD(MONTH,DATEDIFF(MONTH,0,CURRENT_TIMESTAMP)+1,0)

SELECTDATENAME(MONTH,@min) AS MinMonth,

YEAR(@min) AS MinYear,

DATEADD(MONTH,sv.number,@min)

FROMmaster.dbo.spt_values AS sv -- You can use a Tally table instead as well

WHEREsv.type = 'P'

ANDDATEADD(MONTH,sv.number,@min) < @max-2

You can check the below link for a Tally table creation script

http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Teee-SQL SSCrazy Points: 2218 More actions · Answer 3

I need something like this but now its not grouping the months properly:

Select

MIN(DATENAME(MONTH, dtePostedToWebsiteDate))as MinMonth

,MIN(Year(dtePostedToWebsiteDate))as MinYear

From dtlVacancyPostAudit

Where bitPostToWebSite = 1

Group By dtePostedToWebsiteDate

Having dtePostedToWebsiteDate between MIN(dtePostedToWebsiteDate) And GETDATE()

This is the output I'm getting:

MinMonthMinYear

June2012

July2012

September2012

November2012

January2013

February2013

March2013

April2013

May2013

June2013

Teee-SQL SSCrazy Points: 2218 More actions · Answer 4

@kingston Dhasian, Thank you so much for the script that's returning the dates it works perfectly.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

Teee (6/13/2013)
@Kingston Dhasian, Thank you so much for the script that's returning the dates it works perfectly.

I am glad it worked for you and I hope you understand what the code does and how

I would also advise you to go through the articles on Tally articles on this site which help you solve a lot of similar problems

You can start with the link given below

http://www.sqlservercentral.com/articles/T-SQL/62867/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/