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

Gogolo gravatar image

Gogolo
323 22 26 27

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 newest

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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

+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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x129
x5

asked: Nov 03, 2010 at 01:35 AM

Seen: 1345 times

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