current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I am needing to create this table (not really for donuts).

I basically want to output the time to sell a product within a certain time frame.

The time frame is defined in a table and needs to be able to be reconfigured (dropped and recreated) without breaking the query.

That is to say the time_frames we are interested in monitoring will, by design, change often. Today there may be 4 timeframes (6h ea) defined, tomorrow I might make just one (24h), the next day there may be 12 rows(2 ea). The time_frames might also be of differing lengths. The columns would stay the same.

SQLFiddle

CREATE TABLE time_frame 
  (
    id SERIAL NOT NULL
    ,start_time time
    ,end_time time
    ,PRIMARY KEY(id)
  );

CREATE TABLE donut_sales 
  (
    saleid SERIAL NOT NULL
    ,donutid INT
    ,donutname TEXT
    ,stocked timestamp
    ,sold timestamp
    ,PRIMARY KEY (saleid)
  );

--  SELECT * FROM FANCY_PIVOT_TABLE_I_DONT_UNDERSTAND
--  +---------+-------------+----------------+----------------+----------------+
--  | donutid | donutname   |  time_frame_1  |  time_frame_2  |  time_frame_3  |
--  +---------+-------------+----------------+----------------+----------------+
--  |       1 | sprinkles   |      00:17:66  |      00:17:66  |      00:17:66  |
--  |       2 | jelly       |      00:17:66  |      00:17:66  |      00:17:66  |
--  |       3 | custard     |      00:17:66  |      00:17:66  |      00:17:66  |
--  +---------+-------------+----------------+----------------+----------------+
share|improve this question
    
"That is to say the time_frames we are interested in monitoring will, by design, change often." you mean new time_frames rows are added or does the table structure change.. "The time frame is defined in a table and needs to be able to be reconfigured (dropped and recreated) without breaking the query." with the same structure? –  Raymond Nijland Aug 31 '13 at 11:28
    
The structure would stay the same but I might drop and recreate the rows with all new values. Today there could be four rows, and tomorrow there could be 12. –  lazfish Sep 2 '13 at 13:54
add comment

1 Answer

up vote 1 down vote

I actually don't understand how time values appear in your output table, but if you want to count number of donuts sold in each time_frame, you can use this query:

select
    ds.donutid, ds.donutname,
    sum(case when ts.id = 1 then 1 else 0 end) as time_frame_1,
    sum(case when ts.id = 2 then 2 else 0 end) as time_frame_2,
    sum(case when ts.id = 3 then 3 else 0 end) as time_frame_3,
    sum(case when ts.id = 4 then 4 else 0 end) as time_frame_4
from donut_sales as ds
    inner join time_frame as ts on
        ts.start_time <= ds.sold::time and ts.end_time > ds.sold::time
group by ds.donutid, ds.donutname
order by ds.donutid

sql fiddle demo

You can't make dynamic number of time_frames, though, because you cannot make dynamic columns in output in PostgreSQL.

share|improve this answer
    
I was looking specifically for dynamic output. Like this in MSSQL stackoverflow.com/questions/11404062/… –  lazfish Sep 10 '13 at 15:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.