October 4, 2011 at 3:31 pm
Thanks for reading the post... I just registered today because my search of the site is not giving me the result I was hoping for.
I have a stored proc pulling data from three tables. The results are being formatted for display on first a web site and maybe a tablet\mobile app later. The desired result of the query would return the following data
1 Customer 1
1 Open 15
1 Closed 50
1 New 10
2 Customer 2
2 Open 25
2 Closed 550
2 New 122
1 Customer 3
1 Open 89
1 Closed 231
1 New 1
I would like to have the first column be a one or a two for each group but I have no idea how to do it.
I am working with three tables
Customers
Cust_Key
Customer_Name
Orders
Order_Key
Cust_Key
Status Key
Status_LookUp
Status_Key
Status_text
I am building the output using two SQL commands joined with a Union All. What I want to do now is to have either a 1 or a 2 in the first column so I can tell the GirdView object in VS2010 to change colors of the rows for each grouping.
The current data being outputted is --
Column 1 - Customer Name
Column 2 - Distinct Status (there could be 4, or 10)
Column 3 - The count of the orders with the status, by customer
Here is the T-SQL that I am using.
Declare @Customer_Orders as table
(
Sort int,
Customer_Key int,
Customer_Name VarChar(75),
Status_text VarChar(15),
Order_Count VarChar(15)
)
Insert into @Customer_Orders
Select
c.Cust_Key * 100,
c.Cust_Key,
C.Customer_Name,
'',
''
From Customer C
Union All
Select
O.Cust_Key * 100 + 10 as Sort,
O.Cust_Key,
'',
S.status_text,
cast(COUNT(o.Order_Key) as VarChar(15))
From Orders O
Join Status_LookUp S on O.Status_Key = S.status_key
Group By O.Cust_Key, S.status_text
Select Customer_Name as Customer, Status_text as Status, Order_Count as Quantity
From @Customer_Orders
Order By Sort, Customer
If someone can at least give me an idea of what to look for it would be great. Or of you know of a better way to do it... I am all ears.
Tim Sapp
October 4, 2011 at 3:43 pm
October 4, 2011 at 4:01 pm
Yeah I was typing away and hit the enter button and bang! the message was sent. You read it while I was "Editing" my post to add more details. 😀
I hope I have given you enough details on what I need help with.
Tim Sapp
October 5, 2011 at 1:05 am
TDSapp (10/4/2011)
I hope I have given you enough details on what I need help with.
You could give us the criteria for a customer being in group 1 or group 2... That would be helpful.
In itself, adding a column to return 1 or 2 is easy:
select case when criterium_here = 1 then 1 else 2 end as [group], ...
edit: coming to think of it, do you intend to make the even rows show in a different color than the uneven rows? If so, what about this:
select (row_number() over (order by (select 1)) % 2) as [group], ...
Having your results returned in 4 rows per customer is a very bad design though. SQL should be used to return a row per customer and on this row all 3 figures for that customer should be returned. The front-end should then decide how to present the information: in your case, a line for the customer name plus 3 lines for the numbers. Think about what will happen if your application is asked to support 'landscape' overviews too: the end user no longer wants the numbers shown under the customer, but now they want them after the customer on the same line. Will you go and write another query for this landscape overview too?
October 5, 2011 at 5:11 am
R.P.Rozema (10/5/2011)
edit: coming to think of it, do you intend to make the even rows show in a different color than the uneven rows? If so, what about this:
select (row_number() over (order by (select 1)) % 2) as [group], ...
You are correct, this is what I was looking for....
R.P.Rozema (10/5/2011)
Having your results returned in 4 rows per customer is a very bad design though. SQL should be used to return a row per customer and on this row all 3 figures for that customer should be returned. The front-end should then decide how to present the information: in your case, a line for the customer name plus 3 lines for the numbers. Think about what will happen if your application is asked to support 'landscape' overviews too: the end user no longer wants the numbers shown under the customer, but now they want them after the customer on the same line. Will you go and write another query for this landscape overview too?
I actually started thinking about this last night after I got home. For two reasons, one, I would be able to just set up the GridView object to auto-magically swap the row colors, and two, it would take less valuable screen landscape. While not an issue on a PC most of the time, it's a real issue on a tablet.
I may have to look at it today and make a few changes.
Tim
October 6, 2011 at 10:04 am
CELKO (10/5/2011)
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Well, the naming and the table design was something that was done before I got here. We get to use what we are given... This is actually a database that is compiled from about 4 SQL Databases and a couple Access databases. We have to keep the names the same so we can manage what has to be updated and moved around for now. Maybe in the future we can change it to follow the standards, but for now, it is what it is.
CELKO (10/5/2011)
You got everything wrong. A status is a “<something>_status” and it is an attribute not a table with a key. What is the key of the numeric constants pi? Yes, what you did is that kind of fundamental modeling error. Oh, data element name tell use what something is (identifier) and never how it is used in one table (_key)
Kind of direct ain't ya.
As for the status, that is from a look up table where the Primary Key of the table is the field being used in the Orders table. The end user can add a status as the system and the processes in the company change and flow. It keeps us from having to replicate the same text over and over not knowing what we are going to be needing in the future.
CELKO (10/5/2011)
If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
Not a bad link, but I do like the like the one that ColdCoffee posted better (http://www.sqlservercentral.com/articles/Best+Practices/61537/ ) it is more relevant to this site.
CELKO (10/5/2011)
Here is a DDL skeleton to replace your narrativeCREATE TABLE Customers
(cust_id CHAR(15) NOT NULL PRIMARY KEY,
cust_name VARCHAR(25) NOT NULL,
..);
CREATE TABLE Orders
(order_nbr CHAR(10) NOT NULL PRIMARY KEY,
order_status CHAR(3) DEFAULT 'new' NOT NULL
CHECK (order_status IN ('open', 'close', 'new')),
..);
The SQL programming idiom for this kind of query is:
SELECT C.cust_id, C.cust_name,
SUM(CASE WHEN O.order_status = 'new'
THEN 1 ELSE 0 END)
AS new_tot,
SUM(CASE WHEN order_status = 'close'
THEN 1 ELSE 0 END)
AS close_tot,
SUM(CASE WHEN order_status = 'new'
THEN 1 ELSE 0 END)
AS open_tot
FROM customers AS C, Orders AS O
WHERE C.cust_id = O.cust_id
GROUP BY C.cust_id, C.cust_name;
Now pass this to the display layer and let it does its
As I said in the post right before yours I am actually changing how I am going to out the data to the app. I am moving to a single row for each customer with one column containing the count of the orders for each status. What your code above does not take into consideration is having more than three status levels, and never knowing how many there will be in the future. I don't want to have to change a stored proc just every time someone adds a status.
However, I am not sure how to do this step either. Since there can be any number of columns in the status table that means there can be any number of columns for each client. I don't mind if they have zeros in the fields if they don't have a status, but just knowing how to tie them all together is my next step.
Here is a set of example tables and some sample data to work with. (Yes, I can read... 🙂 )
IF OBJECT_ID('TempDB..#Customer','U') IS NOT NULL
DROP TABLE #Customer
CREATE TABLE #Customer
(Cust_Key int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Customer_Short_Name varchar(10) NULL,
Customer_Name varchar(50) NULL)
SET IDENTITY_INSERT #Customer ON
Insert into #Customer
(Cust_Key, Customer_Short_Name, Customer_Name)
Select '1','Cust1','First Customer' Union ALL
Select '2','Val','Valuable Customer' Union ALL
Select '4','UTA','UT of Arlington' Union ALL
Select '5','OU','Oklahoma University' Union ALL
Select '6','GM','General Motors' Union ALL
Select '7','VC','Virginia Chili'
SET IDENTITY_INSERT #Customer OFF
IF OBJECT_ID('TempDB..#Status_LookUp','U') IS NOT NULL
DROP TABLE #Status_LookUp
Go
CREATE TABLE #Status_LookUp
(status_key int IDENTITY(1,1) NOT NULL PRIMARY KEY,
status_text varchar(50) NULL,
status_type varchar(10) NULL)
GO
SET IDENTITY_INSERT #Status_LookUp ON
Insert into #Status_LookUp
(status_key, status_text, status_type)
Select '1','Not Set','ALL' Union ALL
Select '2','Open','ALL' Union ALL
Select '3','Closed','ALL' Union ALL
Select '4','New Order','Order' Union ALL
Select '5','Shipped','ALL' Union ALL
Select '6','Partial Shipped','Order' Union ALL
Select '7','IN ERROR','ALL' Union ALL
Select '8','At Press','LineItem'
SET IDENTITY_INSERT #Status_LookUp OFF
Go
IF OBJECT_ID('TempDB..#Orders','U') IS NOT NULL
DROP TABLE #Orders
Go
CREATE TABLE #Orders(
Order_Key int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Fulfillment_Order_Key VarChar(50) NULL,
Cust_Key int NULL,
Order_Date date NOT NULL,
Status_Key int DEFAULT ((1)) NOT NULL)
Go
SET IDENTITY_INSERT #Orders On
Insert into #Orders
(Order_Key, Fulfillment_Order_Key, Status_Key, Order_Date, Cust_Key)
Select '7488','1000211273','1','2011-09-19','11' Union ALL
Select '7489','1000211285','2','2011-09-19','6' Union ALL
Select '7490','1000211278','4','2011-09-19','8' Union ALL
Select '7491','1000211292','5','2011-09-19','6' Union ALL
Select '7492','1000211276','6','2011-09-19','7' Union ALL
Select '7493','1000211280','4','2011-09-19','11' Union ALL
Select '7494','1000211279','2','2011-09-19','6' Union ALL
Select '7495','1000211284','1','2011-09-19','7' Union ALL
Select '7496','1000211274','4','2011-09-19','6' Union ALL
Select '7497','1000211291','7','2011-09-19','8' Union ALL
Select '7498','1000211286','3','2011-09-19','8' Union ALL
Select '7499','1000211275','2','2011-09-19','11' Union ALL
Select '7500','1000211287','6','2011-09-19','11' Union ALL
Select '7501','1000211290','6','2011-09-19','7' Union ALL
Select '7502','1000211282','7','2011-09-19','8' Union ALL
Select '7503','1000211288','2','2011-09-19','11' Union ALL
Select '7504','1000211289','1','2011-09-19','11' Union ALL
Select '7505','1000211283','1','2011-09-19','6' Union ALL
Select '7506','1000211281','5','2011-09-19','11' Union ALL
Select '7507','1000211277','5','2011-09-19','6' Union ALL
Select '7508','1000211312','6','2011-09-19','11' Union ALL
Select '7509','1000211303','6','2011-09-19','8' Union ALL
Select '7510','1000211297','6','2011-09-19','11' Union ALL
Select '7511','1000211300','4','2011-09-19','6' Union ALL
Select '7512','1000211294','3','2011-09-19','8' Union ALL
Select '7513','1000211301','4','2011-09-19','8' Union ALL
Select '7514','1000211296','2','2011-09-19','11' Union ALL
Select '7515','1000211307','3','2011-09-19','8' Union ALL
Select '7516','1000211306','6','2011-09-19','11' Union ALL
Select '7517','1000211308','5','2011-09-19','6'
SET IDENTITY_INSERT #Orders OFF
What I would really like to do is return a table that has a customer column, and then one column for each status. The status column would be named after the status that the column contains.
I guess I could start off with a temp table with just customer as a field and then run a loop to add columns to the table, one for each status.
What a great day... trying to figure this out with a Sinus infection is not making my day fun.
Tim
October 6, 2011 at 11:34 am
TDSapp (10/6/2011)
CELKO (10/5/2011)
What I would really like to do is return a table that has a customer column, and then one column for each status. The status column would be named after the status that the column contains.
You need to use dynamic SQL to create this. Have a look at Jeff's article on Dynamic Cross Tabs http://www.sqlservercentral.com/articles/Crosstab/65048/
What I have done in the past on the assumption that tables like this will be fairly stable, is create a trigger on the table that dynamically creates a view for the cross tab. That way, you don't have to create the cross tab query every time you want to access it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 7, 2011 at 12:00 am
Ok, so let's assume you've got a trigger that creates the view dynamically. And thus you've got the view all set to return all totals for all statusses in a single row, how is your application going to know for which columns to query? I'd say you define which columns need to be shown in the overview and you create the query for that. At the time a new status is added that per the new requirements needs to be shown in the overview, you introduce a new version of your application/web site that knows to query for and show this new status. But wait, if you have to build a new version of the application/web site, then why would you not at that time build the new view too? I'm a bit over-dramatic here of course, but do you really want your application to blindly follow the statusses that an end-user enters into your lookup table? What if they start entering 10, 20 or even 100 statusses, will your screen be wide enough and do you really want to query for all customers all these statusses so they can be shown on each line? I'd say you go back to the person who wrote the specs and ask him/her for which statusses (s)he wants the overview to show the totals. Doing so avoids all the needed tricks that will eventually bite you in the foot. Wise men said: "Keep it simple" and even though I am not as wise, I say the same.
Note: to get the totals on the single row together with the customer, you can indeed best use the crosstab technique described by Jeff Moden (a link is in my footer text).
October 7, 2011 at 10:02 am
Now that you mention it, my trigger actually did the reverse. The person who designed one of the tables didn't normalize it properly. The trigger I wrote created a view to normalize it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply