Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find date gaps Expand / Collapse
Author
Message
Posted Friday, May 17, 2013 10:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:47 PM
Points: 11, Visits: 125
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


Post #1454089
Posted Friday, May 17, 2013 12:59 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:58 AM
Points: 546, Visits: 1,061
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

Post #1454176
Posted Friday, May 17, 2013 4:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:47 PM
Points: 11, Visits: 125
Thanks Erin, the code worked beautifully. I appreciate your help.
Post #1454211
Posted Monday, May 20, 2013 8:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:58 AM
Points: 546, Visits: 1,061
Glad I could help. :)
Post #1454552
Posted Monday, May 20, 2013 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:47 PM
Points: 11, Visits: 125
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
Post #1454586
Posted Monday, May 20, 2013 11:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:47 PM
Points: 11, Visits: 125
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


Post #1454664
Posted Wednesday, May 22, 2013 3:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1455379
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse