CODEWITHSUNDEEP
sqlsql-servert-sqldatecount
Tiffany C
Tiffany C

Reputation: 63

Adding value based on date criteria

Please see code below. For every that Holiday exists that is in between UW_Date and UW_Date_Month_Start, then I'd like to create a new column called new_int that is UW_int + the number of holidays that have occurred so far that month.

SELECT *
FROM [Table6]
LEFT JOIN [] ON Table6.Name = ??

Original Table:

Name    | UW_Date    | UW_Date_Month_Start | UW_int |
-----------------------------------------------------
Kim     | 9/4/2019   | 9/1/2019            | 3      |
Billy   | 9/10/2019  | 9/1/2019            | 7      |
Steve   | 10/21/2019 | 10/1/2019           | 5      |
Nicki   | 10/10/2019 | 10/1/2019           | 1      |


Holiday  |
-------- |
9/4/2019 |
9/1/2019 |

CREATE TABLE [Table6] ([Name] nvarchar(10), [UW_Date] datetime, [UW_Date_Month_Start] datetime, [UW_int] int)

INSERT INTO [Table6] VALUES ('Kim', '9/4/2019', '9/1/2019', 3)
INSERT INTO [Table6] VALUES ('Billy', '9/10/2019', '9/1/2019', 7)
INSERT INTO [Table6] VALUES ('Steve', '10/21/2019', '10/1/2019', 5)
INSERT INTO [Table6] VALUES ('Nicki', '10/10/2019', '10/1/2019', 1)

CREATE TABLE [Holiday_Table] ([Holiday] datetime)

INSERT INTO [Holiday_Table] VALUES ('9/7/2019')
INSERT INTO [Holiday_Table] VALUES ('10/15/2019')

Final Output:

Name    | UW_Date    | UW_Date_Month_Start | UW_int | new_int
--------------------------------------------------------------
Kim     | 9/4/2019   | 9/1/2019            | 3      | 3
Billy   | 9/10/2019  | 9/1/2019            | 7      | 8
Steve   | 10/21/2019 | 10/1/2019           | 5      | 6
Nicki   | 10/10/2019 | 10/1/2019           | 1      | 1

Upvotes: 1

Views: 80

Answers (1)

GMB
GMB

Reputation: 222442

I would approach this with an inline subquery. This avoids the need for aggregation, which can be expensive on an large number of records.

Also, when no holiday records exists for the corresponding interval, the subquery returns 0, so there is no need handle null (unless when doing left join).

select
    t.*,
    t.UW_int + (
        select count(*) 
        from holiday_table h 
        where h.holiday between t.UW_Date_Month_Start and t.UW_Date
    ) new_int
from table6 t

Demo on DB Fiddle:

Name  | UW_Date             | UW_Date_Month_Start | UW_int | new_int
:---- | :------------------ | :------------------ | -----: | ------:
Kim   | 04/09/2019 00:00:00 | 01/09/2019 00:00:00 |      3 |       3
Billy | 10/09/2019 00:00:00 | 01/09/2019 00:00:00 |      7 |       8
Steve | 21/10/2019 00:00:00 | 01/10/2019 00:00:00 |      5 |       6
Nicki | 10/10/2019 00:00:00 | 01/10/2019 00:00:00 |      1 |       1

Upvotes: 3

Related Questions