October 22, 2008 at 7:12 am
I have a query that lists date ranges for particular id's and i am wanting to know the number of missing days. It works to a point but am getting invalid data as well. example, i have two date ranges:
EffectiveDate ExpirationDate
2004-01-01 00:00:00.0002008-04-07 00:00:00.000
2008-04-08 00:00:00.0002008-07-25 00:00:00.000
as you can see there are no gaps yet the query returns a gap:
GAPRateidFromDateToDateDaysMissing
GAP4473 04/07/200 07/25/2008 108
Here is the query:
DECLARE @gap VARCHAR(3),@OVERLAP VARCHAR(7)
SET @gap='GAP'
SET @OVERLAP='OVERLAP'
select @gap AS GAP,fromDates.Rateid, convert(nvarchar(10),fromDates.Date,101) as FromDate, convert(nvarchar(10),min(ToDates.Date),101) as ToDate, datediff(day,fromDates.date+1,min(toDates.Date)) as DaysMissing
INTO tbl_js_RateDetails_Gaps
from
(
SELECT G1.RateID, G1.ExpirationDate AS Date, G1.Active
FROM GM.dbo.RateDetail AS G1 LEFT OUTER JOIN
GM.dbo.RateDetail AS G2 ON G1.RateID = G2.RateID AND G1.ExpirationDate > G2.EffectiveDate AND
G1.ExpirationDate < G2.ExpirationDate
WHERE (G1.CategoryID = 1) AND (G1.Active = 1)
) fromDates
inner join
(
SELECT G1.RateID, G1.EffectiveDate AS Date, G1.Active
FROM GM.dbo.RateDetail AS G1 LEFT OUTER JOIN
GM.dbo.RateDetail AS G2 ON G1.RateID = G2.RateID AND G1.EffectiveDate > G2.EffectiveDate AND
G1.EffectiveDate < G2.ExpirationDate
WHERE (G1.CategoryID = 1) AND (G1.Active = 1)
) ToDates
on
fromDates.Rateid = ToDates.Rateid and FromDates.Date < ToDates.Date
group by
fromDates.Rateid, fromDates.Date
HAVING DATEDIFF(DAY,fromDates.Date, min(ToDates.Date))>1 AND FROMDATES.DATE>=CONVERT(DATETIME, '2004-01-02 00:00:00', 102)
ORDER BY fromDates.Date
October 22, 2008 at 2:58 pm
Could it have something to do with the way you've disconnected the effective dates to their associated expiration dates? I'm not sure how your subqueries are fitting the data back together with the MAX and GROUP BY. Maybe something like this would help?
;WITH cte (RateID, EffectiveDate, ExpirationDate, sort) AS
(SELECT RateID, EffectiveDate, ExpirationDate,
ROW_NUMBER() OVER (PARTITION BY RateID ORDER BY EffectiveDate) AS sort
FROM #bogus)
SELECT c.RateID,
convert(nvarchar(10), c.ExpirationDate, 101) as FromDate,
convert(nvarchar(10), n.EffectiveDate, 101) as ToDate,
DateDiff(DAY, c.ExpirationDate + 1, n.EffectiveDate)
FROM cte c
INNER JOIN cte n ON c.RateID = n.RateID AND c.sort = n.sort - 1
WHERE c.ExpirationDate < n.EffectiveDate
AND DateDiff(DAY, c.ExpirationDate, n.EffectiveDate) > 1
ORDER BY c.EffectiveDate
October 23, 2008 at 5:09 am
AFTER PLAYING AROUND WITH IT FOR A WHILE, THAT SEEMS TO WORK PERFECT. THANKS FOR YOUR ASSISTANCE!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply