subselect - 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 2005 Forums

Transact-SQL (2005)

subselect

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-18 : 16:24:17

Please help, I have run each of these queries successfully but now want to divide total days by total discharges by provider number and cannot figure out how to code the calculation. Do I need a subquery?

Here are the queries I have:

--1. Average LOS from {total days/total discharges}
--Total Days (S-3 Part I, Line 12, Col. 6)
select hosp_rpt.prvdr_num, hosp_nmrc.Itm_val_num, hosp_nmrc.wksht_cd, hosp_nmrc.line_num,
hosp_nmrc.Clmn_num,hosp_rpt.fy_end_dt
from hosp_nmrc INNER JOIN hosp_rpt
ON hosp_nmrc.RPT_REC_NUM = hosp_rpt.RPT_REC_NUM
where (hosp_nmrc.wksht_cd like 'S300001%'and hosp_nmrc.line_num = '01200' and hosp_nmrc.Clmn_num = '0600')
and (hosp_rpt.prvdr_num = 490011 or hosp_rpt.prvdr_num = 490017 or
hosp_rpt.prvdr_num = 490069 or hosp_rpt.prvdr_num = 490094 or
hosp_rpt.prvdr_num = 490059 or hosp_rpt.prvdr_num = 490136 or
hosp_rpt.prvdr_num = 490041 or hosp_rpt.prvdr_num = 493301 or
hosp_rpt.prvdr_num = 490032 or hosp_rpt.prvdr_num = 490119 or
hosp_rpt.prvdr_num = 490093 or hosp_rpt.prvdr_num = 490046 or
hosp_rpt.prvdr_num = 490044 or hosp_rpt.prvdr_num = 490007 or
hosp_rpt.prvdr_num = 490057 or hosp_rpt.prvdr_num = 420004 or
hosp_rpt.prvdr_num = 490024) and (hosp_rpt.fy_end_dt > '2007-12-31')
order by hosp_rpt.prvdr_num

--Total Discharges (S-3 Part I, Line 12, Col. 15)
select hosp_rpt.prvdr_num, hosp_nmrc.Itm_val_num, hosp_nmrc.wksht_cd, hosp_nmrc.line_num, hosp_nmrc.Clmn_num,
hosp_rpt.fy_end_dt
from hosp_nmrc INNER JOIN hosp_rpt
ON hosp_nmrc.RPT_REC_NUM = hosp_rpt.RPT_REC_NUM
where (hosp_nmrc.wksht_cd like 'S300001%'and hosp_nmrc.line_num = '01200' and hosp_nmrc.Clmn_num = '1500')
and (hosp_rpt.prvdr_num = 490011 or hosp_rpt.prvdr_num = 490017 or
hosp_rpt.prvdr_num = 490069 or hosp_rpt.prvdr_num = 490094 or
hosp_rpt.prvdr_num = 490059 or hosp_rpt.prvdr_num = 490136 or
hosp_rpt.prvdr_num = 490041 or hosp_rpt.prvdr_num = 493301 or
hosp_rpt.prvdr_num = 490032 or hosp_rpt.prvdr_num = 490119 or
hosp_rpt.prvdr_num = 490093 or hosp_rpt.prvdr_num = 490046 or
hosp_rpt.prvdr_num = 490044 or hosp_rpt.prvdr_num = 490007 or
hosp_rpt.prvdr_num = 490057 or hosp_rpt.prvdr_num = 420004 or
hosp_rpt.prvdr_num = 490024) and (hosp_rpt.fy_end_dt > '2007-12-31')
order by hosp_rpt.prvdr_num

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-18 : 16:35:50

Your query is very hard to read. I would re-write like what is below:


select 
  b.prvdr_num
, a.Itm_val_num
, a.wksht_cd
, a.line_num
, a.Clmn_num
, b.fy_end_dt
from 
	hosp_nmrc a
INNER JOIN 
	hosp_rpt b
ON		a.RPT_REC_NUM = b.RPT_REC_NUM 
where 
	a.wksht_cd like 'S300001%'
and a.line_num = '01200' 
and a.Clmn_num = '0600'
and b.prvdr_num In (490011 ,490017 , 490069 , 490094 , 490059 , 490136 , 490041 , 493301 , 490032 , 490119 , 490093 , 490046 , 490044 , 490007, 490057 , 420004, 490024) 
and b.fy_end_dt > '2007-12-31'
order by b.prvdr_num

I am not following exactly what you are asking for... Can you please explain a little clearer how to get the average LOS?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-18 : 16:50:28

quote:
Originally posted by Vinnie881

Your query is very hard to read. I would re-write like what is below:

select
b.prvdr_num
, a.Itm_val_num
, a.wksht_cd
, a.line_num
, a.Clmn_num
, b.fy_end_dt
from
hosp_nmrc a
INNER JOIN
hosp_rpt b
ON a.RPT_REC_NUM = b.RPT_REC_NUM
where
a.wksht_cd like 'S300001%'
and a.line_num = '01200'
and a.Clmn_num = '0600'
and b.prvdr_num In (490011 ,490017 , 490069 , 490094 , 490059 , 490136 , 490041 , 493301 , 490032 , 490119 , 490093 , 490046 , 490044 , 490007, 490057 , 420004, 490024)
and b.fy_end_dt > '2007-12-31'
order by b.prvdr_num

I am not following exactly what you are asking for... Can you please explain a little clearer how to get the average LOS?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

I will do that and post it again with a clearer explanation shortly.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-18 : 16:57:46

If it's just taking the counts of the records returned, the below query would work.



Select 

(select Count(*) as LOS
from 
	hosp_nmrc a
INNER JOIN 
	hosp_rpt b
ON		a.RPT_REC_NUM = b.RPT_REC_NUM 
where 
	a.wksht_cd like 'S300001%'
and a.line_num = '01200' 
and	a.Clmn_num = '0600'
and b.prvdr_num In (490011 ,490017 , 490069 , 490094 , 490059 , 490136 , 490041 , 493301 , 490032 , 490119 , 490093 , 490046 , 490044 , 490007, 490057 , 420004, 490024) 
and b.fy_end_dt > '2007-12-31'
) /
(Select 
Count(*) as Discharge
from 
	hosp_nmrc a
INNER JOIN 
	hosp_rpt b
ON		a.RPT_REC_NUM = b.RPT_REC_NUM 
where 
	a.wksht_cd like 'S300001%'
and a.line_num = '01200' 
and	a.Clmn_num = '1500'
and b.prvdr_num In (490011 ,490017 , 490069 , 490094 , 490059 , 490136 , 490041 , 493301 , 490032 , 490119 , 490093 , 490046 , 490044 , 490007, 490057 , 420004, 490024) 
and b.fy_end_dt > '2007-12-31'
)  as MYAverage

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 17:11:22

quote:
Originally posted by Vinnie881

If it's just taking the counts of the records returned, the below query would work.

You could write it as ,

select 	TotalDays=sum(case when a.Clmn_num='0600' then 1 else 0 end) ,
       	Totaldischarges=sum(case when a.Clmn_num='1500' then 1 else 0 end) ,
       	Myaverage=sum(case when a.Clmn_num='0600' then 1.0 else 0.0 end) / sum(case when a.Clmn_num='1500' then 1 else 0 end) 
	
from 
	hosp_nmrc a
INNER JOIN 
	hosp_rpt b
ON		a.RPT_REC_NUM = b.RPT_REC_NUM 
where 
	a.wksht_cd like 'S300001%'
and a.line_num = '01200' 
--and	a.Clmn_num = '0600'/'1500'
and b.prvdr_num In (490011 ,490017 , 490069 , 490094 , 490059 , 490136 , 490041 , 493301 , 490032 , 490119 , 490093 , 490046 , 490044 , 490007, 490057 , 420004, 490024) 
and b.fy_end_dt > '2007-12-31'

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-18 : 17:22:56

quote:

Originally posted by Vinnie881

If it's just taking the counts of the records returned, the below query would work.



Select 

(select Count(*) as LOS
from 
	hosp_nmrc a
INNER JOIN 
	hosp_rpt b
ON		a.RPT_REC_NUM = b.RPT_REC_NUM 
where 
	a.wksht_cd like 'S300001%'
and a.line_num = '01200' 
and	a.Clmn_num = '0600'
and b.prvdr_num In (490011 ,490017 , 490069 , 490094 , 490059 , 490136 , 490041 , 493301 , 490032 , 490119 , 490093 , 490046 , 490044 , 490007, 490057 , 420004, 490024) 
and b.fy_end_dt > '2007-12-31'
) /
(Select 
Count(*) as Discharge
from 
	hosp_nmrc a
INNER JOIN 
	hosp_rpt b
ON		a.RPT_REC_NUM = b.RPT_REC_NUM 
where 
	a.wksht_cd like 'S300001%'
and a.line_num = '01200' 
and	a.Clmn_num = '1500'
and b.prvdr_num In (490011 ,490017 , 490069 , 490094 , 490059 , 490136 , 490041 , 493301 , 490032 , 490119 , 490093 , 490046 , 490044 , 490007, 490057 , 420004, 490024) 
and b.fy_end_dt > '2007-12-31'
)  as MYAverage

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

I am not sure I follow you. What do you mean by counts?

I have re-written the two queries to look like this:

--1. Average LOS from {total days/total discharges}
--Total Days (S-3 Part I, Line 12, Col. 6)
select hr.prvdr_num,
hn.Itm_val_num,
hn.wksht_cd,
hn.line_num,
hn.Clmn_num,
hr.fy_end_dt

from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM

where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '0600')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31')

order by hr.prvdr_num

--Total Discharges (S-3 Part I, Line 12, Col. 15)
select hr.prvdr_num,
hn.Itm_val_num,
hn.wksht_cd,
hn.line_num,
hn.Clmn_num,
hr.fy_end_dt

from hosp_nmrc hn INNER JOIN hosp_rpt hr
ON hn.RPT_REC_NUM = hr.RPT_REC_NUM

where (hn.wksht_cd like 'S300001%'and hn.line_num = '01200' and hn.Clmn_num = '0600')
and (hr.prvdr_num in (490011, 490017, 490069, 490094, 490059, 490136, 490041,
493301, 490032, 490119, 490093, 490046, 490044, 490007,
490057, 420004, 490024))
and (hr.fy_end_dt > '2007-12-31')

order by hr.prvdr_num

Now what I want to do is divide total days by provider from query 1 by total discharges in query 2. The value associated with each of these is in the itm_val_num column.

Does that clarify it or just make it more confusing!?!?!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 17:37:29

Can you format it. Use code tags. Also, what is different between query 1 and 2 ?? Can you highlight the difference ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources