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-19 : 05:22:49
|
HI guys I need some help / Advice Aim – Any FDMSaccountno, who has the Fee_Squence_Number = ‘42B” After the month_end_date ‘2013-02-01’ change the Fee_Squence_Number to ‘42c’If however there is a Fee_Squence_Number ‘42c’ & ‘42b’after month_end_date ‘2013-02-01’ then add the two retail_amount together and change fee_sequence_number to “42C”my query is SELECT TOP 1000 [FDMSAccountNo],[Fee_Sequence_Number],Description,[Month_end_date],[Retail_amount]into #test FROM [FDMS].[dbo].[Fact_Fee_History]INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Codewhere [Fee_Sequence_Number] in ('42B','42C')and Month_end_date between '2013-01-01' and '2013-08-01'and FDMSAccountNo = '878212486889'update #testset Fee_Sequence_Number = '42C'where Fee_Sequence_Number = '42B'and Month_end_date > '2013-02-01'Looking for any help available |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-09-19 : 05:58:48
|
If Fee_Sequence_Number column is in [FDMS].[dbo].[Fact_Fee_History] table.... update [FDMS].[dbo].[Fact_Fee_History] set Fee_Sequence_Number = CASE WHEN Fee_Sequence_Number = '42B' AND Month_end_date > '2013-02-01' THEN '42C' ELSE Fee_Sequence_Number ENDFROM [FDMS].[dbo].[Fact_Fee_History] INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Codewhere [Fee_Sequence_Number] in ('42B','42C')and Month_end_date between '2013-01-01' and '2013-08-01'and FDMSAccountNo = '878212486889'--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-09-19 : 06:07:35
|
HI Bandi i don’t have the permission to update the table. So it needs to be dropped into a temp table , like in my example above. Also for example if month_end_date '2013-03-01' has fee sequence 42b and 42c, and 42b [Retail_amount] = 2.99 & 42c = 4.99 , i need the retail amount to = 7.98 and the Fee_Sequence_Number] = ‘42c’How can i incorporate that into my update query |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-09-19 : 06:18:57
|
--may be thisupdate #testset Fee_Sequence_Number = MAX(CASE WHEN Fee_Sequence_Number = '42B' and Month_end_date > '2013-02-01' THEN '42C' END), [Retail_amount] = SUM([Retail_amount]) where Fee_Sequence_Number IN ( '42B', '42C')and Month_end_date > '2013-02-01' --Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-09-19 : 10:51:06
|
Unfortnately Bandi This still doesnt work the query needs to be along the lines of /****** Script for SelectTopNRows command from SSMS ******/SELECT TOP 1000 [FDMSAccountNo],[Fee_Sequence_Number],Description,[Month_end_date],[Retail_amount]into #test FROM [FDMS].[dbo].[Fact_Fee_History]INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Codewhere [Fee_Sequence_Number] in ('42B','42C')and Month_end_date between '2013-01-01' and '2013-08-01'and FDMSAccountNo = '878212486889'update #testset Fee_Sequence_Number = '42C'where Fee_Sequence_Number = '42B'and Month_end_date >= '2013-02-01'Update #testset Description = 'PCI DSS MANAGEMENT PM'where Fee_Sequence_Number = '42C'and Month_end_date >= '2013-02-01'select * from #testorder by Month_end_date desc drop table #testWith my update at present, I am only currently updating the fee_sequence_number to the correct number and changing the description to match the fee_sequence_numberI still haven’t managed to work out how to sum retail_amount together if the there is a two fee_sequence_numbers for any given month_end_date |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-09-24 : 07:32:37
|
--May be this ?;WITH CTE AS(SELECT TOP 1000 [FDMSAccountNo],[Fee_Sequence_Number],Description,[Month_end_date],[Retail_amount]FROM [FDMS].[dbo].[Fact_Fee_History]INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Codewhere [Fee_Sequence_Number] in ('42B','42C')and Month_end_date between '2013-01-01' and '2013-08-01'and FDMSAccountNo = '878212486889')update CTEset Fee_Sequence_Number = CASE WHEN Fee_Sequence_Number = '42B' THEN '42C' ELSE Fee_Sequence_Number END, Description = 'PCI DSS MANAGEMENT PM'where Fee_Sequence_Number IN ( '42B', '42C')and Month_end_date >= '2013-02-01'--Chandu |
|
|
|
|
|
|
|