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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply