grouping problem - 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

General SQL Server Forums

New to SQL Server Programming

grouping problem

Author

Topic

FData
Starting Member

24 Posts

Posted - 2012-08-31 : 04:21:46

Hey guys

This is an other stupid question, but one thats puzzling me

This is my query

SELECT
FDMSAccountNo,
[Month_end_date] as hst_date_processed,
Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
When Fee_Code = '00Y' Then 'Refund Transaction Charge'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'Y' then 'Other repeatable'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'N' then 'Other Non repeatable'
Else salesMI_Group1 end as description,
d.repeat_msc,
Sum([Retail_amount]) As Amount
Into #MscCount
FROM FDMS.dbo.Fact_Fee_History f
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code
where Month_end_date >= dateadd(mm,datediff(mm,0,getdate())-12,0)
and Month_end_date < dateadd(mm,datediff(mm,0,getdate()),0)
and FDMSAccountNo ='878000039882'
Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1, d.Repeat_MSC

select * from #MscCount

--drop table #MscCount, #msc

SELECT [FDMSAccountNo],hst_date_processed,
case when description ='MSC' then SUM ([Amount])else 0 end as [MSC_2012],
case when description ='PCI' then SUM ([Amount])else 0 end as [PCI_2012],
case when description ='Refund Transaction Charge' then SUM ([Amount])else 0 end as [RefundTransactionCharge_2012],
case when description ='Other repeatable' then SUM ([Amount])else 0 end as [Other_repeatable2012],
case when description ='Other Non repeatable' then SUM ([Amount])else 0 end as [Other_Non_repeatable2012]
Into #Msc
FROM #MscCount
where FDMSAccountNo = '878000039882'
group by [FDMSAccountNo],hst_date_processed,description

Its returning the following results

FDMSAccountNo hst_date_processed MSC_2012 PCI_2012 878000039882 01/08/2011 31.96 0
878000039882 01/08/2011 0 3.99

Basically i need to sum, all the hst_date_processed by month together , so i achieve a one liner.

so the new layout would be

FDMSAccountNo hst_date_processed MSC_2012 PCI_2012
878000039882 01/08/2011 31.96 3.99

any ideas ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 04:53:07

[code][Month_end_date] as hst_date_processe[/code]

is the [Month_end_date] always the date of the last day of the month ? like Mar 31, Apr 30, May 31 etc ?

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

FData
Starting Member

24 Posts

Posted - 2012-08-31 : 04:59:50

Hi Khtan

Month_end_data is always Beginning of the month

Eg
01/08/2011
01/09/2011
01/10/2011
01/11/2011
01/12/2011
01/01/2012
01/02/2012
01/03/2012
01/04/2012
01/05/2012
01/06/2012
01/07/2012

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 05:03:47

remove the description from the GROUP BY


group by [FDMSAccountNo],hst_date_processed, description

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

FData
Starting Member

24 Posts

Posted - 2012-08-31 : 05:09:14

hi khtan

when i remove the description i get an error msg saying

Msg 8120, Level 16, State 1, Line 2
Column '#MscCount.description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 05:16:21

sorry .. try this


SELECT [FDMSAccountNo],hst_date_processed,
	SUM ( case when description ='MSC' then  ([Amount])else 0 end ) as [MSC_2012],
	SUM ( case when description ='PCI' then  ([Amount])else 0 end ) as [PCI_2012],
	SUM ( case when description ='Refund Transaction Charge' then  ([Amount])else 0 end ) as [RefundTransactionCharge_2012],
	SUM ( case when description ='Other repeatable' then  ([Amount])else 0 end ) as [Other_repeatable2012],
	SUM ( case when description ='Other Non repeatable' then  ([Amount])else 0 end ) as [Other_Non_repeatable2012]
Into 	#Msc
FROM 	#MscCount
where 	FDMSAccountNo = '878000039882'
group by [FDMSAccountNo],hst_date_processed

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

FData
Starting Member

24 Posts

Posted - 2012-08-31 : 05:20:28

you are a star *
thank you very much x

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-31 : 05:30:42

actually you don't need the intermediate temp table. You can use derived table or CTE

SELECT 	[FDMSAccountNo],
	hst_date_processed,
	SUM ( case when description ='MSC' then ([Amount])else 0 end ) as [MSC_2012],
	SUM ( case when description ='PCI' then ([Amount])else 0 end ) as [PCI_2012],
	SUM ( case when description ='Refund Transaction Charge' then ([Amount])else 0 end ) as [RefundTransactionCharge_2012],
	SUM ( case when description ='Other repeatable' then ([Amount])else 0 end ) as [Other_repeatable2012],
	SUM ( case when description ='Other Non repeatable' then ([Amount])else 0 end ) as [Other_Non_repeatable2012]
FROM
(
	SELECT	FDMSAccountNo,
		[Month_end_date] as hst_date_processed,
		Case 	when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
			When Fee_Code = '00Y' Then 'Refund Transaction Charge'
			When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'Y' then 'Other repeatable'
			When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'N' then 'Other Non repeatable'
			Else salesMI_Group1 end as description,
			d.repeat_msc,
		Sum([Retail_amount]) As Amount
	FROM 	FDMS.dbo.Fact_Fee_History f
		INNER JOIN FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code
	where 	Month_end_date >= dateadd(mm,datediff(mm,0,getdate())-12,0)
	and 	Month_end_date < dateadd(mm,datediff(mm,0,getdate()),0)
	and 	FDMSAccountNo ='878000039882'
	Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1, d.Repeat_MSC
) d
GROUP BY [FDMSAccountNo],
	hst_date_processed

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

FData
Starting Member

24 Posts

Posted - 2012-08-31 : 05:38:12

hi Khtan

Thank you for your update,
i have created a new topic called inner join confusion, If you can take a glance at that, you will be able to tell me whether your post above would be Applicable In that scenario

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources