Adding months to a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding months to a stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-09 : 14:17:17

How could I add this query...

DECLARE @year varchar(4)
set @year='2008'

;
With Month_CTE (Date,MonthVal,MonthName,Level) as
(
select DATEADD(m,0,@year),DATEPART(mm,DATEADD(m,0,@year)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,0,@year)),3),1
UNION ALL
select DATEADD(m,1,Date),DATEPART(mm,DATEADD(m,1,Date)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,1,Date)),3),Level + 1
FROM Month_CTE
WHERE Level <=11
)

SELECT MonthVal,MonthName FROM Month_CTE

To this stored procedure to get each month out of the year to display?

ALTER procedure [dbo].[BiWeeklyDates]-- '04/03/2009','05/29/2009'

@startdate datetime,
@enddate datetime
as

select convert(varchar(30),dateadd(week,number*2,@startdate),1) as Dates from master..spt_values
where type='p' and number<=datediff(week,@startdate,@enddate)/2
and @startdate=@startdate and @enddate=@enddate

I found the month query on the internet but would like to combine the two if possible:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:19:10

so what should be your output? month along with days information?

------------------------------------------------------------------------------------------------------
SQL Server MVP

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-09 : 14:36:22

Yes is this possible?

04/03/2009 Jan
04/17/2009 Feb
05/01/2009 Mar
05/15/2009 April
05/29/2009 May
June
July
August
Sept
Oct
Nov
Dec

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-09 : 14:38:52

Sorry about that here's what the output should look like if I entered 04/03/2009 - 05/29/2009 I have to create an online calculator and want to be able to tally the April dates and place that in a textbox I have set up.

	
04/03/2009		Jan	
04/17/2009		Feb	
05/01/2009		Mar	
05/15/2009		April	
05/29/2009		May	
		        June	
		        July	
		        August	
		        Sept	
		        Oct	
		        Nov	
		Dec

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:47:37

quote:
Originally posted by JJ297

Sorry about that here's what the output should look like if I entered 04/03/2009 - 05/29/2009 I have to create an online calculator and want to be able to tally the April dates and place that in a textbox I have set up.

04/03/2009 Jan
04/17/2009 Feb
05/01/2009 Mar
05/15/2009 April
05/29/2009 May
June
July
August
Sept
Oct
Nov
Dec

for this i think what you need is


ALTER procedure [dbo].[BiWeeklyDates]-- '04/03/2009','05/29/2009'


@startdate datetime,
@enddate datetime
as

DECLARE @year varchar(4)
set @year='2008'


;
With Month_CTE (Date,MonthVal,MonthName,Level) as
(
select DATEADD(m,0,@year),DATEPART(mm,DATEADD(m,0,@year)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,0,@year)),3),1
UNION ALL
select DATEADD(m,1,Date),DATEPART(mm,DATEADD(m,1,Date)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,1,Date)),3),Level + 1
FROM Month_CTE
WHERE Level <=11
)

SELECT m.MonthName,d.Dates 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY MonthVal) AS Seq,* FROM Month_CTE) m
LEFT JOIN(
select convert(varchar(30),dateadd(week,number*2,@startdate),1) as Dates,
ROW_NUMBER() OVER(ORDER BY  dateadd(week,number*2,@startdate) AS Seq
from master..spt_values
where type='p' and number<=datediff(week,@startdate,@enddate)/2
and @startdate=@startdate and @enddate=@enddate
)d
on d.Seq=m.Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-09 : 15:01:39

Thanks I tried that but I'm getting Incorrect syntax near the keyword 'AS'.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-02-09 : 15:03:57

This is line 26

Msg 156, Level 15, State 1, Procedure Dates1, Line 26
Incorrect syntax near the keyword 'AS'.

ROW_NUMBER() OVER(ORDER BY dateadd(week,number*2,@startdate) AS Seq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 00:12:52

missed a bracket


ALTER procedure [dbo].[BiWeeklyDates]-- '04/03/2009','05/29/2009'


@startdate datetime,
@enddate datetime
as

DECLARE @year varchar(4)
set @year='2008'


;
With Month_CTE (Date,MonthVal,MonthName,Level) as
(
select DATEADD(m,0,@year),DATEPART(mm,DATEADD(m,0,@year)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,0,@year)),3),1
UNION ALL
select DATEADD(m,1,Date),DATEPART(mm,DATEADD(m,1,Date)) as MonthVal,LEFT(DATENAME(mm,DATEADD(m,1,Date)),3),Level + 1
FROM Month_CTE
WHERE Level <=11
)

SELECT m.MonthName,d.Dates 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY MonthVal) AS Seq,* FROM Month_CTE) m
LEFT JOIN(
select convert(varchar(30),dateadd(week,number*2,@startdate),1) as Dates,
ROW_NUMBER() OVER(ORDER BY  dateadd(week,number*2,@startdate)) AS Seq
from master..spt_values
where type='p' and number<=datediff(week,@startdate,@enddate)/2
and @startdate=@startdate and @enddate=@enddate
)d
on d.Seq=m.Seq