June 22, 2013 at 12:23 pm
I have a table to update. The value to update is an FK to a PK ID on a data table.
The data table has date ranges and the table being updated has Date of Birth fields (Month, Day, Year). My Update statement could loop through all the records RBAR (row by agonizing row) But I was hoping to use a more set based solution. I've tried using the case statement and table joins in the from clause on the update statement but something about this problem is eluding me on how to approach it. Here are the the table schemas and my attempt at an update statement
Table 1 Person:
TABLE [dbo].[TFI_PERSON](
[PERSON_ID] [int] IDENTITY(3500,1) NOT NULL,
[HOROSCOPE_SIGN_ID] [int] NULL,
[DOB_DAY] [int] NOT NULL,
[DOB_MONTH] [int] NOT NULL,
[DOB_YEAR] [int] NOT NULL,
Table 2 Horoscope
TABLE [dbo].[TFI_HOROSCOPE_SIGN](
[HOROSCOPE_SIGN_ID] [int] IDENTITY(1,1) NOT NULL,
[HOROSCOPE_SIGN] [nvarchar](100) NOT NULL,
[HOROSCOPE_BEGIN_DATE] [datetime] NOT NULL,
[HOROSCOPE_END_DATE] [datetime] NOT NULL,
Attempt(s) 1 & 2
UPDATE P
SET P.HOROSCOPE_SIGN_ID = HS.[HOROSCOPE_SIGN_ID]
FROM dbo.TFI_PERSON AS P JOIN [dbo].[TFI_HOROSCOPE_SIGN] AS HS
ON P.[HOROSCOPE_SIGN_ID] = HS.[HOROSCOPE_SIGN_ID]
WHERE
CAST(DOB_YEAR AS NVARCHAR)+ '-' + CAST(DOB_MONTH AS NVARCHAR) + '-' + CAST(DOB_DAY AS NVARCHAR) BETWEEN HS.[HOROSCOPE_BEGIN_DATE] AND HS.[HOROSCOPE_END_DATE]
UPDATE dbo.TFI_PERSON
SET HOROSCOPE_SIGN_ID = (SELECT HOROSCOPE_SIGN_ID
FROM dbo.TFI_HOROSCOPE_SIGN
WHERE CAST(CAST(DOB_YEAR AS NVARCHAR)+ '/' + CAST(DOB_MONTH AS NVARCHAR) + '/' + CAST(DOB_DAY AS NVARCHAR) AS DATETIME) BETWEEN [HOROSCOPE_BEGIN_DATE] AND [HOROSCOPE_END_DATE] )
Thanks for the assist.
June 22, 2013 at 12:29 pm
does TFI_PERSON have a date of birth column stored as datetime format?
why this
[DOB_DAY] [int] NOT NULL,
[DOB_MONTH] [int] NOT NULL,
[DOB_YEAR] [int] NOT NULL,
rather than DOB (datetime) ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 22, 2013 at 12:30 pm
No sir.
That is why you see the string concatenation of the month day and year.
It would be easier but alas it wasn't built that way.
June 22, 2013 at 1:47 pm
Trying to construct the date by concatenating three casts from int to nvarchar() is likely to be somewhat error-prone, and even if you get it right (eliminating spaces, ensuring month and day parts are two characters) it will perform very badly. It's easier to get right and might perform slightly better (not much - not having the the DOB as a datetime value will be a big performance drag) if you use DATEADD to construct the date -
DATEADD(day, DOB_DAY, DATEADD(month, DOB_MONTH, DATEADD(year, DOB_YEAR, 0)))
There's no need for any casting and it avoids the implicit conversion from nvarchar to datetime if you do it like that.
Tom
June 23, 2013 at 9:32 pm
Add a calculated column to the table to create a persisted datetime column. It'll make life a lot easier.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply