Re: Data Grid - Mailing list pgsql-novice

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