SQL get distinct customer count by hour

Question

I have two table with datein and timein that is recorded when an order is placed and another table with the column datepicked and timepicked that is recorded when the invoice from the order is picked up. I need to find out how many customer I have every hour, but some are placing order and some are picking up invoices and some are doing both. There could be more than one order and invoice for the same customer on the same day/hour.

OrderTable:
Ordernum
CustomerID
datein
timein

InvoiceTable:
CustomerID
InvoiceID
Ordernum
datepicked
timepicked 

I tried this SQL, but I can't find out how to get the DISTINCT CUSTOMERID from both tables and the date and hours lined up on both tables, I noticed in the result if there was no order for one hour / day the columns did not lineup.

Select o.datein, i.datepicked, (o.datein) As iDay, HOUR(o.timein) as iH,
DayOfMonth(i.datepicked) As pDay, HOUR(i.timepicked) as pH, Count(*) as Total
from OrderTable o, InvoiceTable i
Where
o.datein >= '2019-01-01' and o.datein <= '2019-12-31'
GROUP BY o.datein, i.datepicked, iDay, iH, pDay, pH

Thanks for any help. Kim

Answer 1

Not sure why the tables setup as they are, but if all you really care about is the DISTINCT customer per date/hour, I would do the following by pre-unioning just those records, then distinct count from that. Dont worry about joining if the transactions were done at separate times unless your consideration is that the order and invoice are BOTH handled within the same hour. What happens if one order is done at 10:59 and the invoice is 11:00 only 1 minute apart, but representative of 2 different hours. It would be the same 1 customer showing up in each individual hour component.

Notice the first "FROM" clause has a union to get all records to the same column name bulk of records, each of their own respective 2019 calendar activity date. Once that is done, get and group by for the COUNT DISTINCT customers.

select
        AllRecs.DateIn,
        hour( AllRecs.TimeIn ) ByHour,
        DayOfMonth(AllRecs.DateIn) pDay, 
        Count( distinct AllRecs.CustomerID ) UniqueCustomers
    from
    ( select 
            ot.CustomerID, 
            ot.datein, 
            ot.timein
        from 
            OrderTable ot
        where
                ot.datein >= '2019-01-01' 
            and ot.datein <= '2019-12-31'
      union all
      select 
            it.CustomerID, 
            it.datepicked datein, 
            it.timepicked timein
        from 
            InvoiceTable it
        where
                it.datepicked >= '2019-01-01' 
            and it.datepicked <= '2019-12-31' ) AllRecs
    group by
        AllRecs.DateIn,
        hour( AllRecs.TimeIn ),
        DayOfMonth(AllRecs.DateIn)