Multiple Orders Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple Orders Question

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2010-02-22 : 14:48:31

I have the following issue..

Most of my data would be the same

IE, Customer Name, Address, Phone Number, etc. But I need to keep track of all the work orders for the customer within the last 30 days.

So Example

Account [Work Order] Name Sold Product

123 1 Mr Customer 1
123 2 Mr Customer -2
123 3 Mr Customer 5

In a case where would need to keep track of a all work order numbers but only show the orders with a total Positive SUM

So Work Order 1 and 2 cancel each other out but the customer ended up with a total of 4 of a product. How do I only show the last order (after summing up everything) and then bringing only the positive number ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 15:07:38

Something like this...

SELECT Account ,Name ,SUM(Product)
FROM table1
GROUP BY Account ,Name

ZMike
Posting Yak Master

110 Posts

Posted - 2010-02-22 : 15:09:46

vijayisonly

Yes something similar to that but I need to pull out whatever work order number ends up with a positive # for any given services which lies my issue because it wants to bring all of the work order numbers back in. Does that make sense ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 15:11:59

quote:
Does that make sense ?

Not quite...Can you show us what your expected output will look like.. for the sample data that you have provided.

ZMike
Posting Yak Master

110 Posts

Posted - 2010-02-22 : 15:30:32

Account Number First Name Last Name Work Order Service 1 Service 2 Sevice 3 Service 4 Service 5 Service 6 Service 7
123 TEST 111111762 -1 -1 1 0 0 1 0
123 TEST 111111925 0 0 0 0 0 -1 0
123 TEST 111111925 0 0 0 0 0 -1 1
123 TEST 111111175 2 2 -1 0 0 0 -1

So Basically the it's all the same account information besides the work orders I have for a customer So if I added all the orders up Service 1 and Service 2 come out with a positive # from the work order 1175. It may not always be the Max order. I can sum up all the information with out a problem but I need to acutally list the work order once I come to the conclusion of the one that comes out to a positive # when ran against all the other orders.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 15:33:09

What if your sample data is this? Which workorder will you list..
1925 or 1175?

Account Number First Name Last Name Work Order Service 1 Service 2 Sevice 3 Service 4 Service 5 Service 6 Service 7
123 TEST 111111762 -1 -1 1 0 0 1 0
123 TEST 111111925 2 4 0 0 0 -1 0
123 TEST 111111926 -2 -1 0 0 0 -1 1
123 TEST 111111175 2 2 -1 0 0 0 -1

ZMike
Posting Yak Master

110 Posts

Posted - 2010-02-22 : 16:02:07

vijayisonly

Im hoping this wont be the case for a customer in the last 30 days but it obiously could happen. I guess in this case the best I could do would be to take the max work order number.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-22 : 16:40:10

Maybe this?
Sample data

declare @t table
(Account_Number int, [Name] varchar (20),Work_Order int, Service1 int)
insert @t
select 123, 'TEST', 111111762, -1
union all select 123, 'TEST', 111111925, 0
union all select 123, 'TEST', 111111926, 0
union all select 123, 'TEST', 111111175, 2
union all select 321, 'TEST1', 222222101, -1
union all select 321, 'TEST1', 222222001, 1
union all select 321, 'TEST1', 222222005, -1
union all select 321, 'TEST1', 222222005, 2

Query

select distinct b.Account_Number,b.[Name],b.Work_Order,a.Service1
from 
(
select Account_Number,[Name],MAX(case when Service1 > 0 then Work_Order else null end) as Work_Order,SUM(Service1) as Service1 
from @t group by Account_Number,[Name]
having SUM(Service1) > 0
) a
inner join @t b
on a.Account_Number = b.Account_Number and a.[Name] = b.[Name]  and a.Work_Order = b.Work_Order

Result

Account_Number Name                 Work_Order  Service1
-------------- -------------------- ----------- -----------
123            TEST                 111111175   1
321            TEST1                222222005   1

ZMike
Posting Yak Master

110 Posts

Posted - 2010-02-22 : 22:17:12

vijayisonly,

I will check this in the morning and get back to you. Thank you for looking into it.

ZMike
Posting Yak Master

110 Posts

Posted - 2010-02-24 : 11:23:23

vijayisonly,

Im pretty sure the derived table will work. What I have is on a little bigger scale and I'm getting an error. Any chance I could send you what I have off line and my error because of the information that's on it ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:29:39

quote:
Originally posted by ZMike

vijayisonly,

Im pretty sure the derived table will work. What I have is on a little bigger scale and I'm getting an error. Any chance I could send you what I have off line and my error because of the information that's on it ?

better to post here at least error message so that somebody can suggest a method to solve it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2010-02-24 : 11:50:45

visakh16,

I actually just figured it out.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 11:57:40

Ok great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources