USE[golivecode]
GO
/****** Object: StoredProcedure [dbo].[Sp_GetVerificationDashBoardDetails] Script Date: 08/24/2015 16:47:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE[dbo].[Sp_golivecode.com]@ gridID int, @PresentationDate varchar(max)
AS
BEGIN
if@ gridID = 2
begin
select distinct CONVERT(varchar, PresentationDate, 103) as[Date], COUNT( * ) AS[CREATEDBATCH],
COUNT(CASE WHEN BatchStatusID >= 7 THEN BatchStatusID ELSE null END) AS[COMPLETEAMOUNTFEED], (COUNT(CASE WHEN BatchStatusID >= 2 THEN BatchStatusID ELSE null END) - COUNT(CASE WHEN BatchStatusID >= 7 THEN batchNo ELSE null END)) AS[PENDINGAMOUNTFEED]
from OutwardBatch_East WHERE PresentationDate = convert(datetime, @PresentationDate, 103)
GROUP BY PresentationDate
end
else if@ gridID = 3
begin
select distinct CONVERT(varchar, PresentationDate, 103) as[Date], COUNT( * ) AS[CREATEDBATCH],
COUNT(CASE WHEN BatchStatusID >= 7 THEN BatchStatusID ELSE null END) AS[COMPLETEAMOUNTFEED], (COUNT(CASE WHEN BatchStatusID >= 2 THEN BatchStatusID ELSE null END) - COUNT(CASE WHEN BatchStatusID >= 7 THEN batchNo ELSE null END)) AS[PENDINGAMOUNTFEED]
from OutwardBatch_North WHERE PresentationDate = convert(datetime, @PresentationDate, 103)
GROUP BY PresentationDate
end
else if@ gridID = 4
begin
select distinct CONVERT(varchar, PresentationDate, 103) as[Date], COUNT( * ) AS[CREATEDBATCH],
COUNT(CASE WHEN BatchStatusID >= 7 THEN BatchStatusID ELSE null END) AS[COMPLETEAMOUNTFEED], (COUNT(CASE WHEN BatchStatusID >= 2 THEN BatchStatusID ELSE null END) - COUNT(CASE WHEN BatchStatusID >= 7 THEN batchNo ELSE null END)) AS[PENDINGAMOUNTFEED]
from OutwardBatch_South WHERE PresentationDate = convert(datetime, @PresentationDate, 103)
GROUP BY PresentationDate
end
else if@ gridID = 5
begin
select distinct CONVERT(varchar, PresentationDate, 103) as[Date], COUNT( * ) AS[CREATEDBATCH],
COUNT(CASE WHEN BatchStatusID >= 7 THEN BatchStatusID ELSE null END) AS[COMPLETEAMOUNTFEED], (COUNT(CASE WHEN BatchStatusID >= 2 THEN BatchStatusID ELSE null END) - COUNT(CASE WHEN BatchStatusID >= 7 THEN batchNo ELSE null END)) AS[PENDINGAMOUNTFEED]
from OutwardBatch_West WHERE PresentationDate = convert(datetime, @PresentationDate, 103)
GROUP BY PresentationDate
end
ENDThis code snippet for Create JSON Table using Store Procedure in SQL.
