How to get All month and Group month wise

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

General SQL Server Forums

New to SQL Server Programming

How to get All month and Group month wise

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-06-26 : 20:28:41

My Query is

SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'MonthName', 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='LA2' AND FT.IsDelete='0' And Year(CC.CompletedDate)='2008' Group BY CC.CompletedDate

My Result Is
------------

CallOutCharge MonthName intMonth Year
------------- --------- ----------- -----------
300 Jun 6 2008
500 Jun 6 2008

But i need all the month of the year. i have all the Month in a table.
but not working

I need a below result

CallOutCharge MonthName IntMonth Year
-------------------- ------------- ----------- -----------
0 January 1 2008
0 February 2 2008
0 March 3 2008
0 April 4 2008
0 May 5 2008
800 June 6 2008
0 July 7 2008
0 August 8 2008
0 September 9 2008
0 October 10 2008
0 November 11 2008
0 December 12 2008

any onr tell me how write

Regards,
Prabu R

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 20:36:40

use LEFT JOIN

SELECT	CallOutCharge	= coalesce(c.CallOutCharge, 0),
	m.MonthName,
	m.MONTH,
	m.YEAR
FROM	Tbl_Month m
	LEFT JOIN
	(
		SELECT	SUM(FT.CallOutCharge) 			AS 'CallOutCharge', 
			CONVERT(CHAR(3), CC.CompletedDate,109) 	AS 'MonthName', 
			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	= 'LA2' 
		AND 	FT.IsDelete	= '0' 
		AND 	YEAR(CC.CompletedDate) = '2008' 
		GROUP BY CC.CompletedDate
	) c
	ON	m.YEAR	= c.YEAR
	AND	m.MONTH	= c.intMonth

KH
[spoiler]Time is always against us[/spoiler]

R.Prabu
Starting Member

33 Posts

Posted - 2008-06-26 : 20:44:39

I have Executed this query

but the Result is

CallOutCharge MONTH
------------- --------------------------------------------------
0 January
0 February
0 March
0 April
0 May
0 June
0 July
0 August
0 September
0 October
0 November
0 December

but the June month have a value

Regards,
Prabu R

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 20:48:44

pls post your month table's DDL, and sample data and also the exact query that you used

KH
[spoiler]Time is always against us[/spoiler]

R.Prabu
Starting Member

33 Posts

Posted - 2008-06-26 : 20:52:09

My Exact Query is

SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'MonthName', 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

My Month Table i have used

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HSSPMS_Tbl_Month](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Month] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_HSSPMS_Tbl_Month] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I need a result very urgent

Regards,
Prabu R

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 20:58:13

How about the query where you left join from HSSPMS_Tbl_Month to your existing query ?

KH
[spoiler]Time is always against us[/spoiler]

R.Prabu
Starting Member

33 Posts

Posted - 2008-06-27 : 11:17:59

Thank you its working fine

Regards,
Prabu R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources