get the all month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get the all month

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-05-19 : 10:22:25

I am having Joined Two Tables and i get sum of "calloutCharges" and get the result in the below procedure

Result
--------
Jan 100
Feb 200
March 300
May 200
Aug 100

But I need result is in the following Format

Jan 100
Feb 200
March 300
Apr 0
May 200
jun 0
jul 0
Aug 100
Sep 0
Oct 0
nov 0
Dec 0

This my Query

SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'Month', month(CC.CompletedDate) As 'intMonth', Year(CC.CompletedDate) As 'Year' FROM HSSPMS_Tbl_Callcentre_Compliants AS CC
INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FT ON FT.ContractorCode = CC.ContractorCode And CC.FaultCleared='1'
WHERE FT.CreatedBy=@OwnerId AND FT.IsDelete='0' And Year(CC.CompletedDate)=@Year Group BY CC.CompletedDate

any one Knows help me

Regards,
Prabu R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 10:46:04

Does HSSPMS_Tbl_Callcentre_Compliants contain data for all months? If yes, you just need LEFT JOIN instead of inner join.If not, use a temporary table to store months and join onto that

DECLARE @Months table
(MonthName varchar(20)
)

INSERT INTO @Months
SELECT 'Jan'
UNION ALL
SELECT 'Feb'
UNION ALL
....
SELECT 'Dec'

SELECT m.MonthName,ISNULL(CallOutCharge,0)
FROM @Months m
LEFT JOIN (SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'Month', month(CC.CompletedDate) As 'intMonth', Year(CC.CompletedDate) As 'Year' FROM HSSPMS_Tbl_Callcentre_Compliants AS CC 
INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FT ON FT.ContractorCode = CC.ContractorCode And CC.FaultCleared='1' 
WHERE FT.CreatedBy=@OwnerId AND FT.IsDelete='0' And Year(CC.CompletedDate)=@Year Group BY CC.CompletedDate)t
ON t.month=m.MonthName

R.Prabu
Starting Member

33 Posts

Posted - 2008-05-22 : 15:16:42

Thank you

Regards,
Prabu R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources