Re: Data Grid

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