Hi, i have the following SQL codes which will return me the following output:
TargetDate ActualDate Percentage Month
======================================
2 1 80 8
1 1 100 9
below are my SQL codes:
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),
Months VARCHAR(100))
INSERT [#ALLTB]
SELECT a.issueid,
a.userid,
b.firstname,
c.TargetDate,
d.ActualDate,
e.CompleteRate,
SUBSTRING(c.TargetDate, 5, 1) AS Month
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
SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate,
SUM(CONVERT(INT, ActualDate)) AS ActualDate,
SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate))
AS CompletePercentage, Months
FROM #FORMULA
GROUP BY Months
ok, then i bind all the desired value into my XtraReport which is in DevExpress. But the output on the table of the report is not the same as i expected. The table should be displaying the output according to the month. E.g. 2 1 80 for August and 1 1 100 for September inside ONE table. But now it is displaying in TWO tables. How can i make it to display in only ONE table?