troubleshooting Question

Wierd sql query - SQL 2000!!

Avatar of enjama
enjama asked on
DatabasesMicrosoft SQL Server
8 Comments1 Solution557 ViewsLast Modified:
Hello - I have a query that I currently have to poplulate variables with results from sub-querys, and I want to make it into a single query.

Here is the code:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblMyTable]
GO

CREATE TABLE [dbo].[tblMyTable] (
      [row_id] [int] NULL ,
      [item_id] [int] NULL ,
      [cust_id] [int] NULL ,
      [action_name] [varchar] (50)  NULL ,
      [status_name] [varchar] (50)  NULL
) ON [PRIMARY]
GO

insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('1','1','1','deliver','in warehouse')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('2','2','1','deliver','on truck')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('3','3','1','deliver','at location')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('4','4','1','return','pending')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('5','5','1','return','approved')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('6','6','1','return','declined')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('7','7','1','deliver','in warehouse')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('8','8','1','deliver','on truck')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('9','9','1','deliver','at location')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('10','10','1','return','pending')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('11','11','1','return','approved')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('12','12','1','return','declined')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('13','13','1','deliver','in warehouse')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('14','14','1','deliver','on truck')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('15','15','1','deliver','at location')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('16','16','1','return','pending')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('17','17','1','return','approved')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('18','18','1','return','declined')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('19','19','2','deliver','in warehouse')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('20','20','2','deliver','on truck')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('21','21','2','deliver','at location')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('22','22','2','return','pending')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('23','23','2','return','approved')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('24','24','2','return','declined')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('25','25','3','deliver','in warehouse')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('26','26','3','deliver','on truck')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('27','27','3','deliver','at location')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('34','28','4','return','pending')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('35','29','4','return','approved')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('36','30','4','return','declined')
insert into tblMyTable(row_id,item_id,cust_id,action_name,status_name) values ('37','31','4','return','declined')


create procedure procGetStatus (@cust_id int)
as
-- get the following type of results without 6 seperate queries in the procedure:
go

-- desired proc calls and results
procGetStatus '1'
deliverinwarehouse      deliverontruck      deliveratlocation returnpending      returnapproved      returndeclined
2      2      2      2      2      2

procGetStatus '2'
deliverinwarehouse      deliverontruck      deliveratlocation returnpending      returnapproved      returndeclined
1      1      1      1      1      1

procGetStatus '3'
deliverinwarehouse      deliverontruck      deliveratlocation returnpending      returnapproved      returndeclined
1      1      1      0      0      0

procGetStatus '4'
deliverinwarehouse      deliverontruck      deliveratlocation returnpending      returnapproved      returndeclined
0      0      0      1      1      2


Any ideas?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros