select command with case statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

select command with case statement?

Author

Topic

ectraders
Starting Member

2 Posts

Posted - 2011-09-24 : 00:54:07

Hello,
I could really use some help writing a select command (at least that's what I think I need). I think I need some sort of case statement. Forgive me - I'm new to all this.

Here is what I am trying to do:

I have two tables

1. pt_tblDailyNote
2. pt_tblPhysiciansReport

I want to be able to count the rows in each table and return to me the value 1 if the total rows in pt_tblDailyNote is greater than 30 and the total rows in the table pt_tblPhysiciansReport is less than 1.

I have figured out how to count the rows, but I don't know how to write the code to return the value 1 if the above parameters are true.

Here is what I have so far...
SELECT TotalVisits=(SELECT Count(*) FROM pt_tblDailyNote WHERE PatientId=@PatientId), TotalNotes=(SELECT Count(*) FROM pt_tblPhysiciansReport WHERE PatientId=@PatientId)
<Parameter Name="PatientId" Value='[[Url:pid]]' />

Any help would be great. Thank you in advance.

E.C.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-24 : 03:25:17

[code]SELECT TotalVisits=CASE WHEN (SELECT Count(*) FROM pt_tblDailyNote WHERE PatientId=@PatientId)> 30 AND TotalNotes=(SELECT Count(*) FROM pt_tblPhysiciansReport WHERE PatientId=@PatientId)=0 THEN 1 END[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ectraders
Starting Member

2 Posts

Posted - 2011-09-25 : 22:50:18

Thank you so much. Can I ask for your help with one other select command?

What if I wanted to query the database and select firstname and lastname (both are columns in the table) from the table pt_tblDailyNote for all people that have greater than 30 entries in the pt_tblDailyNote table and less than 1 entry in the pt_tblPhysiciansReport table?

Thanks again for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 00:31:27

[code]select dn.firstname,dn.lastname
from pt_tblDailyNote dn
left join pt_tblPhysiciansReport pr
on pr.PatientId = dn.PatientId
where pr.PatientId is null
group by dn.firstname,dn.lastname
having count(*) >30
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources