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

How To Get a row(date) for each month Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 5:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224

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
Post #1462990
Posted Thursday, June 13, 2013 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:04 AM
Points: 2,449, Visits: 2,992
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’! **
Post #1463000
Posted Thursday, June 13, 2013 6:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 2,689, Visits: 4,748
Something like this?

DECLARE	@min DATETIME
DECLARE @max DATETIME

SELECT @min = MIN(dtePostedToWebsiteDate)
FROM dtlVacancyPostAudit
WHERE bitPostToWebSite = 1

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

SELECT DATENAME(MONTH,@min) AS MinMonth,
YEAR(@min) AS MinYear,
DATEADD(MONTH,sv.number,@min)
FROM master.dbo.spt_values AS sv -- You can use a Tally table instead as well
WHERE sv.type = 'P'
AND DATEADD(MONTH,sv.number,@min) < @max

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/
Post #1463048
Posted Thursday, June 13, 2013 7:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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:
MinMonth MinYear
June 2012
July 2012
July 2012
July 2012
July 2012
July 2012
July 2012
September 2012
September 2012
September 2012
September 2012
September 2012
September 2012
September 2012
November 2012
November 2012
November 2012
November 2012
November 2012
January 2013
February 2013
February 2013
February 2013
March 2013
March 2013
March 2013
March 2013
March 2013
April 2013
April 2013
May 2013
May 2013
May 2013
June 2013
Post #1463069
Posted Thursday, June 13, 2013 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
@Kingston Dhasian, Thank you so much for the script that's returning the dates it works perfectly.
Post #1463073
Posted Thursday, June 13, 2013 7:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 2,689, Visits: 4,748
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/
Post #1463099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse