Hi, i have the following stored procedure which will display the UserName, TargetDate in count, ActualDate in count and the CompletePercentage. Results get are in June. How can i display the records i get into a row June and the rest of the months i will set to 0. E.g.
Output
==========
Jan 0 0 0
Feb 0 0 0
Mar 0 0 0
Apr 0 0 0
May 0 0 0
Jun 1 1 80
.
.
.
i am not sure whether i should do this grouping in c# code or sql. Whereby i will make this into a report in c# using devexpress. Below are my codes:
ALTER PROCEDURE [dbo].[GetCustomizationDeliveryStatusByDevNameByDate]
@DEVNAME VARCHAR(MAX),
@YEAR VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(MAX)
DECLARE @SQL3 NVARCHAR(MAX)
DECLARE @SQL4 NVARCHAR(MAX)
SELECT @SQL = ' CREATE TABLE #TargetDateTblx(IssueID VARCHAR(100), TargetDate VARCHAR(100))
INSERT [#TargetDateTblx]
SELECT issueid, [fielddata] AS TargetDate
FROM rndbug.dbo.gemini_customfielddata
WHERE customfieldid = 215
CREATE TABLE #ActualDateTblx(IssueID VARCHAR(100), ActualDate VARCHAR(100))
INSERT [#ActualDateTblx]
SELECT issueid, [fielddata] AS ActualDate
FROM rndbug.dbo.gemini_customfielddata
WHERE customfieldid = 217
CREATE TABLE #CompleteRateTblx(IssueID VARCHAR(100), CompleteRate VARCHAR(100))
INSERT [#CompleteRateTblx]
SELECT issueid, [fielddata] AS CompleteRate
FROM rndbug.dbo.gemini_customfielddata
WHERE customfieldid = 234
CREATE TABLE #ALLTB(IssueID VARCHAR(100), UserID VARCHAR(100), UserName VARCHAR(100), TargetDate VARCHAR(100), ActualDate VARCHAR(100), CompleteRate VARCHAR(100))
INSERT [#ALLTB]
SELECT a.issueid,a.userid,b.username,c.TargetDate,d.ActualDate,e.CompleteRate
FROM dbo.gemini_issueresources A, dbo.gemini_users B, #TargetDateTblx c, #ActualDateTblx d, #CompleteRateTblx e
WHERE a.userid = b.userid AND a.issueid = c.IssueID AND a.issueid = d.IssueID AND a.issueid = e.IssueID
'
SET @SQL2 = 'CREATE TABLE #FORMULA(UserID VARCHAR(100), TargetDate VARCHAR(100), ActualDate VARCHAR(100), CompleteRate VARCHAR(100))
INSERT [#FORMULA]
SELECT UserID, COUNT(TargetDate) AS TargetDate, COUNT(ActualDate) AS ActualDate, SUM(CONVERT(INT, CompleteRate)) AS CompleteRate FROM #ALLTB
GROUP BY UserID
SELECT B.username, F.TargetDate, F.ActualDate, (CONVERT(INT, F.CompleteRate)/F.TargetDate) AS CompletePercentage
FROM #FORMULA F, dbo.gemini_users B, #ALLTB A
WHERE B.username IN ('+@DEVNAME+') AND F.UserID = B.UserID AND F.UserID = A.UserID
'
SET @SQL3 = 'CREATE TABLE #FORMULA(UserID VARCHAR(100), TargetDate VARCHAR(100), ActualDate VARCHAR(100), CompleteRate VARCHAR(100))
INSERT [#FORMULA]
SELECT UserID, COUNT(TargetDate) AS TargetDate, COUNT(ActualDate) AS ActualDate, SUM(CONVERT(INT, CompleteRate)) AS CompleteRate FROM #ALLTB
GROUP BY UserID
SELECT B.username, F.TargetDate, F.ActualDate, (CONVERT(INT, F.CompleteRate)/F.TargetDate) AS CompletePercentage
FROM #FORMULA F, dbo.gemini_users B, #ALLTB A
WHERE F.UserID = B.UserID AND F.UserID = A.UserID AND SUBSTRING(A.TargetDate, 7, 4) = '+@YEAR+'
'
SET @SQL4 = 'CREATE TABLE #FORMULA(TargetDate VARCHAR(100), ActualDate VARCHAR(100), CompleteRate VARCHAR(100))
INSERT [#FORMULA]
SELECT COUNT(TargetDate) AS TargetDate, COUNT(ActualDate) AS ActualDate, SUM(CONVERT(INT, CompleteRate)) AS CompleteRate FROM #ALLTB
SELECT TargetDate, ActualDate, (CONVERT(INT, CompleteRate)/TargetDate) AS CompletePercentage
FROM #FORMULA
'
IF(@DEVNAME = '' AND @YEAR = '')
SELECT @SQL = @SQL + @SQL4
ELSE IF(@DEVNAME != '' AND @YEAR != '')
SELECT @SQL = @SQL + ' AND B.username IN ('+@DEVNAME+')' + @SQL2 + ' AND SUBSTRING(A.TargetDate, 7, 4) = '+@YEAR+''
ELSE IF(@DEVNAME != '' AND @YEAR = '')
SELECT @SQL = @SQL + ' AND B.username IN ('+@DEVNAME+')' + @SQL2
ELSE
SELECT @SQL = @SQL + @SQL3
SELECT @SQL = @SQL
EXEC sp_executesql @SQL
END
sorry it looks messy. Any helps would be appreciated.