Copy prior month value and insert into new row

Question

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 Month sales]) = DATEPART(month, DATEADD(month, -1, getdate()))

Thanks for any help

You want to create a table or you want to be able to select the data? I would very much advise against creating a new table. It's going to result in the duplication of data in your database even within the same table, which is a violation of one of the cardinal rules of database design. There are many pitfalls to doing that. — Tom H, Jul 29 '11 at 13:59
Agreed - you could take the query I posted and create a view off of it so that the data is always available — Derek, Jul 29 '11 at 14:04
Just a quick addendum to my last comment... since the table itself looks like a reporting table that is hopefully only updated by a batch process for reporting purposes, this is probably not a big deal if you then put the data into another table as well. — Tom H, Jul 29 '11 at 14:07
Just keep in mind that if you try to keep this data in separate rows (instead of using a view as Derek suggests) then if someone now updates the original row it will not be reflected in your new month's data. It will be out of sync. These synchronization issues always cause lot of headaches which is why is usually a bad idea to keep duplicate data in a normal database. — Tom H, Jul 29 '11 at 15:49

score 2 · Answer 1 · answered Jul 29 '11 at 14:03

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

score 1 · Accepted Answer · answered Jul 29 '11 at 14:03

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

score 0 · Answer 3 · answered Jul 29 '11 at 14:05

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

Copy prior month value and insert into new row

3 Answers3

Linked