Need help for query. - 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 2008 Forums

Transact-SQL (2008)

Need help for query.

Author

Topic

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2009-12-02 : 09:48:42

Hi all,

Need your help for one query. The data is for a hospital database.

The query is required for a report where user will select the start date and end date. If the patient is admitted, already admitted but not discharged before that date or discharged on that date, the count should be taken for the patient.

The Sample data is :

PatientId AdmissionDate DischargeDate
1 01-Nov-2009 05-Nov-2009
2 01-Nov-2009 06-Nov-2009
3 02-Nov-2009 07-Nov-2009
4 03-Nov-2009 Null

The expected output is
"Date" "Total Count of Patient for the day"
01-Nov-2009 2
02-Nov-2009 3
03-Nov-2009 4
04-Nov-2009 4
05-Nov-2009 4
06-Nov-2009 3
07-Nov-2009 2
08-Nov-2009 1

Thanks to all in advance.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-02 : 10:34:57

How's this for a start?


declare @Sample table (PatientId int, AdmissionDate datetime, DischargeDate datetime)
insert @Sample
          select 1, '01-Nov-2009', '05-Nov-2009'
union all select 2, '01-Nov-2009', '06-Nov-2009'
union all select 3, '02-Nov-2009', '07-Nov-2009'
union all select 4, '03-Nov-2009', Null

--see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519 for F_TABLE_DATE
select Date, count(*) as [Total Count of Patient for the day]
from dbo.F_TABLE_DATE('01-Nov-2009', '08-Nov-2009') a inner join @Sample b on a.Date between AdmissionDate and isnull(DischargeDate, '20991231')
group by date

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2009-12-03 : 01:04:57

Hi Ryan Randall,

Thanks for the solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-03 : 03:12:35

or without a function


declare @Sample table (PatientId int, AdmissionDate datetime, DischargeDate datetime)
insert @Sample
          select 1, '01-Nov-2009', '05-Nov-2009'
union all select 2, '01-Nov-2009', '06-Nov-2009'
union all select 3, '02-Nov-2009', '07-Nov-2009'
union all select 4, '03-Nov-2009', Null


select Date, count(*) as [Total Count of Patient for the day]
from 
(
select dateadd(day,number,'01-Nov-2009') as date from master..spt_values where 
type='p' and number<8
) a inner join @Sample b on a.Date between AdmissionDate and isnull(DischargeDate, '20991231')
group by date

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources