LougaLo
asked on
how to find missing dates.
I am trying to find missing dates.
I have a 1 single table with two columns:
ID (PK)
DateOfVisit (datetime)
a Visit is schedule each month
Visit1 (Aug 2010)
Visit2 (Sep 2010)
Visit3 (Oct 2010)
Visit4 (Nov 2010)
Visit5 (Dec 2010)
I need a query to I find the number of missing visit (s)?
for example number of missing visit= 2 when Visit2 (Sep 2010) and Visit5 (Dec 2010) are not entered.
Thank you for your help.
I have a 1 single table with two columns:
ID (PK)
DateOfVisit (datetime)
a Visit is schedule each month
Visit1 (Aug 2010)
Visit2 (Sep 2010)
Visit3 (Oct 2010)
Visit4 (Nov 2010)
Visit5 (Dec 2010)
I need a query to I find the number of missing visit (s)?
for example number of missing visit= 2 when Visit2 (Sep 2010) and Visit5 (Dec 2010) are not entered.
Thank you for your help.
More or less it would be like this,
You can modify it not to use MONTH() in the join if you have any index created over the column, or you can create a range in the table @schedule instead....
But I'm sure you get the point.
Good luck.
You can modify it not to use MONTH() in the join if you have any index created over the column, or you can create a range in the table @schedule instead....
But I'm sure you get the point.
Good luck.
DECLARE @visits TABLE (ID INT IDENTITY,
DateOfVisit datetime)
DECLARE @schedule TABLE (MonthVisit datetime)
INSERT INTO @visits VALUES ('2011-01-15')
INSERT INTO @visits VALUES ('2011-03-15')
INSERT INTO @visits VALUES ('2011-04-15')
INSERT INTO @visits VALUES ('2011-07-15')
INSERT INTO @visits VALUES ('2011-09-15')
INSERT INTO @visits VALUES ('2011-10-15')
INSERT INTO @visits VALUES ('2011-11-15')
INSERT INTO @visits VALUES ('2011-12-15')
INSERT INTO @schedule VALUES('2011-01-01')
INSERT INTO @schedule VALUES('2011-02-01')
INSERT INTO @schedule VALUES('2011-03-01')
INSERT INTO @schedule VALUES('2011-04-01')
INSERT INTO @schedule VALUES('2011-05-01')
INSERT INTO @schedule VALUES('2011-06-01')
INSERT INTO @schedule VALUES('2011-07-01')
INSERT INTO @schedule VALUES('2011-08-01')
INSERT INTO @schedule VALUES('2011-09-01')
INSERT INTO @schedule VALUES('2011-10-01')
INSERT INTO @schedule VALUES('2011-11-01')
INSERT INTO @schedule VALUES('2011-12-01')
SELECT DATENAME(month, S.MonthVisit), COUNT(V.ID)
FROM @schedule as S
LEFT JOIN @visits as V
ON MONTH(S.MonthVisit) = MONTH(V.DateOfVisit)
GROUP BY S.MonthVisit
ASKER
i already data entered and dates in the "TABLE".
attached is example table I have now my database
tbDateOfVisit.xls
attached is example table I have now my database
tbDateOfVisit.xls
ASKER
thank raulggonzalez: see my recent post
;with cte as (select * ,row_number() over (partition by [id] order by [date] asc) as rn
from yourtable)
Select a.*,'Missing '+convert(varchar(4),dated iff(m,a.[d ate],coale sce(b.date ,getdate() )))
' Months Appointments until'+convert(varchar(12) ,coalesce( b.date,get date()))
from cte as a
left outer join cte as B
on a.id=b.id
and b.rn=a.rn+1
Where datediff(m,a.[date],coales ce(b.date, getdate()) )>1
order by a.id,a.rn desc
from yourtable)
Select a.*,'Missing '+convert(varchar(4),dated
' Months Appointments until'+convert(varchar(12)
from cte as a
left outer join cte as B
on a.id=b.id
and b.rn=a.rn+1
Where datediff(m,a.[date],coales
order by a.id,a.rn desc
ASKER
Lowfatspread:
just replaced yourtable with my table
here is the error msg I got
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '+'.
a.[date] (a. underlined in red)
a.* (a. underlined in red)
+ (+ underlined in red)
(b.date (b. underlined in red)
just replaced yourtable with my table
here is the error msg I got
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '+'.
a.[date] (a. underlined in red)
a.* (a. underlined in red)
+ (+ underlined in red)
(b.date (b. underlined in red)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lowfatspread:
NO error where displayed but the output is not correct.
Let me do more investigations. for example
1. for ID 001 I have 7 records in my database table
2. the query output shows only 3 records.
NO error where displayed but the output is not correct.
Let me do more investigations. for example
1. for ID 001 I have 7 records in my database table
2. the query output shows only 3 records.
LougaLo,
What is the expected output for the given sample data?
What is the expected output for the given sample data?
looks ok to me...
depends onn how you want to interpret it...
these both show the same thing...
but the second displays rows for the Missed Months...
"you haven't missed the current month, it isn't over yet..."
wether the gap between May 2010 and Jul 2010 should be reported as a 1 or 2 gaps
depends on the interpretation of "Month" and what we are supposed to be counting Visits or
gaps...
hth
depends onn how you want to interpret it...
these both show the same thing...
but the second displays rows for the Missed Months...
"you haven't missed the current month, it isn't over yet..."
wether the gap between May 2010 and Jul 2010 should be reported as a 1 or 2 gaps
depends on the interpretation of "Month" and what we are supposed to be counting Visits or
gaps...
hth
;with cte as (select * ,row_number() over (partition by [id] order by [dateofvisit] asc) as rn
from yourtable)
Select a.id
,CONVERT(varchar(12),a.dateofvisit) as LastVist
,convert(varchar(4),datediff(m,a.[dateofvisit],coalesce(b.dateofvisit,getdate())))
+ ' Months Appointments until '+convert(varchar(12),coalesce(b.dateofvisit,getdate()))
as Missing
from cte as a
left outer join cte as B
on a.id=b.id
and b.rn=a.rn+1
Where datediff(m,a.[dateofvisit],coalesce(b.dateofvisit,getdate()))>1
order by a.id,a.rn desc
;with cte as (select * ,row_number() over (partition by [id] order by [dateofvisit] asc) as rn
from yourtable)
Select a.id
,CONVERT(varchar(12),a.dateofvisit) as LastVist
,LEFT(datename(m,dateadd(m,v.number,a.dateofvisit)),3)
+' '+convert(char(4),year(dateadd(m,v.number,a.dateofvisit))) as [missed Visit]
,v.number as "Missed # since last"
from cte as a
left outer join cte as b
on a.id=b.id
and b.rn =a.rn+1
cross Join master.dbo.spt_values as v
where convert(int,CONVERT(char(6),a.dateofvisit,112))
<> CONVERT(int,convert(char(6),coalesce(b.dateofvisit,getdate()),112))-1
and v.type='p'
and v.number between 1 and 240
and
convert(int,CONVERT(char(6),dateadd(m,v.number,a.dateofvisit),112)) <
CONVERT(int,convert(char(6),coalesce(b.dateofvisit,getdate()),112))
order by a.id,a.dateofvisit,v.number
ASKER
ASKER
Lowfatspread:,
I would like to count the number of Visit missed. for example if 9 visits are scheduled and we have only 8 entries. the number of missed visit will be 1.
I would like to count the number of Visit missed. for example if 9 visits are scheduled and we have only 8 entries. the number of missed visit will be 1.
Lowfatspread's 1st query should work fine. You just need to group to group the id and get the sum.
Here's the modified query, no points for me.
Here's the modified query, no points for me.
;with cte as (select * ,row_number() over (partition by [id] order by [dateofvisit] asc) as rn
from tblvisit)
Select a.id
,SUM(datediff(m,a.[dateofvisit],coalesce(b.dateofvisit,getdate()))-1) as [Number of visits missed]
from cte as a inner join
cte as B on a.id=b.id and b.rn=a.rn+1
Where datediff(m,a.[dateofvisit],coalesce(b.dateofvisit,getdate()))>1
group by a.id
order by a.id
ASKER
Thomasian,
You are correct. Lowfatspread's first query works fine.
Now, I have another issue with my output.
I will discuss with my supervisor and will get back here or open a different question.
You are correct. Lowfatspread's first query works fine.
Now, I have another issue with my output.
I will discuss with my supervisor and will get back here or open a different question.
If you create a table with the schedule of visits, you can join it and count from your table visits to know when it's missing.
cheers