Update Query using Current Fields

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

SQL Server 2005 Forums

Transact-SQL (2005)

Update Query using Current Fields

Author

Topic

Pom Grewal
Starting Member

14 Posts

Posted - 2012-01-17 : 06:39:39

Hi
I have a table which requires updating with current pricing. There is one field that I need to update with the price for that month and the others I wish for them to "self update" You'll see what I mean by looking at the below query.

For those not familiar with VAT (Value Added Tax) is currently 20% of the unit price.

When running the below query it only updated the unit price field.

Thanks in advance for your help!!

Pom

select t2.[No_],
t1.[Quantity],
t1.[Unit Price],
t1.[Unit Cost (LCY)],
t1.[Amount],
t1.[Amount Including VAT],
t1.[Unit Price (LCY)],
t1.[Outstanding Amount],
t1.[Outstanding Amount (LCY)],
t1.[VAT Base Amount],
t1.[Unit Cost],
t1.[Line Amount],
t1.[Description],
t1.[No_],*
from DHL.dbo.[DHL WEEE$Waste Mgt_ Header Archive] t2
inner join DHL.dbo.[DHL WEEE$Waste Mgt_ Line Archive] t1
on t2.[No_] = t1.[Document No_]
where t1.[No_] = 'B2C-105'
and t1.[Invoice-with No_] = 'TF-000032'
and t2.[Task Date] between '2011-02-01' and '2011-02-28'
and [Unit Price] = '-180'

==========================================================================
update t1
set t1.[Unit Price] = '-180',
t1.[Amount] = [Unit Price]*[Quantity],
t1.[Amount Including VAT] = [Amount]* 1.2,
t1.[Outstanding Amount] = [Amount Including VAT],
t1.[Outstanding Amount (LCY)] = [Amount Including VAT],
t1.[VAT Base Amount] = [Amount],
t1.[Unit Cost] = [Unit Price],
t1.[Line Amount] = [Amount]
from DHL.dbo.[DHL WEEE$Waste Mgt_ Header Archive] t2
inner join DHL.dbo.[DHL WEEE$Waste Mgt_ Line Archive] t1
on t2.[No_] = t1.[Document No_]
where t1.[No_] = 'B2C-105'
and t1.[Invoice-with No_] = 'TF-000032'
and t2.[Task Date] between '2011-02-01' and '2011-02-28'
and [Unit Price] = '-170'

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 06:52:04

You mean this:


t1.[Amount] = [Unit Price]*[Quantity],
t1.[Amount Including VAT] = [Amount]* 1.2,

where you want the result from the first line to be included in the second line?

Can't remember if that is OK, or not, but if NOT then you can do:


@MyTempVariable = t1.[Amount] = [Unit Price]*[Quantity],
t1.[Amount Including VAT] = @MyTempVariable * 1.2,


set t1.[Unit Price] = '-180',

is that a numeric field? If so probably slightly "tidier" not to include the single-quotes (forces an implicit conversion, which just might not do exactly what you want ... splitting-hairs a bit though)


and t2.[Task Date] between '2011-02-01' and '2011-02-28'

for "dates in string format" always use yymmdd (no hyphens) otherwise the value is ambiguous and will be interpreted differently if you were to move toa different server / change server settings, or the language / country that the current user is logged on with etc., so I recommend this:


and t2.[Task Date] between '20110201' and '20110228'

Note also that if the [Task Date] column includes a time component then '2011-02-28' is midnight between 27th/28th and NO value will be included which has a time [after midnight] on 28th. If that's the case then do:


and t2.[Task Date] >= '20110201' and t2.[Task Date] < '20110301'

Pom Grewal
Starting Member

14 Posts

Posted - 2012-01-17 : 07:28:17

How do I apply the @MyTempVariable query to each of the above lines.

On the original query the first line is where I enter the price, the other lines are dependent upon the results of the line above.

The task dates are hypenated as these is how they appear in our DB.

Also the prices are determined by the dates, if I entered a date from another month, this would bring in lines that I do not wish to update.

Thanks

Pom

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 08:39:25

"How do I apply the @MyTempVariable query to each of the above lines."

Not sure I understand you. Use DECLARE to define however many @Variables you want, and then just insert them in the code as per my example.

"The task dates are hypenated as these is how they appear in our DB"

Dates are stored without any formatting in your DB (as the number of days since some Epoc date), the formatting you are seeing will be SQL's default output formatting, and it has no bearing on how you should present dates to SQL. The ONLY format that SQL accepts for guaranteed unambiguous dates is "yyyymmdd". Anything else that works is fine, provided that the methods SQL uses to parse the date don't change, hence good practice to get into the habit of avoiding using date formats that might be subject to different parsing in the future

"Also the prices are determined by the dates, if I entered a date from another month, this would bring in lines that I do not wish to update."

Yup, that's fine, I just want to be sure that SQL is parsing your dates as you intend that it should, in particular if the dates in SQL also include (or might, by accident or otherwise, in the future) a Time component.

Pom Grewal
Starting Member

14 Posts

Posted - 2012-01-18 : 04:43:04

Thanks will try your suggestion!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources