Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Insert Dates into table

Posted on 2014-12-22
Medium Priority
113 Views
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
Question by:halifaxman
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 13

Accepted Solution

Jesus Rodriguez earned 2000 total points
ID: 40513842
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
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
FROM YOUR_TABLE
``````

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

LVL 69

Expert Comment

ID: 40513946
--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

LVL 13

Expert Comment

ID: 40513963
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?