How to Query a missing record from a table?
-
-- Let says I have an application that uses the tale here.
Declare @tbATable Table(ID Int Identity(1,1) Not Null,AName varChar(20),ADate DateTime)
Insert @tbATable (AName,ADate) Values('A',GetDate()-4)
Insert @tbATable (AName,ADate) Values('A',GetDate()-3)
Insert @tbATable (AName,ADate) Values('A',GetDate()-2)
Insert @tbATable (AName,ADate) Values('A',GetDate()-1)
Insert @tbATable (AName,ADate) Values('A',GetDate())
Insert @tbATable (AName,ADate) Values('B',GetDate()-4)
--Insert @tbATable (AName,ADate) Values('B',GetDate()-3)-- Missed date
Insert @tbATable (AName,ADate) Values('B',GetDate()-2)
Insert @tbATable (AName,ADate) Values('B',GetDate()-1)
Insert @tbATable (AName,ADate) Values('B',GetDate())
Insert @tbATable (AName,ADate) Values('C',GetDate()-4)
Insert @tbATable (AName,ADate) Values('C',GetDate()-3)
--Insert @tbATable (AName,ADate) Values('C',GetDate()-2)-- Missed date
--Insert @tbATable (AName,ADate) Values('C',GetDate()-1)-- Missed date
Insert @tbATable (AName,ADate) Values('C',GetDate())
Insert @tbATable (AName,ADate) Values('D',GetDate()-4)
--Insert @tbATable (AName,ADate) Values('D',GetDate()-3)-- Missed date
Insert @tbATable (AName,ADate) Values('D',GetDate()-2)
--Insert @tbATable (AName,ADate) Values('D',GetDate()-1) -- Missed date
Insert @tbATable (AName,ADate) Values('D',GetDate())
Select * From @tbATable
/*
It requires that Patients "Example 'A thru D'"
must enter into the system every day.
So a report will be created to show what patient and date that are missed.
"Example B missed 1 day, C missed 2 days and D missed 2 days"
This is what I am looking for.
ID AName ADate
----------- ------- -------------
NULL B 2004-07-17
NULL C 2004-07-18
NULL C 2004-07-19
NULL D 2004-07-17
NULL D 2004-07-19
(5 row(s) affected)
Would you please help. Thank you Tin le
*/
Tin Le -
Try this..
Declare @tbATable Table(ID Int Identity(1,1) Not Null,AName varChar(20),ADate DateTime)
declare @CurrentDate DateTime
set @CurrentDate = GetDate()
Insert @tbATable (AName,ADate) Values('A',@CurrentDate-4)
Insert @tbATable (AName,ADate) Values('A',@CurrentDate-3)
Insert @tbATable (AName,ADate) Values('A',@CurrentDate-2)
Insert @tbATable (AName,ADate) Values('A',@CurrentDate-1)
Insert @tbATable (AName,ADate) Values('A',@CurrentDate)
Insert @tbATable (AName,ADate) Values('B',@CurrentDate-4)
--Insert @tbATable (AName,ADate) Values('B',@CurrentDate-3) -- Missed date
Insert @tbATable (AName,ADate) Values('B',@CurrentDate-2)
Insert @tbATable (AName,ADate) Values('B',@CurrentDate-1)
Insert @tbATable (AName,ADate) Values('B',@CurrentDate)
Insert @tbATable (AName,ADate) Values('C',@CurrentDate-4)
Insert @tbATable (AName,ADate) Values('C',@CurrentDate-3)
--Insert @tbATable (AName,ADate) Values('C',@CurrentDate-2) -- Missed date
--Insert @tbATable (AName,ADate) Values('C',@CurrentDate-1) -- Missed date
Insert @tbATable (AName,ADate) Values('C',@CurrentDate)
Insert @tbATable (AName,ADate) Values('D',@CurrentDate-4)
--Insert @tbATable (AName,ADate) Values('D',@CurrentDate-3) -- Missed date
Insert @tbATable (AName,ADate) Values('D',@CurrentDate-2)
--Insert @tbATable (AName,ADate) Values('D',@CurrentDate-1) -- Missed date
Insert @tbATable (AName,ADate) Values('D',@CurrentDate)
Select a.adate, b.aname
from (select distinct adate From @tbATable) a cross join
(Select distinct aname From @tbATable) b left join @tbATable c
on a.adate = c.adate and b.aname = c.aname
where c.aname is null
-
I'll throw this out for fun...
- Create a calendar table with just the RecordDate from the 1st day of the 1st year that you started this out until 2028 (why because I like 2028... Ok so companies that said they were y2k Are but ARE NOT 2028 compliant... (may complain that 2028 not leap year))
- Create a table that maintains the patient/client list of whent they started with you and only that information i.e. A 07/01/2004, B 05/20/2004, etc..
- You should then be able to build a sp with a loop that looks at the "recording" table and inserts rows into a #table where the patient/client from 1st day forward is NOT found in the calendar table.
- Report off the #table
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thank you very much Good Hunting,
I really appreciate your opinion, and I value it. And I must say that your opinion very helpful.
Thank you again.
Yes, I did have a table value function to do just that, but the the result is not exactly what I have expected.
Here is the function:
/*=============================================================================
Created By: Tin Le
Description: This function will build up a list of date
that based on the input DateStart & DateStop
Return: 0 = success else failed.
Maintenance
Initial / CO# Initials Date Description)Initial version TL19/JULY/2004Return a table value of date range
=============================================================================*/
ALTER Function dbo.fn_Visit_Calendar(
@dtStartDate DateTime,-- Date from
@dtStopDate DateTime = GetDate,-- Date End
@bitUsetTime Bit = 0,-- Does it need Time
@intSiteID Int = null,-- SiteID
@intSPID Int = null)-- Patient/SPID ID
Returns @tbCalendar Table(VisitDate DateTime,SiteID Int,SPID Int)
As
/*
-- Begin Test variables
Declare @intSiteID Int,@intSPID Int,@dtStartDate DateTime,@dtStopDate DateTime,@bitUsetTime Bit
Set @intSiteID = null
Set @intSPID = null
Set @dtStartDate = GetDate() - 10
Set @dtStopDate = GetDate()
Set @bitUsetTime = 0
Declare @tbCalendar Table(SiteID Int,SPID Int,VisitDate DateTime)
-- End Test variables
*/
Begin
Declare @dtVisitDate DateTime,@dtStart DateTime,@dtStop DateTime, @strDateTime varChar(40)
If @bitUsetTime = 0
Set @dtStart = Cast(Convert(varChar(10),@dtStartDate,101) as DateTime)
Else
Set @dtStart = Cast(Convert(varChar(10),@dtStartDate,101) + ' ' +
Convert(varChar(10),@dtStartDate,108) as DateTime)
If @bitUsetTime = 0
Set @dtStop = Cast(Convert(varChar(10),@dtStopDate,101) as DateTime)
Else
Set @dtStop = Cast(Convert(varChar(10),@dtStopDate,101) + ' ' +
Convert(varChar(10),@dtStopDate,108) as DateTime)
--Select @dtStart dtStart, @dtStop dtStop, @dtStartDate dtDateStart
Set @dtVisitDate = @dtStart
While Cast(DateAdd(day,1,@dtVisitDate) as Int) <= Cast(@dtStopDate as int) Begin
Set @dtVisitDate = DateAdd(day,1,@dtVisitDate)
Insert Into @tbCalendar(VisitDate,SiteID,SPID)
Values(@dtVisitDate,@intSiteID,@intSPID)
End
Return--(@tbCalendar)
--Select * From @tbCalendar
End
and here is the query that I did.
Please forgive for not edit this query because of the time involve.
-- not Work
Declare @dtS DateTime, @dtE DateTime
Set @dtS = '05/01/2004' Set @dtE = '05/28/2004'
SelectDistinct
dr.SPID,dr.EntryDate--, vc.VisitDate
From ClientData.dbo.Frx399006_DiaryTest dr With (nolock) Left JOIN
ClientData.dbo.fn_Visit_Calendar(@dtS,@dtE,0,null,null) vc ON
dr.EntryDate vc.VisitDate
Where dr.SPID = 451901
AND vc.VisitDate NOT IN
(Select Distinct dr.EntryDate
From ClientData.dbo.Frx399006_DiaryTest dr With (nolock) INNER JOIN
ClientData.dbo.fn_Visit_Calendar(@dtS,@dtE,0,null,null) vc ON
dr.EntryDate = vc.VisitDate
Where dr.SPID = 451901
)
Group By dr.SPID,dr.EntryDate--, vc.VisitDate
--Order By dr.SPID,dr.EntryDate, vc.VisitDate
Tin Le -
I have noway of knowing your name, but anyway, your solution is perpect and it work exactly what I always have wanted.
Again, With the references and scripts you all offered, I'm well on my way to accomplishing the task. Your help is very much appreciated!
Thanks,
Tin Le
Tin Le
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply