CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2012
vasu v
vasu v

Reputation: 21

Fill missing dates with previous date in SQL Server

How to fill missing dates related data with previous date related data in sql server?

table : emp

CREATE TABLE [dbo].[emp](
    [empid] [int] NULL,
    [doj] [date] NULL,
    [deptid] [int] NULL,
    [ename] [varchar](50) NULL,
    [sal] [int] NULL
) 
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (1, CAST(N'2017-01-02' AS Date), 10, N'ravi', 100)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (1, CAST(N'2017-01-05' AS Date), 10, N'ravi', 200)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (2, CAST(N'2017-01-08' AS Date), 20, N'banu', 300)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (2, CAST(N'2017-01-07' AS Date), 20, N'banu', 250)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (3, CAST(N'2017-01-10' AS Date), 30, N'jai', 400)
GO

based on above data I want data like below 

empid   |doj        |deptid   |ename    |sal
    1   |2017-01-02 |10       |ravi     |100
    1   |2017-01-03 |10       |ravi     |100-----missing dates required
    1   |2017-01-04 |10       |ravi     |100-----missing dates required
    1   |2017-01-05 |10       |ravi     |200
    1   |2017-01-06 |10       |ravi     |200-----missing dates required
    2   |2017-01-07 |20       |banu     |250
    2   |2017-01-08 |20       |banu     |300
    2   |2017-01-09 |20       |banu     |300-----missing dates required
    3   |2017-01-10 |30       |jai      |400

How can I write a query to achieve this task in SQL Server?

Upvotes: 2

Views: 1237

Answers (2)

Matej Hlavaj
Matej Hlavaj

Reputation: 1038

You can use recursive CTE. (if there will be more rows, use maxrecursion option :) )

CREATE TABLE [dbo].[emp](
    [empid] [int] NULL,
    [doj] [date] NULL,
    [deptid] [int] NULL,
    [ename] [varchar](50) NULL,
    [sal] [int] NULL
) 
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (1, CAST(N'2017-01-02' AS Date), 10, N'ravi', 100)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (1, CAST(N'2017-01-05' AS Date), 10, N'ravi', 200)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (2, CAST(N'2017-01-08' AS Date), 20, N'banu', 300)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (2, CAST(N'2017-01-07' AS Date), 20, N'banu', 250)
GO
INSERT [dbo].[emp] ([empid], [doj], [deptid], [ename], [sal]) VALUES (3, CAST(N'2017-01-10' AS Date), 30, N'jai', 400)
GO   

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SELECT * , ROW_NUMBER() OVER (ORDER BY doj) RN
INTO #tmp
FROM dbo.emp    

;WITH CTE AS (
             SELECT empid, deptid,ename,sal, doj,RN
             FROM #tmp
             UNION ALL 
             SELECT a.empid, a.deptid,a.ename,a.sal, DATEADD(DAY,+1,a.doj),a.RN
             FROM CTE a
             JOIN #tmp b ON a.RN+1 = b.RN
             WHERE DATEADD(DAY,+1,a.doj) != b.doj
             )

SELECT * 
FROM CTE
ORDER BY CTE.doj

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

I'm not sure I entirely understand your criteria here (I was initially expecting some kind of partitioning based on e.g. empid) but this seems to do the job:

declare @emp table (
    [empid] [int] NULL,
    [doj] [date] NULL,
    [deptid] [int] NULL,
    [ename] [varchar](50) NULL,
    [sal] [int] NULL
) 
INSERT @emp ([empid], [doj], [deptid], [ename], [sal]) VALUES
(1, CAST(N'2017-01-02' AS Date), 10, N'ravi', 100),
(1, CAST(N'2017-01-05' AS Date), 10, N'ravi', 200),
(2, CAST(N'2017-01-08' AS Date), 20, N'banu', 300),
(2, CAST(N'2017-01-07' AS Date), 20, N'banu', 250),
(3, CAST(N'2017-01-10' AS Date), 30, N'jai', 400)

;With Fillers as (
    select * from @emp
    union all
    select empid,DATEADD(day,1,doj),deptid,ename,sal
    from Fillers e
    where not exists (select * from @emp e2 where e2.doj = DATEADD(day,1,e.doj)) and
    exists (select * from @emp e3 where e3.doj > e.doj)
)
select * from Fillers
order by doj

Results:

empid       doj        deptid      ename            sal
----------- ---------- ----------- ---------------- -----------
1           2017-01-02 10          ravi             100
1           2017-01-03 10          ravi             100
1           2017-01-04 10          ravi             100
1           2017-01-05 10          ravi             200
1           2017-01-06 10          ravi             200
2           2017-01-07 20          banu             250
2           2017-01-08 20          banu             300
2           2017-01-09 20          banu             300
3           2017-01-10 30          jai              400

Hopefully, the recursive CTE is relatively readable - we add more rows to fill in the gaps if there's no current row for a particular date (e2) and there is at least one more row that represents the overall end-point for performing filling-in (e3).

Upvotes: 2

Related Questions