Missing dates fill with previous date data in sql server

Transact-SQL
1

hi I have one doubt 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 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 need
1 |2017-01-04 |10 |ravi |100 --missing dates need
1 |2017-01-05 |10 |ravi |200
1 |2017-01-06 |10 |ravi |200-----missing dates need
2 |2017-01-07 |20 |banu |250
2 |2017-01-08 |20 |banu |300
2 |2017-01-09 |20 |banu |300----missing dates need
3 |2017-01-10 |30 |jai |400

please tell me how to write query to achive this task in sql server .

2

There may be a way to do this in one go.

--1st step, created a temp table with all dates & emp data

select *
into [TEST].[dbo].[tmp]
from
(SELECT TOP(DATEDIFF(DAY, '01/02/2017', '01/10/2017') + 1)
DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, CONVERT(DATETIME, '01/02/2017')) AS AllDates
FROM sys.columns a) as alldates
left join [TEST].[dbo].[emp] e
on alldates.Alldates = e.doj

-- 2nd step, select data from temp table with replacing null values with closest value (from the last known value in the previous date)

SELECT t.Alldates 'Date'
,ISNULL( t.empid , (SELECT TOP 1 empid FROM [TEST].[dbo].[tmp] WHERE Alldates < t.Alldates AND empid IS NOT NULL ORDER BY Alldates DESC)) 'empid'
,ISNULL( t.doj , (SELECT TOP 1 doj FROM [TEST].[dbo].[tmp] WHERE Alldates < t.Alldates AND doj IS NOT NULL ORDER BY Alldates DESC)) 'doj'
,ISNULL( t.deptid , (SELECT TOP 1 deptid FROM [TEST].[dbo].[tmp] WHERE Alldates < t.Alldates AND deptid IS NOT NULL ORDER BY Alldates DESC)) 'deptid'
,ISNULL( t.ename , (SELECT TOP 1 ename FROM [TEST].[dbo].[tmp] WHERE Alldates < t.Alldates AND ename IS NOT NULL ORDER BY Alldates DESC)) 'ename'
,ISNULL( t.sal , (SELECT TOP 1 sal FROM [TEST].[dbo].[tmp] WHERE Alldates < t.Alldates AND sal IS NOT NULL ORDER BY Alldates DESC)) 'sal'
from [TEST].[dbo].[tmp] t

hope this helps..

3

Try this

WITH cte1 AS
(
SELECT empid, doj AS cur, LEAD(doj) OVER(ORDER BY doj) AS nxt, deptid, ename, sal 
FROM dbo.emp 
),
cte2
as
(
SELECT empid, DATEADD(day, 1, cur) AS rangestart, DATEADD(day, -1, nxt) rangeend, deptid, ename, sal 
FROM cte1 
WHERE DATEDIFF(day, cur, nxt) > 1

)

SELECT empid, doj, deptid, ename, sal FROM emp 
UNION
SELECT empid, rangestart AS doj, deptid, ename , sal FROM cte2 
UNION 
SELECT empid, rangeend,  deptid, ename, sal FROM cte2