August 12, 2013 at 7:13 am
My client has an application which also creates summary reports. My task is to improve those reports performance, as they have been written outside of SQL scope and only query SQL for the row data, i.e. SELECT * FROM myTable WHERE myRange.
I need your advise to improve one of the reports, please. The following is an example of the expected result.
I have used "Empty Space" because sqlservercentral removes the spaces for some reason, but if you will run my SQL you will see it in a correct way
"Customer Name" "Order Date" "Order Amount"
HappyCustomer 1 01/05/2013 1000
"Empty Space" 04/05/2013 500
"Empty Space" 09/07/2013 20
TOTAL FOR HappyCustomer 1 1520
HappyCustomer 2 01/01/2013 200
"Empty Space" 08/04/2013 1200
TOTAL FOR HappyCustomer 2 1400
Again, the application at the moment knows how to deal with it, but I need to re-write it in sql (Stored Procedure).
The first task, to show customer's name only at the first line and to calculate total per customer was easy one
(see working example below - implemented it via PARTITON BY), but I need your advise how to present in a required way, please. I mean how to add those TOTAL FOR additional lines?
WITH myResults AS
(SELECT 1 AS customer_id,
'HappyCustomer 1' AS customer_name,
GETDATE()-10 AS order_date,
12345 AS order_amount
UNION ALL
SELECT 1,'HappyCustomer 1', GETDATE()-9,322.32
UNION ALL
SELECT 1,'HappyCustomer 1', GETDATE()-5,9875
UNION ALL
SELECT 2,'HappyCustomer 2', GETDATE()-50,2387.5
UNION ALL
SELECT 2,'HappyCustomer 2', GETDATE()-4,5467)
SELECT CASE 1 WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date)
THEN customer_name
ELSE ''
END AS customer_name,
order_date,
order_amount,
SUM(order_amount) OVER (PARTITION BY customer_id) AS total_per_customer
FROM myResults;
Thank you in advance
August 12, 2013 at 7:24 am
It is hard to figure out which of your questions you want help with. Also there seems to be confusion about performance vs presentation. SQL server is NOT a presentation tool. It would be better to handle all the presentation stuff in the front end instead of sql server. An extra row for total should be done in your reporting tool.
With over 80 points you should know that we can't help you much here because you didn't post ddl and sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2013 at 7:29 am
There is no 80 points and there is sample data - WITH statement runs and works perfectly (I understand you haven't tried it).
I do know that SQL is not a presentation product - no need to be conceited either. I have spent my time,
working on something and I ask if there is a better way to do that. If you think you cannot help me, than may I ask you to refrain from any reply or just to say "no, there is no way to do that in SQL", please?
Kind Regards
August 12, 2013 at 7:49 am
BOR15K (8/12/2013)
There is no 80 points and there is sample data - WITH statement runs and works perfectly (I understand you haven't tried it).I do know that SQL is not a presentation product - no need to be conceited either. I have spent my time,
working on something and I ask if there is a better way to do that. If you think you cannot help me, than may I ask you to refrain from any reply or just to say "no, there is no way to do that in SQL", please?
Kind Regards
You currently have 82 points. When you post again, this number will increase by one.
If you think that your question is clear, you are deluding yourself. I have read your post several times and I am still unsure what you are asking for.
I read no hint of conceit in Sean's reply. He is one of the most helpful posters here and if you take his advice, you won't go far wrong.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 12, 2013 at 7:58 am
I will rephrase my question then, is there is a way, using T-SQL to add an additional row, which will be a summary for each client? As far as I am aware, there is not, but I might be mistaken.
August 12, 2013 at 8:01 am
BOR15K (8/12/2013)
I will rephrase my question then, is there is a way, using T-SQL to add an additional row, which will be a summary for each client? As far as I am aware, there is not, but I might be mistaken.
It's not pretty, but you could UNION ALL your existing result set with one which contains per-customer aggregated results.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 12, 2013 at 8:01 am
BOR15K (8/12/2013)
There is no 80 points and there is sample data - WITH statement runs and works perfectly (I understand you haven't tried it).I do know that SQL is not a presentation product - no need to be conceited either. I have spent my time,
working on something and I ask if there is a better way to do that. If you think you cannot help me, than may I ask you to refrain from any reply or just to say "no, there is no way to do that in SQL", please?
Kind Regards
No arrogance or conceit intended. I don't see that in my response at all. You did post a cte with some data in it but you also talked about performance. If you are looking for help with performance this is not going to help.
If instead you want to figure out how to add some sort of a total row in the middle of your query you can do that. I stand by my comments that doing this in sql is the wrong place, the performance is likely to be awful like this but here you go.
select *, 1 as SortOrder from myResults
union all
select customer_id, 'Total for ' + customer_name, null, sum(order_amount), 2
from myResults
group by customer_id, customer_name
order by customer_id, SortOrder
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 12, 2013 at 8:07 am
Thank you for the reply.
Because of the distance between SQL Server and the application one, the way it works at the moment,
by pulling all the data to the application server, and manipulating it there is time consuming already.
I think I will try to group by as you have suggested.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply