CODEWITHSUNDEEP
sqlsql-serverdatabase
TheLegend28
TheLegend28

Reputation: 43

SQL Server : insert all days in a month into a view table based on some records

I need to insert all the dates in a month from Table A into a view based on some records.

Scenario as below:

Table A:

   Date     | Scan_In   | Scan_Out
------------+-----------+------------
  3/1/2018  |    Yes    |    Yes    
  4/1/2018  |    No     |    Yes    
  7/1/2018  |    Yes    |    No

View (expected result): this view needs to have all the dates in January from 1st to 31st. Those empty column should be NULL.

   Date     | Scan_In   | Scan_Out
------------+-----------+-----------
  1/1/2018  |    Null   |    Null   
  2/1/2018  |    Null   |    Null   
  3/1/2018  |    Yes    |    Yes    
  4/1/2018  |    No     |    Yes    
  5/1/2018  |    Null   |    Null   
  6/1/2018  |    Null   |    Null   
  7/1/2018  |    Yes    |    No     
  8/1/2018  |    Null   |    Null   
  9/1/2018  |    Null   |    Null   
 10/1/2018  |    Null   |    Null   
 11/1/2018  |    Null   |    Null   
 12/1/2018  |    Null   |    Null

continue until the end date of the month. For 3xample, 31/1/2018 is the end day for January.

Upvotes: 1

Views: 1953

Answers (2)

Sreenu131
Sreenu131

Reputation: 2516

Sample Data

DECLARE @TempData AS TABLE ([Date] DATE, Scan_In VARCHAR(5),Scan_Out  VARCHAR(5))
    INSERT INTO @TempData
    SELECT '2018-01-03' ,'Yes','Yes' UNION ALL   
    SELECT '2018-01-04' ,'No' ,'Yes' UNION ALL   
    SELECT '2018-01-07' ,'Yes','No'  
    SELECT * FROM @TempData

Sql SCript

DECLARE @GiveDate DATE,
        @EndDate DATE,
        @MOnth INT,
        @YEAR INT

SELECT @MOnth=MONTH([Date]),@YEAR=YEAR([Date]) FROM @TempData
SET @GiveDate=CAST(CAST(@year AS varchar(4)) + '-' + CAST(@month AS varchar(2)) + '-' + '1' AS DATE);

SELECT @EndDate=EOMONTH(@GiveDate)
;WITH CTE
AS
(

SELECT DISTINCT Number,DATEADD(DAY,Number,@GiveDate) AS ReqDays
FROM master.dbo.spt_values WHERE Number BETWEEN 0 AND 100
AND DATEADD(DAY,Number,@GiveDate) BETWEEN @GiveDate  AND @EndDate
)
SELECT c.ReqDays,
       t.Scan_In,
       t.Scan_Out

FROM CTE c LEFT JOIN @TempData t
ON c.ReqDays=t.[Date]

Solution Demo : http://rextester.com/TNGU27548

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

A recursive CTE works well for this:

with dates as (
      select cast('2018-01-01' as date) as dte
      union all
      select dateadd(day, 1, dte)
      from dates
      where dte < '2018-01-31'
     )
select d.dte, a.scan_in, a.scan_out
from dates d left join
     a
     on d.dte = a.date
order by d.dte;

Upvotes: 0

Related Questions