Month Display - 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)

Month Display

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-06-05 : 18:25:42

This my procedure

DECLARE @Months table
(MonthName varchar(20)

)

INSERT INTO @Months
SELECT 'Jan' UNION ALL SELECT 'Feb' UNION ALL SELECT 'Mar' UNION ALL SELECT 'Apr' UNION ALL
SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL SELECT 'Jul' UNION ALL SELECT 'Aug' UNION ALL
SELECT 'Sep' UNION ALL SELECT 'Oct' UNION ALL SELECT 'Nov' UNION ALL SELECT 'Dec'

SELECT m.MonthName,ISNULL(CallOutCharge,0) As 'CallOutCharge'
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

This my Result

MonthName CallOutCharge intMonth
-------------------- ------------- -----------
Jan 0 NULL
Feb 0 NULL
Mar 0 NULL
Apr 0 NULL
May 767 5
Jun 0 NULL
Jul 0 NULL
Aug 0 NULL
Sep 0 NULL
Oct 0 NULL
Nov 0 NULL
Dec 0 NULL

But i need to display

MonthName CallOutCharge intMonth
-------------------- ------------- -----------
Jan 0 1
Feb 0 2
Mar 0 3
Apr 0 4
May 767 5
Jun 0 6
Jul 0 7
Aug 0 8
Sep 0 9
Oct 0 10
Nov 0 11
Dec 0 12

Any one help me

Regards,
Prabu R

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-06-06 : 00:20:42

I'm assuming column three in your select statement is intMonth. :)
If that is the case, does your derived table return rows?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 00:27:57

The probelm is you dont have data for other months coming from your derived table t. So the left join will return null for that field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources