sql experts around?

HoMa

to be precise: SqLite is what I need. My head is steaming - I did not manage to solve my problem.

Consider this:
There is a table that describes purchases:

int productId
date dateOfPurchase
float value

Now I want a SQL statement, that gives me a new table like this:

dateOfPurchase  | productId | sum_of_purchase_last_year(value)

And with last year I mean the time between dateOfPurchase and one year before that date. My problem is, that the window changes with every line of the table, so I started doubting, if this is at all possible.
I tried with the sqlite window functions (OVER and RANGE) but ... I failed.

Looking forward for your ideas!
HM

HoMa

@kshegunov Heureka!
You put me onto the right track: I found the following SQL working on SQLITE, solving my problem:

SELECT 
  productId 
, dateOfP 
, (SELECT sum(value) 
   FROM (SELECT value 
         FROM purchases AS pu 
         WHERE pu.productId = purchases.productId 
               AND pu.dateOfP > DATE(purchases.dateOfP, '-1 years') 
               AND pu.dateOfP <= purchases.dateOfP) ) AS Summe

FROM purchases
GROUP BY dateOfP

So thank you and also to Christian, who pointed into the right direction!

Demolishun

Download "DB Browser for Sqlite"
Build tables and look at creation strings.

kshegunov

@HoMa said in sql experts around?:

Now I want a SQL statement, that gives me a new table like this:

I suppose you mean a query that produces such a recordset?
Something like this:

SELECT productId, dateOfPurchase, SUM(value) WHERE dateOfPurchase > date('now', '-1 years') GROUP BY (productId)

Obviously I haven't tested it, but if my old and frail memory serves it should work.

HoMa

Hi and thank you for this reply. This is - unfortunately - not what I am looking for. The period here is always the same: from one year ago until now.
But I want to have it so that it is always 1 year before dateOfPurchase until dateOfPurchase. Having the time slot differently for each row in the result table is, what makes it so difficult - I think.
;) Maybe there are other ideas in your old and frail memories?

Best regards
HM

Christian Ehrlicher

Then replace

date('now', '-1 years')

with a subselect which queries this value.

kshegunov

@HoMa said in sql experts around?:

But I want to have it so that it is always 1 year before dateOfPurchase until dateOfPurchase.

This truly makes no sense to me, but you could refer to the dateOfPurchase column in the where clause instead of 'now'.

HoMa

Thx - again.
This is - unfortunately - again not working. I played around and used a "GROUP BY dateOfPurchase" - which got me closer. I now have one row for each date - which is what I want - and I get a SUM - but it is not what one would expect. Actually I do not understand the values, that get displayed.

This is the latest, but not working "prototype" SQL:

SELECT DATE(dateOfPurchase, '-1 years') AS from, dateOfPurchase AS to, productId, SUM(value) /100.
FROM purchases
WHERE dateOfPurchase > DATE(dateOfPurchase, '-1 years') AND dateOfPurchase <= bis
GROUP BY dateOfPurchase

JonB

@HoMa

WHERE dateOfPurchase > DATE(dateOfPurchase, '-1 years') AND dateOfPurchase <= bis

I don't know what bis is, but don't you think dateOfPurchase > DATE(dateOfPurchase, '-1 years') is always true?
@kshegunov remarked on this in your description of what you say you want.

kshegunov

I believe something like this is what is intended (in the spirit of what @Christian-Ehrlicher said):

SELECT productId as refProduct, dateOfPurchase as refDate, SUM( SELECT value FROM purchases WHERE dateOfPurchase > date(refDate, '-1 years') AND productId = refProduct) AS totalValue  FROM purchases

This is one heavy query, though.

HoMa

Yes - quite impressive. But it looks very plausible. I get a "no such column" on refDate though ...

HoMa

@kshegunov Heureka!
You put me onto the right track: I found the following SQL working on SQLITE, solving my problem:

SELECT 
  productId 
, dateOfP 
, (SELECT sum(value) 
   FROM (SELECT value 
         FROM purchases AS pu 
         WHERE pu.productId = purchases.productId 
               AND pu.dateOfP > DATE(purchases.dateOfP, '-1 years') 
               AND pu.dateOfP <= purchases.dateOfP) ) AS Summe

FROM purchases
GROUP BY dateOfP

So thank you and also to Christian, who pointed into the right direction!