Copy prior month value and insert into new row

Here is an example of the current table I have:

1) Table name: TotalSales

Name    Year  Month  Sales
------  ----  -----  -----
Alfred  2011  1      100

What I want to do is create a table like this, add a new row(Prior month sales):

2) Table name: TotalSales

Name    Year  Month  Sales  Prior month sales
------  ----  -----  -----  -----------------
Alfred  2011  2      110    100

Not sure how to this, but this is what I have been working on:

SELECT Name, Year, Month, Sales, Sales as [Prior Month sales]
FROM TotalSales
WHERE
DATEPART(month, [Prior M开发者_如何学运维onth sales]) = DATEPART(month, DATEADD(month, -1, getdate()))

Thanks for any help

I believe this should work...you need to join to itself on name/prior month, but you have 2 test cases for prior month since year/month are stored separately.

select c.Name, c.Year, c.Month, c.Sales, p.Sales
from TotalSales c
left join TotalSales p
on c.Name = p.Name and (
    (c.Month > 1 and c.Year = p.Year and c.Month = p.Month + 1)
    or (c.Month = 1 and c.Year = p.Year + 1 and p.Month = 12))

To select the given data you need to join the table to itself:

SELECT
    TS.name,
    TS.year,
    TS.month,
    TS.sales,
    COALESCE(TS2.sales, 0) AS prior_month_sales
FROM
    TotalSales TS
LEFT OUTER JOIN TotalSales TS2 ON
    TS2.name = TS.name AND
    (
        (TS2.year = TS.year AND TS2.month = TS.month - 1) OR
        (TS.month = 1 AND TS2.month = 12 AND TS2.year = TS.year - 1)
    )

The LEFT OUTER JOIN is an outer join in case they didn't have any sales the previous month (or this is their first month with the company).

Try something like this to just update the table with the values you want...

UPDATE TotalSales
SET PriorMonthSales = 
(
    SELECT TS.Sales
    FROM TotalSales TS
    WHERE 
    (TotalSales.Month = TS.Month + 1 AND TotalSales.Year = TS.Year)
    OR 
    (TotalSales.Month = 1 AND TS.Month = 12 AND TS.Year = TotalSales.Year -1)
)