如何在特定行中显示数据？ [英] How to display data in a particular row?

问题描述

我有以下存储过程，它将显示UserName，CountDate in count，ActualDate in count和CompletePercentage。结果是六月份。我怎样才能显示6月连续记录，剩下的几个月我将设置为0.例如

输出

======== ==

1月0 0

2月0 0 0

3月0 0

4月0 0 0

5月0 0 0

6月1日80

。

。

。



i我不确定是否应该在c＃代码或sql中进行此分组。因此，我将使用devexpress将其转换为c＃中的报告。以下是我的代码：

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]
	-- Add the parameters for the stored procedure here
	@DEVNAME VARCHAR(MAX),
	@YEAR VARCHAR(MAX)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	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 
					'
	
	-- AND B.username = '+@DEVNAME+'	
    -- Insert statements for procedure here
	--declare @year varchar(30)
	--set @year = convert(date, GETDATE(), 103)

	--select month(@year)
    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.

推荐答案

如果我正确理解你的问题，我就会这样做。

我希望你可以使用它。

This is how I would go about it, if I understood your question correctly.
I hope you can use it.

DataTable dtMonthlyResult = new DataTable();
DataColumn dcMonth = dtMonthlyResult.Columns.Add("Month", typeof(string));
DataColumn dcTarget = dtMonthlyResult.Columns.Add("TargetDate", typeof(int));
DataColumn dcActual = dtMonthlyResult.Columns.Add("ActualDate", typeof(int));
DataColumn dcComplete = dtMonthlyResult.Columns.Add("Completeness", typeof(int));
dtMonthlyResult.PrimaryKey = new DataColumn[] { dcMonth };

添加月份和初始值

Add months and initial values

List<string> monthNames = DateTimeFormatInfo.CurrentInfo.MonthNames.Take(12).ToList();
foreach (string monthName in monthNames)
{
  dtMonthlyResult.Rows.Add(monthName.
    Substring(0, 3), 0, 0, 0);
}

从存储过程中获取数据

此部分您应该已经拥有代码

Get data from the stored procedure
This part you should have code for already

string month = "Jun";
int targetDate = 1;
int actualDate = 1;
int completeness = 80;

找到正确的行并更新

Find the correct row and update

DataRow drFound = dtMonthlyResult.Rows.Find(month);
if (drFound != null)
{
  drFound[dcTarget] = targetDate;
  drFound[dcActual] = actualDate;
  drFound[dcComplete] = completeness;
  dtMonthlyResult.AcceptChanges();
}

打印出表格

Print out the table

Console.WriteLine("Month\tTarget\tActual\tComplete");
Console.WriteLine("--------------------------------");
foreach (DataRow dr in dtMonthlyResult.Rows)
{
  Console.WriteLine("{0,3}\t{1,3}\t{2,3}\t{3,3}", 
  dr[dcMonth], dr[dcTarget], dr[dcActual], dr[dcComplete]);
}
Console.WriteLine("--------------------------------");

如果你想得到在sql本身的整个逻辑，然后用例。类似的东西 -



DECLARE @ COLVALUE1 INT，@ COLVALUE2 INT，@ COLVALUE3 INT

DECLARE @MONTH VARCHAR（50）='June'

SET @ COLVALUE1 =

CASE

WHEN @MONTH<> '六月'那么

0

ELSE

1

END

SET @ COLVALUE2 =

CASE

WHEN @MONTH<> '六月'那么

0

ELSE

1

END

SET @ COLVALUE3 =

CASE

WHEN @MONTH<> '六月'那么

0

ELSE

1

结束



INSERT INTO YOURTABLE（COL1，COL2，COL3）VALUES（@ COLVALUE1，@ COLVALUE2，@ COLVALUE3）

If you want to get the entire logic in the sql itself, then use case. Something like -

DECLARE @COLVALUE1 INT ,@COLVALUE2 INT,@COLVALUE3 INT
DECLARE @MONTH VARCHAR(50) = 'June'
SET @COLVALUE1 =
CASE
WHEN @MONTH <> 'June' THEN
0
ELSE
1
END
SET @COLVALUE2 =
CASE
WHEN @MONTH <> 'June' THEN
0
ELSE
1
END
SET @COLVALUE3 =
CASE
WHEN @MONTH <> 'June' THEN
0
ELSE
1
END

INSERT INTO YOURTABLE(COL1,COL2,COL3)VALUES(@COLVALUE1,@COLVALUE2,@COLVALUE3)

这篇关于如何在特定行中显示数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！