CODEWITHSUNDEEP
sql-servert-sqlreporting-services
Mike S
Mike S

Reputation: 306

TSQL advanced, check rating of record count on the fly

I need a data set which shows Stations details and later I need to identify top 5 used for my report (SSRS),so my goal is to create new column top3 which will denote this and used as filter for chart. I realized that I can not do filter on aggreation in SSRS, so I do this in my SQL part.

I did this with CTE but feel that modern TSQL can do better then this with some line/running new features?? (I was away from it for awhile) Tx all. this is my solution Tx and happy holidays. I'm on MSServer 2016

; WITH cte AS (
SELECT 'St100'  St  union all select 'St101'  St  union all select 'St101'  St  union all select 'St101'  St  UNION all 
SELECT 'St104'  St  union all select 'St105'  St  union all select 'St106'  St  union all select 'St106'  St  UNION all 
SELECT 'St122'  St  union all select 'St122'  St  union all select 'St122'  St  union all select 'St122'  St  union all 
SELECT 'St108'  St  union all select 'St108'  St  union all select 'St108'  St   )


SELECT 
cte1.*, cte2.cc ,
CASE wHEN cte2.cc IS NULL THEN 'N' ELSE 'Y' END top3
FROM cte cte1
LEFT JOIN (SELECT TOP 3 St, COUNT(*) cc FROM cte GROUP BY St ORDER BY COUNT(*) desc ) cte2  ON cte2.St = cte1.St

ORDER BY 1

Upvotes: 0

Views: 55

Answers (1)

Chris Mack
Chris Mack

Reputation: 5208

Something like this will work:

; WITH cte AS (
SELECT 'St100'  St  union all select 'St101'  St  union all select 'St101'  St  union all select 'St101'  St  UNION all 
SELECT 'St104'  St  union all select 'St105'  St  union all select 'St106'  St  union all select 'St106'  St  UNION all 
SELECT 'St122'  St  union all select 'St122'  St  union all select 'St122'  St  union all select 'St122'  St  union all 
SELECT 'St108'  St  union all select 'St108'  St  union all select 'St108'  St   )

SELECT
    St
    , CAST(CASE WHEN SUM(CASE WHEN L <> St OR L IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY R) < 4 THEN 1 ELSE 0 END AS bit) Top3
FROM
    (
        SELECT
            St
            , R
            , LAG(St, 1, NULL) OVER (ORDER BY R) L
        FROM
            (
                SELECT
                    St
                    , ROW_NUMBER() OVER (ORDER BY C DESC, St) R
                FROM
                    (
                        SELECT
                            St
                            , COUNT(*) OVER (PARTITION BY St) C
                        FROM cte
                    ) Q
            ) Q
    ) Q
ORDER BY St

Upvotes: 1

Related Questions