question

Gogolo avatar image
Gogolo asked

Update table from between dates

Hi All, I need help, I have table as it shows below. I need to update each month in table for candidates for month thay where active, if candidate was active during RegDate = Registration Date and RemDate = Removable Date I should put 1 and for other months put 0. Also this query should contain only one year(eg 2010). Thanking you all in advance for your effort. ---------- CREATE TABLE #Tbl( [Candidate] [nvarchar](10) NULL, [RegDate] [datetime] NULL, [RemDate] [datetime] NULL, [M1] [int] NULL, [M2] [int] NULL, [M3] [int] NULL, [M4] [int] NULL, [M5] [int] NULL, [M6] [int] NULL, [M7] [int] NULL, [M8] [int] NULL, [M9] [int] NULL, [M10] [int] NULL, [M11] [int] NULL, [M12] [int] NULL ) ON [PRIMARY] INSERT INTO #Tbl ( Candidate , RegDate , RemDate ) (SELECT 1,'20100103' , '20101105' UNION ALL SELECT 2,'20100203' , '20100803' UNION ALL SELECT 3,'20100103' , '20100403' UNION ALL SELECT 4,'20100403' , '20101003')
updatecumulative-update
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gogolo avatar image Gogolo commented ·
Yes, but nothing updated... My question was wrong for sure.. I didnt asked properly.. I will put some other data in table.At the candidate 1 registration date is 2006 and removable date 11.2010, he needs to have 1.2.3.4.5.6.7.8.9.10.11 set to 1 becose he is there between those dates.Maybe my clarification again is not so good, I apologise for that. THANK YOU AGAIN IN ADVANCES CREATE TABLE #Tbl( [Candidate] [nvarchar](10) NULL, [RegDate] [datetime] NULL, [RemDate] [datetime] NULL, [M1] [int] NULL, [M2] [int] NULL, [M3] [int] NULL, [M4] [int] NULL, [M5] [int] NULL, [M6] [int] NULL, [M7] [int] NULL, [M8] [int] NULL, [M9] [int] NULL, [M10] [int] NULL, [M11] [int] NULL, [M12] [int] NULL ) ON [PRIMARY] INSERT INTO #Tbl ( Candidate , RegDate , RemDate ) (SELECT 1,'20060103' , '20101105' UNION ALL SELECT 2,'20100203' , '20100803' UNION ALL SELECT 3,'20100103' , '20100403' UNION ALL SELECT 4,'20100403' , '20101003')
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
I think this is what you want. It's clunky, because your table is not well defined. If it had a primary key, then I would have joined from the table onto itself, and done the filtering in a sub query. CREATE TABLE #Tbl( [Candidate] nvarchar NULL, [RegDate] [datetime] NULL, [RemDate] [datetime] NULL, [M1] [int] NULL, [M2] [int] NULL, [M3] [int] NULL, [M4] [int] NULL, [M5] [int] NULL, [M6] [int] NULL, [M7] [int] NULL, [M8] [int] NULL, [M9] [int] NULL, [M10] [int] NULL, [M11] [int] NULL, [M12] [int] NULL ) ON [PRIMARY] INSERT INTO #Tbl ( Candidate , RegDate , RemDate ) (SELECT 1,'20060103' , '20101105' UNION ALL SELECT 2,'20100203' , '20100803' UNION ALL SELECT 3,'20100103' , '20100403' UNION ALL SELECT 4,'20100403' , '20101003') DECLARE @updateYear [int] SET @updateYear = 2010 DECLARE @baseDate [datetime] SET @baseDate = DATEADD(yy, @updateYear - 1970, '19700101') UPDATE #Tbl SET [M1] = CASE WHEN @baseDate BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M2] = CASE WHEN DATEADD(m, 1, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M3] = CASE WHEN DATEADD(m, 2, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M4] = CASE WHEN DATEADD(m, 3, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M5] = CASE WHEN DATEADD(m, 4, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M6] = CASE WHEN DATEADD(m, 5, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M7] = CASE WHEN DATEADD(m, 6, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M8] = CASE WHEN DATEADD(m, 7, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M9] = CASE WHEN DATEADD(m, 8, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M10] = CASE WHEN DATEADD(m, 9, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M11] = CASE WHEN DATEADD(m, 10, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END, [M12] = CASE WHEN DATEADD(m, 11, @baseDate) BETWEEN DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AND DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') THEN 1 ELSE 0 END SELECT * FROM #Tbl Here is the less clunky version. Note the NON NULLABLE PRIMARY KEY definition of Candidate. CREATE TABLE #Tbl( [Candidate] nvarchar PRIMARY KEY NOT NULL, [RegDate] [datetime] NULL, [RemDate] [datetime] NULL, [M1] [int] NULL, [M2] [int] NULL, [M3] [int] NULL, [M4] [int] NULL, [M5] [int] NULL, [M6] [int] NULL, [M7] [int] NULL, [M8] [int] NULL, [M9] [int] NULL, [M10] [int] NULL, [M11] [int] NULL, [M12] [int] NULL ) ON [PRIMARY] INSERT INTO #Tbl ( Candidate , RegDate , RemDate ) (SELECT 1,'20060103' , '20101105' UNION ALL SELECT 2,'20100203' , '20100803' UNION ALL SELECT 3,'20100103' , '20100403' UNION ALL SELECT 4,'20100403' , '20101003') DECLARE @updateYear [int] SET @updateYear = 2010 DECLARE @baseDate [datetime] SET @baseDate = DATEADD(yy, @updateYear - 1970, '19700101') UPDATE #Tbl SET [M1] = CASE WHEN @baseDate BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M2] = CASE WHEN DATEADD(m, 1, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M3] = CASE WHEN DATEADD(m, 2, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M4] = CASE WHEN DATEADD(m, 3, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M5] = CASE WHEN DATEADD(m, 4, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M6] = CASE WHEN DATEADD(m, 5, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M7] = CASE WHEN DATEADD(m, 6, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M8] = CASE WHEN DATEADD(m, 7, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M9] = CASE WHEN DATEADD(m, 8, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M10] = CASE WHEN DATEADD(m, 9, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M11] = CASE WHEN DATEADD(m, 10, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END, [M12] = CASE WHEN DATEADD(m, 11, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END FROM #Tbl t INNER JOIN (SELECT [Candidate], DATEADD(m, DATEDIFF(m, '19700101', [RegDate]), '19700101') AS FilteredRegDate, DATEADD(m, DATEDIFF(m, '19700101', [RemDate]), '19700101') AS FilteredRemDate FROM #Tbl) tblFiltered ON [t].[Candidate] = [tblFiltered].[Candidate] SELECT * FROM #Tbl
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gogolo avatar image Gogolo commented ·
Yes sir, this is what I want.. Just one more question, Do I need any where condition for the candidates?
0 Likes 0 ·
Gogolo avatar image Gogolo commented ·
Looks OK, but I need small explanation why: M12 is equal with CASE WHEN DATEADD(m, 11, @baseDate)?? [M12] = CASE WHEN DATEADD(m, 11, @baseDate) BETWEEN [FilteredRegDate] AND [FilteredRemDate] THEN 1 ELSE 0 END
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Gogolo - You just need to set the @updateYear variable to what you want. The reason why month 12 is +11 is because 1 + 11 = 12. @baseDate is in January. If you add 1 month, you get February (month 2), if you add 2 months, you get March (month 3) ... if you add 11 months, you get December (month 12).
0 Likes 0 ·
Gogolo avatar image Gogolo commented ·
Thank you very much for your explanation and thank you all for solution. Fr sure that I will have more question regarding this query, because this is only a part from whole query. thank you all again
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Gogolo - If the answer is what you wanted, then use the tick to mark it as the accepted answer...
0 Likes 0 ·
Show more comments
Fatherjack avatar image
Fatherjack answered
I think is what you need: UPDATE [#Tbl] SET [M1] = CASE WHEN 1 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M2] = CASE WHEN 2 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M3] = CASE WHEN 3 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M4] = CASE WHEN 4 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M5] = CASE WHEN 5 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M6] = CASE WHEN 6 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M7] = CASE WHEN 7 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M8] = CASE WHEN 8 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M9] = CASE WHEN 9 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M10] = CASE WHEN 10 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M11] = CASE WHEN 11 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END , [M12] = CASE WHEN 12 BETWEEN MONTH([t].regdate) AND MONTH([t].remdate) THEN 1 ELSE 0 END FROM [#Tbl] AS t WHERE YEAR(regdate)= 2010
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1, but might be worth adding the `ELSE 0` bit... :)
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
good point .... wait there .... brb
0 Likes 0 ·
Gogolo avatar image Gogolo commented ·
Yes sir, it shoulb like that, but sometime I have registration date on 2004 and removed date on 2012, and I need updates only for 2010??
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@gogolo - i have added a year filter.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@gogolo - sorry, I had > instead of =. try the code now I have edited it. Also please use the comment option rather than adding answers when you need to add information. Thanks
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.