Simplest Method for creating YTD, MTD, LYTD, LYMTD stats report for Sales System

I have a tempory table that I create with a stored procedure as neccessary. In this table are revenue, customer, vendor and sales person. I've tried several types of queries to simplify report generation, but they are all failing because I may have 12 summary records for a given vendor for this year, but perhaps only 9 summary records for last year. Running to separate queries in this instance (one for this years data and one for last years) obviously does not work. I've tried unions, sub-selects, etc. to try and get at this date, but I have reached the end of my rope.

The reports I want to generate are:

1) Salesperson Report detailing each sales staffer's monthly sales IN THE ORDER OF Sept 2004, Sept 2003, Oct 2004, Oct 2003, Nov 2004, Nov 2003, etc.. Time across the top axis, sales people down the y axis. I have had success generating this report because THERE ARE NO EMPTY MONTHS of revenue.

2) Customer Report with same setup, except Vendors purchase from on the y axis.

3) Vendor report, again the same time in x axis, but customers who purchased on the y axis

4) Vendor report, same as 3 above, but with Sales people on the y axis.

Any help on this would be appreciated! Severe time contraint here!
Chaste SwedgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pedros7Commented:
Hi Have you tried using a Cursor to verify whether continuity of monthly data?!
is it possible you to provide a good sample output of that SP you've created?

cheers




0
Chaste SwedgeAuthor Commented:

Hi pedros7,

Thanks for you quick response. Here is the SP the creates the temp aggregate table. The derived table is fine I beleive, but getting at it has been a problem!
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE sp_Gen_Rev_Table

AS

BEGIN      
      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_order_revenue]') and OBJECTPROPERTY(id, N'IsTable') = 1)
            drop table [dbo].[tmp_order_revenue]

---------------  Build Temp Table for Summation of Revenue  --------------------------------------------
      Select
            year(a.shipdate) as 'OYear',month(a.ShipDate) as 'OMonth',b.VendorName,.b.VendorID,
            d.CompanyName,d.CustomerID,c.SalesFirstName+' '+c.SalesLastName as SName,c.SalesPersonID,
            sum(z.ExtCost) 'Total',shipdate
      into tmp_order_revenue
      From PO a
            Inner Join
                  POContent z on a.PurchaseOrderID = z.PurchaseOrderID
            Inner Join
                      Vendors b on a.VendorID = b.VendorID
            Inner Join
                  Customers d on a.CustomerID = d.CustomerID
            Inner Join
                  SalesPerson c on d.SalesPerson = c.SalespersonID
            Inner Join
                  SalesPerson e on a.SalesPersonID = e.SalespersonID
      Where z.ExtCost > 0.00
            and a.ACTIVE <> 'X' and a.ACTIVE <> 'Z' and a.ACTIVE <> 'D'
      Group by b.VendorName,d.CompanyName,c.SalesLastName,c.SalesFirstName,a.ShipDate,b.VendorID,d.CustomerID,c.SalesPersonID
      Order By OYear,OMonth,b.VendorName,d.CompanyName,SName
--------------------------------------------------------------------------------------------------------
      CREATE  CLUSTERED  INDEX [MIndex] ON [dbo].[tmp_order_revenue]([OYear], [OMonth], [VendorID], [CustomerID], [SalesPersonID]) ON [PRIMARY]
      CREATE  INDEX [XVendorID] ON [dbo].[tmp_order_revenue]([VendorID]) ON [PRIMARY]
      CREATE  INDEX [XCustomerID] ON [dbo].[tmp_order_revenue]([CustomerID]) ON [PRIMARY]
      CREATE  INDEX [XSPID] ON [dbo].[tmp_order_revenue]([SalesPersonID]) ON [PRIMARY]
--------------------------------------------------------------------------------------------------------
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
pedros7Commented:
No worries,
Would you be able to grab some of the returned data and post it here too?
say, run the sp in query analyse with 'Results in Text' . and grab a good sample?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chaste SwedgeAuthor Commented:
Here ya go!
----------------------------------------------------------------------------------------------------------------------------------------------------------------


OYear       OMonth      VendorID CompanyName                                                                                          CustomerID  SalesPersonID Total                 shipdate                                              
----------- ----------- -------- ---------------------------------------------------------------------------------------------------- ----------- ------------- --------------------- ------------------------------------------------------
2001        5           153      FHN Partners LLP d/b/a Dada                                                                          548         10000         106.9700              2001-05-05 00:00:00.000
2002        4           131      Your Kitchen  - Keene                                                                                2012        10000         416.3600              2002-04-11 00:00:00.000
2002        5           153      Alexandra's Kitchenware                                                                              33          10000         1653.8400             2002-05-06 00:00:00.000
2002        8           129      Bread & Circus  - Boston                                                                             256         29            57.0000               2002-08-01 00:00:00.000
2002        8           131      Stoddard's Inc.- Copley                                                                              1656        28            1092.4700             2002-08-30 00:00:00.000
2002        10          131      Stoddard's Inc.- Copley                                                                              1656        28            484.2900              2002-10-02 00:00:00.000
2002        10          194      Leroux At Home                                                                                       1064        28            234.0000              2002-10-18 00:00:00.000
2002        11          169      New England Traditions                                                                               1276        36            701.9000              2002-11-01 00:00:00.000
2002        11          194      Leroux At Home                                                                                       1064        28            323.4000              2002-11-15 00:00:00.000
2002        11          194      Stowe Kitchen and Bath                                                                               1673        10000         310.0000              2002-11-24 00:00:00.000
2002        12          131      J K Adams Co                                                                                         933         10000         278.2500              2002-12-18 00:00:00.000
2003        1           121      Duck Soup- Littleton                                                                                 602         27            746.0000              2003-01-17 00:00:00.000
2003        1           121      Skinny Dog                                                                                           2077        36            224.0000              2003-01-17 00:00:00.000
2003        1           125      Main Street Kitchens                                                                                 1121        10000         61.0800               2003-01-20 00:00:00.000
2003        1           155      Nestling Duck Gift Shop                                                                              2084        29            276.0000              2003-01-27 00:00:00.000
2003        1           155      North End Gallery                                                                                    2088        29            41.0000               2003-01-27 00:00:00.000
2003        1           164      Chef's Equipment Emporium                                                                            371         28            2484.6001             2003-01-31 00:00:00.000
2003        1           164      Cook's Nook                                                                                          475         36            35.0000               2003-01-03 00:00:00.000
2003        1           164      Cook's Nook                                                                                          475         36            439.5000              2003-01-06 00:00:00.000
2003        1           164      The Baker's Catalogue & Retail Store                                                                 1000        28            91.2000               2003-01-14 00:00:00.000
2003        1           164      Kitchen Emporium                                                                                     1006        29            151.0000              2003-01-13 00:00:00.000
2003        1           164      L.L. Bean, Inc.                                                                                      1038        28            144.0000              2003-01-27 00:00:00.000
2003        1           164      L.L. Bean, Inc.                                                                                      1038        28            246.2400              2003-01-07 00:00:00.000
2003        1           164      Nantucket Trading Co - Hyannis                                                                       1263        29            110.5000              2003-01-23 00:00:00.000
2003        1           164      Sawyer River Knife & Trading                                                                         1549        29            541.4400              2003-01-21 00:00:00.000
2003        1           164      Souffles                                                                                             1625        29            84.0000               2003-01-23 00:00:00.000
2003        1           164      Stonewall Kitchen - York                                                                             1663        28            672.0000              2003-01-31 00:00:00.000
2003        1           164      Vermont Country Store - catalog                                                                      1875        28            1224.0000             2003-01-24 00:00:00.000
2003        1           164      Your Kitchen  - Keene                                                                                2012        10000         598.3100              2003-01-22 00:00:00.000
2003        1           164      Terra Cotta Pasta                                                                                    2073        29            87.4400               2003-01-08 00:00:00.000
2003        1           169      Kittery Trading Post                                                                                 1023        29            952.9300              2003-01-31 00:00:00.000
2003        1           169      Mise En Place                                                                                        1189        10000         553.5000              2003-01-31 00:00:00.000
2003        1           169      Stonewall Kitchen - York                                                                             1663        28            1762.5601             2003-01-07 00:00:00.000
2003        1           169      Vermont Country Store - catalog                                                                      1875        28            414.2600              2003-01-22 00:00:00.000
2003        1           169      Wild Life Gift Shop                                                                                  2072        10000         249.5000              2003-01-21 00:00:00.000
2003        1           178      Paragon                                                                                              1381        28            42.2500               2003-01-30 00:00:00.000
2003        1           178      Skinny Dog, The - OOB                                                                                1796        36            175.2000              2003-01-21 00:00:00.000
2003        1           178      Barefoot Contessa, The                                                                               2070        29            179.5000              2003-01-27 00:00:00.000
2003        1           178      Greenfield Liquor Shop                                                                               2071        36            149.5000              2003-01-27 00:00:00.000
2003        1           178      Wild Life Gift Shop                                                                                  2072        10000         170.0000              2003-01-30 00:00:00.000
2003        1           181      Different Drummer- Lenox                                                                             582         28            907.6400              2003-01-31 00:00:00.000
2003        1           184      F H Gillingham & Sons                                                                                642         10000         79.6800               2003-01-08 00:00:00.000
2003        1           184      The Baker's Catalogue & Retail Store                                                                 1000        28            754.8000              2003-01-06 00:00:00.000
2003        1           184      Kitchen Arts                                                                                         1004        29            127.9200              2003-01-13 00:00:00.000
2003        1           184      Rooster Brother                                                                                      1519        29            120.1200              2003-01-14 00:00:00.000
2003        1           184      Souffles                                                                                             1625        29            105.0000              2003-01-30 00:00:00.000
2003        1           184      Souffles                                                                                             1625        29            110.7300              2003-01-06 00:00:00.000
2003        1           184      Your Kitchen  - Keene                                                                                2012        10000         511.4600              2003-01-20 00:00:00.000
2003        1           184      Fantastic Favors                                                                                     2095        29            32.4000               2003-01-09 00:00:00.000
2003        1           194      Stowe Kitchen and Bath                                                                               1673        10000         310.0000              2003-01-09 00:00:00.000

(50 row(s) affected)

0
solution46Commented:
jfhoell,

try something like this (you'll probably have to play around with it a bit...). This is the answer for your first question; the method should work for the others too but if you need a hand arsing them gimme a shout :).

Regards,

s46.

SELECT
    base.SalesPerson,
    IsNull(sales2003.Total, 0) Sales2003,
    IsNull(sales2004.Total, 0) Sales2004
FROM tmp_order_revenue base
    LEFT JOIN (
        SELECT
            SalesPerson,
            OYear,
            OMonth,
            Sum(Total)
        FROM tmp_order_revenue
        WHERE OYear = 2003
        GROUP BY OMonth
        ) sales2003
        ON base.SalesPerson = sales2003.SalesPerson
        AND base.OMonth = sales2003.OMonth
    LEFT JOIN (
        SELECT
            SalesPerson,
            OYear,
            OMonth,
            Sum(Total)
        FROM tmp_order_revenue
        WHERE OYear = 2004
        GROUP BY OMonth
        ) sales2004
        ON base.SalesPerson = sales2004.SalesPerson
        AND base.OMonth = sales2004.OMonth
0
solution46Commented:
Oh hell, not read the question properly.... *thinking*
0
pedros7Commented:
i'm just going for lunch, and get the cogs working, that is, if s46 hasn't come up with anything by then!! ;)

(Hi S46!)
0
Chaste SwedgeAuthor Commented:
Thanks to both of You. I have to drive to a client's, but I will take a look later today and see what else is going on. I will try you query s46, and let you know. Thanks again, and to you too pedros7. We'll chat again later today I am sure.
0
Chaste SwedgeAuthor Commented:
I played with it a bit, but got the following error. I think it is confused!!!

Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 4 of 'sales2003'.

SELECT
      base.SName,
      IsNull(sales2003.Total, 0) Sales2003,
          IsNull(sales2004.Total, 0) Sales2004
FROM
      tmp_order_revenue base
      LEFT JOIN (
                  SELECT
                        SName,
                        OYear,
                        OMonth,
                        Sum(Total)
                    FROM tmp_order_revenue
                    WHERE OYear = 2003
                    GROUP BY OMonth,OYear,SName
                      )
      sales2003
              ON base.SName = sales2003.SName
                    AND base.OMonth = sales2003.OMonth
          LEFT JOIN (
                  SELECT
                        SName,
                        OYear,
                        OMonth,
                        Sum(Total)
                    FROM tmp_order_revenue
                    WHERE OYear = 2004
                    GROUP BY OMonth,OYear,SName
                     )
      sales2004
              ON base.SName = sales2004.SName
                    AND base.OMonth = sales2004.OMonth
ORDER BY SName,OMonth,OYear
0
solution46Commented:
Yeah, my mistake. The Sum(Total) needs an alias, e.g. Sum(Total) TotalSales. There's a few other bugs in there too (problems with wriing air-code without testing it!).

OK, tried & tested. You'll be OK using this method for reporting but it's really not good for day-to-day use.

This time it really should work for all your cases but I'm not going to write them all out unless you get REALLY stuck!

Anyway, hope this helps,

s46.





SELECT
    base.SalesPersonID,
    IsNull(current_01.Total, 0) Sales_Current_01,
    IsNull(last_01.Total, 0) Sales_Last_01,
    IsNull(current_02.Total, 0) Sales_Current_02,
    IsNull(last_02.Total, 0) Sales_Last_02,
    IsNull(current_03.Total, 0) Sales_Current_03,
    IsNull(last_03.Total, 0) Sales_Last_03,
    IsNull(current_04.Total, 0) Sales_Current_04,
    IsNull(last_04.Total, 0) Sales_Last_04,
    IsNull(current_05.Total, 0) Sales_Current_05,
    IsNull(last_05.Total, 0) Sales_Last_05,
    IsNull(current_06.Total, 0) Sales_Current_06,
    IsNull(last_06.Total, 0) Sales_Last_06,
    IsNull(current_07.Total, 0) Sales_Current_07,
    IsNull(last_07.Total, 0) Sales_Last_07,
    IsNull(current_08.Total, 0) Sales_Current_08,
    IsNull(last_08.Total, 0) Sales_Last_08,
    IsNull(current_09.Total, 0) Sales_Current_09,
    IsNull(last_09.Total, 0) Sales_Last_09,
    IsNull(current_10.Total, 0) Sales_Current_10,
    IsNull(last_10.Total, 0) Sales_Last_10,
    IsNull(current_11.Total, 0) Sales_Current_11,
    IsNull(last_11.Total, 0) Sales_Last_11,
    IsNull(current_12.Total, 0) Sales_Current_12,
    IsNull(last_12.Total, 0) Sales_Last_12



FROM (
    SELECT DISTINCT SalesPersonID
    FROM tmp_order_revenue) base
-- all this years figures
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 1
        GROUP BY SalesPersonID, OMonth
        ) current_01
        ON base.SalesPersonID = current_01.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 2
        GROUP BY SalesPersonID, OMonth
        ) current_02
        ON base.SalesPersonID = current_02.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 3
        GROUP BY SalesPersonID, OMonth
        ) current_03
        ON base.SalesPersonID = current_03.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 4
        GROUP BY SalesPersonID, OMonth
        ) current_04
        ON base.SalesPersonID = current_04.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 5
        GROUP BY SalesPersonID, OMonth
        ) current_05
        ON base.SalesPersonID = current_05.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 6
        GROUP BY SalesPersonID, OMonth
        ) current_06
        ON base.SalesPersonID = current_06.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 7
        GROUP BY SalesPersonID, OMonth
        ) current_07
        ON base.SalesPersonID = current_07.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 8
        GROUP BY SalesPersonID, OMonth
        ) current_08
        ON base.SalesPersonID = current_08.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 9
        GROUP BY SalesPersonID, OMonth
        ) current_09
        ON base.SalesPersonID = current_09.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 10
        GROUP BY SalesPersonID, OMonth
        ) current_10
        ON base.SalesPersonID = current_10.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 11
        GROUP BY SalesPersonID, OMonth
        ) current_11
        ON base.SalesPersonID = current_11.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate())
      AND OMonth = 12
        GROUP BY SalesPersonID, OMonth
        ) current_12
        ON base.SalesPersonID = current_12.SalesPersonID


-- all last years figures
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 1
        GROUP BY SalesPersonID, OMonth
        ) last_01
        ON base.SalesPersonID = last_01.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 2
        GROUP BY SalesPersonID, OMonth
        ) last_02
        ON base.SalesPersonID = last_02.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 3
        GROUP BY SalesPersonID, OMonth
        ) last_03
        ON base.SalesPersonID = last_03.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 4
        GROUP BY SalesPersonID, OMonth
        ) last_04
        ON base.SalesPersonID = last_04.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 5
        GROUP BY SalesPersonID, OMonth
        ) last_05
        ON base.SalesPersonID = last_05.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 6
        GROUP BY SalesPersonID, OMonth
        ) last_06
        ON base.SalesPersonID = last_06.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 7
        GROUP BY SalesPersonID, OMonth
        ) last_07
        ON base.SalesPersonID = last_07.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 8
        GROUP BY SalesPersonID, OMonth
        ) last_08
        ON base.SalesPersonID = last_08.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 9
        GROUP BY SalesPersonID, OMonth
        ) last_09
        ON base.SalesPersonID = last_09.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 10
        GROUP BY SalesPersonID, OMonth
        ) last_10
        ON base.SalesPersonID = last_10.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 11
        GROUP BY SalesPersonID, OMonth
        ) last_11
        ON base.SalesPersonID = last_11.SalesPersonID
    LEFT JOIN (
        SELECT
            SalesPersonID,
            OMonth,
            Sum(Total) Total
        FROM tmp_order_revenue
        WHERE OYear = year(getdate()) -1
      AND OMonth = 12
        GROUP BY SalesPersonID, OMonth
        ) last_12
        ON base.SalesPersonID = last_12.SalesPersonID
0
KarinLoosCommented:
you could try this :
SELECT C.SalesPersonID ,
          SUM(CASE a.oMonth  WHEN  1 then a.total ELSE 0 END) AS currM1 ,
          SUM(CASE b.oMonth  WHEN  1 then b.total ELSE 0 END) AS PrevM1 ,
          SUM(CASE a.oMonth  WHEN  2 then a.total ELSE 0 END) AS currM2 ,
          SUM(CASE b.oMonth  WHEN  2 then b.total ELSE 0 END) AS PrevM2 ,
          SUM(CASE a.oMonth  WHEN  3 then a.total ELSE 0 END) AS currM3 ,
          SUM(CASE b.oMonth  WHEN  3 then b.total ELSE 0 END) AS PrevM3 ,
          SUM(CASE a.oMonth  WHEN  4 then a.total ELSE 0 END) AS currM4 ,
          SUM(CASE b.oMonth  WHEN  4 then b.total ELSE 0 END) AS PrevM4 ,
          SUM(CASE a.oMonth  WHEN  5 then a.total ELSE 0 END) AS currM5 ,
          SUM(CASE b.oMonth  WHEN  5 then b.total ELSE 0 END) AS PrevM5 ,
          SUM(CASE a.oMonth  WHEN  6 then a.total ELSE 0 END) AS currM6 ,
          SUM(CASE b.oMonth  WHEN  6 then b.total ELSE 0 END) AS PrevM6 ,
          SUM(CASE a.oMonth  WHEN  7 then a.total ELSE 0 END) AS currM7 ,
          SUM(CASE b.oMonth  WHEN  7 then b.total ELSE 0 END) AS PrevM7 ,
          SUM(CASE a.oMonth  WHEN  8 then a.total ELSE 0 END) AS currM8 ,
          SUM(CASE b.oMonth  WHEN  8 then b.total ELSE 0 END) AS PrevM8 ,
          SUM(CASE a.oMonth  WHEN  9 then a.total ELSE 0 END) AS currM9 ,
          SUM(CASE b.oMonth  WHEN  9 then b.total ELSE 0 END) AS PrevM9 ,
          SUM(CASE a.oMonth  WHEN  10 then a.total ELSE 0 END) AS currM10 ,
          SUM(CASE b.oMonth  WHEN  10 then b.total ELSE 0 END) AS PrevM10 ,
          SUM(CASE a.oMonth  WHEN  11 then a.total ELSE 0 END) AS currM11 ,
          SUM(CASE b.oMonth  WHEN  11 then b.total ELSE 0 END) AS PrevM11 ,
          SUM(CASE a.oMonth  WHEN  12 then a.total ELSE 0 END) AS currM12 ,
          SUM(CASE b.oMonth  WHEN  12 then b.total ELSE 0 END) AS PrevM12
FROM tmp_order_revenue             c
LEFT join tmp_order_revenue             a on (A.SalesPersonID = c.SalesPersonID)
LEFT JOIN tmp_order_revenue             b ON  b.SalesPersonID = c.SalesPersonId
              and b.oYear = Year(Getdate()) - 1
where a.oYear = Year(GetDate())
GROUP BY C.SalesPersonID
ORDER BY C.SalesPersonID
0
solution46Commented:
Ahh..!! Knew there was a neater way of doing it - bit of a brain freeze going on there.

Karin, you're almost right but you've got your LEFT JOIN syntax a bit mixed up. Try this...

...
FROM tmp_order_revenue           c
LEFT join tmp_order_revenue a
      on a.SalesPersonID = c.SalesPersonID
      and a.OYear = Year(GetDate())

LEFT JOIN tmp_order_revenue b
      ON  b.SalesPersonID = c.SalesPersonId
                and b.oYear = Year(Getdate()) - 1

GROUP BY C.SalesPersonID
ORDER BY C.SalesPersonID


And pedros7, sorry bud - wasn't ignoring you - hi!



Regards,

s46.
0
pedros7Commented:
hi, just back on this one.

hmm, seem the results aren't what i was expecting...


=-=-=-=--=-=
using the sample author given us, here's the data table insert:


Create table #tempResults (
      OYear int,
      OMonth tinyint,
      VendorID int,
      CompanyName varchar(50),
      CustomerID int,
      SalesPersonID int,
      [SalesPersonID Total] money,
      shipdate datetime
)

insert into #tempResults Values (2001, 5, 153, 'FHN Partners LLP d/b/a Dada', '548', '10000', convert (money, '106.97'), convert (datetime, '2001-05-05 00:00:00.000', 121))
insert into #tempResults Values (2002, 4, 131, 'Your Kitchen  - Keene', '2012', '10000', convert (money, '416.36'), convert (datetime, '2002-04-11 00:00:00.000', 121))
insert into #tempResults Values (2002, 5, 153, 'Alexandras Kitchenware', '33', '10000', convert (money, '1653.84'), convert (datetime, '2002-05-06 00:00:00.000', 121))
insert into #tempResults Values (2002, 8, 129, 'Bread & Circus  - Boston', '256', '29', convert (money, '57.00'), convert (datetime, '2002-08-01 00:00:00.000', 121))
insert into #tempResults Values (2002, 8, 131, 'Stoddards Inc.- Copley', '1656', '28', convert (money, '1092.47'), convert (datetime, '2002-08-30 00:00:00.000', 121))
insert into #tempResults Values (2002, 10, 131, 'Stoddards Inc.- Copley', '1656', '28', convert (money, '484.29'), convert (datetime, '2002-10-02 00:00:00.000', 121))
insert into #tempResults Values (2002, 10, 194, 'Leroux At Home', '1064', '28', convert (money,'234.00'), convert (datetime, '2002-10-18 00:00:00.000', 121))
insert into #tempResults Values (2002, 11, 169, 'New England Traditions', '1276', '36', convert (money, '701.90'), convert (datetime, '2002-11-01 00:00:00.000', 121))
insert into #tempResults Values (2002, 11, 194, 'Leroux At Home', '1064', '28', convert (money, '323.40'), convert (datetime, '2002-11-15 00:00:00.000', 121))
insert into #tempResults Values (2002, 11, 194, 'Stowe Kitchen and Bath', '1673', '10000', convert (money, '310.00'), convert (datetime, '2002-11-24 00:00:00.000', 121))
insert into #tempResults Values (2002, 12, 131, 'J K Adams Co', '933', '10000', convert (money, '278.25'), convert (datetime, '2002-12-18 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 121, 'Duck Soup- Littleton', '602', '27', convert (money, '746.00'), convert (datetime, '2003-01-17 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 121, 'Skinny Dog', '2077', '36', convert (money, '224.00'), convert (datetime, '2003-01-17 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 125, 'Main Street Kitchens', '1121', '10000', convert (money, '61.08'), convert (datetime, '2003-01-20 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 155, 'Nestling Duck Gift Shop', '2084', '29', convert (money, '276.00'), convert (datetime, '2003-01-27 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 155, 'North End Gallery', '2088', '29', convert (money, '41.00'), convert (datetime, '2003-01-27 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Chefs Equipment Emporium', '371', '28', convert (money, '2484.6001'), convert (datetime, '2003-01-31 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Cooks Nook', '475', '36', convert (money, '35.00'), convert (datetime, '2003-01-03 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Cooks Nook', '475', '36', convert (money, '439.50'), convert (datetime, '2003-01-06 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'The Bakers Catalogue & Retail Store', '1000', '28', convert (money, '91.20'), convert (datetime, '2003-01-14 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Kitchen Emporium', '1006', '29', convert (money, '151.00'), convert (datetime, '2003-01-13 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'L.L. Bean, Inc.', '1038', '28', convert (money, '144.00'), convert (datetime, '2003-01-27 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'L.L. Bean, Inc.', '1038', '28', convert (money, '246.24'), convert (datetime, '2003-01-07 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Nantucket Trading Co - Hyannis', '1263', '29', convert (money, '110.50'), convert (datetime, '2003-01-23 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Sawyer River Knife & Trading', '1549', '29', convert (money, '541.44'), convert (datetime, '2003-01-21 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Souffles', '1625', '29', convert (money, '84.00'), convert (datetime, '2003-01-23 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Stonewall Kitchen - York', '1663', '28', convert (money, '672.00'), convert (datetime, '2003-01-31 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Vermont Country Store - catalog', '1875', '28', convert (money, '1224.00'), convert (datetime, '2003-01-24 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Your Kitchen  - Keene', '2012', '10000', convert (money, '598.31'), convert (datetime, '2003-01-22 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 164, 'Terra Cotta Pasta', '2073', '29', convert (money, '87.44'), convert (datetime, '2003-01-08 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 169, 'Kittery Trading Post', '1023', '29', convert (money, '952.93'), convert (datetime, '2003-01-31 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 169, 'Mise En Place', '1189', '10000', convert (money, '553.50'), convert (datetime, '2003-01-31 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 169, 'Stonewall Kitchen - York', '1663', '28', convert (money, '1762.5601'), convert (datetime, '2003-01-07 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 169, 'Vermont Country Store - catalog', '1875', '28', convert (money, '414.26'), convert (datetime, '2003-01-22 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 169, 'Wild Life Gift Shop', '2072', '10000', convert (money, '249.50'), convert (datetime, '2003-01-21 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 178, 'Paragon', '1381', '28', convert (money, '42.25'), convert (datetime, '2003-01-30 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 178, 'Skinny Dog, The - OOB', '1796', '36', convert (money, '175.20'), convert (datetime, '2003-01-21 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 178, 'Barefoot Contessa, The', '2070', '29', convert (money, '179.50'), convert (datetime, '2003-01-27 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 178, 'Greenfield Liquor Shop', '2071', '36', convert (money, '149.50'), convert (datetime, '2003-01-27 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 178, 'Wild Life Gift Shop', '2072', '10000', convert (money, '170.00'), convert (datetime, '2003-01-30 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 181, 'Different Drummer- Lenox', '582', '28', convert (money, '907.64'), convert (datetime, '2003-01-31 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'F H Gillingham & Sons', '642', '10000', convert (money, '79.68'), convert (datetime, '2003-01-08 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'The Bakers Catalogue & Retail Store', '1000', '28', convert (money, '754.80'), convert (datetime, '2003-01-06 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'Kitchen Arts', '1004', '29', convert (money, '127.92'), convert (datetime, '2003-01-13 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'Rooster Brother', '1519', '29', convert (money, '120.12'), convert (datetime, '2003-01-14 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'Souffles', '1625', '29', convert (money, '105.00'), convert (datetime, '2003-01-30 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'Souffles', '1625', '29', convert (money, '110.73'), convert (datetime, '2003-01-06 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'Your Kitchen  - Keene', '2012', '10000', convert (money, '511.46'), convert (datetime, '2003-01-20 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 184, 'Fantastic Favors', '2095', '29', convert (money, '32.40'), convert (datetime, '2003-01-09 00:00:00.000', 121))
insert into #tempResults Values (2003, 1, 194, 'Stowe Kitchen and Bath', '1673', '10000', convert (money, '310.00'), convert (datetime, '2003-01-09 00:00:00.000', 121))

-- insert another sample record similar to 1srt record added in month 5 but year 2002 this time
insert into #tempResults Values (2002, 5, 153, 'FHN Partners LLP d/b/a Dada', '548', '10000', convert (money, '106.97'), convert (datetime, '2001-05-05 00:00:00.000', 121))



=-=--=-=-=
0
pedros7Commented:
to test this i changed the code already here to:

Declare @Year int
Select @Year = 2003

SELECT C.SalesPersonID ,
(...)
FROM #tempResults           c
LEFT join #tempResults a
     on a.SalesPersonID = c.SalesPersonID
     and a.OYear = @Year

LEFT JOIN #tempResults b
     ON  b.SalesPersonID = c.SalesPersonId
                and b.oYear = @Year - 1

GROUP BY C.SalesPersonID
ORDER BY C.SalesPersonID

------------

results didn't match my group by for that period. as returned by:

Select OMonth, OYear, SalesPersonID, Sum([SalesPersonID Total]) from #tempResults
where SalesPersonID = 10000 and oYear = 2003
Group by oMonth, oYear, SalesPersonID With cube
Order By oMonth, oYear, SalesPersonID


what do you think?
0
solution46Commented:
I used to think I was a free man, but now I know I'm just a number.

3418753, to be exact.

s46.
0
KarinLoosCommented:
lol @ sol46,
Sorry pedro but dont get your results... lol lil ol blond me probably
your probably right but tried it and couldnt  see an issue
karin

0
pedros7Commented:
kariloos, nahhhh, today you're well ahead of me! :) haven't had much time to check this one yet, so far my posts was only to quickly work out the creation of temp table and a small select to generate a specific test result:

Select OMonth, OYear, SalesPersonID, Sum([SalesPersonID Total]) from #tempResults
where SalesPersonID = 10000 and oYear = 2003
Group by oMonth, oYear, SalesPersonID With cube
Order By oMonth, oYear, SalesPersonID

When i ran this for the given personid and year, the values returned were very different from yours. Still if you look at my post to create the temp table, you'll see there are no where near as many sales for 2003 for this guy as the values returned by your code, as in:
Select * from #tempResults where SalesPersonID = 10000 and oYear=2003

Mr 10000 is going to be sick of being picked on.. :)

Can you see where i'm coming from? Do think its the right track. will only be able to look at it proper later on this pm, though!

0
solution46Commented:
Pedros7... crawl back under your web dev rock and leave this to the SQL experts  (or Karin anyway, my way was pants) !!!

s46 :))
0
solution46Commented:
OK, gone through this in a bit more detail.

Pedros7 is absolutely right - Karin, your numbers don't add up and I'm a fool for not checking it properly. I think the methodology behind what you're doing is OK but the JOINing isn't specific enough and is causing multiples.

My query works but it's a nightmare and practically impossible to maintin.

I'll have another look at Karin's approach in a bit (I think it's fundamentally the best way to go) but if you've got any other ideas, Pedros7, then post 'em up!

s46.
0
KarinLoosCommented:
Pedro and sol u were right, i adjusted it to give correct resutls
Declare @Year int
Select @Year = 2003
SELECT  a.*, b.*
from (
SELECT   a.SalesPersonID ,
       oYear ,
         SUM(CASE a.oMonth  WHEN  1 then a.total ELSE 0 END) AS currM1 ,
         SUM(CASE a.oMonth  WHEN  2 then a.total ELSE 0 END) AS currM2 ,
         SUM(CASE a.oMonth  WHEN  3 then a.total ELSE 0 END) AS currM3 ,
         SUM(CASE a.oMonth  WHEN  4 then a.total ELSE 0 END) AS currM4 ,
         SUM(CASE a.oMonth  WHEN  5 then a.total ELSE 0 END) AS currM5 ,
         SUM(CASE a.oMonth  WHEN  6 then a.total ELSE 0 END) AS currM6 ,
         SUM(CASE a.oMonth  WHEN  7 then a.total ELSE 0 END) AS currM7 ,
         SUM(CASE a.oMonth  WHEN  8 then a.total ELSE 0 END) AS currM8 ,
         SUM(CASE a.oMonth  WHEN  9 then a.total ELSE 0 END) AS currM9 ,
         SUM(CASE a.oMonth  WHEN  10 then a.total ELSE 0 END) AS currM10 ,
         SUM(CASE a.oMonth  WHEN  11 then a.total ELSE 0 END) AS currM11 ,
         SUM(CASE a.oMonth  WHEN  12 then a.total ELSE 0 END) AS currM12
fROM #tempResults           a
WHERE a.oYear = @year
GROUP BY a.SalesPersonID , oYear
) as a
LEFT JOIN
(
SELECT   A.SalesPersonID ,
       oYear,
         SUM(CASE a.oMonth  WHEN  1 then a.total ELSE 0 END) AS prevM1 ,
         SUM(CASE a.oMonth  WHEN  2 then a.total ELSE 0 END) AS prevM2 ,
         SUM(CASE a.oMonth  WHEN  3 then a.total ELSE 0 END) AS prevM3 ,
         SUM(CASE a.oMonth  WHEN  4 then a.total ELSE 0 END) AS prevM4 ,
         SUM(CASE a.oMonth  WHEN  5 then a.total ELSE 0 END) AS prevM5 ,
         SUM(CASE a.oMonth  WHEN  6 then a.total ELSE 0 END) AS prevM6 ,
         SUM(CASE a.oMonth  WHEN  7 then a.total ELSE 0 END) AS prevM7 ,
         SUM(CASE a.oMonth  WHEN  8 then a.total ELSE 0 END) AS prevM8 ,
         SUM(CASE a.oMonth  WHEN  9 then a.total ELSE 0 END) AS prevM9 ,
         SUM(CASE a.oMonth  WHEN  10 then a.total ELSE 0 END) AS prevM10 ,
         SUM(CASE a.oMonth  WHEN  11 then a.total ELSE 0 END) AS prevM11 ,
         SUM(CASE a.oMonth  WHEN  12 then a.total ELSE 0 END) AS prevM12
fROM #tempResults           a
WHERE a.oYear = @year - 1
GROUP BY a.SalesPersonID , oYear
) as B on (B.SalesPersonID = a.SalesPersonID )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pedros7Commented:
Yes, It works for the sample!!! Brill KarinLoos and its tidy code too (hehe).
I've made a minor change to output in same format as you had before:

Declare @Year int
Select @Year = 2003
SELECT  a.SalesPersonID, @Year as [Current Year], b.prevM1, a.currM1 , b.prevM2 , a.currM2 , b.prevM3 , a.currM3 , b.prevM4  , a.currM4 , b.prevM5 , a.currM5 , b.prevM6 , a.currM6 , b.prevM7 ,  a.currM7 , b.prevM8  , a.currM8 , b.prevM9,a.currM9 ,  b.prevM10,a.currM10 ,  b.prevM11 , a.currM11 , b.prevM12,a.currM12
from (
SELECT   a.SalesPersonID ,
      oYear ,
         SUM(CASE a.oMonth  WHEN  1 then a.[SalesPersonID Total] ELSE 0 END) AS currM1 ,
         SUM(CASE a.oMonth  WHEN  2 then a.[SalesPersonID Total] ELSE 0 END) AS currM2 ,
         SUM(CASE a.oMonth  WHEN  3 then a.[SalesPersonID Total] ELSE 0 END) AS currM3 ,
         SUM(CASE a.oMonth  WHEN  4 then a.[SalesPersonID Total] ELSE 0 END) AS currM4 ,
         SUM(CASE a.oMonth  WHEN  5 then a.[SalesPersonID Total] ELSE 0 END) AS currM5 ,
         SUM(CASE a.oMonth  WHEN  6 then a.[SalesPersonID Total] ELSE 0 END) AS currM6 ,
         SUM(CASE a.oMonth  WHEN  7 then a.[SalesPersonID Total] ELSE 0 END) AS currM7 ,
         SUM(CASE a.oMonth  WHEN  8 then a.[SalesPersonID Total] ELSE 0 END) AS currM8 ,
         SUM(CASE a.oMonth  WHEN  9 then a.[SalesPersonID Total] ELSE 0 END) AS currM9 ,
         SUM(CASE a.oMonth  WHEN  10 then a.[SalesPersonID Total] ELSE 0 END) AS currM10 ,
         SUM(CASE a.oMonth  WHEN  11 then a.[SalesPersonID Total] ELSE 0 END) AS currM11 ,
         SUM(CASE a.oMonth  WHEN  12 then a.[SalesPersonID Total] ELSE 0 END) AS currM12
fROM #tempResults           a
WHERE a.oYear = @year
GROUP BY a.SalesPersonID , oYear
) as a
LEFT JOIN
(
SELECT   A.SalesPersonID ,
      oYear,
         SUM(CASE a.oMonth  WHEN  1 then a.[SalesPersonID Total] ELSE 0 END) AS prevM1 ,
         SUM(CASE a.oMonth  WHEN  2 then a.[SalesPersonID Total] ELSE 0 END) AS prevM2 ,
         SUM(CASE a.oMonth  WHEN  3 then a.[SalesPersonID Total] ELSE 0 END) AS prevM3 ,
         SUM(CASE a.oMonth  WHEN  4 then a.[SalesPersonID Total] ELSE 0 END) AS prevM4 ,
         SUM(CASE a.oMonth  WHEN  5 then a.[SalesPersonID Total] ELSE 0 END) AS prevM5 ,
         SUM(CASE a.oMonth  WHEN  6 then a.[SalesPersonID Total] ELSE 0 END) AS prevM6 ,
         SUM(CASE a.oMonth  WHEN  7 then a.[SalesPersonID Total] ELSE 0 END) AS prevM7 ,
         SUM(CASE a.oMonth  WHEN  8 then a.[SalesPersonID Total] ELSE 0 END) AS prevM8 ,
         SUM(CASE a.oMonth  WHEN  9 then a.[SalesPersonID Total] ELSE 0 END) AS prevM9 ,
         SUM(CASE a.oMonth  WHEN  10 then a.[SalesPersonID Total] ELSE 0 END) AS prevM10 ,
         SUM(CASE a.oMonth  WHEN  11 then a.[SalesPersonID Total] ELSE 0 END) AS prevM11 ,
         SUM(CASE a.oMonth  WHEN  12 then a.[SalesPersonID Total] ELSE 0 END) AS prevM12
fROM #tempResults           a
WHERE a.oYear = @year - 1
GROUP BY a.SalesPersonID , oYear
) as B on (B.SalesPersonID = a.SalesPersonID )

0
KarinLoosCommented:
phew, wipes sweat off forehead ..
.. i could sure use your guys help with another question i am busy with Q_21226264.html

Cheers
Karin
0
solution46Commented:
no probs - will pop over now :)

nice group effort...
0
Chaste SwedgeAuthor Commented:
Hey folks,

First, thanks so much for all of the help.... wait....
0
Chaste SwedgeAuthor Commented:
Sorry for the delay. Thanks to all of you for your input. Some things worked, and some things did not, but I've gotten past the hurtles with help from all of you. I will split out the points accordingly. Thanks again to you all!
0
solution46Commented:
You're welcome - cheers for the points!

s46.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.