Little help with my report

Smakosh

Hello! It will be nice to someone help me with my report or give same ideas. I want get in the results 'Product name' and how many days his quantity=0. For example I have this table

So I want to see:

Period is not necessary.

PS. I dont want you do this for  me, if you give me some tip it will be Okey.

Thomas Dodds

logical column with a 1 or 0 based on the criteria of quantity by day ... then sum up the new logical column over time ...

Smakosh

Thanks Thomas I do like you say.

Did you know how I can do this with Period. I must write question like this "show me period, product, quantity, and how many when day -1 quantity of produt was 1 OR NULL".

For 0/1 column i do this formula:

SUM((CASE WHEN "Dane"."Quantity"=0 THEN 1 WHEN "Dane"."Quantity">0 THEN 0   WHEN  "Dane"".Quantity)" IS NULL THEN 1 ELSE NULL END) BY "Product"."Product (name)"

Result:

Smakosh

mistake:

Did you know how I can do this with Period. I must write question like this "show me period, product, with quantity=0 or NULL, and how many days quantity was 0 or NULL when day -1 quantity of product was 1

Thomas Dodds

You are much better off 'architecting' a physical data structure that records the PRODUCT, DAY, QUANTITY, PREVIOUS DAY QUANTITY, LAST NON-ZERO DAY

Have a row for EVERY Product and Day ... now you have no nulls only zeros for appropriate day.

PREVIOUS DAY QUANTITY = LAG(QUANTITY) OVER(PARTITION BY PRODUCT ORDER BY DAY)

LAST NON-ZERO DAY = (SELECT MAX(DAY) FROM TABLE WHERE PRODUCT = x.PRODUCT and QUANTITY > 0)

     ^ a sub-select in the row will get you this

Smakosh

Thanks Thomas