SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Query with Criteria
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 09/19/2013 :  05:22:49  Show Profile  Reply with Quote
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_Code
where [Fee_Sequence_Number] in ('42B','42C')
and Month_end_date between '2013-01-01' and '2013-08-01'
and FDMSAccountNo = '878212486889'


update #test
set Fee_Sequence_Number = '42C'
where Fee_Sequence_Number = '42B'
and Month_end_date > '2013-02-01'

Looking for any help available

Edited by - masond on 09/19/2013 05:40:12

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 09/19/2013 :  05:58:48  Show Profile  Reply with Quote
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 END
FROM [FDMS].[dbo].[Fact_Fee_History]
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where [Fee_Sequence_Number] in ('42B','42C')
and Month_end_date between '2013-01-01' and '2013-08-01'
and FDMSAccountNo = '878212486889'


--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 09/19/2013 :  06:07:35  Show Profile  Reply with Quote
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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 09/19/2013 :  06:18:57  Show Profile  Reply with Quote
--may be this
update #test
set 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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 09/19/2013 :  10:51:06  Show Profile  Reply with Quote
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_Code
where [Fee_Sequence_Number] in ('42B','42C')
and Month_end_date between '2013-01-01' and '2013-08-01'
and FDMSAccountNo = '878212486889'


update #test
set Fee_Sequence_Number = '42C'
where Fee_Sequence_Number = '42B'
and Month_end_date >= '2013-02-01'

Update #test
set Description = 'PCI DSS MANAGEMENT PM'
where Fee_Sequence_Number = '42C'
and Month_end_date >= '2013-02-01'

select * from #test
order by Month_end_date desc

drop table #test


With 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_number

I 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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 09/24/2013 :  07:32:37  Show Profile  Reply with Quote
--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_Code
where [Fee_Sequence_Number] in ('42B','42C')
and Month_end_date between '2013-01-01' and '2013-08-01'
and FDMSAccountNo = '878212486889'
)
update CTE
set 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000