January 22, 2009 at 11:13 am
I have two tables:
An OrderHeader table and an OrderLine table
OrderHeader just contains details like orderdate,orderName, orderID.
OrderLine contains the lineItems. There are two types of line items, debit and credit
I am trying to figure out the total revenue for the last 6 months.
I can see i have 68 credit lines using this query:
select ol.*--sum(ol.quantity * ol.price)
from OrderLine as ol join OrderHeader as oh(nolock) on oh.orderid = ol.orderid
where oh.saleslocationcode = 'IRE'
and oh.orderstatus = 'Submitted'
and oh.createddate between '2008-08-01' and getdate()
and ol.RevenueType = 'CRT'--Credit lines
--68 lines returned
select ol.*--sum(ol.quantity * ol.price)
from OrderLine as ol join OrderHeader as oh(nolock) on oh.orderid = ol.orderid
where oh.saleslocationcode = 'IRE'
and oh.orderstatus = 'Submitted'
and oh.createddate between '2008-08-01' and getdate()
and ol.RevenueType = 'DBT'--debit lines
--1200 lines
so far so good.
when i try to combine both in a query to get the debit amount - the credit amount im getting very strange results. here is my query:
select sum(ol.quantity * ol.price) - sum(ol1.quantity * ol1.price)
from orderline as ol join orderHeader as oh(nolock) on ol.orderid = oh.orderid
join orderline as ol1 on ol1.orderid = oh.orderid
where oh.saleslocationcode = 'IRE'
and oh.orderstatus = 'Submitted'
and oh.createddate between '2008-08-01' and getdate()
and ol.revenuetype = 'DBT'
and ol1.revenuetype = 'CRT'
i modified my query to see how many credit rows it is returning. it should return 68, but it is returning over 500
select ol1.* --sum(ol.quantity * ol.price)- sum(ol1.quantity * ol1.price)
from orderline as ol join orderHeader as oh(nolock) on ol.orderid = oh.orderid
join orderline as ol1 on ol1.orderid = oh.orderid
where oh.saleslocationcode = 'IRE'
and oh.orderstatus = 'Submitted'
and oh.createddate between '2008-08-01' and getdate()
and ol.revenuetype = 'DBT'
and ol1.revenuetype = 'CRT'
I cannot see why my query with the join is returning over 500 credit rows when I know there are only 68.
I appriciate that without seeing the data this may be difficult to help me with, but is this a problem that cannot be solved using
just joins? Will i have to use temp tables in this case?
January 22, 2009 at 11:31 am
you're not correlating the two sets or orderlines, which is why you're getting more rows than expected. Unless you have a one-to-one correlation between OL and OL1, there's no expectation that you will get 68 rows back.
For example - if one of your credit rows happens to be attached to an invoice with 50 debit rows on it, then the credit row will appear associated with EACH of the 50.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 22, 2009 at 12:43 pm
i cant see how writing this as a co-related query, il be able to work out the debit amount minus the credit amount though.
January 22, 2009 at 1:15 pm
Can you post your data?
With some data, We'll see what we can do, and explain why it did not work.
Cheers,
J-F
January 22, 2009 at 1:54 pm
Ok Winston, this is untested, since I don't have the tables structure or any data, but it should put you on track as to what you need to do.
SELECT ol.OrderID,
(DebitAmount
- CreditAmount) AS DebitMinusCreditAmounr
FROM (SELECT ol.OrderID,
(ol.quantity
* ol.price) AS DebitAmount
FROM OrderLine AS ol
JOIN OrderHeader AS oh(nolock)
ON oh.orderid = ol.orderid
WHERE oh.saleslocationcode = 'IRE'
AND oh.orderstatus = 'Submitted'
AND oh.createddate BETWEEN '2008-08-01' AND getdate()
AND ol.RevenueType = 'DBT'--debit lines
) AS Debit
INNER JOIN (SELECT ol.OrderID,
(ol.quantity
* ol.price) AS CreditAmount
FROM OrderLine AS ol
JOIN OrderHeader AS oh(nolock)
ON oh.orderid = ol.orderid
WHERE oh.saleslocationcode = 'IRE'
AND oh.orderstatus = 'Submitted'
AND oh.createddate BETWEEN '2008-08-01' AND getdate()
AND ol.RevenueType = 'CRT'--Credit lines
) AS Credit
ON Debit.OrderID = Credit.OrderID
Try this out,
Cheers,
J-F
January 22, 2009 at 2:02 pm
It would really help if you would read the article I link to in my signature. It will help you format your question so we can help you better.
The problem with your query is how you have joined the detail records with the header records. I am going to restructure your query a little, but it should show you what needs to happen.
SELECT oh.OrderID
,{other columns}
FROM dbo.OrderHeader AS oh -- start with OrderHeader
LEFT JOIN dbo.OrderLine AS ol1 ON ol1.OrderID = oh.OrderID AND ol1.RevenueType = 'CRT' -- credits
LEFT JOIN dbo.OrderLine AS ol2 ON ol2.OrderID = oh.OrderID AND ol2.RevenueType = 'DBT' -- debits
WHERE oh.SalesLocationCode = 'IRE'
AND oh.OrderStatus = 'Submitted'
AND oh.CreatedDate >= '2008-08-01'
AND oh.CreatedDate < DATEADD(day, DATEDIFF(day, 0, getdate()), 0) -- everything less than today
The above code is untested - but, it should give you the idea. We need to join in just the OrderLines that are credits, and as a separate join just the debits. We need to use an outer join because there may not be any credits/debits for the order (yet).
Once you have the above returning the correct data - you can then add in the grouping to SUM the totals and create your balances.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply