CODEWITHSUNDEEP
sqlsql-servert-sql
deepti
deepti

Reputation: 729

need to sort sql data

iam using sql to build report in report builder, SQL query i am using is as below

    select count(*) [Total Clients], li.title,
 SUBSTRING(li.title,CHARINDEX('_',li.title,CHARINDEX('_',li.title)+1)+1,2) as month1,
 CASE SUBSTRING(li.title,CHARINDEX('_',li.title,CHARINDEX('_',li.title)+1)+1,5) 
            WHEN '01' THEN 'Jan'
            WHEN '02' THEN 'Feb'
            WHEN '03' THEN 'Mar'
            WHEN '04' THEN 'Apr'
            WHEN '05' THEN 'May'
            WHEN '06' THEN 'June'
            WHEN '07' THEN 'Jul'
            WHEN '08' THEN 'Aug'
            WHEN '09' THEN 'Sep'
            WHEN '10' THEN 'Oct'
            WHEN '11' THEN 'Nov'
            WHEN '12' THEN 'Dec'
            END As [Month],
            SUBSTRING(li.title,CHARINDEX('_',li.title)+1,4),
li.CI_UniqueID,coll.name,coll.CollectionID,
SUM (CASE WHEN ucs.status=3 or ucs.status=1  then 1 ELSE 0 END ) as 'Installed / Not Applicable',
sum( case When ucs.status=2 Then 1 ELSE 0 END ) as 'Required',
sum( case When ucs.status=0 Then 1 ELSE 0 END ) as 'Unknown',
round((CAST(SUM (CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) as float)/count(*) )*100,2) as 'Compliant%',
    round((CAST(count(case when ucs.status not in('3','1') THEN '*' end) as float)/count(*))*100,2) as 'NotCompliant%'
    From v_Update_ComplianceStatusAll UCS
inner join v_r_system sys on ucs.resourceid=sys.resourceid
inner join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
inner join v_collection coll on coll.collectionid=fcm.collectionid
inner join v_AuthListInfo LI on ucs.ci_id=li.ci_id
where coll.CollectionID like '%SMS00001%' and
--title like '%SUG%' 
 Title like '%P1%' and
Title like '%SUG_' + '' + CAST(year(getdate())-1 as varchar) +  '' + '%'
--or Title like '%SUG_' + '' + CAST(year(getdate())-1 as varchar) +  '' + '%'
group by li.title,li.CI_UniqueID,coll.name,coll.CollectionID

data displayed from query

+---------------+---------------------------+--------+-------+------------------+---------------------------------------------------------------------------------------------+----------------------+---------------+-----------------------------+----------+---------------------+---------------+
| Total Clients |           title           | month1 | Month | (No column name) |                                         CI_UniqueID                                         |         name         |  CollectionID |  Installed / Not Applicable | Required |  Unknown Compliant% | NotCompliant% |
+---------------+---------------------------+--------+-------+------------------+---------------------------------------------------------------------------------------------+----------------------+---------------+-----------------------------+----------+---------------------+---------------+
|            30 | SUG_2017_01_P1_RFC3456    |     01 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_E586ED3A-EDD5-4145-98FB-C0B373F7E4CA | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_01-03_P1_RFC2781 |     01 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_5AEB5495-8913-4541-B29E-7D55C16E6B68 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_03_P1_RFC2781    |     03 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_ED07143C-B357-454E-B02E-7D81AEE40869 | All Systems SMS00001 |             0 |                           0 |       30 |                   0 |           100 |
|            30 | SUG_2017_04_P1_RFC3103    |     04 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_2722FDDB-6D6B-407F-A0CE-063372571E82 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_04-05_P1_RFC2781 |     04 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_10D742F8-FB4B-4E19-BF05-5210C790F440 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_06_P1_RFC3123    |     06 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_3063A272-0DF9-4033-94E2-C52AF1CFD4BC | All Systems SMS00001 |            25 |                           1 |        4 |               83.33 |         16.67 |
|            30 | SUG_2017_10_P1_RFC3103    |     10 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_9C3F338B-E8BA-4AB5-8ECF-1EA8729825DA | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_11_P1_RFC3103    |     11 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_B42D69FC-2564-4542-8D5B-F5348A4080FF | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_12_P1_RFC3103    |     12 | NULL  |             2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_5C3AD217-7747-4BAC-AD06-3851014BCB94 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
+---------------+---------------------------+--------+-------+------------------+---------------------------------------------------------------------------------------------+----------------------+---------------+-----------------------------+----------+---------------------+---------------+

titles shown as 

SUG_2017_01_P1_RFC3456
SUG_2017_01-03_P1_RFC2781
SUG_2017_03_P1_RFC2781

i need to show only 01-03 months from these 3 rows i need on 01-03 SUG_2017_01-03_P1_RFC2781 and i need to discard jan row SUG_2017_01_P1_RFC3456 and march row means SUG_2017_03_P1_RFC2781

that applies to all rows i need

i need combine rows and not individual rows.

new data

+---------------+---------------------------+--------+-------+------+---------------------------------------------------------------------------------------------+----------------------+---------------+-----------------------------+----------+---------------------+---------------+
| Total Clients |           title           | month1 | Month | Year |                                         CI_UniqueID                                         |         name         |  CollectionID |  Installed / Not Applicable | Required |  Unknown Compliant% | NotCompliant% |
+---------------+---------------------------+--------+-------+------+---------------------------------------------------------------------------------------------+----------------------+---------------+-----------------------------+----------+---------------------+---------------+
|            30 | SUG_2017_01_P1_RFC3456    |     01 | Jan   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_E586ED3A-EDD5-4145-98FB-C0B373F7E4CA | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_01-03_P1_RFC2781 |     01 | Jan   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_5AEB5495-8913-4541-B29E-7D55C16E6B68 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_03_P1_RFC2781    |     03 | Mar   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_ED07143C-B357-454E-B02E-7D81AEE40869 | All Systems SMS00001 |            11 |                          15 |        4 |               36.67 |         63.33 |
|            30 | SUG_2017_04_P1_RFC3103    |     04 | Apr   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_2722FDDB-6D6B-407F-A0CE-063372571E82 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_04-05_P1_RFC2781 |     04 | Apr   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_10D742F8-FB4B-4E19-BF05-5210C790F440 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_06_P1_RFC3123    |     06 | June  | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_3063A272-0DF9-4033-94E2-C52AF1CFD4BC | All Systems SMS00001 |            25 |                           1 |        4 |               83.33 |         16.67 |
|            30 | SUG_2017_10_P1_RFC3103    |     10 | Oct   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_9C3F338B-E8BA-4AB5-8ECF-1EA8729825DA | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_11_P1_RFC3103    |     11 | Nov   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_B42D69FC-2564-4542-8D5B-F5348A4080FF | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2017_12_P1_RFC3103    |     12 | Dec   | 2017 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_5C3AD217-7747-4BAC-AD06-3851014BCB94 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2018_01_P1_RFC3103    |     01 | Jan   | 2018 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_A49E2378-BCB6-40BE-BE84-735CCFBEE43F | All Systems SMS00001 |            25 |                           1 |        4 |               83.33 |         16.67 |
|            30 | SUG_2018_02_P1_RFC3118    |     02 | Feb   | 2018 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_E6CBD108-2B1C-4C94-85F8-57174BEC34C4 | All Systems SMS00001 |            25 |                           1 |        4 |               83.33 |         16.67 |
|            30 | SUG_2018_03_P1_RFC3128    |     03 | Mar   | 2018 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_6AA69C57-9532-4ED1-BA40-1540C840BD69 | All Systems SMS00001 |            26 |                           0 |        4 |               86.67 |         13.33 |
|            30 | SUG_2018_05_P1_RFC3104    |     05 | May   | 2018 |  ScopeId_A66804AF-F55C-40D6-8AAF-82CF49CC1E5B/AuthList_D3A10469-5DE5-4998-9C59-877D3BC7225F | All Systems SMS00001 |            12 |                          14 |        4 |                  40 |            60 |
+---------------+---------------------------+--------+-------+------+---------------------------------------------------------------------------------------------+----------------------+---------------+-----------------------------+----------+---------------------+---------------+

Upvotes: 0

Views: 97

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following query

WITH commonQueryCTE AS(
  -- your query is here
),
paramQueryCTE AS(
  SELECT
    *,
    SUBSTRING(Title,5,4) Y,
    SUBSTRING(Title,10,2) M1,
    IIF(SUBSTRING(Title,12,1)='-',SUBSTRING(Title,13,2),NULL) M2
  FROM commonQueryCTE
)
SELECT *
FROM paramQueryCTE c
WHERE NOT EXISTS(SELECT * FROM paramQueryCTE p WHERE c.Y=p.Y AND c.M1 BETWEEN p.M1 AND p.M2 AND p.M2 IS NOT NULL)
   OR c.M2 IS NOT NULL
ORDER BY c.Y,c.M1

SQL Fiddle Demo - http://www.sqlfiddle.com/#!18/bf900/1

As variant you also can use SELECT ... INTO #TempTable FROM ... and after that use #TempTable in that query.

Upvotes: 1

Related Questions