Update Query with Criteria - 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

Update Query with Criteria

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_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

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

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

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_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

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_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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources