2

I need to make a report that will output a result in a daily row result based on the datarange selected.

Example:

Table: sales
->id
->type
->gross
->created_at
->updated_at

For example my query is

SELECT COUNT(case when type = 'Completed Sale' then id end) as complete, 
       COUNT(case when disposition = 'Partial Sale' then id end) as partial, 
       SUM(gross)as revenue 
FROM sales 
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32';

The range is Sept 20 to Sept 24

So basically the output will be like:

complete | partial | revenue
1          1         2.3

What I want the output to be like this

start date | end date    | complete | partial | revenue
___________|_____________|__________|_________|____________
2015-09-20 | 2015-09-21  | 1        | 1       | 2.3
2015-09-21 | 2015-09-22  | 3        | 4       | 4.3
2015-09-22 | 2015-09-23  | 3        | 4       | 4.3
2015-09-23 | 2015-09-24  | 4        | 4       | 3.3

So as you can see, the output is the daily basis of the start date and end date. If choose 1 month range then there will be 30 or 31. Is that possible?

4

All that you have to add is the day (be it the start or the end date). This can be obtained using a cast to date, for example. When you add it to the query, you'll need a GROUP BY clause, too, on this expression - that will produce you the daily aggregates.

The whole query will look like

SELECT created_at::date AS start_date,
       created_at::date + 1 AS end_date,
       COUNT(case when type = 'Completed Sale' then id end) AS complete, 
       COUNT(case when disposition = 'Partial Sale' then id end) AS partial, 
       SUM(gross) AS revenue 
FROM sales 
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32'
GROUP BY created_at::date;

Notes:

  • using start_date and end_date like this can be slightly confusing - in normal life one expects the end date to be inclusive. With the way you use it might not be clear which data belong to which day - I would simply omit the end date.
  • in the GROUP BY clause it's enough to mention the cast expression only once - adding one day in another columns does not result in a different grouping.
  • OMG it works. Thank you so much and for the explanation. I thought this is not possible so I'm thinking that I'll just do the manipulation in PHP but anyway. Thanks! – jackhammer013 Sep 24 '15 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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