CODEWITHSUNDEEP
sqlsql-server
deepti
deepti

Reputation: 729

getting error in Case statement SQL getting NULL

Iam using SQL to get query in report builder, i have put case statement in sql query but i am getting NULL in month col

    select * into #temp from
(select count(*) [Total Clients], li.title,
 SUBSTRING(li.title,CHARINDEX('_',li.title,CHARINDEX('_',li.title)+1)+1,2) as 'month1',
 CASE WHEN 'month1' = '01' THEN 'Jan'
            WHEN 'month1' = '02' THEN 'Feb'
            WHEN 'month1' = '03' THEN 'Mar'
            WHEN 'month1' = '04' THEN 'Apr'
            WHEN 'month1' = '05' THEN 'May'
            WHEN 'month1' = '06' THEN 'June'
            WHEN 'month1' = '07' THEN 'Jul'
            WHEN 'month1' = '08' THEN 'Aug'
            WHEN 'month1' = '09' THEN 'Sep'
            WHEN 'month1' = '10' THEN 'Oct'
            WHEN 'month1' = '11' THEN 'Nov'
            WHEN 'month1' = '12' THEN 'Dec'
            END As [Month],
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()) as varchar) +  '' + '%'
--or Title like '%SUG_' + '' + CAST(year(getdate())-1 as varchar) +  '' + '%'
group by li.title,li.CI_UniqueID,coll.name,coll.CollectionID) as e

this is query output iam getting is NULL value in month col

please find picture

enter image description here

can you please help why iam getting NULL in month COL

Upvotes: 0

Views: 240

Answers (3)

kc2018
kc2018

Reputation: 1460

You can replace the CASE statement with this:

 case when SUBSTRING(li.title,CHARINDEX('_',li.title,CHARINDEX('_',li.title)+1)+1,2) between '01' and '12' 
      then convert( char(3),cast( '2018/'+ SUBSTRING(li.title,CHARINDEX('_',li.title,CHARINDEX('_',li.title)+1)+1,2) +'/01' as datetime),0)  
 end as [MONTH],

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Your case expression would be something 

(case (SUBSTRING(li.title,CHARINDEX('_',li.title,CHARINDEX('_',li.title)+1)+1,2))
    when '01' then 'Jan'
    ... 
 end)

You are using alise as column name

Upvotes: 0

an33sh
an33sh

Reputation: 1124

You can try something like this, 

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,2) 
            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],
li.CI_UniqueID,coll.name,coll.CollectionID ....

Or handle it in an outer query

Upvotes: 3

Related Questions