SQL Server Performance

Comparing Data From Day Before

Discussion in 'General Developer Questions' started by JackBauer82, Jan 16, 2007.

  1. JackBauer82 New Member

    Hi, I have 3 Tables: Club, Now, Before.
    Table Club has just one field called Team i.e.,
    Arizona Cardinals,
    Atlanta Falcons,
    Baltimore Ravens,
    Buffalo Bills,
    Carolina Panthers,
    Cincinnati Bengals,
    Dallas Cowboys,
    Denver Broncos

    Table Now is the data for Today and Table Before is the data for Yesterday. Below are the columns for each table

    Club:
    Team

    Now:
    Product
    Value
    Team
    Days_Old

    Before:
    Product
    Value
    Team
    Days_Old

    The Days_Old has four results:
    0-5
    6-10
    11-15
    15+

    Which is how long the product has been there, I need a query which will do the following:

    To show any products that were under one category in Table Before but in a new category in Table Now, (so the Before Table will probably have an outer join from the Now table)

    Before.Product Before.Days_Old Now.Product Now.Days_Old
    1 0-5 1 0-5
    2 6-10 2 11-15
    3 6-10 3 6-10
    4 0-5 4 0-5

    So we know that Product 2 has moved from 6-10 to 11-15, this product will hold a value and I want to know how much that value has moved across, below is an example of everything that has moved from 6-10 to 11-15.

    So result to look like:
    Club Before.Value After.Value [Difference]
    Arizona Cardinals $500.00 $500.00 $0.00
    Atlanta Falcons $214.00 $260.00 $46.00
    Baltimore Ravens $0.00 $0.00 $0.00
    Buffalo Bills $0.00 $0.00 $0.00
    Carolina Panthers $5600.00 $5800.00 $200.00
    Cincinnati Bengals $0.00 $0.00 $0.00
    Dallas Cowboys $300.00 $315.00 $15.00

    Each product will have only one team but there can be numerous products, i.e. 10 could be in Days_Old 0-5 and another 5 might be in 15+. I also want Table Team to show all the teams in this table even if there is no team that appear in Before or Now Tables: Ideally the end result would look like below which shows the values that have moved into the new category of the Now Table

    Query:
    Team 6-10 11-15 15+
    Arizona Cardinals $54.00 $0.00 $0.00
    Atlanta Falcons $0.00 $0.00 $0.00
    Baltimore Ravens $12.00 $0.00 $15.00
    Buffalo Bills $56.00 $0.00 $0.00
    Carolina Panthers $0.00 $4.00 $15.00
    Cincinnati Bengals $100.00 $0.00 $5.00
    Dallas Cowboys $0.00 $15.00 $0.00

Share This Page