asked on

Need a solid Expert on this Group By Query Syntax Help.

TABLES:
FIRST PASS

Table: CustomerDetail
CustomerID DeliveryDate AsOfDate
1 4/1/2017 4/2/2017 1:30PM
1 4/2/2017 4/2/2017 1:30PM
1 4/3/2017 4/2/2017 1:30PM

Table: CustomerDetailLog
CustomerID DeliveryDate LastAsOfDate
[No Records]

Table: CustomerCloseDate
CustomerID DeliveryCloseDate AsOfDate
[No Records]

DeliveryDate and DeliveryCloseDate are DATE datatype and AsOfDate is a DATETIME

The CustomerDetailLog table is going to be used to track the MAX AsOfDate for each CustomerID/DeliveryDate. I need to get all the CustomerID, DeliveryDate, MAX(AsOfDate) group records from the CustomerDetail table for loading the data from that table later in the process. The CustomerCloseDate table is where things get tricky. At the end of every month we will have a record in this table with the last day of the month DeliveryCloseDate and a AsOfDate which is like a lock date. If there is no record for that month DeliveryCloseDate in CustomerCloseDate I would just be tracking the MAX(AsOfDate) for each DeliveryDate from the CustomerDetail compared to the CustomerDetailLog table. If there is a record in the CustomerCloseDate for a MONTH YEAR I really don't need the day I can only get the CustomerID/DeliveryDate MAX(AsOfDate) for the whole month in the CustomerDetail table where the AsOfDate <= AsOfDate DATETIME in the CustomerCloseDate table MONTH YEAR.

Here are the example passes that will help visualize as it can be confusing.

Looking at the first pass example above we don't have a close date record or CustomerDetailLog record. So records would now look like this in the tables based on getting a SELECT CustomerID, DeliveryDate, MAX(AsOfDate) grouped.

FIRST PASS RESULTS:
Table: CustomerDetail
CustomerID DeliveryDate AsOfDate
1 4/1/2017 4/2/2017 1:30PM
1 4/2/2017 4/2/2017 1:30PM
1 4/3/2017 4/2/2017 1:30PM

Table: CustomerDetailLog
CustomerID DeliveryDate LastAsOfDate
1 4/1/2017 4/2/2017 1:30PM
1 4/2/2017 4/2/2017 1:30PM
1 4/3/2017 4/2/2017 1:30PM

Table: CustomerCloseDate
CustomerID DeliveryCloseDate AsOfDate
[No Records]

SECOND PASS:
New Records now are loaded to the CustomerDetail Table new AsOfDate > than the the LastAsOfDate in CustomerDetailLog for CustomerID, DeliveryDate and LastAsOfDate

Table: CustomerDetail
CustomerID DeliveryDate AsOfDate
1 4/1/2017 4/2/2017 1:30PM
1 4/2/2017 4/2/2017 1:30PM
1 4/3/2017 4/2/2017 1:30PM
1 4/1/2017 4/3/2017 2:45PM
1 4/2/2017 4/3/2017 2:45PM
1 4/3/2017 4/3/2017 2:45PM

Want to update the > AsOfDate Grouped By DeliveryDate, MAX(AsOfDate) or insert if there is no record for that DeliveryDate, MAX(AsOfDate) in this table

Table: CustomerDetailLog
CustomerID DeliveryDate LastAsOfDate
1 4/1/2017 4/3/2017 2:45PM
1 4/2/2017 4/3/2017 2:45PM
1 4/3/2017 4/3/2017 2:45PM

Table: CustomerCloseDate
CustomerID DeliveryCloseDate AsOfDate
[Still No Records]

THIRD PASS:
Now we have a close date for April 2017 the AsOfDate can be greater than that month as well. We also get some new records now for April but the AsOfDate > the close date 5/5/2017 3:30PM as you can see below and some less.

Table: CustomerDetail
CustomerID DeliveryDate AsOfDate
1 4/1/2017 4/2/2017 1:30PM
1 4/2/2017 4/2/2017 1:30PM
1 4/3/2017 4/2/2017 1:30PM
1 4/1/2017 4/3/2017 2:45PM
1 4/2/2017 4/3/2017 2:45PM
1 4/3/2017 4/3/2017 2:45PM
1 4/1/2017 4/20/2017 7:33PM
1 4/2/2017 4/25/2017 8:11PM
1 4/3/2017 5/5/2017 6:45PM

In this case above we would not want to update the 5/5/2017 6:45PM record the other two we would.
Table: CustomerDetailLog
CustomerID DeliveryDate LastAsOfDate
1 4/1/2017 4/20/2017 7:33PM
1 4/2/2017 4/25/2017 8:11PM
1 4/3/2017 4/3/2017 2:45PM (this record would not change)

Table: CustomerCloseDate
CustomerID DeliveryCloseDate AsOfDate
1 4/30/2017 5/5/2017 3:30PM

NEEDED QUERY:
I need a query that can handle these scenarios where I can get the GROUPED CustomerID, DeliveryDate and AsOfDate combinations from the CustomerDetail table for a next load then I can do the updates to the CustomerDetailLog to track the dates etc.

ASKER CERTIFIED SOLUTION

Arifhusen Ansari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sbornstein2

ASKER

I really need the query side that can combine looking at if there is a record in the closed table logic or using the max to the log table. I can see what your doing but I need to be able to put it together in one query against the CustomerDetail table getting the CustomerID, DeliveryDate and MAX(AsOfDate) into a CTE for example. Then once I have that additional processes will get all the data in the CustomerDetail table joined to that CTE. The last process would then update OR insert into the CustomerDetailLog for that CustomerID and DeliveryDate if that makes sense.

I don't see how you are matching against the close date table if a record exists then I can only get records from the CustomerDetail table for that month 1st day to the =< AsOfDate.

1st step: First check is to get the grouped by CustomerID, DeliveryDate, MAX(AsOfDate) in the CustomerDetail table.
2nd step: Reduce any records from this 1st step result if there is a Month End Close AsOfDate for anything within that month with a AsOfDate > the close AsOfDate
3rd step: Only take records from Step 2 that the DeliveryDate is not in the CustomerDetailLog table OR the Step 2 DeliveryDate AsOfDate > than the Log table AsOfDate.

If we have a 4/30/2017 DeliveryDate with AsOfDate = 5/5/2017. After I get Step 1 grouped I would only be able to use anything with MONTH YEAR 4 2017 don't really care about the day in this check it's the whole month that has a AsOfDate <= 5/5/2017.

I know confusing hope I am not losing you :)

sbornstein2

ASKER

Thanks sorry for the delay awarding the points.