SQL Help Needed - Retreiving Data between 2 Dates

[Mort-lemur]

Hi,

 

Im trying to make my life a little easier when it comes to doing some tax returns, Currently I am exporting the entire orders table and the orders status table and manually sorting / deleting / merging records to give me the figures I need.

 

This should be possible by an SQL command, but I dont know where to start, and believe me I have tried.

 

What Im looking for is an SQL command that will download the following data in a table between two dates (or between 2 order numbers if dates is not possible)

 

From Orders: Orders_id, date_purchased, Orders_status

 

From Orders_total: Irders_id, ot_total, ot_tax

 

But I want to include only orders that have an order status of either, 3, 8, ,10 or 16.

 

So basically this would tell me how much I had taken between 2 dates, by order number and the amount of tax taken - with orders of status cancelled, pending, declined etc removed.

 

Can anyone please help ?

 

Thanks

[NodsDorf]

I wrote this awhile ago to pull orders myself.

 

This query list all orders, customer names, billing state, items and quantity, and final price. There will be multiple enteries for any order # in which there are more then 1 different item purchased.

 

Select o.orders_id, o.customers_name, o.customers_state, o.payment_method,
op.products_model, op.products_name, op.products_price,
op.products_quantity, ot.value, o.orders_status
From orders as o
Inner join orders_products as op on o.orders_id=op.orders_id
Inner join orders_total as ot on o.orders_id=ot.orders_id
Where o.orders_id >= 17334 and ot.title = 'Total:'
Order By op.orders_id

//

Conditional rules that can be used

Rule #1 o.orders_status IN ('3','100001','100002','100004','100007')

Rule #2 ot.value > 0

Rule #3 change last line "Order By" to "Group By"

 

Adding the conditional rules will effect the query.

 

Rule # 1 The first rule will ignore open orders only finding orders with a status of Delivered, Denied, Refunded, Cancelled. It will not show orders that are marked funny because of paypal reversals or the open orders check/money order that have not been paid.

 

Rule #2 will ignore orders that have a total cost of 0. Adding this rule will ignore orders that were paid using gift certificates or coupon codes which brought the payment value to 0. This information maybe needed if we are to figure in the gift cert and coupon purchases as a loss.

 

Rule #3 this will force the query to 1 line of data per order. Since the original query creates multiple lines of data when there are 2 or more different products purchased, this can be used to ignore the additional products only showing the first item purchased with the total value. This can be useful if you are figuring in just the total sales

[Mort-lemur]

Thanks Don,

 

I have spent the afternoon tinkering with that and have ended up with this:

 

SELECT  o.orders_id, o.date_purchased, ot.value, o.orders_status
FROM orders AS o
INNER JOIN orders_total AS ot ON o.orders_id = ot.orders_id
WHERE o.orders_id >=2000 AND o.orders_id <=2300
AND (o.orders_status = '3' OR o.orders_status = '2' OR o.orders_status = '6' OR o.orders_status = '7' OR o.orders_status = '9' OR o.orders_status = '16')
AND ot.title = 'Total:'
group BY o.orders_id

 

Which lets me set a start and end order number, and the statuses I want to display, it also gives me the order date and the order total.

 

However, what has got me stumped is how to add another column to the output to display the tax amount of the order. - I have been trying for about two hours, but cant get the tax to display next to the order total column.

 

Any Ideas would be great thanks.

[NodsDorf]

We had the same issue Heather.

 

We found it easier to just do it separately in another query that got the taxed orders. Then merge it together with excel.

 

The tax is stored in order_total table. This expression eliminates it from being found in my original query.

AND ot.title = 'Total:'

 

You could look at your order_total table in the title column for the appropriate language it could be called Tax: or VAT: not sure how yours is setup.

 

Then just run another query...

SELECT  o.orders_id, o.date_purchased, ot.value, o.orders_status
FROM orders AS o
INNER JOIN orders_total AS ot ON o.orders_id = ot.orders_id
WHERE o.orders_id >=2000 AND o.orders_id <=2300
AND o.orders_status IN ('3', '2','6','7','9','16')
AND ot.title = 'Tax:'
group BY o.orders_id

 

Hope that puts you on the right track.

[Mort-lemur]

Thanks again, But that didnt work for me as the title was "Vat @ 20%" and I think this caused the sql to fail as it thought it was a wildcard command %.

 

So I have used the ot_class instead, and the following 2 SQL commands work:

 

For Order Total:

SELECT  o.orders_id, o.date_purchased, ot.title, ot.value, ot.class, o.orders_status
FROM orders AS o
INNER JOIN orders_total AS ot ON o.orders_id = ot.orders_id
WHERE o.orders_id >=2000 AND o.orders_id <=2300
AND (o.orders_status = '3' OR o.orders_status = '2' OR o.orders_status = '6' OR o.orders_status = '7' OR o.orders_status = '9' OR o.orders_status = '16')
AND ot.class = 'ot_total'
group BY o.orders_id

 

and for the tax:

SELECT  o.orders_id, o.date_purchased, ot.title, ot.value, ot.class, o.orders_status
FROM orders AS o
INNER JOIN orders_total AS ot ON o.orders_id = ot.orders_id
WHERE o.orders_id >=2000 AND o.orders_id <=2300
AND (o.orders_status = '3' OR o.orders_status = '2' OR o.orders_status = '6' OR o.orders_status = '7' OR o.orders_status = '9' OR o.orders_status = '16')
AND ot.class = 'ot_tax'
group BY o.orders_id

 

So all is well....

 

The only thing i would like to fix is that if the vat on an order is 0 then the vat SQL will not pick up that order number and so the totals column can be longer than the vat column needing me to investigate and add cells.

 

Thanks

[NodsDorf]

Yep so on the second query, save that to a separate tab of an excel sheet make a new column on the first query sheet/tab.

 

Then use vlookup in excel to merge the data from the second sheet to the first sheet. put this function in your new column on sheet 1 =vlookup(A1,!sheet2$A$1:$H:$1200,8,FALSE) then drag that down the column and bingo.

 

What you want to do is make sure your order_id is in the first column of both sheets (which is should be based on your queries), vlookup will look at the first cell and then pull the data from the second sheet and insert it into the cell you put the function in.

 

The logic on vlookup if you haven't used it before is broken down like this

=vlookup( ---starts the function

A1, ---tells it what cell on the first sheet to match on the second sheet

!sheet2$A$1:$H$1200, --indicates sheet 2 as the target sheet for range $A$1:$H:$1200

8, --indicates what column of data to return

FALSE) --indicates to only return matches and closes the function

 

Using the $ sign on the range makes it a static range so you can drag the function down without the range auto incrementing on you.

 

I of course don't know your range or what column you need to return since I haven't seen your excel sheet but that should help you make quick work of merging the data.

[NodsDorf]

Also on the VAT thing you probably would of had to use

AND ot.title LIKE 'VAT%'

But using the class works as well, just getting a unique identifier for that table to get the data is the goal.