Count of SLA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count of SLA

Author

Topic

vicky_chune
Starting Member

1 Post

Posted - 2012-09-18 : 13:16:15

Hi Team,

I am trying to find out the count of a particular Decision using the below query however when i run the below query i get 2 rows
one with the count of the 'Y' Decision and other with the count of 'N' decision but i just want one row with the total count of decision and total count of 'Y' decision.

can someone help me with this.

with cte as (
select top 100 B4NOrder_ID,Retailer_Code, order_Time,first_Desc_Time ,convert (varchar(19),datediff(MINUTE,order_Time, first_Desc_Time),108) As Total_Time,
case when TBL_MSTR_B4NCREDIT.Retailer_Code not like 'BFN%' and datediff(MINUTE,order_Time, first_Desc_Time) <= 5 then 'Y'
when TBL_MSTR_B4NCREDIT.Retailer_Code like 'BFN%' and datediff(MINUTE,order_Time, first_Desc_Time) <= 15 then 'Y'
else 'N'
end As SLA_Decision
from TBL_CREDIT
where Order_Status in ('Retailer Confirmed','Dispatched')
and order_Date = '2012-01-09 00:00:00'
)

Select count(SLA_Decision) As Total_Orders,
CASE WHEN EXISTS (SELECT * FROM cte
WHERE SLA_Decision = 'Y')
THEN COUNT (SLA_Decision)
END
from cte
group by cte.SLA_Decision

Vicky.Chune

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:22:39

make last select as


..
Select count(SLA_Decision) As Total_Orders
from cte

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-18 : 14:02:57

Last selection section should be something like:


select count(SLA_Decision) as Total_Orders
      ,sum(case when SLA_Decision='Y' then 1 else 0 end) as Total_Decision_Y
  from cte

Also, the first select section referes to table TBL_MSTR_B4NCREDIT, and the table used in from section is TBL_CREDIT. Maybe I overlooked something, but this makes no sence to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources