September 4, 2008 at 7:07 am
Hi All
I hope you can help me with the following query.
It’s quite complex query and I’m sure that a cube in Analysis Services would do the job but I was wondering whether it is possible to write a T-SQL query to solve the problem.
I have two tables, Orders and OrdeLines.
Orders table.
Order_Number, Total_Cases
OrderLines table.
Order_Number, Line_No, No_Of_Cases.
In the Orders table the field Total_Cases contains the total of all the No_Of_Cases in all the OrderLines records for the parent Order record.
No for the tricky part, I’m trying to write a query that would produce a grid of data like as follows:
No of Cases123456.......
per order
No of Lines
per order
1
2
3No. of orders.
4
5
6
.
.
So I’m basically trying to produce a grid of data that shows the number of orders that are split by the X axis, number of order lines in an order and the Y axis, total no of cases per order.
Is it possible to have a query that would produce this data? Would the solution require temporary tables?
Many Thanks.
Nick.
September 4, 2008 at 9:06 am
Have some ideas... Can you provide a small data extraction from both tables to test.
September 4, 2008 at 9:42 am
Hi
thanks for the reply.
below is a sample of data from the tables.
Orders table.
Order_Number, Total_Cases
1,4
2,6
3,3
4,2
5,5
OrderLines table.
Order_Number, Line_No, No_Of_Cases
1,1,1
1,2,2
1,3,1
2,1,3
2,2,3
3,1,1
3,2,1
3,3,1
4,1,1
4,2,1
5,1,2
5,2,3
I hope this helps.
Many thanks.
Nick.
September 4, 2008 at 9:58 am
IS this sort of what you looking for?
DECLARE @Orders table
(Order_Number INT , Total_Cases INT)
INSERT INTO @Orders
SELECT 1,4 UNION ALL
SELECT 2,6 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,2 UNION ALL
SELECT 5,5
DECLARE @OrderLines table
(Order_Number INT, Line_No INT , No_Of_Cases INT )
INSERT INTO @OrderLines
SELECT 1,1,1 UNION ALL
SELECT 1,2,2 UNION ALL
SELECT 1,3,1 UNION ALL
SELECT 2,1,3 UNION ALL
SELECT 2,2,3 UNION ALL
SELECT 3,1,1 UNION ALL
SELECT 3,2,1 UNION ALL
SELECT 3,3,1 UNION ALL
SELECT 4,1,1 UNION ALL
SELECT 4,2,1 UNION ALL
SELECT 5,1,2 UNION ALL
SELECT 5,2,3
SELECT Line_No,[1],[2],[3],[4],[5],[6]
FROM
(SELECT Line_No
,Order_Number
,No_Of_Cases
FROM @OrderLines ) p
PIVOT (COUNT(Order_Number) FOR No_Of_Cases IN ([1],[2],[3],[4],[5],[6])) pvt
ORDER BY Line_No
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 4, 2008 at 10:00 am
You should be able to achieve this with the Pivot function, but I recommend not bothering. Have a front-end application do your pivots for you. They're better at it than SQL is.
Create a view that joins your two tables together, connect Excel to the view with the Pivot Table wizard, drag and drop the columns you want onto the right parts of the Pivot Table, and bang! you have your report.
Want to filter it? No problem. Want to add another column? No problem. Want to switch the rows and columns? No problem.
All of those operations require writing whole new queries in SQL. In Excel, they require a couple of mouse-clicks and a drag-and-drop or two.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2008 at 10:02 am
Any chance you can do your grid by hand (using the data provided) so that I can see exactly what you are hoping to see.
No of Cases 1 2 3 4 5 6 .......
per order
No of Lines
per order
1
2
3 No. of orders.
4
5
6
September 4, 2008 at 10:28 am
Hi All
Many thanks for all the replies.
The table I'm after should look like the following with the data provided:
No of Cases 1 2 3 4 5 6 .......
per order
No of Lines
per order
1
2 1 1 1
3 1 1
4
5
6
I can provided more data if that will help?
Many thanks.
Nick.
September 4, 2008 at 2:36 pm
This can actually be viewed as two different issues: 1) how to get the data you want and 2) how to get that data into the format that you want.
This first is actually pretty easy:
Select Total_Cases, Total_Lines, Count(*)
From (Select Order_Number
, Total_Cases
, (Select Count(*) From OrderLines OL
Where OL.Order_Number = O.Order_Number)
As Total_Lines
From Orders O) AS OrderCounts
Group By Total_Cases, Total_Lines
As previously mention, the second part can be easily handled by a front end tool like Excel. If you really want to do it on the SQL Server, then you will need either a Dynamic Cross-Tab or a Dynamic PIVOT (unless you already know the upper bounds for Total_Cases).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2008 at 3:56 am
Hi All
Thanks again for all the replies.
I'm going to try both solutions mentioned above.
rbarryyoung: The upper bounds for Total_Cases is not know but I am going to limit it to 50. Dynamic Cross-Tabs and Dynamic PIVOTs are out at the moment as I'm still only just getting to grips with PIVOT. I am going to use yours and GSquared's idea to collect the data in a view and do the pivot in an excel spreadsheet.
I'm also going to use Christopher Stobbs script to get it to work in T-SQL.
Once I have the script working I will try and convert the script using dynamic pivots or cross tabs so I don’t have to worry about the upper bounds of Total_Cases.
I just like to know I can solve the problem both ways :-).
Many thanks for all your help.
Nick.
September 5, 2008 at 8:30 am
http://www.simple-talk.com has a good article by Phil Factor and Robyn Page on how to put together a dynamic pivot in T-SQL. You might want to look at that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply