Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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
 

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.........
Posted 30-Aug-12 23:00pm
Edited 31-Aug-12 2:38am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
v2

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

  Print Answers RSS
0 CPallini 365
1 OriginalGriff 275
2 Sergey Alexandrovich Kryukov 247
3 George Jonsson 130
4 ChauhanAjay 104
0 OriginalGriff 6,464
1 Sergey Alexandrovich Kryukov 5,915
2 CPallini 5,175
3 George Jonsson 3,559
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 31 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100