March 12, 2009 at 12:06 am
Hi All,
I want to find the missing date from a series of date.
create table t_date
(
dt datetime
)
go
insert into t_date
select '05/jan/2009'
union all
select '06/jan/2009'
union all
select '08/jan/2009'
union all
select '09/jan/2009'
"07/jan/2009" is missing,i need to identify it.
I have one more table called 'Holiday_History', if that particular date falls under any holiday,then i have to say it as 'Holiday'.
create table Holiday_History
(
Holiday_Dt datetime
)
insert into Holiday_History
select '07/jan/2009'
select 02/feb/2009'
Expected Result:
Date Day Holiday Status
05/Jan/2009 Monday No Available
06/Jan/2009 Tuesday No Available
07/Jan/2009 Wednesday Yes Missing
08/Jan/2009 Thursday No Available
09/Jan/2009 Friday No Available
Inputs are welcome!
karthik
March 12, 2009 at 12:12 am
What kind of joins you're aware of?
_____________
Code for TallyGenerator
March 12, 2009 at 12:20 am
ALL TYPES.
karthik
March 12, 2009 at 12:26 am
Meantime i have witten the below query to identify the missing date.
Declare @MinDate datetime,@MaxDate datetime
select @MinDate = min(dt) from t_date
select @MaxDate = max(dt) from t_date
SELECT x.full_dt,t.dt
FROM t_date t right outer join
(select n-1 + @MinDate as full_Dt
from tally t
where n-1 +@MinDate <= @maxdate)x
on t.dt = x.full_dt
output:
2009-01-05 00:00:00.0002009-01-05 00:00:00.000
2009-01-06 00:00:00.0002009-01-06 00:00:00.000
2009-01-07 00:00:00.000NULL
2009-01-08 00:00:00.0002009-01-08 00:00:00.000
2009-01-09 00:00:00.0002009-01-09 00:00:00.000
karthik
March 12, 2009 at 12:42 am
Going little
I have written the below query to get the expected result.
Declare @MinDate datetime,@MaxDate datetime
select @MinDate = min(dt) from t_date
select @MaxDate = max(dt) from t_date
SELECT x.full_dt,t.dt,datename(dw,x.full_dt),case when Holiday_dt is null then ' Available' else 'Holiday' end as Status
FROM t_date t right outer join (select n-1 + @MinDate as full_Dt,Holiday_dt
from tally t left outer join Holiday_History hh on n-1 +@MinDate = hh.Holiday_dt
where n-1 +@MinDate <= @maxdate)x on t.dt = x.full_dt
karthik
March 12, 2009 at 12:42 am
Now you need to LEFT JOIN to holidays.
_____________
Code for TallyGenerator
March 12, 2009 at 12:44 am
I have tested the execution time of the query.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 10 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(5 row(s) affected)
SQL Server Execution Times:
CPU time = 3 ms, elapsed time = 3 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
so i believe my query will give good performance. Right?
karthik
March 12, 2009 at 12:46 am
I am looking for some other workaround to do the same. So that i can get extend my knowledge.
karthik
March 23, 2009 at 3:42 am
A small help,
if i found any msiing data between the range,then i have to insert that date into the table.
say for example,
2009-01-05 00:00:00.000 2009-01-05 00:00:00.000
2009-01-06 00:00:00.000 2009-01-06 00:00:00.000
2009-01-07 00:00:00.000 NULL
2009-01-08 00:00:00.000 2009-01-08 00:00:00.000
2009-01-09 00:00:00.000 2009-01-09 00:00:00.000
2009-01-07 is coming between the date range. so i have to insert it into the table. suppose 2009-01-09 is missing then we no need to insert it.
if the last row is missing from the range,then we no need to insert it.
karthik
March 24, 2009 at 5:08 pm
karthikeyan (3/23/2009)
2009-01-07 is coming between the date range. so i have to insert it into the table. suppose 2009-01-09 is missing then we no need to insert it.
if the last row is missing from the range,then we no need to insert it.
What would you need the output "07/Jan/2009 Wednesday Yes Missing" for, if the next requirement is to insert the missing dates? But,well, if you need it,...
I slightly modified your base table t_date to have an insertd column just to know what dates have been inserted. If you can't modify that table structure store it in a tmp table an join it.
Note: Same like your original code the following code will slow down as the numbers in your tally table will increase. Try it with different amount up to 10 million and you'll see the difference. Therefore, you'd need to make sure your tally table doesn't have too many numbers in it...
CREATE TABLE #t_date
(
dt datetime,
inserted datetime
)
insert into #t_date
select '05/jan/2009',getdate()
union all
select '06/jan/2009',getdate()
union all
select '08/jan/2009',getdate()
union all
select '09/jan/2009',getdate()
WAITFOR DELAY '00:00:01' -- just to differentiate between the dates already there and the missing date inserted
CREATE TABLE #Holiday_History
(
Holiday_Dt datetime
)
insert into #Holiday_History
select '07/jan/2009'
select '02/feb/2009'
DECLARE @minDate DATETIME,
@maxDate DATETIME,
@curDate DATETIME
SELECT @minDate=MIN(dt), @maxDate =MAX(dt),@curDate =GETDATE() FROM #t_date
SET STATISTICS time on
INSERT INTO #t_date
SELECT
@minDate + Tally.N-1,
getdate()
FROM Tally
LEFT OUTER JOIN #t_date t ON t.dt = @minDate + Tally.N-1
WHERE t.dt is null
AND Tally.N < datediff(dd,@minDate,@maxDate)
SELECT
dt,
datename(dw,dt) AS Day,
CASE
WHEN Holiday_Dt IS null THEN 'no'
ELSE 'yes'
END AS Holiday,
CASE
WHEN insertd<@curDate THEN 'Available'
ELSE 'Missing'
END
AS Status
FROM #t_date t
LEFT OUTER JOIN #Holiday_History h ON t.dt=h.Holiday_Dt
ORDER BY dt
SET STATISTICS time off
-- DROP TABLE #Holiday_History
-- DROP TABLE #t_date
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply