Subquery, SQL , Case Statment - 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 2012 Forums

Transact-SQL (2012)

Subquery, SQL , Case Statment

Author

Topic

imfarhan
Starting Member

4 Posts

Posted - 2013-05-30 : 10:44:11

When I run the following SQL run ok and results ok as well


select episode_id,d1,
   CASE 
    WHEN EXISTS
   	(Select * from emp d
   	where d.episode_id = a.episode_id
   	and d1 = 5)
   Then '1'
   Else '0'
   END AS colC
from emp a

Output
EpisodeID D1 colC
845 51 0
1 1575 0
2 5 1
3 5 1
4 1154 0

The problem in the following SQL run ok but the control field 'Reamdission status' not showing the righ results


select patient_bk,admission_method_NC,admission_dttm,
 CASE 
 WHEN EXISTS ( 
  Select * from pp_rds.inpatient_spell d
  where d.patient_bk= m.patient_bk 
  and d.patient_bk = 83340
  and d. admission_method_nc like '2%'
  and date(admission_dttm) = '2013-04-28'
  	     )
  THEN 'In there'
  ELSE 'Not Match'
  END AS ReAdmission_Status
FROM pp_rds.inpatient_spell m
where patient_bk = '83340'
and admission_method_nc like '2%'

Hope make sense appreciate you help
Regards
F

F

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-30 : 10:53:42

come up with a couple of sample records, and the desired ouput you're looking for...

By the way are you using "case" statment like that - querying the same table via Exist operator that is used in the main query. I believe it would be much better to query like this

SELECT
patient_bk,admission_method_NC,admission_dttm,
CASE WHEN date(admission_dttm) = '2013-04-28' THEN 'In there' ELSE 'Not Match' END as ReAdmission_Status
FROM pp_rds.inpatient_spell m
where patient_bk = '83340'
and admission_method_nc like '2%'

Cheers
MIK

imfarhan
Starting Member

4 Posts

Posted - 2013-05-30 : 11:12:33

Thanks for your prompt resonpe and Its work fine but I'm trying to explain what I'm trying to do

In the subquery( I need to check for previous week/month admission)
If its exitst then its check(outer query) for previous 6month if the same patient admitted or not so in the below example(A) it should pull the last row but in the Example (B)

Example (A)

quote:

Pat_ID AdmissionDt Readmisison_6_Month
173460 2008-08-25 17:59:00.0 Not Match
173460 2010-01-12 18:00:00.0 Not Match
173460 2013-05-01 16:18:00.0 In there

From above example should only display only last row as it not previous 6month
Example (B)
83340 2013-03-16 18:55:00.0 Not Match
83340 2013-04-28 19:28:00.0 In there
Should be display both


SELECT
patient_bk,admission_method_NC,admission_dttm,
CASE WHEN date(admission_dttm) between '2013-04-01' and '2013-05-28'  THEN 'In there' ELSE 'Not Match' END as ReAdmission_Status
FROM pp_rds.inpatient_spell m
where patient_bk = '83340'
and admission_method_nc like '2%'  						  
--and date(admission_dttm) between '2013-11-01' and '2013-03-31'

Hope make sense now
Thanks again for you help

F

imfarhan
Starting Member

4 Posts

Posted - 2013-05-30 : 11:18:09

Sorry about the output is not clear...

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-30 : 11:25:33

declare @tab table (Pat_Id int,AdmissionDt datetime)
INSERT INTO @tab VALUES (173460,'2008-08-25 17:59:00'),(173460,'2010-01-12 18:00:00'),(173460,'2013-05-01 16:18:00'),
(83340,'2013-03-16 18:55:00'),(83340,'2013-04-28 19:28:00')
select
Pat_Id
,AdmissionDt
,CASE WHEN AdmissionDt > '2013-04-01' THEN 'In there' ELSE 'Not Match' END NameThisColumnAsDesired
from @tab

You can restrict the data set of your table as requried by adding simply where clause to the query

Cheers
MIK

imfarhan
Starting Member

4 Posts

Posted - 2013-05-30 : 11:56:38

Yes true,

Thanks a lot for your help! first time i post on this forum and get the quite good response.

Regards
Farhan

F

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-30 : 12:34:15

you're welcome.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources