September 28, 2011 at 2:11 pm
Below are my two queries that I am working with. In Query A I get all the chargecodes that have Revenue associated with them and the right amount of revenue and volume. Aprox5300 rows... In B I get all chargecodes some with and some without revenue. aprox 12188 rows... I need the data in A to show up in the correct spot in B but still have all the chargecodes in B. I hope this makes sense...
--Query A
SELECT
Distinct(rtrim(TPB900.chg_cod_ext_id)) as ChargeCode,
isnull(TPB900.chg_desc,'Unknown'),
isnull(TST150.dpt_ext_id,'Unknown'),
TST150.nrv_cd,
d.cpt4_ext_id,
sum(Isnull(TST150.pst_unt_no,0)),
sum(Isnull(TST150.tot_rev_at,0))
FROM paragon_rpt.dbo.TST150_STATS_DAILY TST150
Join paragon_rpt.dbo.TSM030_ORGANIZATION
On TST150.rev_org_int_id = paragon_rpt.dbo.TSM030_ORGANIZATION.org_int_id
Join paragon_rpt.dbo.TPB900_CHG_CODE_MST TPB900
On TST150.chg_cod_int_id = TPB900.chg_cod_int_id
join paragon_rpt.dbo.TSM180_MST_COD_DTL c
On TST150.chg_type_int_id = c.cod_dtl_int_id
and c.cod_dtl_ext_id in ('CHARGE', 'ROOM/BED CHARGE', 'EXPLOSION')
left Join paragon_rpt.dbo.TSM911_CPT4_REF d
ON TPB900.cpt4_int_id = d.cpt4_int_id
where TST150.pst_ts >= '09-1-2010 0:0:0.000' and TST150.pst_ts < '09-01-2011 0:0:0.000'
Group By TPB900.chg_cod_ext_id,
TST150.dpt_ext_id,
TST150.nrv_cd,
d.cpt4_ext_id,
TPB900.chg_desc
order by ChargeCode
--Query B
select
Distinct(rtrim(TPB900.chg_cod_ext_id)) as ChargeCode,
isnull(TPB900.chg_desc,'Unknown'),
isnull(TPB900.gl_account_id,'Unknown'),
isnull(TST1501.volume,0),
isnull(TST1501.revenue,0),
isnull(TST1501.nrv,'NA')nrv ,
isnull(d.cpt4_ext_id,'Unknown')
from paragon_rpt.dbo.TPB900_CHG_CODE_MST TPB900 (nolock)
left join (select --isnull(TST150.dpt_ext_id,'Unknown')dpt,
TST150.chg_cod_int_id chrgint,
TST150.nrv_cd nrv,
sum(isnull(TST150.pst_unt_no,0)) volume,
sum(isnull(TST150.tot_rev_at,0)) revenue
from paragon_rpt.dbo.TST150_STATS_DAILY TST150(nolock)
Join paragon_rpt.dbo.TSM030_ORGANIZATION
On TST150.rev_org_int_id = paragon_rpt.dbo.TSM030_ORGANIZATION.org_int_id
join paragon_rpt.dbo.TSM180_MST_COD_DTL c
On TST150.chg_type_int_id = c.cod_dtl_int_id
and c.cod_dtl_ext_id in ('CHARGE', 'ROOM/BED CHARGE', 'EXPLOSION')
where TST150.pst_ts >= '09-01-2010 00:00:00.000' and TST150.pst_ts < '09-01-2011 00:00:00.000'
group by --TST150.dpt_ext_id,
TST150.chg_cod_int_id
, TST150.nrv_cd
) TST1501
on TPB900.chg_cod_int_id = TST1501.chrgint--chg_cod_int_id
Join TSM995_REF_MST_FAC ref
on ref.ref_int_id = TPB900.chg_cod_int_id
left Join paragon_rpt.dbo.TSM911_CPT4_REF d (nolock)
ON TPB900.cpt4_int_id = d.cpt4_int_id
--where TST150.pst_ts >= '09-1-2010 0:0:0.000' and TST150.pst_ts <= '08-31-2011 0:0:0.000'
where ref.row_sta_cd = 'A' and TPB900.row_sta_cd = 'A'
and ref.org_int_id = '1'
Group By TPB900.chg_cod_ext_id,
TPB900.gl_account_id,
TST1501.volume,
TST1501.revenue,
TST1501.nrv,
d.cpt4_ext_id,
TPB900.chg_desc
order by ChargeCode
September 28, 2011 at 2:43 pm
Assuming I understand what you're asking, make query A into a sub-select that acts as a table for query B. I can't vouch for performance, but that's how I'd start. You can just left-join on to based on what you said about some values matching and some not.
By the way, you've got select DISTINCT and GROUP BY, two aggregation operations at the same time. Are you sure you need both?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2011 at 2:54 pm
Grant,
Thanks for the advice but we did try that before I posted and it doesn't work. what is going on in I get one sum in query A(correctone) and another in B(incorrect one) B is less than A's total. I need them to match. I hope this helps more.
also thanks for the pointing out the Distinct and the group by. I will certainly change it. I know i dont need distinct but even removing it doesn't give me what i need. Thanks again for your reply.
September 29, 2011 at 8:00 am
We have created a table with all the data in it. now we need to write a query that says if charge code appears twice then give me the code with the Max volume else give me the charge code that only appears once. any advice on how to write this? Thanks for all the help given.
September 29, 2011 at 10:20 am
basshole8117 (9/29/2011)
We have created a table with all the data in it. now we need to write a query that says if charge code appears twice then give me the code with the Max volume else give me the charge code that only appears once. any advice on how to write this? Thanks for all the help given.
You pretty much stated your solution in your question.
SELECT
max(Distinct(rtrim(TPB900.chg_cod_ext_id))) as ChargeCode,
isnull(TPB900.chg_desc,'Unknown'),
isnull(TST150.dpt_ext_id,'Unknown'),
TST150.nrv_cd,
d.cpt4_ext_id,
sum(Isnull(TST150.pst_unt_no,0)),
sum(Isnull(TST150.tot_rev_at,0))
from ...
group by
isnull(TPB900.chg_desc,'Unknown'),
isnull(TST150.dpt_ext_id,'Unknown'),
TST150.nrv_cd,
d.cpt4_ext_id
Something like should work for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2011 at 2:56 am
Can you give us some ddl and sample data so we can verify what we're doing? This code is very hard to read with all those generated names and aliases. The problem in itself doesn't seem to hard, it's just the way you've written/generated the code that makes it hard to get right.
My first guess would be to write this as a crosstab. See the link in my footer to Jeff's article for more info on crosstabs.
September 30, 2011 at 4:01 am
If your main concern is 'Result of query A is NOT matching with query B' and you are confident that the results should match, please execute the queries without NOLOCK hint.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply