Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find date gaps


Find date gaps

Author
Message
GUID
GUID
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 127
Hello everyone,

I need to find a way to fill gaps in case a week is missing, example each record has Begin_Date and End_Date, and some records can have multiple Begin_Date and End_Date but they should be continuation of the previous week.

Example :

Row_Nbd Account_Nbr Account_Name Account_Desc Begin_Date End_Date
1 1 test1 chk 1/1/2012 3/1/2012
2 1 test1 chk 3/2/2012 6/30/2012
3 1 test chk 8/2/2012 12/31/2012

as you can see there us a missing date range right above row_nbr 3 which (7/1/2012 to 8/1/2012) how can find the missing week?

Thanks
Erin Ramsay
Erin Ramsay
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 1099
Would something like this get you started?




create table #temp (
Row_Ndb int,
Account_Nbr int,
Account_Name char(5),
Account_Desc char(3),
Begin_Date date,
End_Date date
)

insert into #temp values
(1, 1, 'test1', 'chk', '1/1/2012', '3/1/2012'),
(2, 1, 'test1', 'chk', '3/2/2012', '6/30/2012'),
(3, 1, 'test' , 'chk', '8/2/2012', '12/31/2012')


with cte
as
(
select row_ndb
, account_nbr
, account_name
, Account_desc
, Begin_date
, End_date
, row_number() over (partition by Account_Nbr order by begin_date) rownum
from #temp
)
select *
from
(
select cte1.*
, case when datediff(d,cte2.End_date,cte1.begin_date) > 2 then datediff(d,cte2.End_date,cte1.begin_date) else 0 end as DaysMissing
, case when datediff(d,cte2.End_date,cte1.begin_date) > 2 then dateadd(d,1,cte2.end_date) else null end as MissingBegin_Date
, case when datediff(d,cte2.End_date,cte1.begin_date) > 2 then dateadd(d,-1,cte1.Begin_date) else null end as MissingEnd_Date
from cte cte1
left join cte cte2
on cte1.account_nbr = cte2.account_nbr and cte1.rownum = cte2.rownum + 1
) source
where source.DaysMissing > 0

drop table #temp


GUID
GUID
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 127
Thanks Erin, the code worked beautifully. I appreciate your help.
Erin Ramsay
Erin Ramsay
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 1099
Glad I could help. Smile
GUID
GUID
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 127
I am facing another challenge. I tried to make the code work with no success. the issue i am facing is, i need to fill gaps also,

if the year is not completed example :

( 1, 1, 'test1', 'chk', '2/1/2012', '3/1/2012' ),
( 2, 1, 'test1', 'chk', '3/2/2012', '6/30/2012' ),
( 3, 1, 'test', 'chk', '8/2/2012', '12/01/2012' );

the year should always start at 1.1.2012 and end 12.31.2012 and if there is a keep needs to be filled up, so in this scenario i am missing 1.1.2012 to 1.31.2012
7.1.2012 to 8.1.2012 (Which already fixed thanks to Erin)
12.2.2012 to 12.31.2012
GUID
GUID
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 127
I believe i have the code working now :
but it just seems too long


CREATE TABLE #temp
(
Row_Ndb INT ,
Account_Nbr INT ,
Account_Name CHAR(5) ,
Account_Desc CHAR(3) ,
Begin_Date DATE ,
End_Date DATE
)

INSERT INTO #temp
VALUES
( 1, 1, 'test1', 'chk', '1/1/2012', '3/1/2012' ),
( 2, 1, 'test1', 'chk', '3/2/2012', '6/30/2012' ),
( 3, 1, 'test1', 'chk', '7/11/2012', '7/22/2012' ),
( 3, 1, 'test1', 'chk', '8/1/2012', '10/11/2012' ),
( 4, 1, 'test1', 'chk', '10/19/2012', '1/1/2013' );
WITH cte
AS ( SELECT
row_ndb ,
account_nbr ,
account_name ,
Account_desc ,
Begin_date ,
End_date ,
ROW_NUMBER() OVER ( PARTITION BY Account_Nbr ORDER BY begin_date ) rownum
FROM
#temp
),
cte_Middle
AS ( SELECT DISTINCT
*
FROM
( SELECT
cte1.* ,
CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1
THEN DATEDIFF(d, cte2.End_date, cte1.begin_date)

ELSE 0
END AS DaysMissing ,
CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1
THEN DATEADD(d, 1, cte2.end_date)

ELSE NULL
END AS MissingBegin_Date ,
CASE WHEN DATEDIFF(d, cte2.End_date, cte1.begin_date) > 1
THEN DATEADD(d, -1, cte1.Begin_date)

ELSE NULL
END AS MissingEnd_Date
FROM
cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date

LEFT JOIN cte cte2
ON cte1.account_nbr = cte2.account_nbr
AND cte1.rownum = cte2.rownum + 1
) source
WHERE
source.DaysMissing > 0
),
Cte_Top
AS ( SELECT DISTINCT
*
FROM
( SELECT
cte1.* ,
CASE WHEN DATEDIFF(d, '1/1/2012', Min_Begin_Date) > 1
THEN DATEDIFF(d, '1/1/2012', Min_Begin_Date)
ELSE 0
END AS DaysMissing ,
CASE WHEN Min_Begin_Date > '1/1/2012'
AND Min_Begin_Date < Min_End_Date
THEN '1/1/2012'
ELSE NULL
END AS MissingBegin_Date ,
CASE WHEN DATEDIFF(d, '1/1/2012', Min_Begin_Date) > 1
THEN DATEADD(d, -1, Min_Begin_Date)
ELSE NULL
END AS MissingEnd_Date
-- ,
--Min_Begin_Date ,
--Min_End_Date
FROM
cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date
CROSS APPLY ( SELECT TOP 1
( Begin_Date ) Min_Begin_Date ,
( End_Date ) Min_End_Date ,
account_Name
FROM
cte c3
WHERE
cte1.account_nbr = c3.account_nbr
ORDER BY
begin_date ASC
) cte3
LEFT JOIN cte cte2
ON cte1.account_nbr = cte2.account_nbr
AND cte1.rownum = cte2.rownum + 1
WHERE
Min_End_Date = cte1.End_Date
) source
),
Cte_Button
AS ( SELECT
*
FROM
( SELECT
cte1.* ,
CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1
THEN DATEDIFF(d, Max_End_Date, '12/31/2012')
ELSE 0
END AS DaysMissing ,
CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1
--AND Min_Begin_Date < Min_End_Date
THEN DATEADD(d, 1, Max_End_Date)
ELSE NULL
END AS MissingBegin_Date ,
CASE WHEN DATEDIFF(d, Max_End_Date, '12/31/2012') > 1
THEN '12/31/2012'
ELSE NULL
END AS MissingEnd_Date
-- ,
--Min_Begin_Date ,
--Min_End_Date
FROM
cte cte1 --CASE Min_Begin_Date > '1/1/2012' < Min_End_Date THEN '1/1/2012' ELSE DATEADD(d, 1, cte2.end_date
CROSS APPLY ( SELECT TOP 1
( Begin_Date ) Max_Begin_Date ,
( End_Date ) Max_End_Date ,
account_Name
FROM
cte c3
WHERE
cte1.account_nbr = c3.account_nbr
ORDER BY
begin_date DESC
) cte3
LEFT JOIN cte cte2
ON cte1.account_nbr = cte2.account_nbr
AND cte1.rownum = cte2.rownum + 1

WHERE
Max_End_Date = cte1.End_Date
) source
)
SELECT
*
FROM
cte_Middle
WHERE DaysMissing > 0

UNION
SELECT
*
FROM
Cte_Top
where DaysMissing > 0

UNION
SELECT
*
FROM
Cte_Button
WHERE
DaysMissing > 0

DROP TABLE #temp



dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
Sorry! Post withdrawn due to Homer Simpson moment.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search