Click here to Skip to main content
13,051,447 members (50,020 online)
Rate this:
 
Please Sign up or sign in to vote.
Hi,

month year    Date                    percentage        Total
7   2012    2012-07-31 23:59:59.000 2.28876597367919    90.5969864581347
8   2012    2012-08-31 23:59:59.000 8.25863055502575    98.8556170131604
12  2012    2012-12-31 23:59:59.000 1.1443829868396     100


If there is no data for the month of sept , oct , nov then we put 0's for all the columns.

How to do this can any one help me to do this,


Thank's in advance....
Posted 31-Aug-12 2:29am
Updated 31-Aug-12 3:18am
v3
Comments
ssd_coolguy 31-Aug-12 8:33am
   
can you elaborate more?
pranathis012 31-Aug-12 8:36am
   
i want to display monthly report by using line Graph.
For that i display all the months data if there is no data for any month it cosider as zero.But it's not like that that's want i'm asking
__TR__ 31-Aug-12 8:40am
   
The data you have shown, is it the result of your query or is it the table data ?
pranathis012 31-Aug-12 8:47am
   
query only
__TR__ 31-Aug-12 9:19am
   
can you post your tables used in the query and some sample data if possible?
And also the query you wrote.
pranathis012 1-Sep-12 4:52am
   
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
ryanb31 31-Aug-12 8:42am
   
Depends on how your SQL currently is. Does it currently return a record if there is no data for that month? If so, use COALESCE. If not, you can join to a derived table or even UNION using SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY somefield) AS eachMonth FROM AnyTableThatHasAtLeast12Records.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You simply create table with all the months (simply January to December). You can use that to select all month, even when there is no value for that month.

SELECT m.*, t.* FROM Months m, MyTable t
  WHERE t.year = 2012 
    AND m.month BETWEEN 6 AND 12
    AND t.month = m.month;

You might want to adjust it to your needs but hopefully the basic idea is clear.

Good luck!
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

A said you first need to get/create the data for all the rows you need to include in the result set since rows cannot be created from nothing. After that you would have an outer join to join the months to existing rows in your data.

While you can create a table containing the months, another way is to generate date on-the-fly. For example have a look at the following: Using Table-Valued Functions in SQL Server[^]

When using TVF as a 'table', your query could look something like:
SELECT ...
FROM GenerateMonths(date1, date2) a
LEFT OUTER JOIN SomeTable b
ON a.SomeColumn = b.SomeColumn
WHERE ...
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

create a table,

monthList
month 
1    
2     
3   
.
.
.
12    


Now use below query
@year as varchar(4)

select a.month, b.year, isnull(b.date,convert(datetime,@year + '-' + a.month + '-01')), isnull(per,0),isnull(total,0) 
 
from monthlist a
left join yourTbl b on a.month=b.month 

Happy Coding!
:)
  Permalink  
v2
Comments
pranathis012 3-Sep-12 0:04am
   
for ex in a year i want more than 1 year data means how to do this.
like 2010,2011,2012,2013,2014....
aarti meswania 3-Sep-12 1:52am
   
refer updated solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 3 Sep 2012
Copyright © CodeProject, 1999-2017
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