Click here to Skip to main content
15,911,141 members
Please Sign up or sign in to vote.
4.33/5 (2 votes)
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:
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), 
              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?
Posted
Updated 30-Jun-14 5:04am
v2
Comments
George Jonsson 30-Jun-14 9:57am    
You get this output
2 1 80 8
1 1 100 9
Where is it going wrong?
What did you expect?
Jamie888 30-Jun-14 20:36pm    
yup, the result in SQL is correct but when i bind it to my report which is in MS VS 2010 which i have a table with 36 cells, each month contains 3 cells which are for TargetDate, ActualDate and Percentage. E.g. The result/output for August as above would be displayed on the row August whereas September output would be in row September inside only ONE table. But instead of ONE table, it is being separated into TWO table.
George Jonsson 30-Jun-14 22:09pm    
So the SQL code gives you the correct result, but the c#? code you use to present the data fails?
How can we know where it goes wrong when you only show the SQL code?
Jamie888 30-Jun-14 23:28pm    
the codes in C# looks like below:
this.xrTableCell6.BorderColor = System.Drawing.Color.Black;
this.xrTableCell6.Borders = ((DevExpress.XtraPrinting.BorderSide)((((DevExpress.XtraPrinting.BorderSide.Left | DevExpress.XtraPrinting.BorderSide.Top)
| DevExpress.XtraPrinting.BorderSide.Right)
| DevExpress.XtraPrinting.BorderSide.Bottom)));
this.xrTableCell6.DataBindings.AddRange(new DevExpress.XtraReports.UI.XRBinding[] {
new DevExpress.XtraReports.UI.XRBinding("Text", null, "GetCustomizationDeliveryStatusByDevNameByDate.JanTar")});
this.xrTableCell6.Name = "xrTableCell6";
this.xrTableCell6.StylePriority.UseBorderColor = false;
this.xrTableCell6.StylePriority.UseBorders = false;
this.xrTableCell6.Text = "xrTableCell6";
this.xrTableCell6.Weight = 0.89184069759234808D;

the above codes is used to bind the field with the value get from SQL. I just dragged a table adapter and make a datasource with it. There is no specific codes in the cs file. Almost all are drag and drop.
George Jonsson 30-Jun-14 23:42pm    
Sorry, I have never used DevExpress so I can't help you with that.
Found this link, though.
https://documentation.devexpress.com/#windowsforms/CustomDocument3455

Dint understood the question ( For using group by clause please see group by clause[^]
 
Share this answer
 
Comments
gggustafson 30-Jun-14 11:09am    
How is this a solution? Please delete this as a solution and put it under Have a Question or Comment? under the original question.
PLease elaborate your question/requirement as in given question can't explain your requirement
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900