calculate future appts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

calculate future appts

Author

Topic

aggie555
Starting Member

2 Posts

Posted - 2009-10-26 : 18:10:06

I have a table where we are storing appointments. I am trying to figure out the distinct count of InformationID where
there exists an appointment on particular day in the future grouped by each day.
For example On 1/1/2009 there was 300 distinct people who have an appointment in the future. 1/2/2009 there were 200, on 1/3/2009 there was 500.
I am joining 2 tables. on the registrantid
Table1
AppointmentID
[AppointmentGUID]
RegistrantID
[DateStart]
[CreatedDate]

Datestart is the appointment date
created date is when the appointment was created.

Table2
RegistrantID
InformationID
NameFirst
NameLast

SELECT dbo.table1.DateStart, dbo.table1.CreatedDate, dbo.table2.InformationID, dbo.table2.NameFirst,
dbo.table2.NameLast
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.RegistrantID = dbo.table2.RegistrantID
where table1.CreatedDate<=10/10/2009

Data looks like thsi

Datestart created date informationid namefirst name last
2009-12-07 2009-10-01 21944 jack hu
2009-12-21 2009-10-01 21944 jack hu
2008-03-01 2008-02-16 54121 lyn ki
2008-04-01 2008-02-16 54121 lyn ki
2008-05-01 2008-01-16 7845 kent yi

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-26 : 18:21:40

SELECT dbo.table1.DateStart, dbo.table1.CreatedDate, dbo.table2.InformationID, dbo.table2.NameFirst,
dbo.table2.NameLast
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.RegistrantID = dbo.table2.RegistrantID
where table1.CreatedDate<=10/10/2009

Will give you all the appointments made prior to 10/11/2009
adding
where table1.CreatedDate<=10/10/2009
and DateStart > getdate()

will give you all the appointments made prior to 10/10 that are for
10/26 at this second or later

Datestart >= DATEADD(day,datediff(day,0,getdate)+1,0) will give you all the appointments made on or before 10/10 that are for tomorrow and beyond

Jim

Everyday I learn something that somebody else already knew

aggie555
Starting Member

2 Posts

Posted - 2009-10-26 : 18:50:28

How do i group the InformationID daily. that On 1/1/2009 there was 300(InformationID) distinct people who have an appointment in the future and 1/2/2009 there were 200(InformationID)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-27 : 08:07:11

SELECT t1.CreatedDate,[People] = count(distinct t2.InformationID)
FROM dbo.table1 t1
INNER JOIN
dbo.table2 t2 ON t1.RegistrantID = t2.RegistrantID
where t1.CreatedDate<=10/10/2009
GROUP BY t1.CreatedDate

Jim

Everyday I learn something that somebody else already knew

wormz666
Posting Yak Master

110 Posts

Posted - 2009-10-29 : 04:26:48

try to between condition

SELECT t1.CreatedDate,count(distinct t2.InformationID) as [People]
FROM dbo.table1 t1
INNER JOIN
dbo.table2 t2 ON t1.RegistrantID = t2.RegistrantID
where t1.CreatedDate between 10/10/2009 and getdate()

its filter everything that has been on the date you range...

finding the truth
making a maze on my mind....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources