How To Get a row(date) for each month

  • 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

  • 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’! **
  • 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/

  • 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

    July2012

    July2012

    July2012

    July2012

    July2012

    September2012

    September2012

    September2012

    September2012

    September2012

    September2012

    September2012

    November2012

    November2012

    November2012

    November2012

    November2012

    January2013

    February2013

    February2013

    February2013

    March2013

    March2013

    March2013

    March2013

    March2013

    April2013

    April2013

    May2013

    May2013

    May2013

    June2013

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

  • 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/

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

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