CODEWITHSUNDEEP
sqlsql-server
tkvo
tkvo

Reputation: 171

SQL query Total sale

I have a sale table and would like to calculate a total.

Here is the SQL Fiddle link.

The expected result is 482.33.

Some one (@rs) helped me with the query before, but it does not work in this case. I would like to get the total of this customer between '2012/12/07' to '2012/12/09' subtract any RETURNED (negative) item with the same SKU as an item purchased during the date range.

The result came out wrong. Could you please help.

Upvotes: 1

Views: 1575

Answers (3)

Jacco
Jacco

Reputation: 3272

Here is another option:

DECLARE @customer varchar(10) = '1111111111' 
DECLARE @from date = '12/07/12'
DECLARE @till date = DATEADD(DAY, 2, @from)
DECLARE @exclusiveTill date = DATEADD(DAY, 1, @till) 
-- added @exclusiveTill based on the comments

SELECT
  [sub].[custid],
  MAX([sub].[LatestDop]) AS [dateid],
  SUM([sub].[PriceSum]) AS [price]
FROM
(
  SELECT
    [custid],
    MAX([dop]) AS [LatestDop],
    [sku],
    SUM([price]) AS [PriceSum],
    SUM(CASE WHEN [price] >= 0 THEN 1 ELSE 0 END) AS [numberOfPositives]
  FROM [sale]
  WHERE
    [custid] = @customer
    AND [dop] >= @from
    AND (CASE WHEN [price] < 0 THEN @till ELSE [dop] END) < @exclusiveTill
  GROUP BY
    [custid],
    [sku]
) AS [sub]
WHERE 
  [sub].[numberOfPositives] > 0
GROUP BY 
  [sub].[custid]

This was made with this Fiddle.

This query is rather literally translating your answer and comments into sql:

  • All sales in a certain timespan
  • Where negative sales after this timespan should be included
  • But a negative sale should have at least one positive with the same sku (in the timespan)
  • And the sum of all prices of the sales satisfying the above criteria should be determined

Upvotes: 2

davids
davids

Reputation: 5597

with the new information try this:

DECLARE @datemin date = '12/7/12',
        @datemax date = '12/9/12',
        @custid varchar(10)='1111111111'
;WITH RECS AS(
    SELECT custid,dop,sku,Price 
      FROM sale 
      WHERE dop BETWEEN @datemin AND @datemax AND custid=@custid AND price>=0)

SELECT custid,MAX(dop)dateid,SUM(price)price
FROM(
  SELECT custid,dop,price FROM RECS
  UNION ALL
  SELECT custid,dop,price FROM sale 
      WHERE sku IN (SELECT sku FROM RECS) AND price < 0 AND dop > @datemax)t
GROUP BY custid

It isn't pretty, but it shows a totally different approach, and it was fast to put together.

You create a list of transactions within the date range, plus a list of negative transactions after the date range where the sku happened within the daterange and get your totals from there.

CAUTION: This has TROUBLE written all over it.

What happens if the same sku was purchased prior to the date range AND within the date range, then the prior purchase was returned? This would reduce it from the date range.

How would you know which was actually returned?

Why not decrement the return from the period in which it actually happened? This is very common, especially if the return happens some time later.

There are many more questions to be answered, but that just gives you something to think about.

Upvotes: 0

rs.
rs.

Reputation: 27467

Try this

;WITH T AS
(
  SELECT * FROM sale WHERE Price > 0
  AND [dop] BETWEEN '12/07/12' AND '12/09/12'
  UNION
  SELECT A.* FROM sale a WHERE price < 0
  AND EXISTS (SELECT SKU FROM sale x
            WHERE x.Price > 0 AND a.sku = x.sku
            AND a.custid = x.custid 
            AND x.[dop] BETWEEN '12/07/12' AND '12/09/12')
)
SELECT Custid, max(dop) dateid, sum(price) Price
FROM T
GROUP BY custid;

SQL DEMO HERE

Upvotes: 1

Related Questions