Welcome to the iTechForums.
If this is your first visit, be sure to check out the
FAQ by clicking the link above.
You may have to
register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
To register now click here.
|
Latest Threads
Advertisements
Forum Statistics
Threads:
Posts: 2
Members:
Number of Users Online:
Welcome to our newest member, |
|
 |
05-06-2008, 01:55 PM
|
|
Junior Member
|
|
Join Date: May 2008
Posts: 1
|
|
MS SQL (Management Studio): How would I best populate a column with a value
that occurred in the prior year? If my SQL table has these 4 columns:
Machine DateSalesPY Sales
A Jan-08 $10 $8
B Jan-08 $15 $12
C Jan-08 $20 $15
A Jan-07 $8
B Jan-07 $12
C Jan-07 $15
What would be the easiest syntax to use to create the Prior Year (PY Sales) column? Basically, the code would look at the Machine and Date for each row and then find the Date 1 year ago for that same Machine and populate PY Sales with the Sales for that Date.
Is there an easy way to do this? Appreciate any advice you might offer as I'm a SQL rookie.
|
05-06-2008, 01:55 PM
|
|
Junior Member
|
|
Join Date: May 2008
Posts: 9
|
|
Update tablename
set PYSales = B.Sales
from Tablename A join TablenameB on A.Machine = B.Machine
and B.Machine = left(A.machine,4) + int(right(A.machine,2)-1)
you might need to convert the INT() calculation to force the leading zero.
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
All times are GMT. The time now is 11:05 AM.
