ALTER PROCEDURE sp_Name ( parameter varchar(10), parameter varchar(8), parameter varchar(8) ) AS ;WITH CTE AS ( SELECT row_number() OVER(ORDER BY A.DateFull ASC)AS ROWID, convert (VARCHAR,convert(DATETIME,a.DateFull,112),103)AS DATE,A.WEEKDAYNAME, ISNULL(B.TOTAL_SCANNED,0) AS TOTAL_SCANNED, ISNULL(N.DELETE_INVOICES,0)AS SDELETE_INVOICES, ISNULL(E_INVOICE,0)AS e_INVOICE, ISNULL(OBLogged,0) as LOPENING, ISNULL(LOGGED_COUNT,0) AS LOGGED_COUNT, ISNULL(K.LDELETE_INVOICE,0)AS LDELETE_INVOICE, ISNULL(OBVerified,0) as VOPENING, ISNULL(VERIFIED_COUNT,0) AS VERIFIED_COUNT, ISNULL(L.VDELETE_INVOICE,0)AS VDELETE_INVOICE, ISNULL(PROCESSED_COUNT,0) AS PROCESSED_COUNT, ISNULL(PAID_COUNT,0) AS PAID_COUNT FROM ( SELECT DATEKEY ,DateFull, CHARACTERDATE,WEEKDAYNAME FROM L_DATE WHERE DateFull>='20120101' AND DateFull<=getdate() ) A LEFT OUTER JOIN (SELECT convert(VARCHAR,OBDate,112)AS OBDate,Location,OBLogged FROM Table1 WHERE Location=@LOC )S ON A.DateFull=S.OBDate LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) AS SCANNED_DATE, count(ITHE_LOGSRLNO) AS TOTAL_SCANNED FROM tagble2 WHERE LOC=@LOC AND ITHE_DELETE_FLAG IS NULL GROUP BY CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) )B ON A.DateFull=B.SCANNED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) AS SCANNED_DATE, count(ITHE_LOGSRLNO) AS DELETE_INVOICES FROM VU_ITRK_HEADER WHERE LOC=@LOC AND ITHE_DELETE_FLAG='D' GROUP BY CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) )N ON A.DateFull=N.SCANNED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,BUDAT,112) AS Einvoice_DATE,count(LOGSRL) AS E_INVOICE FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(BUDAT)=1 AND BUDAT>'20120101' AND left(LOGSRL,2)='80' AND LOC=@LOC GROUP BY BUDAT )E_IN ON A.DateFull=E_IN.Einvoice_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,BUDAT,112) AS LOGGED_DATE,count(LOGSRL) AS LOGGED_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(BUDAT)=1 AND BUDAT>'20120101' AND left(LOGSRL,2)<>'80' AND DELFLG='' AND LOC=@LOC GROUP BY BUDAT )C ON A.DateFull=C.LOGGED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,BUDAT,112) AS LOGGED_DATE,count(LOGSRL) AS LDELETE_INVOICE FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(BUDAT)=1 AND BUDAT>'20120101' AND left(LOGSRL,2)<>'80' AND DELFLG='X' AND LOC=@LOC GROUP BY BUDAT )K ON A.DateFull=K.LOGGED_DATE LEFT OUTER JOIN (SELECT convert(VARCHAR,OBDate,112)AS OBDate,Location,OBVerified FROM M_DASHBOARD_GALSS_OPENING_BALANCE WHERE Location=@LOC )R ON A.DateFull=R.OBDate LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,APRDDAT,112) AS VERIFIED_DATE,count(LOGSRL) AS VERIFIED_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(APRDDAT)=1 AND DELFLG='' AND LOC=@LOC AND USTATUS = 'PA' GROUP BY APRDDAT )D ON A.DateFull=D.VERIFIED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,APRDDAT,112) AS VERIFIED_DATE,count(LOGSRL) AS VDELETE_INVOICE FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(APRDDAT)=1 AND LOC=@LOC AND DELFLG='X' AND USTATUS = 'PA' GROUP BY APRDDAT )L ON A.DATEKEY=L.VERIFIED_DATE LEFT OUTER JOIN (SELECT convert(VARCHAR,OBDate,112)AS OBDate,Location,OBProcessed FROM M_DASHBOARD_GALSS_OPENING_BALANCE WHERE Location=@LOC )X ON A.DateFull=X.OBDate LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,PTDAT,112) AS PROCESSED_DATE,count(LOGSRL) AS PROCESSED_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(PTDAT)=1 AND LOC=@LOC GROUP BY PTDAT )E ON A.DateFull=E.PROCESSED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,AUGDT,112) AS PAID_DATE,count(LOGSRL) AS PAID_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(AUGDT)=1 AND LOC=@LOC GROUP BY AUGDT )F ON A.DateFull=F.PAID_DATE ) ,CTE1 AS ( SELECT ROWID=row_number() OVER(ORDER BY ROWID ASC),DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,LOPENING,LOGGED_COUNT,LDELETE_INVOICE, VOPENING,VERIFIED_COUNT,VDELETE_INVOICE,PROCESSED_COUNT,PAID_COUNT FROM CTE ) ,CTE2 AS ( SELECT ROWID,DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,CAST(LOPENING AS INT)AS LOPENING,LOGGED_COUNT,LDELETE_INVOICE, (CAST(LOPENING AS INT)+CAST(LOGGED_COUNT AS INT))AS TOTAL, ((CAST(TOTAL_SCANNED AS INT)+CAST(LOPENING AS INT))-CAST(LOGGED_COUNT AS INT))AS CLOSING, CAST(VOPENING AS INT) AS VOPENING,VERIFIED_COUNT,VDELETE_INVOICE, (CAST(VOPENING AS INT)+CAST(VERIFIED_COUNT AS INT))as VTOTAL, (((CAST(LOGGED_COUNT AS INT)+CAST(E_INVOICE AS INT))+CAST(VOPENING AS INT))-CAST(VERIFIED_COUNT AS INT))as VCLOSING, PROCESSED_COUNT,PAID_COUNT FROM CTE1 D WHERE ROWID = '1' UNION SELECT ROWID,DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,NULL AS LOPENING,LOGGED_COUNT,LDELETE_INVOICE,NULL AS TOTAL, NULL AS CLOSING, NULL AS VOPENING,VERIFIED_COUNT,VDELETE_INVOICE,NULL AS VTOTAL,NULL AS VCLOSING,PROCESSED_COUNT,PAID_COUNT FROM CTE1 D WHERE ROWID > '1' ) ,CTE3 AS ( SELECT ROWID,DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,LOPENING,LOGGED_COUNT,LDELETE_INVOICE, (CAST(LOPENING AS INT)+CAST(LOGGED_COUNT AS INT))AS TOTAL, ((CAST(TOTAL_SCANNED AS INT)+CAST(LOPENING AS INT))-CAST(LOGGED_COUNT AS INT))AS CLOSING, VOPENING,VERIFIED_COUNT,VDELETE_INVOICE, (CAST(VOPENING AS INT)+CAST(VERIFIED_COUNT AS INT))AS VTOTAL, (((CAST(LOGGED_COUNT AS INT)+CAST(E_INVOICE AS INT))+CAST(VOPENING AS INT))-CAST(VERIFIED_COUNT AS INT))AS VCLOSING, PROCESSED_COUNT,PAID_COUNT FROM CTE2 AS D WHERE ROWID = '1' UNION ALL SELECT D1.ROWID,D1.DATE,D1.WEEKDAYNAME,D1.TOTAL_SCANNED,D1.SDELETE_INVOICES,D1.e_INVOICE, D2.CLOSING AS LOPENING,D1.LOGGED_COUNT,D1.LDELETE_INVOICE, (ISNULL(D2.CLOSING,0)+ISNULL(D1.LOGGED_COUNT,0))AS TOTAL, ((ISNULL(D1.TOTAL_SCANNED,0)+ISNULL(D2.CLOSING,0))-ISNULL(D1.LOGGED_COUNT,0))AS CLOSING, D2.VCLOSING AS VOPENING,D1.VERIFIED_COUNT,D1.VDELETE_INVOICE, (ISNULL(D2.VCLOSING,0)+ISNULL(D1.VERIFIED_COUNT,0))AS VTOTAL, (((ISNULL(D1.LOGGED_COUNT,0)+ISNULL(D1.E_INVOICE,0))+ISNULL(D2.VCLOSING,0))-ISNULL(D1.VERIFIED_COUNT,0))AS VCLOSING, D1.PROCESSED_COUNT,D1.PAID_COUNT FROM CTE2 AS D1 CROSS JOIN CTE3 AS D2 WHERE D2.ROWID+1=D1.ROWID ) SELECT * FROM CTE3 WHERE convert(DATETIME,DATE,103) BETWEEN CONVERT(DATETIME,@FROMDATE,103) AND CONVERT(DATETIME,@TODATE,103) OPTION (MAXRECURSION 0) Result