2

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?

1
  • Can you be more specific as what you mean by "missing datas related data"? Commented May 24, 2017 at 7:48

2 Answers 2

2

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).

Sign up to request clarification or add additional context in comments.

Comments

0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.