Need Help to write a query

Question

Post reply

Need Help to write a query

Govardhana

SSC Enthusiast

Points: 115
More actions
July 24, 2020 at 6:34 am

#3774327

I have a table like this
I need to write a query to get the following output
Columns
Tenor, 0, 0.5, 1, 1.5, 2, 2.5, 3, ....... , 10 (0 to 10)
Sum of Count of Col A and Col B where Col values are >= the abs values of the columns

For 0 count all the abs values in Col A and Col B group by Tenor where abs(value) > 0
For 0.5 count all the abs values in Col A and Col B group by Tenor where abs(value) > 0.5

and so on. Output should be like the above table

July 28, 2020 at 6:58 am

This was removed by the editor as SPAM
July 29, 2020 at 7:05 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

If I understand correctly, this should work for you

SELECT Tenor,
       COUNT(CASE WHEN ABS(ColA) > 0 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 0 THEN ColB END) AS [0],
       COUNT(CASE WHEN ABS(ColA) > 0.5 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 0.5 THEN ColB END) AS [0.5],
       COUNT(CASE WHEN ABS(ColA) > 1.0 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 1.0 THEN ColB END) AS [1.0],
       COUNT(CASE WHEN ABS(ColA) > 1.5 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 1.5 THEN ColB END) AS [1.5],
       COUNT(CASE WHEN ABS(ColA) > 2.0 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 2.0 THEN ColB END) AS [2.0],
       COUNT(CASE WHEN ABS(ColA) > 2.5 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 2.5 THEN ColB END) AS [2.5]
FROM myTable
GROUP BY Tenor
ORDER BY Tenor;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Govardhana SSC Enthusiast Points: 115 More actions · Answer 2

Yes the result is correct. Can we make the column more dynamic? For now we need till 10, tomorrow if we need till 20 we may have to write 20 more lines of code.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

To make this dynamic have a look here

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Govardhana SSC Enthusiast Points: 115 More actions · Answer 4

Got it Thanks.

DECLARE @query_string as NVARCHAR(MAX),

@cols as NVARCHAR(MAX)

with data

as (

select top 21 (row_number() over(order by (select null))-1)*0.5 as rnk

from master..spt_values

)

,cols_data

as(

select ',count(case when abs(a.col_a)>='+cast(rnk as varchar(10)) +' then 1 end) + '

+'count(case when abs(a.col_b)>='+cast(rnk as varchar(10)) +' then 1 end) as ['+cast(rnk as varchar(10))+'] '+ CHAR(10) as col

from data

)

select @cols = string_agg(col, ' ')

from cols_data

SET @query_string = 'SELECT a.col ' +CHAR(10)

+ @cols

+' from t a '+CHAR(10)

+'GROUP BY a.col'

execute(@query_string)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

>> from master..spt_values <<

Referencing master db like that is a horrible idea, just stop doing it. It's very easy instead to create your own inline table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".