0

May I ask for some help with the following, please

Perhaps it would help you understand the problem if I show you the results I would like to get from my query ….

region      shipdate    shipday     count
Berkshire   2017-02-16  Thursday    3
No Dels     2017-02-16  Friday      0    <<<< This row does not exist in the table
Collection  2017-02-18  Saturday    5
Surrey      2017-02-18  Saturday    1
No Dels     2017-02-19  Sunday      0    <<<< This row does not exist in the table
Wales       2017-02-20  Monday      5
Scotland    2017-02-21  Tuesday     2
No Dels     2017-02-16  Wednesday   0    <<<< This row does not exist in the table
Bucks       2017-02-23  Thursday    2

Explaining the results. On Thursday, 2017-02-16, there are 3 deliveries scheduled to Berkshire. The following day there are no deliveries at all. Saturday has 5 Collections and 1 delivery to Surrey.

The problem is my query …

Select o.region, o.shipDate, to_char(o.shipDate, 'Day') as shipDay, count(o.region)
From orders o
Where o.shipDate Between '2017-02-16' And '2017-02-23'
  And o.region IS NOT NULL
Group By o.shipDate, o.region
Order By o.shipDate, o.region

Produces the following results ...

region      shipdate    shipday     count
Berkshire   2017-02-16  Thursday    3
Collection  2017-02-18  Saturday    5
Surrey      2017-02-18  Saturday    1
Wales       2017-02-20  Monday      5
Scotland    2017-02-21  Tuesday     2
Bucks       2017-02-23  Thursday    2

What I mean is, where the aggregate count(o.region) = 0, there are obviously no deliveries or collections scheduled so there are no rows to return. Looking around SO there is a great answer by maniek to a question about listing dates from a range.

The answer is ... Link

select i::date from generate_series('2017-02-16', 
  '2017-02-23', '1 day'::interval) i

This does list a range of dates exactly like I want, but I cannot figure out how to join this query with mine. An example of what I have tried is ...

Select o.region, o.shipDate, to_char(o.shipDate, 'Day') as shipDay, count(o.region)
From orders o 

    Left Outer Join
    (select i::date from generate_series('2017-02-16', 
      '2017-02-23', '1 day'::interval) i) bookDate On bookDate.i = o.shipDate

Where o.shipDate Between '2017-02-16' And '2017-02-23'
  And o.region IS NOT NULL
Group By o.shipDate, o.region
Order By o.shipDate, o.region

No matter which join I use ... Left, Right, Left Outer, Right Outer or even Inner, my results are always the same.

My question is how can I generate a row for a date that is not in the table ?

Community
  • 1
  • 1
Johnny
  • 539
  • 8
  • 20
  • 1
    Executive summary: you can JOIN directly to `generate_series`. – mu is too short May 19 '17 at 19:50
  • @mu is too short. Thank you for taking the time to read. I am reading the question to the original question you have linked to. – Johnny May 19 '17 at 19:56
  • I think your link and answer in the duplicate question does answer my question. May I ask, should I leave this question for posterity, or would it be better to delete it? – Johnny May 19 '17 at 20:13
  • 1
    Duplicates aren't necessarily bad, sometimes they just mean that finding the other question and is hard. I found the duplicate because this sounded familiar and I already knew the answer so I could search for the answer (filtered to include only my own answers) rather than the question. I don't think there's anything wrong with leaving your question around. – mu is too short May 19 '17 at 20:52

0 Answers0