johnnyg123
asked on
Sql user function
I am try to write a sql user function that accepts a date range start and a date range end for a 6 month period and does the following:
Divide the 6 month period into 2 separate 3 month periods
Take all the order dates for a customer that occur in the 3 month period closest to the end of the 6 month period and return
customerid, total_items_ordered and order_total
(If there are no orders within the 6 month range, return 0)
Here is some sample data from a table named customer orders
customerid order_date items_ordered order_total
1 3/7/2016 4 10
2 2/10/2016 7 15
2 6/10/2016 5 50
2 8/10/2016 10 200
2 9/23/2016 4 100
If the date range start is 5/1/2016 and the date range end is 10/31/2016 (the 3 month ranges would be 5/1/2016 to 7/31/2016 and
8/1/2016 to 10/31/2016)
I would like the output to be
Customer Total_Items_ordered Total_Order_total
1 0 0 (No orders between 5/1/2016 and 10/31/2016)
2 14 300 (Take orders from 8/10/2016 and 9/23/2016 since these are closest to 3 month
of 08/01/2016 to 10/31/2016.....ignore order from 6/10/2016)
Divide the 6 month period into 2 separate 3 month periods
Take all the order dates for a customer that occur in the 3 month period closest to the end of the 6 month period and return
customerid, total_items_ordered and order_total
(If there are no orders within the 6 month range, return 0)
Here is some sample data from a table named customer orders
customerid order_date items_ordered order_total
1 3/7/2016 4 10
2 2/10/2016 7 15
2 6/10/2016 5 50
2 8/10/2016 10 200
2 9/23/2016 4 100
If the date range start is 5/1/2016 and the date range end is 10/31/2016 (the 3 month ranges would be 5/1/2016 to 7/31/2016 and
8/1/2016 to 10/31/2016)
I would like the output to be
Customer Total_Items_ordered Total_Order_total
1 0 0 (No orders between 5/1/2016 and 10/31/2016)
2 14 300 (Take orders from 8/10/2016 and 9/23/2016 since these are closest to 3 month
of 08/01/2016 to 10/31/2016.....ignore order from 6/10/2016)
ASKER
Thanks for the response
I did indeed want all the customers
I don't mean to seem ungrateful but the output for your solution does not match what I was hoping for
It does not include the filtering I was hoping for and does not take into account the date range scenario I was hoping for
Also, not a function
I did indeed want all the customers
I don't mean to seem ungrateful but the output for your solution does not match what I was hoping for
It does not include the filtering I was hoping for and does not take into account the date range scenario I was hoping for
Also, not a function
No. It is just a start. Best i could get done in the time using a tablet on a train. I also have problems using mm.dd.yyyy data so it helps me to see it through a query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello.
Have you tried the query above at all?
Still need assistance?
Have you tried the query above at all?
Still need assistance?
ASKER
Sorry for delay ... great solution!
Thanks.
You want all customers listed by this function?
Is that correct