Nemo
Nemo

Reputation: 1111

Postgres SQL to get the data after a date

I have data in table 1 that shows the service code for customers along with their service date. The table #2 shows the service code and the order number associated with that service code and when that order took place i.e., report_date. As shown in the desired_result, I want to see the count of distinct orders for each service code that took place after the service_date.

enter image description here

Upvotes: 0

Views: 466

Answers (1)

Simo Kivistö
Simo Kivistö

Reputation: 4503

Here is the query that returns srvc_cust_code and distinct count of orders per srvc_cust_code where the report_date is later than service_date:

SELECT srvc_cust_code, count(DISTINCT orders) FROM table1
    JOIN table2 USING (srvc_cust_code)
    WHERE report_date > service_date
    GROUP BY srvc_cust_code;

If you need more info, please take a look at the PostgreSQL Documentation:
7.2. Table Expressions (infomation on JOIN, USING and GROUP BY)
4.2.7. Aggregate Expressions (information on function count() and DISTINCT)

Upvotes: 3

Related Questions