May 15, 2014 at 7:19 am
Here is some INSERT code that is being flagged with a syntax error. I suspect the problem is further up the line:
INSERT INTO [dbo].[CalDates]
(MonthStart,
NextMonth,
MonthDescr,
MonthNam,
YearMonth,
MonthNum,
MMDDYYYY,
IsBusinessDay );
Here is the error:
Msg 102, Level 15, State 1, Line 78
Incorrect syntax near ';'.
I'm trying to create a calendar table that will serve my organizations needs, so I have pulled parts of SQL from others here on SSC (Todd FiField, Alex Kuznetsov, Boyd Tyrrell).
Some basic questions:
1) Do the table columns have to be SET to their local variable counterparts? (like the last SELECT statement below)
2) When I reference a column from the tally table (n), how does this execution 'know' what I'm referencing, don't I have to specify [i]dbo[/i].[i]tablename[/i]?
3) The table is created, rows are inserted, then the table is ALTERED to create indices. Could the table be CREATEd, then the indices be set up immediately afterwards? Then INSERT rows and/or UPDATE rows.
Here is the code that precedes that INSERT, although I have messed around with it so much that I know there is a lot of junk in it.
CREATE TABLE CalDates
(MonthID INT IDENTITY(1,1),
MMDDYYYY DATETIME NOT NULL,
MonthStart DATETIME,
NextMonth DATETIME,
MonthDescr CHAR(6),
MonthNam CHAR(3),
YearMonth INT,
MonthNum INT,
DayNum INT,
IsBusinessDay CHAR(1));
DECLARE
@MMDDYYYY DATETIME,
@MonthStart DATETIME,
@NextMonth DATETIME,
@MonthDescr CHAR(6),
@MonthNam CHAR(3),
@MonthNum INT,
@DayNum INT,
@YearMonth INT,
@IsBusinessDay CHAR(1),
@Year INT;
SET @MonthStart = '1/1/2013';
SET @NextMonth = DATEADD(month,1,@MonthStart);
WHILE @MonthStart < '1/1/2025'
BEGIN
SELECT n FROM [dbo].[numbers];
SELECT DATEADD(DAY,n,'20130101') AS MMDDYYYY;
SET @DayNum = DATEPART(Day, @MonthStart);
SET @MonthNum = DATEPART(Month, @MonthStart);
SET @MonthNam = CASE @MonthNum
WHEN 1 THEN 'JAN'
WHEN 2 THEN 'FEB'
WHEN 3 THEN 'MAR'
WHEN 4 THEN 'APR'
WHEN 5 THEN 'MAY'
WHEN 6 THEN 'JUN'
WHEN 7 THEN 'JUL'
WHEN 8 THEN 'AUG'
WHEN 9 THEN 'SEP'
WHEN 10 THEN 'OCT'
WHEN 11 THEN 'NOV'
ELSE 'DEC' END;
SET @Year = DATEPART(year,@MonthStart);
SET @YearMonth = ((@Year*100) + @MonthNum);
SET @MonthDescr = @MonthNam + '-' + RIGHT(CONVERT(VARCHAR,@Year),2);
SELECT IsBusinessDay = 'Y';
-- If the Day is Sunday, it is not a business day
IF DATEPART(Weekday,DATEADD(DAY,n,'20130101')) = 1
BEGIN
SET @IsBusinessDay = 'N'
END
ELSE
BEGIN
SET @IsBusinessDay = 'Y'
END;
SELECT IsBusinessDay = @IsBusinessDay;
Any help/insight you could provide, would be appreciated. Thanks.
May 15, 2014 at 7:25 am
alicesql (5/15/2014)
Here is some INSERT code that is being flagged with a syntax error. I suspect the problem is further up the line:INSERT INTO [dbo].[CalDates]
(MonthStart,
NextMonth,
MonthDescr,
MonthNam,
YearMonth,
MonthNum,
MMDDYYYY,
IsBusinessDay );
Your insert statement doesn't have any values.
INSERT INTO [TableName]
([Columns])
Values ([Values])
It is hard to tell the rest of what you have going on but why do you need a loop? From what you posted you shouldn't need a loop at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 7:43 am
n needs to be a variable; declare n (select n from dbo.numbers) as a variable, set it as SET @N = (SELECT N FROM dbo.numbers) then reference @N in the DATEPART statement.
SELECT n FROM dbo.numbers;
SELECT DATEADD(DAY,n,'20130101') AS MMDDYYYY;
That won't work.
DECLARE @N int
SET @N = (SELECT n FROM dbo.numbers)
SELECT DATEADD(DAY,@n,'20130101') AS MMDDYYYY;
will work.
You also missed an END after your CASE statement; you ended the CASE statement but didn't end your WHILE loop. It should say END END instead of just END.
I'm still looking this over so I doubt this will fix all of your problems but it will clear a couple of them up so you can focus on the others.
Also...what exactly are you trying to do with that WHILE loop?
May 15, 2014 at 7:47 am
If anybody else wants to see this code with some formatting...
CREATE TABLE CalDates (
MonthID INT IDENTITY(1, 1)
,MMDDYYYY DATETIME NOT NULL
,MonthStart DATETIME
,NextMonth DATETIME
,MonthDescr CHAR(6)
,MonthNam CHAR(3)
,YearMonth INT
,MonthNum INT
,DayNum INT
,IsBusinessDay CHAR(1)
);
DECLARE @MMDDYYYY DATETIME
,@MonthStart DATETIME
,@NextMonth DATETIME
,@MonthDescr CHAR(6)
,@MonthNam CHAR(3)
,@MonthNum INT
,@DayNum INT
,@YearMonth INT
,@IsBusinessDay CHAR(1)
,@Year INT;
SET @MonthStart = '1/1/2013';
SET @NextMonth = DATEADD(month, 1, @MonthStart);
WHILE @MonthStart < '1/1/2025'
BEGIN
SELECT n
FROM [dbo].[numbers];
SELECT DATEADD(DAY, n, '20130101') AS MMDDYYYY;
SET @DayNum = DATEPART(Day, @MonthStart);
SET @MonthNum = DATEPART(Month, @MonthStart);
SET @MonthNam = CASE @MonthNum
WHEN 1
THEN 'JAN'
WHEN 2
THEN 'FEB'
WHEN 3
THEN 'MAR'
WHEN 4
THEN 'APR'
WHEN 5
THEN 'MAY'
WHEN 6
THEN 'JUN'
WHEN 7
THEN 'JUL'
WHEN 8
THEN 'AUG'
WHEN 9
THEN 'SEP'
WHEN 10
THEN 'OCT'
WHEN 11
THEN 'NOV'
ELSE 'DEC'
END;
SET @Year = DATEPART(year, @MonthStart);
SET @YearMonth = ((@Year * 100) + @MonthNum);
SET @MonthDescr = @MonthNam + '-' + RIGHT(CONVERT(VARCHAR, @Year), 2);
SELECT IsBusinessDay = 'Y';
-- If the Day is Sunday, it is not a business day
IF DATEPART(Weekday, DATEADD(DAY, n, '20130101')) = 1
BEGIN
SET @IsBusinessDay = 'N'
END
ELSE
BEGIN
SET @IsBusinessDay = 'Y'
END;
SELECT IsBusinessDay = @IsBusinessDay;
It is formatted but there are some errors. I suspect this only a portion of the real code but hard to say for sure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 7:52 am
Posting before morning coffee = advice fail.
OK. This won't work unless you do THIS too:
SET @n = 1
SET @n = (SELECT n FROM dbo.numbers WHERE n = @n);
And this:
...
WHEN 11 THEN 'NOV'
ELSE 'DEC'
END
SET @n = @n + 1
END;
When I say "work", I mean execute without spewing red all over SSMS. I don't know if it will give you what you want, or the correct result.
May 15, 2014 at 8:02 am
SQL is delicious (5/15/2014)
OK. This won't work unless you do THIS too:
SET @n = 1
SET @n = (SELECT n FROM dbo.numbers WHERE n = @n);
BWAHAHAHAHA!!!! That sets the value to 1 and then looks up the row in dbo.numbers where n = 1 and sets the value of @n to the same value it already is. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 8:20 am
Sean Lange (5/15/2014)
BWAHAHAHAHA!!!! That sets the value to 1 and then looks up the row in dbo.numbers where n = 1 and sets the value of @n to the same value it already is. 😀
I was just trying to help. I didn't have time to go through all of it.
May 15, 2014 at 8:56 am
Let's replace the whole looping concept and do this in two steps. The first step is to create the date values as rows in CalDates. Since everything else is all based on that value we can then just update the other columns.
Since I am not sure if your numbers table starts with 0 or 1 I just created a cte here with 10,000 rows.
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
insert CalDates (MMDDYYYY)
select DATEADD(Day, N - 1, '20130101')
from cteTally
where DATEADD(Day, N, '20130101') <= '20250101'
UPDATE CalDates
set MonthStart = dateadd(mm, datediff(mm, 0, MMDDYYYY), 0)
, NextMonth = dateadd(mm, datediff(mm, 0, MMDDYYYY) + 1, 0)
, MonthDescr = UPPER(CONVERT(char(3), MMDDYYYY, 109)) + '-' + RIGHT(convert(char(4), YEAR(MMDDYYYY)), 2)
, MonthNam = UPPER(CONVERT(char(3), MMDDYYYY, 109))
, YearMonth = cast(YEAR(MMDDYYYY) * 100 as varchar) + CAST(datepart(MONTH, dateadd(mm, datediff(mm, 0, MMDDYYYY), 0)) as CHAR(2))
, DayNum = DATEPART(DAY, MMDDYYYY)
, IsBusinessDay = Case when DATEPART(WEEKDAY, MMDDYYYY) = 1 then 'N' else 'Y' end
select *
from CalDates
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 10:05 am
I'm having some trouble understanding how the CTE coding works. I should probably have prefaced my initial question by stating the I am extremely new at this. In my former career, I was a 3GL programmer analyst and gained some SQL experience via COBOL II/DB2. But that was a long time ago. I admit to struggling with the things I'm trying to accomplish now. CTEs and window functions are a bit beyond my skill level. I want to understand how that works and will explore both of those techniques on my own time later today. For now, I finally have this syntactically 'clean'. (Although I had to change my SET...WHERE statements to IFs...)
I would also like to learn how to maintain formatting when copying/pasting from SSMS to here...(OK, yay I figured that out!)
Here is the latest version, which I hope will work, Thanks again for everyone's input and help.
CREATE TABLE CalDates
(MonthID INT IDENTITY(1,1),
MMDDYYYY DATETIME NOT NULL,
MonthStart DATETIME,
NextMonth DATETIME,
MonthDescr CHAR(6),
MonthNam CHAR(3),
YearMonth INT,
MonthNum INT,
DayNum INT,
IsBusinessDay CHAR(1));
DECLARE
@MMDDYYYY DATETIME,
@MonthStart DATETIME,
@NextMonth DATETIME,
@MonthDescr CHAR(6),
@MonthNam CHAR(3),
@MonthNum INT,
@DayNum INT,
@YearMonth INT,
@IsBusinessDay CHAR(1),
@Year INT,
@N INT;
SET @N=1;
SET @MonthStart = '1/1/2013';
SET @NextMonth = DATEADD(month,1,@MonthStart);
WHILE @MonthStart < '1/1/2025'
BEGIN
SELECT DATEADD(DAY,@N,'20130101') AS MMDDYYYY;
SET @DayNum = DATEPART(Day, @MonthStart);
SET @MonthNum = DATEPART(Month, @MonthStart);
SET @MonthNam = CASE @MonthNum
WHEN 1 THEN 'JAN'
WHEN 2 THEN 'FEB'
WHEN 3 THEN 'MAR'
WHEN 4 THEN 'APR'
WHEN 5 THEN 'MAY'
WHEN 6 THEN 'JUN'
WHEN 7 THEN 'JUL'
WHEN 8 THEN 'AUG'
WHEN 9 THEN 'SEP'
WHEN 10 THEN 'OCT'
WHEN 11 THEN 'NOV'
ELSE 'DEC'
END;
SET @Year = DATEPART(year,@MonthStart);
SET @YearMonth = ((@Year*100) + @MonthNum);
SET @MonthDescr = @MonthNam + '-' + RIGHT(CONVERT(VARCHAR,@Year),2);
SET @IsBusinessDay = 'Y';
-- If the Day is Sunday, it is not a business day
IF DATEPART(Weekday,DATEADD(DAY,@N,'20130101')) = 1
SET @IsBusinessDay = 'N'
ELSE
SET @IsBusinessDay = 'Y';
-- If it is New Years, it is not a business day
IF MonthNum = 1 and DayNum =1
SET @IsBusinessDay = 'N';
-- If New Years is a Sunday, then Monday is not a business day
IF MonthNum = 1 AND DayNum = 2 AND DATEPART(Weekday,MonthStart)=2
SET @IsBusinessDay = 'N';
--If New Years is a Saturday, then Friday not a business day
IF MonthNum = 12 and DayNum = 31 AND DATEPART(Weekday,MonthStart)=6
SET @IsBusinessDay = 'N';
--If it is May, a Monday, at the end of the month, Memorial day is not a business day
IF MonthNum = 5 AND (DATEPART(Weekday,MonthStart) = 2)
AND DayNum > 22
SET @IsBusinessDay = 'N';
--If it is July 4th, it is not a business day
IF MonthNum = 7 AND DayNum = 4
SET @IsBusinessDay = 'N';
-- If Friday is 7/3 or Monday is 7/5, those are not business days
IF MonthNum = 7 AND ((DayNum = 3 AND DATEPART(Weekday,MonthStart)=6)
OR (DayNum=5 AND DATEPART(Weekday,MonthStart)=2))
SET @IsBusinessDay = 'N';
--If it is September, and Monday and the first week, Labor day is not a business day
IF MonthNum = 9 AND DATEPART (Weekday,MonthStart) = 2
AND DayNum < 7
SET @IsBusinessDay = 'N';
--If it is November, and Thursday and at the end of the month, Thanksgiving is not a business day
IF MonthNum = 11 AND (DATEPART (Weekday, MonthStart) = 5
AND DayNum > 21)
SET @IsBusinessDay = 'N';
--If it is Christmas, it is not a business day
IF (MonthNum = 12 AND DayNum = 25)
SET @IsBusinessDay = 'N';
--If Christmas is Saturday, Friday is not a business day
--If Christmas is Sunnday, Monday is not a business day
IF (MonthNum = 12 AND DayNum = 24 AND DATEPART(Weekday,MonthStart)=6) OR
(MonthNum = 12 AND DayNum = 26 AND DATEPART(Weekday,MonthStart)=2)
SET @IsBusinessDay = 'N';
INSERT INTO [dbo].[CalDates]
(MonthStart,
NextMonth,
MonthDescr,
MonthNam,
YearMonth,
MonthNum,
MMDDYYYY,
IsBusinessDay )
SELECT @MonthStart,
@NextMonth,
@MonthDescr,
@MonthNam,
@YearMonth,
@MonthNum,
@MMDDYYYY,
@IsBusinessDay;
SET @MonthStart = @NextMonth;
SET @NextMonth = DATEADD(Month, 1, @NextMonth);
SET @N = @N + 1;
END;
--END WHILE
ALTER TABLE [dbo].[CalDates] ADD CONSTRAINT [PK_CalDates] PRIMARY KEY CLUSTERED
(MonthID ASC)
WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,
SORT_IN_TEMPDB=OFF, IGNORE_DUP_KEY=OFF,
ONLINE=OFF, FILLFACTOR=100) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [CalDates_Dates] ON [dbo].[CalDates]
([MonthStart] ASC,
[NextMonth] ASC)
INCLUDE ([MonthDescr])
WITH (PAD_INDEX = OFF, FILLFACTOR=100)
GO
May 15, 2014 at 10:21 am
alicesql (5/15/2014)
I'm having some trouble understanding how the CTE coding works.
A cte is just an inline view. You should read up on them because they are very powerful and you will run into them over and over.
You could change the code I wrote to use a persistent numbers or tally table. The idea is to not loop through all the dates and deal with everything row by row. If you already have this working and your calendar table is built then you should be good to go. It would benefit you greatly in the future to start thinking set based instead of row based. It is a change in the way you view data but you need to think about what you want to do to a column instead of what you want to do to the row. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2014 at 7:51 am
I appreciate that you're relatively new at this, as we were all there at one point or another. Sean hit the nail on the head. The CTE is a good tool to have and is worth the investment of time to learn. My advice is to take the time to learn and play with them in a test environment.
A tally table will also change the way you think about performance. When you stop thinking in loops (row by row) and start to think in sets, it will change the way you look at data. You'll also start tapping some serious performance power. An excellent article to get you started is http://www.sqlservercentral.com/articles/Tally+Table/72993/.
I've heard the tally table described as "the Swiss Army Knife of SQL" and it really is.
May 16, 2014 at 9:05 am
Thanks to everyone who took the time to help me with this. Sean, your comment about looking at columns instead of rows gave me a bit of an 'aha' moment. That may well be the key to my enlightenment. And, Ed, thanks for the link to tally tables. I built one earlier this week because it seems like one of those things that would be a good foundation for further work.
May 16, 2014 at 9:17 am
alicesql (5/16/2014)
Thanks to everyone who took the time to help me with this. Sean, your comment about looking at columns instead of rows gave me a bit of an 'aha' moment. That may well be the key to my enlightenment. And, Ed, thanks for the link to tally tables. I built one earlier this week because it seems like one of those things that would be a good foundation for further work.
I am glad to pay that bit of enlightenment forward. It came from somebody else originally and has kind of become a moniker around the regulars on this site. I know that for me that phrase really was an 'aha' moment for me too. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2014 at 9:36 am
Sean Lange (5/16/2014)
alicesql (5/16/2014)
Thanks to everyone who took the time to help me with this. Sean, your comment about looking at columns instead of rows gave me a bit of an 'aha' moment. That may well be the key to my enlightenment. And, Ed, thanks for the link to tally tables. I built one earlier this week because it seems like one of those things that would be a good foundation for further work.I am glad to pay that bit of enlightenment forward. It came from somebody else originally and has kind of become a moniker around the regulars on this site. I know that for me that phrase really was an 'aha' moment for me too. 😀
Yeah, that was an eye-opener for me too. When you read Jeff's "Tally OH!" article, there's a link in there to an article he wrote on tally tables that's also well worth the read to get a foundation in how it replaces a loop. I probably should have linked to that one instead to get you started. It's really good stuff, so have fun with it.
May 16, 2014 at 9:39 am
I just read what he wrote from the link in your signature. Another bit of enlightenment for me, thanks!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply