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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-24 : 13:25:13

Hey guys

Sorry to bother you so much lately

I have one final question for the day

Aim – To produce a one liner by FDMSaccountno to determine whether that particular fdmsaccountno had clientline in the following years

Currently my query is grouping by month_end_date

Which produces the following results

FDMSAccountNo month_end_date 2011Clientline 2012Clientline 2013Clientline
878713861887 2010-12-01 0 0 0
878713861887 2011-01-01 1 0 0
878713861887 2011-02-01 1 0 0
878713861887 2011-03-01 1 0 0
878713861887 2011-04-01 1 0 0
878713861887 2011-05-01 1 0 0
878713861887 2011-06-01 1 0 0
878713861887 2011-07-01 1 0 0
878713861887 2011-08-01 1 0 0
878713861887 2011-09-01 1 0 0
878713861887 2011-10-01 1 0 0
878713861887 2011-11-01 1 0 0
878713861887 2011-12-01 1 0 0
878713861887 2012-01-01 0 1 0
878713861887 2012-02-01 0 1 0
878713861887 2012-03-01 0 1 0
878713861887 2012-04-01 0 1 0
878713861887 2012-05-01 0 1 0
878713861887 2012-06-01 0 1 0
878713861887 2012-07-01 0 1 0
878713861887 2012-08-01 0 1 0
878713861887 2012-09-01 0 1 0
878713861887 2012-10-01 0 1 0
878713861887 2012-11-01 0 1 0
878713861887 2012-12-01 0 1 0
878713861887 2013-01-01 0 0 1
878713861887 2013-02-01 0 0 1
878713861887 2013-03-01 0 0 1
878713861887 2013-04-01 0 0 1
878713861887 2013-05-01 0 0 1
878713861887 2013-06-01 0 0 1
878713861887 2013-07-01 0 0 1
878713861887 2013-08-01 0 0 1
878713861887 2013-09-01 0 0 1

Required results

FDMSAccountNo 2011Clientline 2012Clientline 2013Clientline
878713861887 Yes Yes Yes

My query is

/****** Script for SelectTopNRows command from SSMS ******/
SELECT
[FDMSAccountNo],
month_end_date,
case when year(month_end_date) = '2011' then 1 else 0 end as [2011Clientline] ,
case when year(month_end_date) = '2012' then 1 else 0 end as [2012Clientline] ,
case when year(month_end_date) = '2013' then 1 else 0 end as [2013Clientline]
FROM [FDMS].[dbo].[Fact_Fee_History]
where Fee_Sequence_Number = '32r'
and FDMSAccountNo = '878713861887'
group by FDMSAccountNo,Month_end_date

looking forward to your hel

regards
D

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-10-24 : 14:19:40

Maybe this:


select fdmsaccountno
      ,month_end_date
      ,case when sum(case when year(month_end_date)='2011' then 1 else 0 end)=0 then 'No' else 'Yes' end as 2011clientline
      ,case when sum(case when year(month_end_date)='2012' then 1 else 0 end)=0 then 'No' else 'Yes' end as 2012clientline
      ,case when sum(case when year(month_end_date)='2013' then 1 else 0 end)=0 then 'No' else 'Yes' end as 2013clientline
  from fdms.dbo.fact_fee_history
 where fee_sequence_number='32r'
   and fdmsaccountno='878713861887'
   and year(month_end_date) in ('2011','2012','2013')
 group by fdmsaccountno
         ,month_end_date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-24 : 15:29:05

[code]SELECT FDMSAccountNo,
month_end_date,
MAX(CASE WHEN month_end_date > '20110101' AND month_end_date <= '20120101' THEN 'Yes' ELSE 'No' END) AS [2011Clientline],
MAX(CASE WHEN month_end_date > '20120101' AND month_end_date <= '20130101' THEN 'Yes' ELSE 'No' END) AS [2012Clientline],
MAX(CASE WHEN month_end_date > '20130101' AND month_end_date <= '20140101' THEN 'Yes' ELSE 'No' END) AS [2013Clientline]
FROM dbo.Fact_Fee_History
WHERE Fee_Sequence_Number = '32r'
AND FDMSAccountNo = '878713861887'
GROUP BY FDMSAccountNo,
Month_end_date;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 02:32:13

i think this is enough


SELECT		FDMSAccountNo,
		MAX(CASE WHEN month_end_date > '20110101' AND month_end_date <= '20120101' THEN 'Yes' ELSE 'No' END) AS [2011Clientline],
		MAX(CASE WHEN month_end_date > '20120101' AND month_end_date <= '20130101' THEN 'Yes' ELSE 'No' END) AS [2012Clientline],
		MAX(CASE WHEN month_end_date > '20130101' AND month_end_date <= '20140101' THEN 'Yes' ELSE 'No' END) AS [2013Clientline]
FROM		dbo.Fact_Fee_History
WHERE		Fee_Sequence_Number = '32r'
		AND FDMSAccountNo = '878713861887'
GROUP BY	FDMSAccountNo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources