x

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')
more ▼

asked Nov 03, 2010 at 01:35 AM in Default

avatar image

Gogolo
323 24 26 31

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')
Nov 03, 2010 at 02:20 AM Gogolo
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Nov 03, 2010 at 02:32 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

Yes sir, this is what I want.. Just one more question, Do I need any where condition for the candidates?

Nov 03, 2010 at 02:39 AM Gogolo

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

Nov 03, 2010 at 02:55 AM Gogolo

@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).

Nov 03, 2010 at 03:08 AM Matt Whitfield ♦♦

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

Nov 03, 2010 at 04:01 AM Gogolo

@Gogolo - If the answer is what you wanted, then use the tick to mark it as the accepted answer...

Nov 03, 2010 at 04:21 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Nov 03, 2010 at 01:49 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

+1, but might be worth adding the ELSE 0 bit... :)

Nov 03, 2010 at 01:58 AM Matt Whitfield ♦♦

good point .... wait there .... brb

Nov 03, 2010 at 02:09 AM Fatherjack ♦♦

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??

Nov 03, 2010 at 02:09 AM Gogolo

@gogolo - i have added a year filter.

Nov 03, 2010 at 02:11 AM Fatherjack ♦♦

@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

Nov 03, 2010 at 02:28 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x164
x5

asked: Nov 03, 2010 at 01:35 AM

Seen: 1663 times

Last Updated: Nov 03, 2010 at 01:44 AM

Copyright 2016 Redgate Software. Privacy Policy