x
login about faq Site discussion (meta-askssc)

Update table from between dates

0

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 '10 at 01:35 AM in Default

Gogolo gravatar image

Gogolo
313 ● 12 ● 21 ● 25

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 '10 at 02:20 AM Gogolo
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
2 answers: sort newest ▼
0

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 '10 at 02:32 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

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

Nov 03 '10 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 '10 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 '10 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 '10 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 '10 at 04:21 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
0

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 '10 at 01:49 AM

Fatherjack gravatar image

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

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

Nov 03 '10 at 01:58 AM Matt Whitfield ♦♦

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

Nov 03 '10 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 '10 at 02:09 AM Gogolo

@gogolo - i have added a year filter.

Nov 03 '10 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 '10 at 02:28 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
▼ Viewable by all users
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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x113
x5

asked: Nov 03 '10 at 01:35 AM

Seen: 728 times

Last Updated: Nov 03 '10 at 01:44 AM

Related Questions

Cumulative Updates

UPDATE (with a join) performance... can I improve this?

SQL statement for replace/update a stringvalue in an Oracle table

Updating a Table - What is best way to add/update, keeping performance in mind?

Run an Automatically Generated UPDATE Statement

UPDATING OF A TABLE AFTER INSERTING A ROW INTO ANOTHER TABLE

Update Query

Create a Trigger to update total count on one table when quantity is entered in another table

How do I INSERT or UPDATE an array of values without iteration?

Update a table from another table

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.

AnswerHubEnterprise
Social Q&A