October 2, 2008 at 1:26 pm
Dugi (10/2/2008)
Hmmm I don't think so that the PARTITION BY will work without ORDER BY ...!?
You're right when dealing with ROW_NUMBER(). So, make the PARTITION BY and the ORDER BY the same:
row_number() over(
PARTITION BY CUSTOMER,SalesOrders.SalesOrderId
ORDER BY CUSTOMER,SalesOrders.SalesOrderId
) AS rn
----------------------------------------------------------------------------------
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?
October 2, 2008 at 1:35 pm
October 2, 2008 at 9:35 pm
Waiting for philip to answer, I guess he has the right results 🙂
Thanks Matt and Dugi
.
October 2, 2008 at 11:43 pm
Guys I will test now, I have man flu....:)
Watch this space......6.42am someone pin a medal on me.....
Many Thanks,
Phil.
Update:
row_number() over(
PARTITION BY CUSTOMER,SalesOrders.SalesOrderId
ORDER BY CUSTOMER,SalesOrders.SalesOrderId
) AS rn
This returned error:
Msg 209, Level 16, State 1, Line 19
Ambiguous column name 'CUSTOMER'.
Msg 209, Level 16, State 1, Line 20
Ambiguous column name 'CUSTOMER'.
So I changed this to CustomerName. The query ran however the CustomerName + SalesOrderId was repeated for each row.
Thanks for all your efforts.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 3, 2008 at 12:59 am
Hi Philip,
All of us are trying to help you, but are shooting in the dark. I see that you have a simple problem that is not solved even after we all tried for the last 24 hours.
The reason is that, we dont have access to your data. Some one from this forum might be able to fix it in less than a minute, if he/she has an idea of your table structure and data. I would suggest doing the following.
Please post the structure of the tables and some sample data. I know that your data is sensitive. But put some dummy data. Then show us the result you want to achieve from the given source data. Believe me, some of the people watching this thread will jump in and give you the answer in a few seconds.
.
October 3, 2008 at 2:19 am
October 3, 2008 at 3:09 am
Hi Dugi. I really appreciate the efforts to all of those who have posted.
I am unable to do as requested as I would be in breach of the contractual agreement with our app vender.
As the query is to be used in a report could I not use the GROUP BY when I create the report using the query as a dataset. I have only done a handful of reports so I am unsure of all I can do (the power of VS / SQL Reporting Services)!
Basically the user needs to be able to filter by Customer / Date Range / Sales Order ID (but that is another story and another forum!).
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 3, 2008 at 3:10 am
Phil,
Please replace "SUM(BalanceOutstanding) AS BalanceOutstanding" with "BalanceOutstanding" in the top-select (outer query)...
...because of two reasons (as I can visualize):
1. SUM(...) is an aggregate (group) function and you should GROUP BY the columns (that have been specified in the select-list). In this case, the first missing GROUP BY column is "rn", and hence the error you've encountered.
2. Functionally, you're already computing the sum in the inner query. All you've to do is use the "alias" (BalanceOutstanding) in your outer query.
HTH!
Madhu
October 3, 2008 at 3:22 am
Madhu not sure if you are looking at the most recent query?
Here it is:
SELECT
case when rn = 1 then CustomerName else '' end AS CustomerName,
case when rn = 1 then SalesOrderId else '' end AS SalesOrderId,
PaymentDate,
PaymentMade,
BalanceOutstanding
FROM(
SELECT
Customers.CustomerName,
SalesOrders.SalesOrderId,
ProgressPayments.PaymentDate,
ProgressPayments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding,
row_number() over(
PARTITION BY Customers.CustomerName,SalesOrders.SalesOrderId
ORDER BY Customers.CustomerName,SalesOrders.SalesOrderId
) AS rn
--row_number() over(
--PARTITION BY CUSTOMER,SalesOrders.SalesOrderId
--ORDER BY CUSTOMER,SalesOrders.SalesOrderId
--) AS rn
FROM ProgressPayments
INNER JOIN SalesOrders ON ProgressPayments.SalesOrder = SalesOrders.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
GROUP BY
Customers.CustomerName,
SalesOrders.SalesOrderId,
ProgressPayments.PaymentDate,
ProgressPayments.InvoiceValue
) AS r
order by rn
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 4, 2008 at 3:33 am
Philip Horan (10/2/2008)
Jacob that is perfect, what did you change?Finally was my approach valid in constructing a query to use in a report?
Many Thanks,
Phil.
Update:
Jacob in answer to my own question my approach is flawed! The query executes but returns only the first Customer & SalesOrderId for ALL payments.
My intention was to return each Customer / SalesOrderId for each payment against that customer i.e.
Customer..................SalesOrderId..............PaymentDate................Payment
Customer A................12345......................01/01/2008...................£100
.............................................................25/02/08.......................£500
Customer B................123456.....................30/03/08.......................£1500
..............................................................30/04/08.......................£1000
..............................................................30/05/08.......................£10000
This is formation issue which should be done in your Reports and not using sql
Failing to plan is Planning to fail
October 4, 2008 at 5:08 am
Thanks for posting guys. I will get the desired output via reports.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply