May 17, 2013 at 10:32 am
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
May 17, 2013 at 12:59 pm
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
May 17, 2013 at 4:48 pm
Thanks Erin, the code worked beautifully. I appreciate your help.
May 20, 2013 at 8:18 am
Glad I could help. 🙂
May 20, 2013 at 9:22 am
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
May 20, 2013 at 11:57 am
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
May 22, 2013 at 3:21 am
Sorry! Post withdrawn due to Homer Simpson moment.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply