Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to find missing dates.

Posted on 2011-02-11
14
Medium Priority
?
346 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:LougaLo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34877494
Hi,

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

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34877902
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.


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

Open in new window

0
 

Author Comment

by:LougaLo
ID: 34878108
i already data entered and dates in the "TABLE".
attached is example table I have now my database
 tbDateOfVisit.xls
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:LougaLo
ID: 34878115
thank raulggonzalez: see my recent post
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34878160
;with cte as (select * ,row_number() over (partition by [id] order by [date] asc) as rn
                   from yourtable)
Select  a.*,'Missing '+convert(varchar(4),datediff(m,a.[date],coalesce(b.date,getdate())))
            ' Months Appointments until'+convert(varchar(12),coalesce(b.date,getdate()))
   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],coalesce(b.date,getdate()))>1
 order by a.id,a.rn desc
0
 

Author Comment

by:LougaLo
ID: 34878174
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)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 34878190
missing +?  can you post your version of the query if it fails again

;with cte as (select * ,row_number() over (partition by [id] order by [date] asc) as rn
                   from yourtable)
Select  a.*,'Missing '+convert(varchar(4),datediff(m,a.[date],coalesce(b.date,getdate())))
          + ' Months Appointments until'+convert(varchar(12),coalesce(b.date,getdate()))
   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],coalesce(b.date,getdate()))>1
 order by a.id,a.rn desc
0
 

Author Comment

by:LougaLo
ID: 34878309
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.

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 34878413
LougaLo,

What is the expected output for the given sample data?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34879065
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

;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

Open in new window

0
 

Author Comment

by:LougaLo
ID: 34881057
Hi all,
attached is the file showing more detail and the expected output:

 tbVisit.xls
0
 

Author Comment

by:LougaLo
ID: 34881062
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.
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 34881080
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.

;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 

Open in new window

0
 

Author Comment

by:LougaLo
ID: 34881272
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question