Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

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)
Avatar of PortletPaul
PortletPaul
Flag of Australia image

http://rextester.com/MOYVZR7670

You want all customers listed by this function?
Is that correct
Avatar of johnnyg123

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
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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello.
Have you tried the query above at all?
Still need assistance?
Sorry for delay ... great solution!
Thanks.