| Author |
 Topic  |
|
|
kfluffie
Posting Yak Master
103 Posts |
 Posted - 12/24/2012 : 04:48:49
|
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
1451 Posts |
Posted - 12/24/2012 : 04:56:19
|
SELECT ProductNumber, SUM(Quantity) Qty
FROM YourTable
GROUP BY ProductNumber
--
Chandu |
 |
|
|
kfluffie
Posting Yak Master
103 Posts |
Posted - 12/24/2012 : 05:15:44
|
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 |
|
|
|
kfluffie
Posting Yak Master
103 Posts |
Posted - 12/24/2012 : 05:17:02
|
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). |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/24/2012 : 05:53:31
|
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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/24/2012 : 07:37:52
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 12/24/2012 : 08:00:32
|
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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/27/2012 : 08:07:55
|
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 |
|
|
| |
Topic |
|
|
|
Adding a title based on conditions?
