Thread: Data Grid

I'm not sure if there is a way to do this without using a programming
language/function ie a for loop for example. Ideally I would like to do
it all in postgresql

I have 2 tables, for example:

Item
====
id
name
cost
sold_date
item_type_id

Item Type
=========
id
item_type

Now I want to make a report whereby I sum the cost of each item type per
month (within a range) so I end up with the following:

               01/04  02/04  03/04  04/04  05/04  06/04  ...  etc
item_type A
item_type B
item_type C
.
.
.
etc

THe problem is that I don't know how many item types or months I need to
do it for (obviously it will be for all item types in the item type
table, but could be for 1 month or 100 months!). The only way I can see
of doing it is summing the total cost per item for a month, then
repeating that query for each month in the actual application is this
correct or is there a way to do it natively?

Many Thanks

Jake

Jake Stride wrote:

> I'm not sure if there is a way to do this without using a programming
> language/function ie a for loop for example. Ideally I would like to
> do it all in postgresql
>
> I have 2 tables, for example:
>
> Item
> ====
> id
> name
> cost
> sold_date
> item_type_id
>
> Item Type
> =========
> id
> item_type
>
> Now I want to make a report whereby I sum the cost of each item type
> per month (within a range) so I end up with the following:
>
>               01/04  02/04  03/04  04/04  05/04  06/04  ...  etc
> item_type A
> item_type B
> item_type C
> .
> .
> .
> etc
>
> THe problem is that I don't know how many item types or months I need
> to do it for (obviously it will be for all item types in the item type
> table, but could be for 1 month or 100 months!). The only way I can
> see of doing it is summing the total cost per item for a month, then
> repeating that query for each month in the actual application is this
> correct or is there a way to do it natively?
>
> Many Thanks
>
> Jake


I've done something similar, but I use a function and a view to get the
results, and the report columns and rows are transposed from how you
would like it, ie the dates are along side and the items are along the
top. The items are coded directly into the function and view
definitions, but the dates and counts are dynamically updated, so if you
were to use this you would manually have to update the function and vew
to accomodate them.  If you're happy with that I could send you a copy
of the function and view, which with only a little modification on your
part, could give you results.

Ron

Ron St-Pierre wrote:

>
> I've done something similar, but I use a function and a view to get
> the results, and the report columns and rows are transposed from how
> you would like it, ie the dates are along side and the items are along
> the top. The items are coded directly into the function and view
> definitions, but the dates and counts are dynamically updated, so if
> you were to use this you would manually have to update the function
> and vew to accomodate them.  If you're happy with that I could send
> you a copy of the function and view, which with only a little
> modification on your part, could give you results.
>
> Ron
>
Yes I would be interested in seeing what you have, the columns/rows
aren't an issue as I can adjust them when I get them from the database
and draw the graph. The problem I may have is that I don't know how many
types there will be as people can add/remove them from the 'type' table
as they please, but I may be able to work something out with what you
have done already.

Thanks

Jake

Jake Stride wrote:

> Ron St-Pierre wrote:
>
>>
>> I've done something similar, but I use a function and a view to get
>> the results, and the report columns and rows are transposed from how
>> you would like it, ie the dates are along side and the items are
>> along the top. The items are coded directly into the function and
>> view definitions, but the dates and counts are dynamically updated,
>> so if you were to use this you would manually have to update the
>> function and vew to accomodate them.  If you're happy with that I
>> could send you a copy of the function and view, which with only a
>> little modification on your part, could give you results.
>>
>> Ron
>>
> Yes I would be interested in seeing what you have, the columns/rows
> aren't an issue as I can adjust them when I get them from the database
> and draw the graph. The problem I may have is that I don't know how
> many types there will be as people can add/remove them from the 'type'
> table as they please, but I may be able to work something out with
> what you have done already.
>
> Thanks
>
> Jake

I'm not sure that this is what you're looking for, but if it isn't it
may just provide you with a starting point to solve it. Okay, here it
is. My function just uses a count of things, you could modify it to do a
sum of sales. I haven't tried but you could probably get the function to
generate the items too. Also, change sales_date to month, etc, and you
would need an itemSummary table with fields sold_date, item01, etc.

Anyway, this might be a starting point for you to solve your problem.

Ron


CREATE OR REPLACE FUNCTION updateItemSummary() RETURNS void AS '
    DECLARE
        compDate DATE;
        currCount INT;
    BEGIN
        compDate := current_date;
        LOOP
            DELETE FROM itemSummary WHERE sold_date = compDate;
            INSERT INTO itemSummary (sold_date) VALUES (compDate);
            UPDATE itemSummary
              SET item01 = (SELECT count(*) FROM sales_data WHERE
sold_date = compDate AND item_type_id = 55),
              item01 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 59),
              item02 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 48),
              item03 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 36),
              item04 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 96),
              item05 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 99),
              item06 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 98),
              item07 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 97),
              item08 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 96),
              item09 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 95),
              item11 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 93),
             dow = to_char(compDate::timestamp, Dy)
             WHERE compDate = sold_date;
            compDate = compDate - interval 1 day;
            IF compDate = current_date - interval 15 days THEN
                EXIT;
            END IF;
        END LOOP;
        RETURN;
    END;
' LANGUAGE 'plpgsql';

SELECT updateItemSummary();


CREATE VIEW currentstats
 AS SELECT DISTINCT ON (sold_date)
  sold_date AS date,
  dow AS "day",
  item02,
  item03,
  item04,
  item05,
  item06,
  item07,
  item08,
  item09,
  item10
   FROM itemSummary
  ORDER BY updatedate DESC
 LIMIT 15;


    date    | day |item01|item02|item03|item04|item05   |item06| item07   |item08| item09|item10

------------+-----+------+------+------+------+---------+------+----------+------+-------+------

 2004-10-13 | Wed |    0 |    0 |    0 |    0 |       0 |    0 |        0 |    0 |  1878 |  1878

 2004-10-12 | Tue | 1893 | 2142 | 3599 | 1310 |    1721 | 3558 |    16904 | 1073 | 17978 | 17978

 2004-10-11 | Mon |    0 |    0 | 3598 | 1313 |     991 | 3558 |    16867 |  942 |  1542 |  1542

 2004-10-10 | Sun |    0 |    0 |    0 |    0 |       0 |    0 |        0 |    0 |     0 |     0

 2004-10-09 | Sat |    0 |    0 |    0 |    0 |       0 |    0 |        0 |    0 |     0 |     0

 2004-10-08 | Fri | 1893 | 2142 | 3599 | 1307 |    1747 | 3558 |    16978 | 1076 |  1272 |  1272

 2004-10-07 | Thu | 1893 | 2141 | 3600 | 1312 |    1724 | 3557 |    16961 | 1075 |   643 |   643

 2004-10-06 | Wed | 1892 | 2141 | 3598 | 1310 |    1777 | 3555 |    16979 | 1075 |  2356 |  2356

 2004-10-05 | Tue | 1890 | 2140 | 3596 | 1308 |    1747 | 3553 |    16892 | 1075 |   343 |   343

 2004-10-04 | Mon | 1888 | 2141 | 3599 | 1308 |    1731 | 3552 |    16934 | 1075 |   132 |   132

 2004-10-03 | Sun |    0 |    0 |    0 |    0 |       0 |    0 |        0 |    0 |     0 |     0

 2004-10-02 | Sat |    0 |    0 |    0 |    0 |       0 |    0 |        0 |    0 |     0 |     0

 2004-10-01 | Fri | 1888 | 2144 | 3599 | 1310 |    1667 | 3552 |    16957 | 1073 |    94 |    94

 2004-09-30 | Thu | 1880 | 2144 | 3603 | 1311 |    1723 | 3557 |    16802 | 1068 |   294 |   294

 2004-09-29 | Wed | 1879 | 2144 | 3604 | 1309 |    1792 | 3560 |    16907 | 1072 |   285 |   285



Ron