CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 3

SQL Server : business days to all calendar days

I have the following tables. As Canada day is July 1st and my data source is in business days only. Notice that July 1st is missing from TABLE_A (name: conm) which is my source table.

CREATE TABLE [dbo].[comn]
(
    CONM varchar(48),
    valuedate datetime,
    closeprice decimal(5,2)
)
GO

INSERT INTO comn VALUES ('SAPUTO INC', '2016-06-27', 37.66);
INSERT INTO comn VALUES ('SAPUTO INC', '2016-06-28', 38.34);
INSERT INTO comn VALUES ('SAPUTO INC', '2016-06-29', 38.48);
INSERT INTO comn VALUES ('SAPUTO INC', '2016-06-30', 38.37);
INSERT INTO comn VALUES ('SAPUTO INC', '2016-07-04', 38.12);
INSERT INTO comn VALUES ('SAPUTO INC', '2016-07-05', 38.59);
INSERT INTO comn VALUES ('SAPUTO INC', '2016-07-06', 38.75);
GO

I also have TABLE_B (businessdaysCAN) with all Canadian holidays.

CREATE TABLE [dbo].[businessdaysCAN]
(
    valuedate datetime,
    isholidayCA decimal(5,2)
)
GO

INSERT INTO businessdaysCAN VALUES ('2016-02-15',1);
INSERT INTO businessdaysCAN VALUES ('2016-03-25', 1);
INSERT INTO businessdaysCAN VALUES ('2016-05-23', 1);
INSERT INTO businessdaysCAN VALUES ('2016-07-01', 1);
INSERT INTO businessdaysCAN VALUES ('2016-08-01', 1);
INSERT INTO businessdaysCAN VALUES ('2016-09-05', 1);
INSERT INTO businessdaysCAN VALUES ('2016-10-10', 1);
GO

I would like to have an output table such as when there is a Canadian holiday, I have the holiday date in my final table with the price of the day before.

CONM        valuedate                closeprice
------------------------------------------------
SAPUTO INC  2016-06-27 00:00:00.000     37.66
SAPUTO INC  2016-06-28 00:00:00.000     38.34
SAPUTO INC  2016-06-29 00:00:00.000     38.48
SAPUTO INC  2016-06-30 00:00:00.000     38.37
SAPUTO INC  2016-07-04 00:00:00.000     38.12
SAPUTO INC  2016-07-05 00:00:00.000     38.59
SAPUTO INC  2016-07-06 00:00:00.000     38.75

Upvotes: 0

Views: 80

Answers (2)

Maurício Pontalti Neri
Maurício Pontalti Neri

Reputation: 1080

You can try it

DECLARE @comn table (conm varchar(10) , valueDate date,  closePrice decimal(10,2));
DECLARE @businessdaysCAN table(calDate  date, isHolidayCA bit);

INSERT @comn (conm,valueDate,closePrice) VALUES
 ('SAPUTO INC','2016-06-27',37.66)
,('SAPUTO INC','2016-06-28',38.34)
,('SAPUTO INC','2016-06-29',38.48)
,('SAPUTO INC','2016-06-30',38.37)
,('SAPUTO INC','2016-07-04',38.12)
,('SAPUTO INC','2016-07-05',38.59)
,('SAPUTO INC','2016-07-06',38.75);

INSERT @businessdaysCAN (calDate, isHolidayCA) VALUES
 ('2016-05-23',1)
,('2016-07-01',1)
,('2016-08-01',1);

WITH 
cteMinMaxDates as
(
select 
 conm 
,Min(valueDate) as startdate
,Max(valueDate) as enddate
from @comn
Group by conm
),
cteAllDates 
     AS (SELECT  conm 
                ,startdate 
                ,enddate 
                ,valueDate = ( SELECT valueDate FROM @comn a where  a.valueDate = t.startdate and a.conm = t.conm)
         FROM   cteMinMaxDates t 
         UNION ALL 
         SELECT conm 
                ,Dateadd(day, 1, startdate) startdate 
                ,enddate 
                ,valueDate = CASE WHEN ( SELECT valueDate FROM @comn a where  a.valueDate = startdate and a.conm = conm) IS NULL THEN valueDate ELSE ( SELECT valueDate FROM @comn a where  a.valueDate = startdate and a.conm = conm) END 
         FROM   cteAllDates 
         WHERE  startdate < enddate) 
SELECT 
     D.conm
    ,D.startdate valuedate
    ,IsNull(A.closePrice , B.closePrice) closePrice
FROM cteAllDates D 
       LEFT JOIN @comn A 
              ON D.conm = A.conm 
                 AND D.startdate = A.valuedate 
       LEFT JOIN @comn B 
              ON D.valuedate = B.valuedate 
WHERE  A.valuedate IS NOT NULL 
       OR EXISTS (SELECT 1 FROM @businessdaysCAN c where d.startdate = c.calDate)

Result 

conm       valuedate  closePrice
---------- ---------- ---------------------------------------
SAPUTO INC 2016-06-27 37.66
SAPUTO INC 2016-06-28 38.34
SAPUTO INC 2016-06-29 38.48
SAPUTO INC 2016-06-30 38.37
SAPUTO INC 2016-07-01 38.37
SAPUTO INC 2016-07-04 38.12
SAPUTO INC 2016-07-05 38.59
SAPUTO INC 2016-07-06 38.75

If you need all dates in the range remove the

WHERE  A.valuedate IS NOT NULL 
       OR EXISTS (SELECT 1 FROM @table_b c where d.startdate = c.calDate)

Result 

conm       valuedate  closePrice
---------- ---------- ---------------------------------------
SAPUTO INC 2016-06-27 37.66
SAPUTO INC 2016-06-28 38.34
SAPUTO INC 2016-06-29 38.48
SAPUTO INC 2016-06-30 38.37
SAPUTO INC 2016-07-01 38.37
SAPUTO INC 2016-07-02 38.37
SAPUTO INC 2016-07-03 38.37
SAPUTO INC 2016-07-04 38.12
SAPUTO INC 2016-07-05 38.59
SAPUTO INC 2016-07-06 38.75

Upvotes: 0

SQLBadPanda
SQLBadPanda

Reputation: 635

This will give you a table of all dates since 01/01/2000, if you need a longer or shorter range just change the date in the code (if longer you may need to add an additional "PASS" in the CTE but 65k is a lot of days!); you can then LEFT JOIN or RIGHT JOIN this to your table and use the values from this where yours is null...

WITH
    _PASS0 AS (SELECT 1 AS Num UNION ALL SELECT 1),         --2 ROWS
    _PASS1 AS (SELECT 1 AS Num FROM _PASS0 AS A, _PASS0 AS B),  --4 ROWS
    _PASS2 AS (SELECT 1 AS Num FROM _PASS1 AS A, _PASS1 AS B),  --16 ROWS
    _PASS3 AS (SELECT 1 AS Num FROM _PASS2 AS A, _PASS2 AS B),  --256 ROWS
    _PASS4 AS (SELECT 1 AS Num FROM _PASS3 AS A, _PASS3 AS B),  --65,536 ROWS
    _TALLY AS (SELECT 0 AS Number UNION ALL SELECT ROW_NUMBER() OVER(ORDER BY Num) AS Number FROM _PASS4)

SELECT dateadd(DAY,Number,'20000101') AS CalendarDate
FROM _Tally
WHERE  Number <= datediff(DAY,'20000101',getdate());

Upvotes: 0

Related Questions