Question 312465 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: View adjust Date based on hour

I have a view that queries sales data into a condensed table which sums net_sales for each location. The problem i have is when there are sales after midnight. My code is grouping by Date so any sales after midnight are listed on the wrong day.

SELECT r.name AS store,
r.id,
date(se.date) AS date,
c.dow,
c.week,
c.period,
c.year,
sum(se.netsales) AS net_sales,
sum(se.numberofguests) AS guest_count
FROM sales_employee se
JOIN restaurants r ON r.locationid::text = se.location
JOIN calendar c ON c.date = date(se.date)
GROUP BY r.name, r.id, (date(se.date)), c.dow, c.week, c.period, c.year
ORDER BY (date(se.date)) DESC, r.name;

There is another column in sales_employee (orderhour) which is bigint from 0-23. The system shuts down at 4am so what i need to do is collect sales from orderhour[4] through orderhour[3] and apply the sales of orderhour[0-3] to the previous date. Can that be done?

Tags:

2

Answers


  1. 0

    Whereever you use the date, use:

    (datecolumn - interval '4 hours')::date

    instead. Subtracting four hours moves transactions between 00:00 and 04:00 to one day earlier. So for example:

    JOIN calendar c ON c.date = date(se.date - interval '4 hours')
    Login or Signup to reply.
  2. 0

    I tried modifying your SQL query to allocate sales made between midnight and 4 AM to the previous day, using a CASE statement to adjust the date based on the orderhour column. Give it a try

      SELECT
        r.name AS store,
        r.id,
        CASE 
            WHEN se.orderhour >= 4 THEN date(se.date)
            ELSE date(se.date) - interval '1 day'
        END AS adjusted_date,
        c.dow,
        c.week,
        c.period,
        c.year,
        sum(se.netsales) AS net_sales,
        sum(se.numberofguests) AS guest_count
    FROM
        sales_employee se
    JOIN
        restaurants r ON r.locationid::text = se.location
    JOIN
        calendar c ON c.date = CASE 
                                 WHEN se.orderhour >= 4 THEN date(se.date)
                                 ELSE date(se.date) - interval '1 day'
                               END
    GROUP BY
        r.name, r.id, adjusted_date, c.dow, c.week, c.period, c.year
    ORDER BY
        adjusted_date DESC, r.name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search