November 30, 2017 at 8:38 am
Hello ~
I am trying to add sequential dates to a table in 2008. By sequential I mean I want to Append, Update or Insert dates (not sure which, or if I need something else altogether different than these options) representing only the Saturday Dates for 2018 into a date field as well as increment the ID field.
At the moment I am trying to do this using an Excel since deriving the weekend dates using autofill makes it fairly simple. However, now that I have the dates I'm struggling to get them inserted into the table while incrementing the ID field.
I'm looking for some initial guidance to point me in the right direction...
Thank you very much...
November 30, 2017 at 9:19 am
MarkW.Rhythm1 - Thursday, November 30, 2017 8:38 AMHello ~I am trying to add sequential dates to a table in 2008. By sequential I mean I want to Append, Update or Insert dates (not sure which, or if I need something else altogether different than these options) representing only the Saturday Dates for 2018 into a date field as well as increment the ID field.
At the moment I am trying to do this using an Excel since deriving the weekend dates using autofill makes it fairly simple. However, now that I have the dates I'm struggling to get them inserted into the table while incrementing the ID field.
I'm looking for some initial guidance to point me in the right direction...
Thank you very much...
Please share DDL for the table and some sample data and expected results based on that sample data. To get guidance on how to get/post this, read the articles in my signature.
November 30, 2017 at 10:23 am
Something like this, will insert all Saturdays for 2018, but I´m sure you have something else in the table.
CREATE TABLE #Calendar(cal_id int IDENTITY, cal_date date);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E a, E b
)
INSERT INTO #Calendar( cal_date)
SELECT DATEADD( wk, n, '20171230')
FROM cteTally
WHERE DATEADD( wk, n, '20171230') < '20190101';
SELECT *
FROM #Calendar;
GO
DROP TABLE #Calendar;
November 30, 2017 at 10:46 am
Hey Luis ~ Thank you for the info. I hope my response reflects at least some understanding; however, I am still very new to SQL
Below is the code I have created to accomplish this task, however, I am receiving a Operand type clash: date is incompatible with int on Line 7
[Code]
DECLARE @Date Date
SET @Date = '1/6/2018';
WHILE @Date <'1/1/2019'
BEGIN
INSERT INTO tri.SaturdaySch(Saturday, TriHours, TriMandHoliday)
VALUES (@Date,'6AM-12PM', 'O');
SET @Date = @Date + 7;
END
[/Code]
I hope the above code renders clarification to what I am trying to accomplish which is to populate the 'Saturday' field with Saturday Dates for all of 2018, and populate the 'TriHours' field with '6AM-12PM', and the 'TriMandHoliday' field with the letter O
Thank You very much for you help...
November 30, 2017 at 10:52 am
MarkW.Rhythm1 - Thursday, November 30, 2017 10:46 AMHey Luis ~ Thank you for the info. I hope my response reflects at least some understanding; however, I am still very new to SQLBelow is the code I have created to accomplish this task, however, I am receiving a Operand type clash: date is incompatible with int on Line 7
[Code]DECLARE @Date Date
SET @Date = '1/6/2018';
WHILE @Date <'1/1/2019'
BEGIN
INSERT INTO tri.SaturdaySch(Saturday, TriHours, TriMandHoliday)
VALUES (@Date,'6AM-12PM', 'O');
SET @Date = @Date + 7;
END
[/Code]
I hope the above code renders clarification to what I am trying to accomplish which is to populate the 'Saturday' field with Saturday Dates for all of 2018, and populate the 'TriHours' field with '6AM-12PM', and the 'TriMandHoliday' field with the letter O
Thank You very much for you help...
Have to use the DATEADD function: SET @Date = DATEADD(DAY,7,@Date);
November 30, 2017 at 11:14 am
If you want inserts, Luis' code is easy to use. Create the Calendar table, then you can merge/insert/update using that joined to your table. This will do inserts:
--CREATE TABLE SaturdaySch
--(
-- Saturday DATE
-- , TriHours VARCHAR(20)
-- , TriMandHoliday CHAR(1)
--);
CREATE TABLE #Calendar(cal_id int IDENTITY, cal_date date);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E a, E b
)
INSERT INTO #Calendar( cal_date)
SELECT DATEADD( wk, n, '20171230')
FROM cteTally
WHERE DATEADD( wk, n, '20171230') < '20190101';
INSERT dbo.SaturdaySch
(
Saturday ,
TriHours ,
TriMandHoliday
)
SELECT cal_date, '6AM-12PM', '0'
FROM #Calendar;
GO
DROP TABLE #Calendar;
SELECT top 100
*
FROM dbo.SaturdaySch AS ss
If you need updates, then MERGE might work well for this.
November 30, 2017 at 11:20 am
Lynn Pettis - Thursday, November 30, 2017 10:52 AMMarkW.Rhythm1 - Thursday, November 30, 2017 10:46 AMHey Luis ~ Thank you for the info. I hope my response reflects at least some understanding; however, I am still very new to SQLBelow is the code I have created to accomplish this task, however, I am receiving a Operand type clash: date is incompatible with int on Line 7
[Code]DECLARE @Date Date
SET @Date = '1/6/2018';
WHILE @Date <'1/1/2019'
BEGIN
INSERT INTO tri.SaturdaySch(Saturday, TriHours, TriMandHoliday)
VALUES (@Date,'6AM-12PM', 'O');
SET @Date = @Date + 7;
END
[/Code]
I hope the above code renders clarification to what I am trying to accomplish which is to populate the 'Saturday' field with Saturday Dates for all of 2018, and populate the 'TriHours' field with '6AM-12PM', and the 'TriMandHoliday' field with the letter O
Thank You very much for you help...
Have to use the DATEADD function: SET @Date = DATEADD(DAY,7,@Date);
So Good!
Thank You, Lynn
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply