SP检索月份详细信息时出错 [英] Error in SP to retrieve Month Details
本文介绍了SP检索月份详细信息时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想在图中显示我的项目状态.
我在网页中使用曲线图".
我正在考虑将 X轴作为Date ，将 Y轴作为Percentage .
这是我的表格格式
批准:
Hi,
I want to display my Project status in a Graph.
I''m using Curve Graph in my webpage.
I''m considering the X-axis as Date, and Y-axis as Percentage.
This is my Table format
Approval:
month year Date percentage Cumulative_percentage
NULL NULL NULL 0 0
6 2011 2011-06-30 23:59:59.000 0.774743366259926 0.774743366259926
7 2011 2011-07-31 23:59:59.000 3.87371683129963 4.64846019755956
8 2011 2011-08-31 23:59:59.000 5.42320356381949 10.071663761379
9 2011 2011-09-30 23:59:59.000 0.774743366259926 10.846407127639
10 2011 2011-10-31 23:59:59.000 13.1706372264187 24.0170443540577
11 2011 2011-11-30 23:59:59.000 4.2610885144296 28.2781328684873
12 2011 2011-12-31 23:59:59.000 9.68429207824908 37.9624249467364
1 2012 2012-01-31 23:59:59.000 6.58531861320937 44.5477435599458
2 2012 2012-02-29 23:59:59.000 19.7559558396281 64.3036993995739
3 2012 2012-03-31 23:59:59.000 4.64846019755956 68.9521595971335
4 2012 2012-04-30 23:59:59.000 8.13480534572923 77.0869649428627
5 2012 2012-05-31 23:59:59.000 0.968429207824908 78.0553941506876
6 2012 2012-06-30 23:59:59.000 11.6211504938989 89.6765446445865
7 2012 2012-07-31 23:59:59.000 2.32423009877978 92.0007747433663
8 2012 2012-08-31 23:59:59.000 6.83711020724385 98.8378849506101
12 2012 2012-12-31 23:59:59.000 1.16211504938989 100
这是我要在图表中显示的表格.
现在，我的疑问是记录中是否缺少任何月份数据，我们在该月份中输入"0".
例如:在上表中，u观察最后两个记录.
因为我们错过了第9、10、11个月的数据，因此我们将 0 s
我的最终输出是这样的
This is my Table To display in Graph.
Now my doubt is if any month data is missing in the records,we put "0" in that month.
For ex: in above table, u observe the last two records.
In that we miss 9,10,11th month data in that we put 0''s
my final output is like this
8 2012 2012-08-31 6.83711 98.8378
9 2012 2012-09-30 0 98.8378
10 2012 2012-10-31 0 98.8378
11 2012 2012-11-30 0 98.8378
12 2012 2012-12-31 1.1621 100
为此我写了这样的存储过程，
for that i wrote Stored Procedure like this,
ALTER PROCEDURE [dbo].[lntsp_Drawing_Not_Released_Approved_Details]
(
@Trans_Type VARCHAR(15),
@Proj_Code VARCHAR(15),
@Disc_Code VARCHAR(15)=NULL,
@To_Date DATETIME=NULL,
@Dwg_Type BIT=NULL,
@SE_Code INT=NULL,
@SED_Code INT=NULL
)
AS
BEGIN
DECLARE @Drawing_Schedule TABLE
(
Doc_Id INT,
Doc_No VARCHAR(50),
Title VARCHAR(250),
Weightage FLOAT,
Approval_Date DATETIME,
Release_Date DATETIME,
Disc_Code VARCHAR(15),
Dwg_Category_Code VARCHAR(15),
Dwg_Size_Code VARCHAR(15)
)
INSERT INTO @Drawing_Schedule
SELECT Doc_Id, Doc_No, Title, Weightage, Approval_Date,
Release_Date, Disc_Code, Dwg_Category_Code, Dwg_Size_Code
FROM PTS_H_Drawing_Schedule DS
WHERE DS.Proj_Code=@Proj_Code
AND DS.Approval_Tag='Y'
END
IF @Trans_Type='SCurve'
BEGIN
DECLARE @Total_Weightage_Approval FLOAT
SELECT @Total_Weightage_Approval=SUM(ISNULL(Weightage, 1))
FROM @Drawing_Schedule
WHERE Dwg_Category_Code='DWG000002'
DECLARE @Planned_For_Approval_Date TABLE
(
Doc_Id INT,
For_Date DATETIME,
Weightage FLOAT
)
INSERT INTO @Planned_For_Approval_Date
select 0 as Doc_Id, Proj_Start_Date, 0 as Weightage
from PTS_H_Projects where Proj_Code=@Proj_Code
INSERT INTO @Planned_For_Approval_Date
SELECT ISNULL(DR.Doc_Id, DS.Doc_Id),
ISNULL(MIN(DR.Approval_Date), MIN(DS.Approval_Date)), ISNULL(Weightage, 1)
FROM @Drawing_Schedule DS
LEFT OUTER JOIN PTS_T_Drawing_Revision DR
ON DS.Doc_Id=DR.Doc_Id --AND DR.SNo=1
WHERE Ds.Dwg_Category_Code='DWG000002'
GROUP BY DR.Doc_Id, DS.Doc_Id, Weightage
DECLARE @Planned_For_Approval_Month TABLE
(
SNo INT IDENTITY(1,1),
For_Month INT,
For_Year INT,
DateC DATETIME ,
Weightage FLOAT,
Percentage FLOAT
)
INSERT INTO @Planned_For_Approval_Month
(For_Month, For_Year,DateC, Weightage, Percentage)
SELECT MONTH(For_Date) AS For_Month,
YEAR(For_Date) AS For_Year,
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,For_Date)+1,0)),
SUM(Weightage) AS Weightage,
SUM(Weightage*100/@Total_Weightage_Approval) AS Percentage
FROM @Planned_For_Approval_Date
GROUP BY MONTH(For_Date), YEAR(For_Date) ,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,For_Date)+1,0))
ORDER BY For_Year, For_Month
--PLANNED_FOR_APPROVAL
SELECT P2.For_Month, P2.For_Year,P2.DateC, P2.Percentage,
SUM(P1.Weightage*100/@Total_Weightage_Approval) AS Cumulative_Percentage
FROM
@Planned_For_Approval_Month P1
JOIN @Planned_For_Approval_Month P2
ON P1.SNo <= P2.SNo
GROUP BY P2.For_Month, P2.For_Year, P2.Percentage,P2.DateC
ORDER BY For_Year, For_Month
END
谁能帮助我做到这一点.
在此先感谢.........
can anyone help me to do this.
Thanks in advance.........
推荐答案
，您可以在@Planned_For_Approval_Date表中生成所有日期的表，然后插入缺少的日期.这可以用于最终加入并获得值为0的缺失日期...
声明@maxdate日期
声明@mindate日期
从@Planned_For_Approval_Date中选择@ maxdate = MAX(For_Date)，@ mindate = MIN(For_Date)
而@ mindate< = @ maxdate
开始
SELECT @ mindate = DATEADD(s，-1，DATEADD(mm，DATEDIFF(m，0，@ mindate)+2,0))
如果不存在(从@Planned_For_Approval_Date中选择1，其中For_Date = @ mindate)
INSERT INTO @Planned_For_Approval_Date
()
值
结束
you could generate a table of all dates in your @Planned_For_Approval_Date table and insert the missing dates. This could be used for final join and get missing dates with 0 value...
declare @maxdate date
declare @mindate date
select @maxdate=MAX(For_Date),@mindate=MIN(For_Date) from @Planned_For_Approval_Date
while @mindate<=@maxdate
begin
SELECT @mindate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@mindate)+2,0))
if not exists (select 1 from @Planned_For_Approval_Date where For_Date=@mindate)
INSERT INTO @Planned_For_Approval_Date
()
values
end
这篇关于SP检索月份详细信息时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
