Calendar Only Using SELECT - SQL Server Forums

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

Calendar Only Using SELECT

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 01:30:02

First of all, I need to preface that I know there are much
better ways to do this. I know it would be preferable to
either set up a calendar table, stored procedure,
UDF or even a while loop to do this.

However,we have a business need to set this up in a
select statement. I'm not able to put any stored procedures,
UDF's, views or tables into the database for this application.
We're really only limited to select statements.

With that, I have the following query. I would like to add
a column that shows a 14 day period starting with the first
day (@firstday). I will also need to set a period year for
each period (not necessarily the same year of the date...ie
end of December and Beginning of January have crossover
some years). I also need to set a period number for each period.

Here is the current query that I put together for this:
[CODE]
DECLARE @FirstDay DATE
SET @FirstDay='2001-01-08'

SELECT DATEADD(DD, A.NUMBER, @FirstDay) AS SDate, Seq

FROM(
SELECT (v1.number * 2048) + v2.number AS NUMBER,
ROW_NUMBER() OVER (ORDER BY (v1.number * 2048) + v2.number) AS Seq
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2

WHERE v1.type = 'P' AND
v2.type = 'P' AND
((v1.number * 2048) + v2.number) BETWEEN 0 AND 36500) A

WHERE DATEADD(DD, A.NUMBER, @FirstDay)>='2009-12-28' AND
DATEADD(DD, A.NUMBER, @FirstDay)<='2010-01-10'

ORDER BY 1
[/CODE]

Desired Results once the 4 additional columns are added:
[CODE]
Date PayYear PayPeriod DayofPeriod WeekofPeriod
2009-12-28 2010 1 1 1
2009-12-29 2010 1 2 1
2009-12-30 2010 1 3 1
2009-12-31 2010 1 4 1
2010-01-01 2010 1 5 1
2010-01-02 2010 1 6 1
2010-01-03 2010 1 7 1
2010-01-04 2010 1 8 2
2010-01-05 2010 1 9 2
2010-01-06 2010 1 10 2
2010-01-07 2010 1 11 2
2010-01-08 2010 1 12 2
2010-01-09 2010 1 13 2
2010-01-10 2010 1 14 2[/code]

**Note: The Seq column isn't needed in the final result set

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 01:33:25

how do you determine when a period should start and end as you've specified there are cross overs . is there any predefined logic for defining periods?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 01:41:21

The first date: @firstday is the start of the sequence. From there, I want to have consecutive numbering that partitions at "14"

So for example...

2001-01-08 = 1
2001-01-09 = 2
2001-01-10 = 3
2001-01-11 = 4
2001-01-12 = 5
2001-01-13 = 6
2001-01-14 = 7
2001-01-15 = 8
2001-01-16 = 9
2001-01-17 = 10
2001-01-18 = 11
2001-01-19 = 12
2001-01-20 = 13
2001-01-21 = 14
2001-01-22 = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 02:11:08

[code]
DECLARE @FirstDay DATE
SET @FirstDay='2001-01-08'

;With CTE(DateVal)
AS
(
SELECT @FirstDay
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM CTE
WHERE DATEADD(dd,1,DateVal)<= DATEADD(yy,1,@firstDay)
)

SELECT DateVal,
(sq-1) % 14 + 1 AS PeriodNo,
(((sq-1) % 14 + 1)-1) / 7 +1 AS PeriodWeek
FROM
(
SELECT DateVal,ROW_NUMBER() OVER (ORDER BY DateVal) AS Sq
FROM CTE
)t

OPTION (MAXRECURSION 0)

output
-------------------------------------------------
DateVal PeriodNo PeriodWeek
2001-01-08 1 1
2001-01-09 2 1
2001-01-10 3 1
2001-01-11 4 1
2001-01-12 5 1
2001-01-13 6 1
2001-01-14 7 1
2001-01-15 8 2
2001-01-16 9 2
2001-01-17 10 2
2001-01-18 11 2
2001-01-19 12 2
2001-01-20 13 2
2001-01-21 14 2
2001-01-22 1 1
2001-01-23 2 1
2001-01-24 3 1
2001-01-25 4 1
2001-01-26 5 1
2001-01-27 6 1
2001-01-28 7 1
2001-01-29 8 2
2001-01-30 9 2
2001-01-31 10 2
2001-02-01 11 2
2001-02-02 12 2
2001-02-03 13 2
2001-02-04 14 2
2001-02-05 1 1
2001-02-06 2 1
2001-02-07 3 1
2001-02-08 4 1
2001-02-09 5 1
2001-02-10 6 1
2001-02-11 7 1
2001-02-12 8 2
2001-02-13 9 2
2001-02-14 10 2
2001-02-15 11 2
2001-02-16 12 2
2001-02-17 13 2
2001-02-18 14 2
2001-02-19 1 1
2001-02-20 2 1
2001-02-21 3 1
2001-02-22 4 1
2001-02-23 5 1
2001-02-24 6 1
2001-02-25 7 1
2001-02-26 8 2
2001-02-27 9 2
2001-02-28 10 2
2001-03-01 11 2
2001-03-02 12 2
2001-03-03 13 2
2001-03-04 14 2
2001-03-05 1 1
2001-03-06 2 1
2001-03-07 3 1
2001-03-08 4 1
2001-03-09 5 1
2001-03-10 6 1
2001-03-11 7 1
2001-03-12 8 2
2001-03-13 9 2
2001-03-14 10 2
2001-03-15 11 2
2001-03-16 12 2
2001-03-17 13 2
2001-03-18 14 2
2001-03-19 1 1
2001-03-20 2 1
2001-03-21 3 1
2001-03-22 4 1
2001-03-23 5 1
2001-03-24 6 1
2001-03-25 7 1
2001-03-26 8 2
2001-03-27 9 2
2001-03-28 10 2
2001-03-29 11 2
2001-03-30 12 2
2001-03-31 13 2
2001-04-01 14 2
2001-04-02 1 1
2001-04-03 2 1
2001-04-04 3 1
2001-04-05 4 1
2001-04-06 5 1
2001-04-07 6 1
2001-04-08 7 1
2001-04-09 8 2
2001-04-10 9 2
2001-04-11 10 2
2001-04-12 11 2
2001-04-13 12 2
2001-04-14 13 2
2001-04-15 14 2
2001-04-16 1 1
2001-04-17 2 1
2001-04-18 3 1
2001-04-19 4 1
2001-04-20 5 1
2001-04-21 6 1
2001-04-22 7 1
2001-04-23 8 2
2001-04-24 9 2
2001-04-25 10 2
2001-04-26 11 2
2001-04-27 12 2
2001-04-28 13 2
2001-04-29 14 2
2001-04-30 1 1
2001-05-01 2 1
2001-05-02 3 1
2001-05-03 4 1
2001-05-04 5 1
2001-05-05 6 1
2001-05-06 7 1
2001-05-07 8 2
2001-05-08 9 2
2001-05-09 10 2
2001-05-10 11 2
2001-05-11 12 2
2001-05-12 13 2
2001-05-13 14 2
2001-05-14 1 1
2001-05-15 2 1
2001-05-16 3 1
2001-05-17 4 1
2001-05-18 5 1
2001-05-19 6 1
2001-05-20 7 1
2001-05-21 8 2
2001-05-22 9 2
2001-05-23 10 2
2001-05-24 11 2
2001-05-25 12 2
2001-05-26 13 2
2001-05-27 14 2
2001-05-28 1 1
2001-05-29 2 1
2001-05-30 3 1
2001-05-31 4 1
2001-06-01 5 1
2001-06-02 6 1
2001-06-03 7 1
2001-06-04 8 2
2001-06-05 9 2
2001-06-06 10 2
2001-06-07 11 2
2001-06-08 12 2
2001-06-09 13 2
2001-06-10 14 2
2001-06-11 1 1
2001-06-12 2 1
2001-06-13 3 1
2001-06-14 4 1
2001-06-15 5 1
2001-06-16 6 1
2001-06-17 7 1
2001-06-18 8 2
2001-06-19 9 2
2001-06-20 10 2
2001-06-21 11 2
2001-06-22 12 2
2001-06-23 13 2
2001-06-24 14 2
2001-06-25 1 1
2001-06-26 2 1
2001-06-27 3 1
2001-06-28 4 1
2001-06-29 5 1
2001-06-30 6 1
2001-07-01 7 1
2001-07-02 8 2
2001-07-03 9 2
2001-07-04 10 2
2001-07-05 11 2
2001-07-06 12 2
2001-07-07 13 2
2001-07-08 14 2
2001-07-09 1 1
2001-07-10 2 1
2001-07-11 3 1
2001-07-12 4 1
2001-07-13 5 1
2001-07-14 6 1
2001-07-15 7 1
2001-07-16 8 2
2001-07-17 9 2
2001-07-18 10 2
2001-07-19 11 2
2001-07-20 12 2
2001-07-21 13 2
2001-07-22 14 2
2001-07-23 1 1
2001-07-24 2 1
2001-07-25 3 1
2001-07-26 4 1
2001-07-27 5 1
2001-07-28 6 1
2001-07-29 7 1
2001-07-30 8 2
2001-07-31 9 2
2001-08-01 10 2
2001-08-02 11 2
2001-08-03 12 2
2001-08-04 13 2
2001-08-05 14 2
2001-08-06 1 1
2001-08-07 2 1
2001-08-08 3 1
2001-08-09 4 1
2001-08-10 5 1
2001-08-11 6 1
2001-08-12 7 1
2001-08-13 8 2
2001-08-14 9 2
2001-08-15 10 2
2001-08-16 11 2
2001-08-17 12 2
2001-08-18 13 2
2001-08-19 14 2
2001-08-20 1 1
2001-08-21 2 1
2001-08-22 3 1
2001-08-23 4 1
2001-08-24 5 1
2001-08-25 6 1
2001-08-26 7 1
2001-08-27 8 2
2001-08-28 9 2
2001-08-29 10 2
2001-08-30 11 2
2001-08-31 12 2
2001-09-01 13 2
2001-09-02 14 2
2001-09-03 1 1
2001-09-04 2 1
2001-09-05 3 1
2001-09-06 4 1
2001-09-07 5 1
2001-09-08 6 1
2001-09-09 7 1
2001-09-10 8 2
2001-09-11 9 2
2001-09-12 10 2
2001-09-13 11 2
2001-09-14 12 2
2001-09-15 13 2
2001-09-16 14 2
2001-09-17 1 1
2001-09-18 2 1
2001-09-19 3 1
2001-09-20 4 1
2001-09-21 5 1
2001-09-22 6 1
2001-09-23 7 1
2001-09-24 8 2
2001-09-25 9 2
2001-09-26 10 2
2001-09-27 11 2
2001-09-28 12 2
2001-09-29 13 2
2001-09-30 14 2
2001-10-01 1 1
2001-10-02 2 1
2001-10-03 3 1
2001-10-04 4 1
2001-10-05 5 1
2001-10-06 6 1
2001-10-07 7 1
2001-10-08 8 2
2001-10-09 9 2
2001-10-10 10 2
2001-10-11 11 2
2001-10-12 12 2
2001-10-13 13 2
2001-10-14 14 2
2001-10-15 1 1
2001-10-16 2 1
2001-10-17 3 1
2001-10-18 4 1
2001-10-19 5 1
2001-10-20 6 1
2001-10-21 7 1
2001-10-22 8 2
2001-10-23 9 2
2001-10-24 10 2
2001-10-25 11 2
2001-10-26 12 2
2001-10-27 13 2
2001-10-28 14 2
2001-10-29 1 1
2001-10-30 2 1
2001-10-31 3 1
2001-11-01 4 1
2001-11-02 5 1
2001-11-03 6 1
2001-11-04 7 1
2001-11-05 8 2
2001-11-06 9 2
2001-11-07 10 2
2001-11-08 11 2
2001-11-09 12 2
2001-11-10 13 2
2001-11-11 14 2
2001-11-12 1 1
2001-11-13 2 1
2001-11-14 3 1
2001-11-15 4 1
2001-11-16 5 1
2001-11-17 6 1
2001-11-18 7 1
2001-11-19 8 2
2001-11-20 9 2
2001-11-21 10 2
2001-11-22 11 2
2001-11-23 12 2
2001-11-24 13 2
2001-11-25 14 2
2001-11-26 1 1
2001-11-27 2 1
2001-11-28 3 1
2001-11-29 4 1
2001-11-30 5 1
2001-12-01 6 1
2001-12-02 7 1
2001-12-03 8 2
2001-12-04 9 2
2001-12-05 10 2
2001-12-06 11 2
2001-12-07 12 2
2001-12-08 13 2
2001-12-09 14 2
2001-12-10 1 1
2001-12-11 2 1
2001-12-12 3 1
2001-12-13 4 1
2001-12-14 5 1
2001-12-15 6 1
2001-12-16 7 1
2001-12-17 8 2
2001-12-18 9 2
2001-12-19 10 2
2001-12-20 11 2
2001-12-21 12 2
2001-12-22 13 2
2001-12-23 14 2
2001-12-24 1 1
2001-12-25 2 1
2001-12-26 3 1
2001-12-27 4 1
2001-12-28 5 1
2001-12-29 6 1
2001-12-30 7 1
2001-12-31 8 2
2002-01-01 9 2
2002-01-02 10 2
2002-01-03 11 2
2002-01-04 12 2
2002-01-05 13 2
2002-01-06 14 2
2002-01-07 1 1
2002-01-08 2 1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 02:11:59

I've shown it for a year and you can repeat it similarly for any number of years

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 02:13:31

That's wicked Visakh...but I should have said before, I can't use CTE's in this environment either. Do you think it could be achieved using derived tables?

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 02:18:20

Actually, unless I'm missing something, it seems to be calculating properly when I just add your "sq" columns to my query like so:



DECLARE @FirstDay DATE
SET @FirstDay='2001-01-08'

SELECT DATEADD(DD, A.NUMBER, @FirstDay) AS SDate, seq, (seq-1) % 14 + 1, (((seq-1) % 14 + 1)-1) / 7 +1

FROM(
SELECT  (v1.number * 2048) + v2.number AS NUMBER, 
		ROW_NUMBER() OVER (ORDER BY (v1.number * 2048) + v2.number) AS Seq
FROM master.dbo.spt_values v1 
CROSS JOIN master.dbo.spt_values v2

WHERE	v1.type = 'P' AND
		v2.type = 'P' AND 
		((v1.number * 2048) + v2.number) BETWEEN 0 AND 36500) A

WHERE DATEADD(DD, A.NUMBER, @FirstDay)>='2009-12-28' and DATEADD(DD, A.NUMBER, @FirstDay)<='2010-01-10'

ORDER BY 1

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 02:49:49

Vis, it looks like it's working like I noted before, but I'm not really following the logic for the 2 columns:
(seq-1) % 14 + 1 AS DayOfPeriod, (((seq-1) % 14 + 1)-1) / 7 +1 AS WeekOfPeriod

It's obvious to me that I'm not understanding it because when I try to apply a similar approach to the PayYear and the PayPeriod number, I get nothing. So, like I mentioned earlier, the pay year is dependent on when the pay periods start for the year. Since the first pay year was 2001, that's what's set as PayYear 1. The number of pay periods in a year depend on the year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 02:57:48

seq is just a sequence number in my case. if you want to replace it with payyear how should it go? should it repeat 1 to 14 for whole year?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 09:30:57

Sorry, I know I'm not explaining this very well. I have a function that accomplishes what I'm trying to do (author-Wolffy from DeveloperBarn a few years ago). It works well and I can follow it. I just can't use it in my current application, so I was trying to achieve the same idea through a select statement. Here's the function. Hopefully this makes it more clear:

[CODE]
create table test.dbo.PayPeriods
(Date_Id int Primary Key not null, Date_Index datetime not null, Pay_Year smallint not null, Pay_Period tinyint not null, Day_Of_Period tinyint not null)

DECLARE
@lastDay as DateTime
, @firstDay as DateTime
, @payYear as integer
, @payPeriod as TinyInt
, @dayOfPeriod as TinyInt
, @looper as integer
, @thisDay as DateTime
;
BEGIN
Set @firstDay = CAST('2001-01-08' as DateTime);
Set @lastDay = DATEADD(d, 364, @firstDay)
Set @looper = 1;
Set @payYear = 2010;

While @looper <= 20 BEGIN
Set @payPeriod = 1;
Set @dayOfPeriod = 1;
Set @thisDay = @firstDay
While @thisDay < @lastDay BEGIN
INSERT INTO test.dbo.PayPeriods
(DATE_ID, DATE_INDEX, PAY_YEAR,PAY_PERIOD, DAY_OF_PERIOD)
VALUES(cast(convert(char(10), @thisDay,112) as int), @thisDay, @payYear, @payPeriod, @dayOfPeriod);
Set @dayOfPeriod = @dayOfPeriod + 1;
If @dayOfPeriod >14 BEGIN
Set @dayOfPeriod = 1;
Set @payPeriod = @payPeriod + 1
END
Set @thisDay = DATEADD(d, 1, @thisDay);
END
Set @payYear = @payYear + 1
Set @firstDay = @lastDay
Set @lastDay = DATEADD(d, 364, @firstDay)
Set @looper = @looper + 1
END
END;

select * from Test.dbo.PayPeriods
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources