• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

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
0
Mark Wilson
Asked:
Mark Wilson
  • 2
1 Solution
 
Jesus RodriguezIT ManagerCommented:
run this query on your sql and you will see the result
 SELECT [DY]
      ,CASE PERIOD_WEEK
            WHEN 4 THEN PERIOD*4
			ELSE PERIOD_WEEK+((PERIOD-1)*4) END AS AMT_WEEKS
	  ,DATEADD(WK,CASE PERIOD_WEEK
                  WHEN 4 THEN PERIOD*4
			      ELSE PERIOD_WEEK+((PERIOD-1)*4) END 
				 ,DY) AS [DY FINAL]
	  ,'From ' + CONVERT(VARCHAR(12),DY,100) + ' To '+ CONVERT(VARCHAR(12),DATEADD(WK,CASE PERIOD_WEEK
																			 WHEN 4 THEN PERIOD*4
																			 ELSE PERIOD_WEEK+((PERIOD-1)*4) END 
																			 ,DY),0) as [YOUR FORMAT]
FROM YOUR_TABLE

Open in new window


You can change this CONVERT(VARCHAR(12),DY,100)  by CONVERT(VARCHAR(12),DY,113)  and will print in this format dd Month of yyyy
0
 
Scott PletcherSenior DBACommented:
--specify date range to list
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '20141004'
SET @end_date = '20160101'

------------------------------------------------------------------------------------------------------------------------

DECLARE @known_period_1_start_date 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 = '20140104'

;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_period_1 / 28) % 13 + 1 AS Period,
    (total_days_since_start_of_period_1 / 7) % 4 + 1 AS Period_Week
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, Period_Day) AS total_days_since_start_of_period_1
) AS assign_alias_2        
WHERE t.tally BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date)
ORDER BY t.tally
0
 
Jesus RodriguezIT ManagerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now