Mike Mike - 24 days ago 11
SQL Question

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

Answer Source

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