CODEWITHSUNDEEP
sqlpostgresql
Bradley
Bradley

Reputation: 2141

How do you select from a date range as the data source

Short of creating a table with all of the values of a date range, how would I select from a datarange as a datasource.

What I'm trying to accomplish is to create a running total of all items created within the same week from separate tables, while showing weeks with 0 new

example table:

items
-----------------------------
created_on | name     | type 
-----------------------------
2012-01-01 | Cards    | 1
2012-01-09 | Red Pen  | 2
2012-01-31 | Pencil   | 2
2012-02-01 | Blue Pen | 2

types
--------------
name     | id 
--------------
Fun      | 1
Writing  | 2

sample output:

----------------------------
year | week | fun | writing 
----------------------------
2012 | 1    | 1   | 0
2012 | 2    | 0   | 1
2012 | 3    | 0   | 0
2012 | 4    | 0   | 0
2012 | 5    | 0   | 2

Upvotes: 0

Views: 98

Answers (2)

Bradley
Bradley

Reputation: 2141

Very close erikxiv, but you got me in the right direction. I have multiple tables I need to grab information from, this the additional select in the select fields.

select 
    date_year.num,
    date_week.num,
    (   select count(*) from items x
        and EXTRACT(YEAR FROM x.created_on) = date_year.num
        and EXTRACT(WEEK FROM x.created_on) = date_week.num
    ) as item_count
from
    (SELECT generate_series(2011, date_part('year', CURRENT_DATE)::INTEGER) as num) as date_year,
    (SELECT generate_series(1,52) as num) as date_week
where
    (
        date_year.num < EXTRACT (YEAR FROM CURRENT_DATE) 
        OR 
        (
            date_year.num = EXTRACT (YEAR FROM CURRENT_DATE) AND
            date_week.num <= EXTRACT (WEEK FROM CURRENT_DATE)
        )
    )

Upvotes: 0

erikxiv
erikxiv

Reputation: 4075

You could generate a number series for the week numbers

SELECT
  w.week
FROM
  (SELECT generate_series(1,52) as week) as w

Example

SELECT
  w.year,
  w.week,
  COUNT(i1) as fun,
  COUNT(i2) as writing
FROM (SELECT 2012 as year, generate_series(1,6) as week) as w
LEFT JOIN items i1 ON i1.type = 1 AND w.week = EXTRACT(WEEK FROM i1.created_on)
LEFT JOIN items i2 ON i2.type = 2 AND w.week = EXTRACT(WEEK FROM i2.created_on)
GROUP BY 
  w.year,
  w.week
ORDER BY
  w.year,
  w.week

Upvotes: 2

Related Questions