November 10, 2010 at 10:09 am
Dear Experts,
I have quite a problem here :-
I am trying to add up data that sits on tables as some kind of hierarchy. The data is as follows :-
itemcode qty treetype
1001 2 N
1002 5 N
1003 1 S
1004 6 I
1005 9 I
1006 3 N
I would to sum up the data in a report as follows :-
the rows with treetype N are grouped as they are, but for treetype I the rows quantities are summed up and added as the totals for tree type S. Please note that the qty 1 for the row with tree type S is not added up. It is there for display purposes only.
itemcode qty treetype
1001 2 N
1002 5 N
1003 15 S
1006 3 N
Please help.
Regards,
November 10, 2010 at 3:01 pm
I agree with Celko on this one. You haven't provided us enough of the business rules to know how you do your rollups on I into S and rowId identifications. For example, what happens when there's more I's after row 1006, or an S without an I.
Also, providing the DDL and sample data in a consumable form will help, as he also mentioned. Check the first link in my sig for this if you require some assistance in understanding how to do that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 12, 2010 at 9:46 am
something like this
selectitemcode = min(itemcode) ,
qty = sum(case when treetype = 'S' then 0 esle qty end) ,
treetype = max(case when treetype = 'I' then '' else treetype end)
from
(select itemcode, qty, treetype, seq = (select count(*) from tbl t2 where t2.itemcode <= t.itemcode and treetype <> 'I')
from tbl t
) a
group by seq
Cursors never.
DTS - only when needed and never to control.
November 15, 2010 at 11:28 pm
Dear Experts,
What I actually request for is a way to create some kind of grouping for the data into a hierarchical structure where the S elements are the parents and the I underneath is the child items.
I have one table INV1 which has the data as shown above and the table ITT1 which has the father and child elements. The item codes are just unique identifiers of the items. The summation should be done on the quantity column of INV1, the table ITT1 shows the father and child hierarchy in the Father and Code columns respectively.
November 15, 2010 at 11:33 pm
Find files attached.
Cheers !
November 15, 2010 at 11:36 pm
martin.edward (11/10/2010)
Dear Experts,I have quite a problem here :-
I am trying to add up data that sits on tables as some kind of hierarchy. The data is as follows :-
itemcode qty treetype
1001 2 N
1002 5 N
1003 1 S
1004 6 I
1005 9 I
1006 3 N
I would to sum up the data in a report as follows :-
the rows with treetype N are grouped as they are, but for treetype I the rows quantities are summed up and added as the totals for tree type S. Please note that the qty 1 for the row with tree type S is not added up. It is there for display purposes only.
itemcode qty treetype
1001 2 N
1002 5 N
1003 15 S
1006 3 N
Please help.
Regards,
I dont think I am quite getting you on this.
What happens if there are multiple instances of "S" ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 16, 2010 at 1:02 am
Great Question Sachin,
When you have multiple instances of 'S' this is what the data should look like
itemcode qty treetype linenum
1001 2 N 0
1002 5 N 1
1003 1 S 2
1004 6 I 3
1005 9 I 4
1006 3 N 5
1007 1 S 6
1008 4 I 7
1009 6 I 8
1001 3 N 9
I would expect this :
itemcode qty treetype Linenum
1001 2 N `1
1002 5 N 2
1003 15 S 3
1006 3 N 5
1007 10 S 6
1001 3 N 9
November 16, 2010 at 1:25 am
martin.edward (11/16/2010)
Great Question Sachin,When you have multiple instances of 'S' this is what the data should look like
itemcode qty treetype linenum
1001 2 N 0
1002 5 N 1
1003 1 S 2
1004 6 I 3
1005 9 I 4
1006 3 N 5
1007 1 S 6
1008 4 I 7
1009 6 I 8
1001 3 N 9
I would expect this :
itemcode qty treetype Linenum
1001 2 N `1
1002 5 N 2
1003 15 S 3
1006 3 N 5
1007 10 S 6
1001 3 N 9
Can you please explain how are you getting qty "10" for this
1007 10 S 6 ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 16, 2010 at 1:27 am
Just ignore my previous post 🙂
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 16, 2010 at 2:35 am
Sure !
November 16, 2010 at 5:34 am
Any news on this ?
November 16, 2010 at 10:34 pm
This has to be one of the most dreadful query I have ever designed.The query assumes that Itemcode for treetype="I"
are not same.
DECLARE @t TABLE(
id INT IDENTITY,
itemcode INT,
qty INT,
treetype VARCHAR(10)
)
INSERT @t
SELECT 1001,2,'N'
UNION
SELECT 1002,5,'N'
UNION
SELECT 1003,1,'S'
UNION
SELECT 1004,6,'I'
UNION
SELECT 1005,9,'I'
UNION
SELECT 1006,1,'I'
UNION
SELECT 1007,3,'N'
UNION
SELECT 1008,1,'S'
UNION
SELECT 1009,4,'I'
UNION
SELECT 1010,6,'I'
UNION
SELECT 1011,3,'N'
SELECT * FROM @t;
WITH cte
AS (SELECT *,
Row_number()
OVER(ORDER BY itemcode) rid
FROM @t t1
WHERE treetype = 'I'
OR treetype = 'S'),
cte1
AS (SELECT *
FROM cte t1
OUTER APPLY (SELECT TOP 1 (qty) s
FROM cte t2
WHERE t2.treetype = 'I'
AND t1.rid = t2.rid) t)
UPDATE cte1 SET qty = s;
WITH cte2
AS (SELECT Sum(qty) s,
Min(id) id
FROM (SELECT *,
id - rid s
FROM (SELECT qty,
itemcode,
id,
Dense_rank()
OVER(ORDER BY CASE
WHEN qty IS NULL THEN 1
ELSE 0
END, itemcode) rid
FROM @t
WHERE qty IS NOT NULL
AND treetype = 'I'
OR treetype = 'S') t) t1
GROUP BY s),
cte3
AS (SELECT *
FROM (SELECT s,
id rid
FROM cte2
WHERE s IS NOT NULL) c
INNER JOIN (SELECT *
FROM @t
WHERE qty IS NULL) t
ON t.id = c.rid - 1
WHERE s IS NOT NULL)
UPDATE t
SET t.qty = c.s
FROM @t t
INNER JOIN cte3 c
ON t.itemcode = c.itemcode
SELECT *
FROM @t
WHERE treetype <> 'I'
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply