Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45

    Unanswered: Subquery for history reference

    Hi,
    I'm doing some statistics queries which I place mainly in SPs, but I find some of the syntax gets overly complicated and so I think I'm doing it the wrong way.

    For instance I like to sample sales for an interval with comparison to previous year's sales la:

    DECLARE @From AS SMALLDATETIME, @To AS SMALLDATETIME

    SELECT @From = '20030801', @To = '20030831'

    SELECT
    Store,
    SUM(Revenue) Sales,
    (SELECT SUM(Revenue) FROM Sales
    WHERE Store = S.Store
    AND SaleDate >= DATEADD(yyyy, -1, @From) AND SaleDate <= DATEADD(yyyy, -1, @To)
    ) AS LastYearSales
    FROM Sales S
    WHERE SaleDate >= @From AND SaleDate <= @To
    GROUP BY Store

    Also I'd like to select the value of
    CASE WHEN LastYearSales <> 0 THEN (Sales - LastYearSales) / LastYearSales ELSE NULL END AS SalesDiff

    Now, I could just swap the LastYearSales in this statement for the subquery statement, but it looks so bulky to me.

    My questions:
    1. Is there a better/neater/more convenient way to write this quey without having so many copies of the same subquery?

    2. With several identical subqueries, will the optimizer perform just the one?

    Thanks for any advice,
    Robert

    PS Is there a way to post code with a pre-type tag? DS

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The optimizer will perform a join for each copy of the query.

    "Neater" is a relative term. It depends on what makes sense to you. I prefer this method where the two values are calculated independently, rather than nesting the queries. I left-joined them both to the Stores table (you do have a Stores table, right?) to return a record for each store regardless of whether it had sales in either the current or previous year. You can change the joins, add filters, or fiddle around with ISNULL() functions to suit your purposes:

    select Stores.Store, CurrentYear.Sales, LastYearSales.Sales,
    CASE WHEN LastYearSales.Sales <> 0 THEN (CurrentYear.Sales - LastYearSales.Sales) / LastYearSales.Sales ELSE NULL
    END AS SalesDiff
    from Stores
    left outer join
    (Select Store, Sum(Revenue) Sales
    from Sales
    Where SaleDate Between @From and @To
    Group by Store) as CurrentYear
    on Stores.Store = CurrentYear.Store
    left outer join
    (Select Store, Sum(Revenue) Sales
    from Sales
    Where DateAdd(yyyy, 1, SaleDate) Between @From and @To
    Group by Store) as LastYearSales
    on Stores.Store = LastYearSales.Store

    blindman

  3. #3
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45

    Smile Thanks

    Thanks Blindman!

    Putting the subquery in the FROM clause as a join didn't occur to me.
    And it does look "neater" ;-)
    (to me at least)

    I think I'll now manage to get some groupings by month and so too this way.

    Cheers,
    Robert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •