SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding a title based on conditions?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kfluffie
Posting Yak Master

103 Posts

Posted - 12/24/2012 :  04:48:49  Show Profile  Reply with Quote
Hi,
I have a set of order lines which are being retrieved by a C# application.

These order lines are than verified and ordered through another system.

When the order lines have the same product number I would like to group them together as well as it's quantity.

For example:
ProductNumber Quantity
A 3
B 5
B 5
C 3

Should be:
A 3
B 10
C 3

But I also have a bit field called Processed so when the user checks this field (changes it from false to true) it means that the order line has been processed and C# or SQL needs to be able to update the original rows from this "virtual" created row.

Can anyone help me with this?

Best regards,
Tomas

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/24/2012 :  04:56:19  Show Profile  Reply with Quote
SELECT ProductNumber, SUM(Quantity) Qty
FROM YourTable
GROUP BY ProductNumber

--
Chandu
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts

Posted - 12/24/2012 :  05:15:44  Show Profile  Reply with Quote
quote:
Originally posted by bandi

SELECT ProductNumber, SUM(Quantity) Qty
FROM YourTable
GROUP BY ProductNumber

--
Chandu



So how will SQL know which rows to update the Processed flag in your query?

Best regards,
Tomas
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts

Posted - 12/24/2012 :  05:17:02  Show Profile  Reply with Quote
Otherwise how can I add a title?


A 3
B 4
B 4
C 3

A 3
Product B
B 8
C 3

But only for the rows that has two or more rows (i.e. two or more product numbers).
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/24/2012 :  05:53:31  Show Profile  Reply with Quote
May be this......?

DECLARE @TAB TABLE(ProductNumber char(1), qty int)
INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3)

;with CTE 
AS (
	SELECT ProductNumber, SUM(qty) qty FROM @TAB GROUP BY ProductNumber
   )
SELECT ISNULL(c.ProductNumber, t1.ProductNumber), ISNULL(c.qty, t1.qty),
	 CASE WHEN t1.qty != c.qty THEN 'Product'+t1.ProductNumber ELSE '' END processed 
FROM @TAB t1 LEFT JOIN CTE c ON t1.ProductNumber = c.ProductNumber AND t1.qty != c.qty


--
Chandu
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/24/2012 :  07:37:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
or this
DECLARE @TAB TABLE(ProductNumber char(1), qty int)
INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3)

SELECT ProductNumber, SUM(qty) as qty, case when count(*)>1 then 'Product '+ProductNumber else '' end as title
FROM @TAB GROUP BY ProductNumber


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 12/24/2012 :  08:00:32  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

or this
DECLARE @TAB TABLE(ProductNumber char(1), qty int)
INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3)

SELECT ProductNumber, SUM(qty) as qty, case when count(*)>1 then 'Product '+ProductNumber else '' end as title
FROM @TAB GROUP BY ProductNumber


Madhivanan

Failing to plan is Planning to fail


Hi Madhivanan,
Your solution looks nice..

--
Chandu
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/27/2012 :  08:07:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by bandi

quote:
Originally posted by madhivanan

or this
DECLARE @TAB TABLE(ProductNumber char(1), qty int)
INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3)

SELECT ProductNumber, SUM(qty) as qty, case when count(*)>1 then 'Product '+ProductNumber else '' end as title
FROM @TAB GROUP BY ProductNumber


Madhivanan

Failing to plan is Planning to fail


Hi Madhivanan,
Your solution looks nice..

--
Chandu


Thanks

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000