CODEWITHSUNDEEP
sqlsql-servercrystal-reports
Sean Haddy
Sean Haddy

Reputation: 1666

Two query Sql Expression

I'm not sure if this can be done within crystal, but here it goes.

There are two values that are needed to obtain more report information on this report. First is finding units sold... the second is finding the appropriate final price for reporting... My initial thought was to try and assign a query to a variable within the SQL Expression, to get the first value in order to pass to the second... but it's not liking my variable.

CASE WHEN "Items"."ItemId" IS NULL THEN NULL
ELSE
    LOCAL NumberVar totalSold :=(SELECT SUM([QuantitySold]) AS TotalSold 
                    FROM Skus 
                    WHERE Skus.ItemId = "Items"."ItemId" 
                    AND Skus.SkuEnabled = 1);

    SELECT TOP 1 Price 
    FROM [MYDB].[dbo].[PriceTables] p
    WHERE p.UnitsSoldBreak >= totalSold 
      AND p.ItemId = "Items"."ItemId"
    ORDER BY p.UnitsSoldBreak asc

END

Essentially, an item has multiple final tiers for pricing, and these queries allow me to see what the final price was based upon how many were sold. The queries work just fine within SQL Management Studio, just have to figure how to migrate it to an expression to display the final price (single number).

The engine is coughing on the Local variable declaration.

Upvotes: 1

Views: 120

Answers (1)

ShaneBlake
ShaneBlake

Reputation: 11096

You might want to use a subquery instead of a variable. Give something like this a try:

CASE WHEN "Items"."ItemId" IS NULL THEN NULL
ELSE

    SELECT TOP 1 Price 
    FROM [MYDB].[dbo].[PriceTables] p
    WHERE p.UnitsSoldBreak >= ( SELECT SUM([QuantitySold]) 
                FROM Skus 
                WHERE Skus.ItemId = "Items"."ItemId" 
                  AND Skus.SkuEnabled = 1)
      AND p.ItemId = "Items"."ItemId"
    ORDER BY p.UnitsSoldBreak asc

END

Upvotes: 2

Related Questions