June 19, 2017 at 11:54 am
Hello, I have some data I'm trying to summarize and I'd like to have this in a queryable view instead of getting the data in a two step process... how do I get all this logic into one query to be placed into a view? I am not super versed in doing nested queries and combining logic.
--First Query:
select issue_number, line_number, type_code, case when alt_type_code in ('NONE','') then '00' else alt_type_code end as alt_type_code, case when ratio is not null and block_type = 'CMI' then (ratio * data_value_1) else data_value_1 end as data_value_1,
case when ratio is not null and block_type = 'CMI' then (ratio * data_value_2) else data_value_2 end as data_value_2
into #temp_data
from table_name
--Second Query:
select issue_num, type_code, alt_type_code, sum(data_value_1) as data_value_1_sum, sum(data_value_2) as data_value_2_sum
from #temp_data
group by issue_num, type_code, alt_type_code
I basically need to group and sum the data but I have to do this odd multiplication based on another field in the data first ... and I tried doing it in one query but it wouldn't sum properly and it wouldn't group the values right.
June 19, 2017 at 12:07 pm
You do not need a temp table.
This should work
CREATE VIEW dbo.YourViewName
WITH SCHEMABINDING
AS
SELECT issue_number,
type_code,
CASE
WHEN alt_type_code IN (
'NONE',
''
)
THEN '00'
ELSE alt_type_code
END AS alt_type_code,
SUM(CASE
WHEN ratio IS NOT NULL
AND block_type = 'CMI'
THEN (ratio * data_value_1)
ELSE data_value_1
END) AS data_value_1_sum,
SUM(CASE
WHEN ratio IS NOT NULL
AND block_type = 'CMI'
THEN (ratio * data_value_2)
ELSE data_value_2
END) AS data_value_2_Sum
FROM table_name
GROUP BY issue_num,
type_code,
CASE
WHEN alt_type_code IN (
'NONE',
''
)
THEN '00'
ELSE alt_type_code
END
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 19, 2017 at 2:28 pm
Here's a reduced version of your query. It should be equivalent, but test it as I might have made a mistake due to the lack of sample data.
SELECT issue_number,
type_code,
CASE WHEN alt_type_code in ('NONE','')
THEN '00'
ELSE alt_type_code END AS alt_type_code,
SUM( alt.ratio * data_value_1) AS data_value_1,
SUM( alt.ratio * data_value_2) AS data_value_2
FROM table_name
CROSS APPLY (SELECT CASE WHEN ratio IS NOT NULL AND block_type = 'CMI' THEN ratio ELSE 1 END AS ratio) alt
GROUP BY issue_number,
type_code,
CASE WHEN alt_type_code in ('NONE','')
THEN '00'
ELSE alt_type_code END;
June 19, 2017 at 4:03 pm
Thank you thank you! I believe that solves my problem!! Much appreciated.
June 20, 2017 at 11:23 am
Do you understand why it worked? And why Luis's code worked and mine didn't?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 20, 2017 at 12:21 pm
I think this would work.
SELECT SUBQ.issue_number, SUBQ.type_code, SUBQ.alt_type_code,
SUM(SUBQ.data_value_1) AS data_value_1_sum,
SUM(SUBQ.data_value_2) AS data_value_2_sum
FROM (SELECT issue_number, line_number, type_code,
CASEWHEN alt_type_code IN ('NONE', '')
THEN '00'
ELSE
alt_type_code
END AS alt_type_code,
CASEWHEN ratio IS NOT NULL AND block_type = 'CMI'
THEN (ratio * data_value_1)
ELSE
data_value_1
END AS data_value_1,
CASEWHEN ratio IS NOT NULL AND block_type = 'CMI'
THEN (ratio * data_value_2)
ELSE
data_value_2
END AS data_value_2
FROM table_name) AS SUBQ
GROUP BY SUBQ.issue_number, SUBQ.type_code, SUBQ.alt_type_code
Many ways around the barn.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 20, 2017 at 1:12 pm
Michael L John - Tuesday, June 20, 2017 11:23 AMDo you understand why it worked? And why Luis's code worked and mine didn't?
Yes, I had tried to do it similarly to how you did it at one point and it didn't group the records properly cause I was trying to do two different groupings at once.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy