centralmike
asked on
Access module into a teradata procedure or db2 procedure
I have access database that runs 3 business rules and updates a final table in access. The logic is in the module rule mod. I need to loop through 1 million row tables in access and perform procedure logic against the record set. I am thinking before the database gets to big I should process this information on Teradata platform. Somehow convert this access module in a stored procedure if that is possible. Or there might be another way through subqueries to get the final outcome.
Sample Database
Customer Product Orderdate
11111 VIP 01/01/2017
11111 PLANE 01/16/2017
11111 HOTEL 02/05/2017
22222 VIP 01/01/2017
22222 PLANE 02/28/2017
33333 VIP 01/01/2017
33333 VIP 01/06/2017
33333 HOTEL 01/17/2017
44444 VIP 01/01/2017
55555 VIP 01/01/2017
55555 VIP 02/15/2017
66666 VIP 01/01/2015
66666 VIP 01/28/2017
66666 PLANE 01/29/2017
66666 OTHER 02/01/2017
Business Rule 1 - The first product that has to be ordered is the VIP PRODUCT by customer.
If the VIP PRODUCT is not ordered all rules fail.
Business Rule 2 - If VIP PRODUCT was ordered where there any orders placed within 30 days of the first order for each customer.
Final Output - should look like this:
CustomerID CustomerName OrderRuleTest ProductRuleTest OrderDateTest OrderDateFlag
11111 Customer A PASS PASS PASS 1
33333 Customer C PASS PASS PASS 1
Well the question I am asking is this something that can be created with straight sql queries? I have created stored procedures before just haven't ventured in procedure logic with sql statements. The following sql answers the second business rule.
SELECT A.CustomerID, A.OrderDate, (SELECT Count(*) FROM tblOrder
WHERE (tblOrder.CustomerID = A.CustomerID)
AND (tblOrder.OrderDate > A.OrderDate)
AND (tblOrder.OrderDate < DateAdd("d", 31, A.OrderDate))) AS OtherOrders, A.ProductID
FROM tblOrder AS A;
Business Rule 1 - The first product that has to be ordered is the VIP PRODUCT by customer.
If the VIP PRODUCT is not ordered all rules fail.
Business Rule 2 - If VIP PRODUCT was ordered where there any orders placed within 30 days of the first order for each customer.
I will load the results into table showing which customer pass/failed the rules like the following: a
Final Output - should look like this:
CustomerID CustomerName OrderRuleTest ProductRuleTest OrderDateTest OrderDateFlag
11111 Customer A PASS PASS PASS 1
33333 Customer C PASS PASS PASS 1
I thought this might be more efficient to handle on the database instead of access with the volume of records.
myWork_v5.accdb
Sample Database
Customer Product Orderdate
11111 VIP 01/01/2017
11111 PLANE 01/16/2017
11111 HOTEL 02/05/2017
22222 VIP 01/01/2017
22222 PLANE 02/28/2017
33333 VIP 01/01/2017
33333 VIP 01/06/2017
33333 HOTEL 01/17/2017
44444 VIP 01/01/2017
55555 VIP 01/01/2017
55555 VIP 02/15/2017
66666 VIP 01/01/2015
66666 VIP 01/28/2017
66666 PLANE 01/29/2017
66666 OTHER 02/01/2017
Business Rule 1 - The first product that has to be ordered is the VIP PRODUCT by customer.
If the VIP PRODUCT is not ordered all rules fail.
Business Rule 2 - If VIP PRODUCT was ordered where there any orders placed within 30 days of the first order for each customer.
Final Output - should look like this:
CustomerID CustomerName OrderRuleTest ProductRuleTest OrderDateTest OrderDateFlag
11111 Customer A PASS PASS PASS 1
33333 Customer C PASS PASS PASS 1
Well the question I am asking is this something that can be created with straight sql queries? I have created stored procedures before just haven't ventured in procedure logic with sql statements. The following sql answers the second business rule.
SELECT A.CustomerID, A.OrderDate, (SELECT Count(*) FROM tblOrder
WHERE (tblOrder.CustomerID = A.CustomerID)
AND (tblOrder.OrderDate > A.OrderDate)
AND (tblOrder.OrderDate < DateAdd("d", 31, A.OrderDate))) AS OtherOrders, A.ProductID
FROM tblOrder AS A;
Business Rule 1 - The first product that has to be ordered is the VIP PRODUCT by customer.
If the VIP PRODUCT is not ordered all rules fail.
Business Rule 2 - If VIP PRODUCT was ordered where there any orders placed within 30 days of the first order for each customer.
I will load the results into table showing which customer pass/failed the rules like the following: a
Final Output - should look like this:
CustomerID CustomerName OrderRuleTest ProductRuleTest OrderDateTest OrderDateFlag
11111 Customer A PASS PASS PASS 1
33333 Customer C PASS PASS PASS 1
I thought this might be more efficient to handle on the database instead of access with the volume of records.
myWork_v5.accdb
ASKER
Ken, Thanks for responding. The first rule the VIP package has to be ordered first. the second rule Any other product including the VIP Package has to be ordered within 30 days of the first order. The only customer that meet that criteria would be 11111 and 33333
ASKER
Sorry Teradata is platform I already have.
ASKER
Kent, If this can't be accomplished in Teradata, I also have access UDB(DB2).
Hi Mike,
There seems to be some missing pieces. When I look at your final output, I don't see how OrderDateTest and OrderDateFlag are produced. (I'm assuming that getting the Customer Name when you have the Customer ID is nearly trivial.)
Rolling up the data can be done with SQL very close to your "business rule 2" query. It can also be built into a single query, but ACCESS supports a simpler version of SQL queries than most of the other engines, particularly the older versions.
Using DB2 style SQL my first pass at solving this looks like this:
ID 2
---------- -----------
11111 1
22222 0
33333 1
33333 1
44444 0
55555 0
55555 0
66666 0
66666 2
Looking at the result, any row with a zero for the count will get filtered out. Those IDs have VIP records, but no other product within 30 days.
That gives us:
ID 2
---------- -----------
11111 1
33333 1
33333 1
66666 2
ID 11111 fits my simple query (has a VIP record and another purchase within 30 days), but obviously I'm missing something.
ID 33333 has two VIP records just days apart and another purchase that fits the 30 day window for both of them. Does this get collapsed into a single row for that ID or is there more involved than that?
There seems to be some missing pieces. When I look at your final output, I don't see how OrderDateTest and OrderDateFlag are produced. (I'm assuming that getting the Customer Name when you have the Customer ID is nearly trivial.)
Rolling up the data can be done with SQL very close to your "business rule 2" query. It can also be built into a single query, but ACCESS supports a simpler version of SQL queries than most of the other engines, particularly the older versions.
Using DB2 style SQL my first pass at solving this looks like this:
select id, (select count(*)
from mike t1
where product <> 'VIP'
and orderdate between t0.orderdate and t0.orderdate + 30 days and t0.id = t1.id)
from mike t0
where product = 'VIP'ID 2
---------- -----------
11111 1
22222 0
33333 1
33333 1
44444 0
55555 0
55555 0
66666 0
66666 2
Looking at the result, any row with a zero for the count will get filtered out. Those IDs have VIP records, but no other product within 30 days.
That gives us:
ID 2
---------- -----------
11111 1
33333 1
33333 1
66666 2
ID 11111 fits my simple query (has a VIP record and another purchase within 30 days), but obviously I'm missing something.
ID 33333 has two VIP records just days apart and another purchase that fits the 30 day window for both of them. Does this get collapsed into a single row for that ID or is there more involved than that?
ASKER
You are correct ID 1 and 3 gets collapse into 1 row. ID 6 does not qualify because the first order was placed in 01/01/2015. The second order for customer 6 is placed two years later.
ASKER
The order date does not come into play until after the first VIP order.
Ok. I can see how the two ID 3 rows collapse into a single row. What rule collapses ID 1 into ID 3?
ASKER
id 1 meets both rules. The first rule meets the vip order. The second record for id 1 meets the second business rule. A order was placed within 30 days of the first order. The third record for id 1 does not meet any rule at all, it happens 33 days after the first order. I would insert customer 1 an 3 into a table like the follow:
Final Output - should look like this:
CustomerID CustomerName OrderPlace ProductRuleTest OrderDateTest
11111 Customer A PASS PASS PASS
33333 Customer C PASS PASS PASS
I thought this type of task would have to loop through the recordset to answer these questions.
Final Output - should look like this:
CustomerID CustomerName OrderPlace ProductRuleTest OrderDateTest
11111 Customer A PASS PASS PASS
33333 Customer C PASS PASS PASS
I thought this type of task would have to loop through the recordset to answer these questions.
Ah. I misunderstood. Was thinking that ID 1 and ID 3 collapsed into the same row.
ID 6 has 2 VIP rows. Both of the "other product" rows for ID 6 are within 30 days of the second VIP row. It would seem that it would meet your needs, but the stated rule is that only the first VIP row counts.
With that at my disposal, I can make that query a bit cleaner and easier. Be right back. :)
ID 6 has 2 VIP rows. Both of the "other product" rows for ID 6 are within 30 days of the second VIP row. It would seem that it would meet your needs, but the stated rule is that only the first VIP row counts.
With that at my disposal, I can make that query a bit cleaner and easier. Be right back. :)
Ok. This query shows that ID 1 and ID 3 meet the rules.
Now we need to identify the rest of the rules. (This query might work in ACCESS.)
SELECT *
FROM
(
SELECT id, min(OrderDate) orderdate FROM mike WHERE product = 'VIP' GROUP BY id
) t0
LEFT JOIN mike t1
ON t0.ID = t1.ID
AND t1.product <> 'VIP'
AND t1.orderdate between t0.orderdate and t0.orderdate + 29 daysNow we need to identify the rest of the rules. (This query might work in ACCESS.)
ASKER
Good evening Kent, I was able to load the data into a teradata table and ran the following query to produce the results listed below.
--------------------------
SELECT
t1.CUSTOMER_ID as M_CUSTOMER_ID, T1.PRODUCT_NAME AS M_PRODUCT_NAME, T1.ORDERDATE AS M_ORDERDATE
FROM
(
SELECT CUSTOMER_ID AS CUSTOMER_ID, min(ORDERDATE) as ORDERDATE, PRODUCT_NAME AS PRODUCT_NAME FROM
D_CD0_TB.MH_CUSTOMER WHERE PRODUCT_NAME = 'VIP' GROUP BY CUSTOMER_ID,PRODUCT_NAME
) t0
LEFT outer JOIN D_CD0_TB.MH_CUSTOMER t1
ON t0.CUSTOMER_ID = t1.CUSTOMER_ID
AND t1.PRODUCT_NAME <> 'VIP'
AND t1.ORDERDATE between t0.ORDERDATE and t0.ORDERDATE + 30
;
Results
--------------------------
CustomerID,Product,Orderda
null,null,null
null,null,null
null,null,null
null,null,null
11111,PLANE,2017-01-16
33333,HOTEL,2017-01-17
I can load the results into a table and requery the table to get rid of the nulls from the query. Unless there is another way to get rid of the null from the subquery. But I think the left outer join produces the null values. Let me know your thoughts.
--------------------------
SELECT
t1.CUSTOMER_ID as M_CUSTOMER_ID, T1.PRODUCT_NAME AS M_PRODUCT_NAME, T1.ORDERDATE AS M_ORDERDATE
FROM
(
SELECT CUSTOMER_ID AS CUSTOMER_ID, min(ORDERDATE) as ORDERDATE, PRODUCT_NAME AS PRODUCT_NAME FROM
D_CD0_TB.MH_CUSTOMER WHERE PRODUCT_NAME = 'VIP' GROUP BY CUSTOMER_ID,PRODUCT_NAME
) t0
LEFT outer JOIN D_CD0_TB.MH_CUSTOMER t1
ON t0.CUSTOMER_ID = t1.CUSTOMER_ID
AND t1.PRODUCT_NAME <> 'VIP'
AND t1.ORDERDATE between t0.ORDERDATE and t0.ORDERDATE + 30
;
Results
--------------------------
CustomerID,Product,Orderda
null,null,null
null,null,null
null,null,null
null,null,null
11111,PLANE,2017-01-16
33333,HOTEL,2017-01-17
I can load the results into a table and requery the table to get rid of the nulls from the query. Unless there is another way to get rid of the null from the subquery. But I think the left outer join produces the null values. Let me know your thoughts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Kent. Your solution works great. I thought it would take some procedural processing to deliver a solution to my business problem. This was great learning experience what you can do with derived tables and sub-queries.
It looks like the OP selected the wrong comment as the answer. It should probably be the post immediately above the selected comment.
Thanks,
Kent
Thanks,
Kent
ASKER
I closed the wrong question. The question should be closed under the following id 42118297.
ASKER
Kent provided a great solution to my original request. Truly a good learning expereince.
What you're trying to do is pretty common, but the SQL can be a bit challenging. There are a lot of "hidden rules" that must often be considered. Any of the popular database engines (Access, SQL Server, Oracle, DB2, MySQL, etc.) can handle this and the SQL will look very similar for all of them. With only a million rows, I'd probably not use Teradata unless you're already licensed for the product and want this data in a common repository with other data.
The data suggests that VIP can be ordered more than once. What rules are in place when HOTEL, PLANE, and OTHER rows are paired with VIP rows? Do these rows match to the most recent VIP row, both rows, or perhaps there are some fuzzy rules depending on how all of the rows stack up.
Building up the complexity...
A) The HOTEL record could be associated with either VIP record.
2016/01/01 VIP
2016/01/10 VIP
2016/01/20 HOTEL
B) It would seem that each HOTEL record has a pairing VIP record, though both HOTEL records could be paired with the 2016/01/01 row.
2016/01/01 VIP
2016/01/05 HOTEL
2016/01/10 VIP
2016/01/20 HOTEL
C) Two PLANE records are added to the example above. The 2016/01/02 PLANE record can only pair with the 2016/01/10 VIP record to stay within the 30 day limit. But the 2016/01/20 PLANE record is problematic. Does it apply to the first VIP record or would that be disallowed by business rules since a newer VIP record exists? Does it apply to the second VIP record because it's the more recent, or is that disallowed because there are now 2 PLANE records for a single VIP record?
2016/01/01 VIP
2016/01/05 HOTEL
2016/01/10 VIP
2016/01/20 HOTEL
2016/01/20 PLANE
2016/02/01 PLANE
With those two rules explained, we can probably come pretty close to writing SQL to answer your question. If your data is moved to a more modern engine, the analytical tools can make the queries quite easy.
Kent