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.
Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-09-10 : 08:37:34
|
Hey Guys Hope your well, can you advise me what i am doing wrong I trying to create a basic case statement This is my query Declare @FROM varchar(10)Declare @TO varchar(10)set @FROM = (select dateadd(MM,-1,max([Month_end_date]))from [FDMS].[dbo].Fact_Fee_History)set @TO = (select (max([Month_end_date]))from Fact_Fee_History)-- Products identifying Merchants who Have Clientline and Virtual Terminals --- SELECT FDMSAccountNo, (max([Month_end_date])) as [Month_end_date],case WHEN [Fee_Sequence_Number] = '32R' THEN 'Yes' ELSE 'No' END as Clientline,case WHEN [Fee_Sequence_Number] = '6JT' THEN 'Yes' ELSE 'No' END as [VT] FROM Fact_Fee_Historywhere (month_end_date BETWEEN @FROM and @TO)and fdmsaccountno = '878755671889'and Fee_Sequence_Number in ('32r', '6jt')group by fdmsaccountno,[Fee_Sequence_Number]order by FDMSAccountNo desc But its returning the following results FDMSAccountNo Month_end_date Clientline VT878755671889 2013-08-01 Yes No878755671889 2013-08-01 No YesCan anyone explain why ?? |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-09-10 : 08:45:28
|
For FDMSAccountNo = 878755671889 and Month_end_date = 2013-08-01 there are both sequence numbers '32r', '6jt'....If you wish to have sinle line for each combination FDMSAccountNo you can query as follows:SELECT FDMSAccountNo, max([Month_end_date]) as [Month_end_date],MAX(case WHEN [Fee_Sequence_Number] = '32R' THEN 'Yes' ELSE 'No' END) as Clientline,MAX(case WHEN [Fee_Sequence_Number] = '6JT' THEN 'Yes' ELSE 'No' END) as [VT] FROM Fact_Fee_Historywhere (month_end_date BETWEEN @FROM and @TO)and fdmsaccountno = '878755671889'and Fee_Sequence_Number in ('32r', '6jt')group by fdmsaccountnoorder by FDMSAccountNo desc--Chandu |
|
|
|
|
|
|
|