Mark Wilson
asked on
Insert Dates into table
I have a table with three fields Day (Date), Period (int), Period_Week (int)I want to insert data into the table as follows
Starting date - 4th October 2014 to 1st January 2016
Day Period Period_Week
04/10/2014 10 4
05/10/2014 10 4
06/10/2014 10 4
07/10/2014 10 4
08/10/2014 10 4
09/10/2014 10 4
10/10/2014 10 4
11/10/2014 11 1
The Period Week runs from Sat to Fri, and there are 4 weeks in a Period, which is why on the 11th October it goes onto Period 11 Week 1.
There are 13 periods in a year and 4 weeks in a period, the last date of period 13 week 4 is 02/01/15
Any help on this would be appreciated
Starting date - 4th October 2014 to 1st January 2016
Day Period Period_Week
04/10/2014 10 4
05/10/2014 10 4
06/10/2014 10 4
07/10/2014 10 4
08/10/2014 10 4
09/10/2014 10 4
10/10/2014 10 4
11/10/2014 11 1
The Period Week runs from Sat to Fri, and there are 4 weeks in a Period, which is why on the 11th October it goes onto Period 11 Week 1.
There are 13 periods in a year and 4 weeks in a period, the last date of period 13 week 4 is 02/01/15
Any help on this would be appreciated
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also on my comment you can said with will be the First day of week of the SQl to start the qury like this
SET DATEFIRST 6 -- this will set Saturday as the first day for the query
SET DATEFIRST 6 -- this will set Saturday as the first day for the query
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '20141004'
SET @end_date = '20160101'
--------------------------
DECLARE @known_period_1_start_date
--will this always be the first Sat of the year?
--if so, this code can easily be made generic to work for every year
SET @known_period_1_start_date
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
SELECT 1 AS tally
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
),
cteTally10K AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
FROM cteTally100 c1
CROSS JOIN cteTally100 c2
)
SELECT
Period_Day,
(total_days_since_start_of
(total_days_since_start_of
FROM cteTally10K t
CROSS APPLY (
SELECT DATEADD(DAY, t.tally, @start_date) AS Period_Day
) AS assign_alias_1
CROSS APPLY (
SELECT DATEDIFF(DAY, @known_period_1_start_date
) AS assign_alias_2
WHERE t.tally BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date)
ORDER BY t.tally