Cacher is the code snippet organizer for pro developers

select s_Name SiteName, s_addr_zone [Division],EventId,a.McuId,ItemName,CASE WHEN AtTime > [OpenedOn] THEN FORMAT(DATEADD(n,-2,[OpenedOn]),'dd-MMM-yyyy HH:mm:ss') ELSE  FORMAT(AtTime,'dd-MMM-yyyy HH:mm:ss') END [AtTime],Msg,EventType,FORMAT(OpenedOn,'dd-MMM-yyyy HH:mm:ss')[OpenedOn],ISNULL(ClosedBy,'') [Closed],ISNULL(FORMAT(ClosedOn,'dd-MMM-yyyy HH:mm:ss'),'')[ClosedOn],ISNULL(ClosingReason,'') [ClosingReason] , CASE WHEN TktIsOpen = 0 THEN 'Close' ELSE 'Open' END [TktStatus], TktIsOpen from lbtevents a , lbtsiteitemsmaster b, lbtsitemas c  where EventCode ='xCD' AND EventType ='Low'   AND CAST(openedOn as date) between '08-June-2018' AND '22-JUNE-2018' AND b.qstatus = 1 and b.i_Status = 1 AND a.GroupNo = b.GroupNo AND a.UnitNo = b.UnitNo AND a.McuId = b.McuId AND c.mcuid =a.Mcuid AND c.qstatus =1 and c.s_status = 1 AND c.BankCode = '25'


SELECT FORMAT(cast([OpenedOn] as date),'dd-MMM-yyyy') [OpenedOn],COUNT(*) [Total Count],
ISNULL(SUM(CASE WHEN TktIsOpen = 0 THEN 1 END ),'0') [ClosedCount],
ISNULL(SUM(CASE WHEN tktisopen = 1 THEN 1 END ),'0') [OpenedCount] 
FROM (
select s_Name SiteName, s_addr_zone [Division],EventId,a.McuId,ItemName,FORMAT(AtTime,'dd-MMM-yyyy HH:mm:ss')[AtTime],
Msg,EventType,FORMAT(OpenedOn,'dd-MMM-yyyy HH:mm:ss')[OpenedOn],ISNULL(ClosedBy,'') [Closed],
ISNULL(FORMAT(ClosedOn,'dd-MMM-yyyy HH:mm:ss'),'')[ClosedOn],ISNULL(ClosingReason,'') [ClosingReason] , 
CASE WHEN TktIsOpen = 0 THEN 'Close' ELSE 'Open' END [TktStatus], TktIsOpen from lbtevents a , lbtsiteitemsmaster b, lbtsitemas c  
where EventCode ='714' AND EventType ='Low'  AND  CAST(OpenedOn AS date) >= '2018-11-11' AND   
 b.qstatus = 1 and b.i_Status = 1 AND a.GroupNo = b.GroupNo AND a.UnitNo = b.UnitNo AND 
 a.McuId = b.McuId AND c.mcuid =a.Mcuid AND c.qstatus =1 and c.s_status = 1 AND c.BankCode = '25'
)a Group By cast([OpenedOn] as date)