Record Grouping Problem

  • Hello,

    I have a query in which the results express monthly revenue per company. The problem that I have is that for each month, a new line is created to express the revenue for a given company for that month. The thing that I want is for there to be one record for the company with each monthly revenue figure represented in the one record. The query will become a stored procedure with parameters. Don't be thrown by the syntax in the beginning of the query, where a UDF is used, look more closely at the SELECT statement:

    **************************

    DECLARE @Category varchar(8)

    DECLARE @Item varchar(30)

    DECLARE @Year varchar(4) --use split function in TxnRptg

    DECLARE @Month varchar(5) -- use split, or GetAllMonths

    DECLARE @MonthTable TABLE

     ( value varchar(100) Not Null)

    SET @Category = 'Units'

    SET @Item = 'LOG AUDITING BY DRIVER'

    SET @Year = '2007'

    SET @Month = 'all'

    --Determine if the user wants all months, or just some months

    IF NOT @Month = 'All'

     BEGIN

      INSERT INTO @MonthTable

       (value)

      SELECT

       LTRIM(value)

      FROM

       TxnRptg.dbo.fn_split(@Month,',')

      

      UPDATE @MonthTable

      SET Value = TxnRptg.dbo.fn_ConvertMonth(value)

     END

    ELSE

     BEGIN

      INSERT INTO @MonthTable

       (value)

      SELECT

       LTRIM(value)

      FROM

       TxnRptg.dbo.fn_GetAllMonths()

     END

    SELECT

     lg.Customer AS CustNum,

     lg.Company,

     ls.Name,

     ls.Addr1,

     ls.Addr2,

     ls.Addr3,

     ls.Addr4,

     ls.City,

     ls.State,

     ls.Zip,

     ls.StartDate,

     ls.ActiveStatus,

     CASE

      WHEN lg.TxnMonth = 1 THEN ISNULL(SUM(lg.qty),0)

      ELSE 0

     END AS JanUnits,

     CASE

      WHEN lg.TxnMonth = 1 THEN ISNULL(SUM(lg.Price),0)

      ELSE 0

     END AS JanRev,

     CASE

      WHEN lg.TxnMonth = 2 THEN ISNULL(SUM(lg.qty),0)

      ELSE 0

     END AS FebUnits,

    CASE

      WHEN lg.TxnMonth = 2 THEN ISNULL(SUM(lg.Price),0)

      ELSE 0

     END AS FebRev,

    lg.Item

    FROM

      dbo.LOGS lg

      INNER JOIN SalesReporting.dbo.LawsonCustomers ls

      ON lg.Company = ls.Company

       AND

       lg.Customer = ls.Customer

     WHERE

      lg.Item = @Item

      AND

      lg.TxnMonth In (Select value From @MonthTable)

      AND

      lg.TxnYear = @Year

     GROUP BY

      lg.Customer, lg.Company, ls.Name, ls.Addr1, ls.Addr2, ls.Addr3,

      ls.Addr4, ls.City, ls.State, ls.Zip, ls.StartDate, ls.ActiveStatus,

      lg.Item, lg.TxnYear, lg.TxnMonth

     ORDER BY

      CAST(lg.Customer as Int), lg.Company

    ***********************************************

    What do I need to change so that a company's revenue for each month will appear in one record? I tried nesting this witin an inner SELECT statement, but I received syntax errors when I attempted that.

    Thank you for your help!

    CSDunn

  • Did you mean something like this as far as the revenue calculation?

    SELECT

     CustNum = lg.Customer

      ,TxnMonth = lg.TxnMonth

      ,Revenue = Sum(Isnull(lg.Price,0))

    FROM

      dbo.Logs AS lg

    GROUP BY

      lg.Customer

      ,lg.TxnMonth

    ORDER BY

      TxnMonth

      ,CustNum

     

  • The Revenue and Unit calculations have to be expressed in terms of a given month. Thus, the CASE statements that check for the month, then calculate Units and Revenue for that month.

  • Did you run the code I posted to see what it returns?

  • Sorry, I just ran the posted code, and the following is a sample of the results;

    CustNum TxnMonth Revenue

    1057      1            51.00

    1057      2            68.00

    1057      3            127.50

    1057      12           93.50

    Now I did just discover that the problem I was having with the nested SELECT statement I was trying earlier was due to the fact that I had the outer query expressed in the wrong place. I was trying to execute the outer query before the variable declarations and the test on @Month instead of afterwards. I have the outer query in the correct location now, and I'm getting back the results I expected.

    Thank you again for your help!

    CSDunn

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply