Creating query that lists a series of derived dates 2

[RichardOneil]

Good Afternoon -

I have a table that lists pay date by payroll. What I need to get is a list of the all the dates that fall within a pay period. Pay dates are every two weeks, so I am able to reliably capture the starting point of each pay period. Here is my code thus far:

Select payroll,
	     DateAdd("d",0-13,checkdate) AS FROMDATE,
	     checkdate AS THRUDATE
      From PpPayrollScheduleDictionary
      Where active='Y' and DateDiff("m",checkdate,getdate())<18
      Order by payroll,checkdate

It returns:
PAYROLL 08/27/07 09/10/07
PAYROLL 09/11/07 09/24/07

I need it to return:

PAYROLL 08/27/07 09/10/07
PAYROLL 08/28/07 09/10/07
PAYROLL 08/29/07 09/10/07
PAYROLL 08/30/07 09/10/07
...up to
PAYROLL 09/10/07 09/10/07
then start the sequence over on the next pay date

PAYROLL 09/11/07 09/24/07
PAYROLL 09/12/07 09/24/07
PAYROLL 09/13/07 09/24/07
PAYROLL 09/14/07 09/24/07

My guess is that this is a relatively easy thing to do but I am still a SQL newb, so Thanks in advance for the assist.

 

[Geoka]

The below code might give you some idea.

select * from #test
Select dateadd(day,A.number, T.fDate )
From   #Test As T
       Inner Join (
         Select Number 
         From   master..spt_values 
         Where  Type = 'P'
         ) As A
         On dateadd(day,A.number, T.fDate ) Between T.fDate And T.tDate

Have a look at the thread "Find numerical list of numbers between" (thread183-1526960) for its limitations .

George

 

[RichardOneil]

That did the trick... thank you very much for your help. I am not familiar with "master..spt_values" and would like to understand what is happening when this query runs. Could you explain? Thank again, this will be enormously helpful.

 

[gmmastros]

spt_values is a table that exists in your master database that everyone has access to. spt_values stores configuration options. When you filter this table on Type = 'P', you will get 256 rows with an incrementing number column (from 0 to 255).

Often times, there is a benefit to have a numbers table in your database. On some rare occasions, people cannot change the structure of their DB (not even to add a table). Personally, every database I create has a numbers table (from 1 to 1,000,000). It takes about 8 megabytes to store that table, but I can use it for lots of things (like you recently discovered).

For more information regarding a numbers table...
[google]SQL Server numbers table[/google]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom