March 12, 2019 at 3:06 am
Hi there,
so I have this table I've added additional columns to. For existing rows I want to update the columns with new values. PK_Time is a Primary Key int and represents the date as YYYYMMDD and SSMS constantly highlights me the new columns as 'invalid column names' but I'm willing to ignore that. However somehow he does not like that I check all the new columns in the where clause to only update those which are empty.
Here's the table definitionCREATE TABLE [dbo].[D_Time](
[PK_Time] [int] NOT NULL,
[Time_Year_ID] [int] NOT NULL,
[Time_Year_Name] [nchar](4) NOT NULL,
[Time_Quarter_ID] [nvarchar](16) NOT NULL,
[Time_Quarter_Name] [nvarchar](16) NOT NULL,
[Time_Month_ID] [nvarchar](16) NOT NULL,
[Time_Month_Code] [nvarchar](16) NOT NULL,
[Time_Month_Name] [nvarchar](16) NOT NULL,
[Time_Date] [date] NOT NULL,
[Time_Day_of_Month] [int] NULL,
[Time_Day_of_Year] [int] NULL,
[Time_Day_of_Week] [int] NULL,
[Time_Day_of_Week_Name] [varchar](10) NULL,
[Time_Week_ID] [int] NULL,
[Time_Week_of_Year] [varchar](12) NULL,
[IsWeekend] [bit] NULL,
[IsLeapYear] [bit] NULL,
CONSTRAINT [PK_D_Time] PRIMARY KEY CLUSTERED
(
[PK_Time] ASC,
[Time_Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and here what I've been working on sofar to make it halfway work.
SET LANGUAGE German
UPDATE dbo.D_Time
SET
[Time_Day_of_Month] = ( SELECT CONVERT(char(2),DATENAME(dd,CONVERT(datetime, CAST(PK_Time as char(8))))) from dbo.D_Time),
[Time_Day_of_Year] = ( SELECT DATEPART(dy,CONVERT(datetime, CAST( PK_Time as char(8)))) from dbo.D_Time),
[Time_Day_of_Week] = ( SELECT DATEPART(dw,CONVERT(datetime, CAST(PK_Time as char(8)))) from dbo.D_Time),
[Time_Day_of_Week_Name] = ( SELECT CONVERT(char(16),DATENAME(dw,CONVERT(datetime, CAST(PK_Time as char(8))))) from dbo.D_Time),
[Time_Week_ID] = ( SELECT CONVERT(char(2),DATENAME(week,CONVERT(datetime, CAST(PK_Time as char(8))))) from dbo.D_Time),
[Time_Week_of_Year] = ( SELECT 'KW ' + CONVERT(char(2),DATEPART(week,CONVERT(datetime, CAST(PK_Time as char(8))))) + '/ ' + CONVERT(char(4),DATEPART(year,CONVERT(datetime, CAST(PK_Time as char(8))))) from dbo.D_Time),
[IsWeekend] = (SELECT
CASE
WHEN (DATEPART(dw, PK_Time) = 1 OR DATEPART(dw, PK_Time) = 7)
THEN '1'
ELSE '0'
END from dbo.D_Time),
[IsLeapYear] = (SELECT
CASE
WHEN DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 4 = 0
AND DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 100 != 0
OR DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 400 = 0
THEN '1'
ELSE '0'
END
from dbo.D_Time)
WHERE EXISTS (SELECT 1
from dbo.D_Time where
[Time_Day_of_Month] IS NULL AND
[Time_Day_of_Year] IS NULL AND
[Time_Day_of_Week] IS NULL AND
[Time_Day_of_Week_Name] IS NULL AND
[Time_Week_ID] IS NULL AND
[Time_Week_of_Year] IS NULL AND
[IsWeekend] IS NULL AND
[IsLeapYear] IS NULL)
GO
Just FYI: The SET LANGUAGE is for the day of the week needed exclusively.
March 12, 2019 at 7:52 am
I'd ditch the subqueries, otherwise you'd have to "correlate" each one to make this work. For more info google "correlated subqueries".
I edited the query for what I would try but didn't test it.
SET LANGUAGE German
UPDATE dbo.D_Time
SET
[Time_Day_of_Month] = CONVERT(char(2),DATENAME(dd,CONVERT(datetime, CAST(PK_Time as char(8))))),
[Time_Day_of_Year] = DATEPART(dy,CONVERT(datetime, CAST( PK_Time as char(8)))),
[Time_Day_of_Week] = DATEPART(dw,CONVERT(datetime, CAST(PK_Time as char(8)))),
[Time_Day_of_Week_Name] = CONVERT(char(16),DATENAME(dw,CONVERT(datetime, CAST(PK_Time as char(8))))),
[Time_Week_ID] = CONVERT(char(2),DATENAME(week,CONVERT(datetime, CAST(PK_Time as char(8))))),
[Time_Week_of_Year] = 'KW ' + CONVERT(char(2),DATEPART(week,CONVERT(datetime, CAST(PK_Time as char(8)))))
+ '/ ' + CONVERT(char(4),DATEPART(year,CONVERT(datetime, CAST(PK_Time as char(8))))),
[IsWeekend] =
CASE
WHEN (DATEPART(dw, PK_Time) = 1 OR DATEPART(dw, PK_Time) = 7)
THEN '1'
ELSE '0'
END,
[IsLeapYear] =
CASE
WHEN DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 4 = 0
AND DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 100 != 0
OR DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 400 = 0
THEN '1'
ELSE '0'
END
WHERE
[Time_Day_of_Month] IS NULL AND
[Time_Day_of_Year] IS NULL AND
[Time_Day_of_Week] IS NULL AND
[Time_Day_of_Week_Name] IS NULL AND
[Time_Week_ID] IS NULL AND
[Time_Week_of_Year] IS NULL AND
[IsWeekend] IS NULL AND
[IsLeapYear] IS NULL
March 12, 2019 at 7:58 am
Hi Patrick,
unfortunately it's not that easy it seems :crying:
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
is what I get from your version of the script, before he was complaining about subqueries returning more than one value (Msg 512, Level 16, State 1)
March 12, 2019 at 9:33 am
DinoRS - Tuesday, March 12, 2019 7:58 AMHi Patrick,unfortunately it's not that easy it seems :crying:
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.is what I get from your version of the script, before he was complaining about subqueries returning more than one value (Msg 512, Level 16, State 1)
Getting closer anyways! in this clause:
WHEN (DATEPART(dw, PK_Time) = 1 OR DATEPART(dw, PK_Time) = 7)
try casting pk_time into a valid datetime, when I tried to use an int without casting with datepart, it returned:
"Arithmetic overflow error converting expression to data type datetime."
(At least converting it to varchar(8) or something might help get past the overflow error).
March 12, 2019 at 10:10 am
There are three options I would suggest. The first two might not be possible if you have constraints on modifying or creating tables.
1) Add a computed column that will do the conversion from INT to DATETIME to make it simpler to do the calculations.
2) Create a calendar table that would have all of these calculations already.
3) Use a CROSS APPLY to do the conversion from INT to DATETIME, which will make your calculations simpler.
SET LANGUAGE German
UPDATE dbo.D_Time
SET
[Time_Day_of_Month] = CONVERT(char(2),DATENAME(dd,dt.PK_Date)),
[Time_Day_of_Year] = DATEPART(dy,dt.PK_Date),
[Time_Day_of_Week] = DATEPART(dw,dt.PK_Date),
[Time_Day_of_Week_Name] = CONVERT(char(16),DATENAME(dw,dt.PK_Date)),
[Time_Week_ID] = CONVERT(char(2),DATENAME(week,dt.PK_Date)),
[Time_Week_of_Year] = 'KW ' + CONVERT(char(2),DATEPART(week,dt.PK_Date))
+ '/ ' + CONVERT(char(4),DATEPART(year,dt.PK_Date)),
[IsWeekend] =
CASE
WHEN (DATEPART(dw, dt.PK_Date) = 1 OR DATEPART(dw, dt.PK_Date) = 7)
THEN '1'
ELSE '0'
END,
[IsLeapYear] =
CASE
WHEN DATEPART(yy,dt.PK_Date) % 4 = 0
AND DATEPART(yy,dt.PK_Date) % 100 != 0
OR DATEPART(yy,dt.PK_Date) % 400 = 0
THEN '1'
ELSE '0'
END
FROM dbo.D_Time
CROSS APPLY (VALUES (CONVERT(datetime, CAST(PK_Time as char(8)))) ) dt(PK_Date)
WHERE
[Time_Day_of_Month] IS NULL AND
[Time_Day_of_Year] IS NULL AND
[Time_Day_of_Week] IS NULL AND
[Time_Day_of_Week_Name] IS NULL AND
[Time_Week_ID] IS NULL AND
[Time_Week_of_Year] IS NULL AND
[IsWeekend] IS NULL AND
[IsLeapYear] IS NULL
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 12, 2019 at 10:11 am
Spot on Patrick! I knew I missed something in there.
working script for reference
SET LANGUAGE German
UPDATE dbo.D_Time
SET
[Time_Day_of_Month] = CONVERT(char(2),DATENAME(dd,CONVERT(datetime, CAST(PK_Time as char(8))))),
[Time_Day_of_Year] = DATEPART(dy,CONVERT(datetime, CAST( PK_Time as char(8)))),
[Time_Day_of_Week] = DATEPART(dw,CONVERT(datetime, CAST(PK_Time as char(8)))),
[Time_Day_of_Week_Name] = CONVERT(char(16),DATENAME(dw,CONVERT(datetime, CAST(PK_Time as char(8))))),
[Time_Week_ID] = CONVERT(char(2),DATENAME(week,CONVERT(datetime, CAST(PK_Time as char(8))))),
[Time_Week_of_Year] = 'KW ' + CONVERT(char(2),DATEPART(week,CONVERT(datetime, CAST(PK_Time as char(8)))))
+ '/ ' + CONVERT(char(4),DATEPART(year,CONVERT(datetime, CAST(PK_Time as char(8))))),
[IsWeekend] =
CASE
WHEN (DATEPART(dw,CAST( PK_Time as char(8))) = 1) OR (DATEPART(dw, CAST(PK_Time as char(8))) = 7)
THEN '1'
ELSE '0'
END,
[IsLeapYear] =
CASE
WHEN DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 4 = 0
AND DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 100 != 0
OR DATEPART(yy,CONVERT(datetime, CAST(PK_Time as char(8)))) % 400 = 0
THEN '1'
ELSE '0'
END
WHERE
[Time_Day_of_Month] IS NULL AND
[Time_Day_of_Year] IS NULL AND
[Time_Day_of_Week] IS NULL AND
[Time_Day_of_Week_Name] IS NULL AND
[Time_Week_ID] IS NULL AND
[Time_Week_of_Year] IS NULL AND
[IsWeekend] IS NULL AND
[IsLeapYear] IS NULL
Why did I do this that way at all? My boss assumed I could define default values for the new columns, could've created a bunch of functions but as I only intend to do this once not bothering with the function route.
Interesting idea with the computed column Drew, this essentially is a calendar table I was not allowed to drop and recreate due to FK Constraints but still whoever initially made this, had included not enough metrics (my opinion) but the worst part actually was the dates didn't go back far enough which wasn't discovered in years until I basically came around and said 'Hi, what's up?'
CROSS APPLY never came across my mind even tho you showed here's an perfect example for that.
Ofcourse I could've gone with the Time_Date Column which provides a proper date but in the end I thought 'nah, too easy that way!'. I made myself an excercise from this.
Thanks for all the great feedback!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply