Proivde Hierechy wise sum and display all

Question

Proivde Hierechy wise sum and display all

Shanmuga Raj

Ten Centuries

Points: 1178
More actions
January 9, 2014 at 10:32 am

#302424

Hi
I need to Return values with hierachy sum.
I have
provided the tables with data and result expected
below
============
CREATE TABLE
============
CREATE TABLE
[dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2)
NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON
[PRIMARY]
CREATE TABLE [dbo].[Travel_Quantity](
[Load_Id] [int]
NULL,
[Mode_Sno] [int] NULL,
[Mode_Id] [nchar](2)
NULL,
[Mode_Parent_Sno] [int] NULL,
[QA] [numeric](18, 0) NULL,
[QC]
[numeric](18, 0) NULL,
[QY] [numeric](18, 0) NULL
) ON [PRIMARY]
============
INSERT DATA INTO TABLE
1
============
INSERT INTO [dbo].[Travel_Master]
([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR'
,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info]
,[Has_Nodes]) VALUES ( 1,'SE' ,'SEA' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'SP' ,'SHIP' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id]
,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BT' ,'BOAT' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'RD' ,'ROAD' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id]
,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')
INSERT
INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes])
VALUES ( 1,'CA' ,'CAR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id]
,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BI' ,'BIKE' ,'No')
INSERT
INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes])
VALUES ( 1,'CY' ,'CYCLE' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id]
,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'TR' ,'TRAM' ,'No')
INSERT
INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes])
VALUES ( 1,'BM' ,'BMW' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id]
,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AI' ,'AUDI'
,'No')
============
INSERT DATA INTO TABLE
2
============
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'1' ,'AP' ,-1,4 ,0,0 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'2' ,'SE' ,-1,0 ,5,0 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'3' ,'SP' ,2,0 ,0,3 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'4' ,'BT' ,2,0 ,0,5 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'5' ,'RD' ,-1,0 ,2,0 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'6' ,'BU' ,5,0 ,0,10 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'7' ,'CA' ,5,3 ,0,0 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'8' ,'BI' ,5,0 ,0,15 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'9' ,'CY' ,5,0 ,0,2 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'10' ,'TR' ,5,0 ,0,5 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'11' ,'BM' ,7,0 ,0,6 )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA],[QC],[QY]) VALUES ( 1,'12' ,'AI' ,7,0 ,0,14 )
============
RULES
============
QA or QC will
be present in the Parent Node where we have 'Has_Nodes' = Yes
QY will be data
of the Leaf in Tree, which has to be muliptied against Its
roots.
========================
EXPECTED
RESULT
========================
Mode_Info | Mode_Detail | QA | QC |QY
Air | |4 | |
Sea | | |5|
SEA |SHIP | | |15
SEA |BOAT | | |25
ROAD | | |2 |
ROAD |BUS | | |20
ROAD |BIKE | | |30
ROAD |CYCLE | | |4
ROAD |TRAM | | |10
ROAD |CAR |3 | |
ROAD |BMW | | |36
ROAD |AUDI | | |84

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Excellent job posting ddl and sample data. I can't understand what you want for output. I can't seem to figure out the relationship between these tables.

I think that somehow in here you have some sort of hierarchy but I can't get it. SEA Has_Nodes but what are they? It's parent is -1 so I assume it must be the root but there is nothing I can see that would indicate it is a child of that.

Also, in your desired output it is a bit confusing. It seems like AIR has no children but you have nothing for QA, the values of QA in the QC column and nothing again QY. Help me sort out what you are looking for and we can figure this out.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 2

Sean Lange (1/9/2014)
Excellent job posting ddl and sample data. I can't understand what you want for output. I can't seem to figure out the relationship between these tables.
I think that somehow in here you have some sort of hierarchy but I can't get it. SEA Has_Nodes but what are they? It's parent is -1 so I assume it must be the root but there is nothing I can see that would indicate it is a child of that.
Also, in your desired output it is a bit confusing. It seems like AIR has no children but you have nothing for QA, the values of QA in the QC column and nothing again QY. Help me sort out what you are looking for and we can figure this out.

Hi,

Yes. Where the Parent is -1, those records are root values. If no data below, then they donot have child.

I would like to insert my Desired output Image from my machine in the Post, but since i dont know how to do, i am trying to explain my expected output.

The parent data will have either QA or QC

Child will have data in QY.

Hope this clarifies.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Shanmuga Raj (1/9/2014)
Sean Lange (1/9/2014)
Excellent job posting ddl and sample data. I can't understand what you want for output. I can't seem to figure out the relationship between these tables.
I think that somehow in here you have some sort of hierarchy but I can't get it. SEA Has_Nodes but what are they? It's parent is -1 so I assume it must be the root but there is nothing I can see that would indicate it is a child of that.
Also, in your desired output it is a bit confusing. It seems like AIR has no children but you have nothing for QA, the values of QA in the QC column and nothing again QY. Help me sort out what you are looking for and we can figure this out.
Hi,
Yes. Where the Parent is -1, those records are root values. If no data below, then they donot have child.
I would like to insert my Desired output Image from my machine in the Post, but since i dont know how to do, i am trying to explain my expected output.
The parent data will have either QA or QC
Child will have data in QY.
Hope this clarifies.

You have to remember that we can't see your screen and have no idea what your project is trying to do. You can attach an image if you want. Just scroll down on the page when posting and click the "Edit Attachments" button.

It would go a long way to understanding if you can try to be explicit in your description. Things like "The parent data will have either QA or QC" does not make sense. Which one should it be? What are the business rules?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You might want to review your sample data as it won't give the expected results. Root for ship and boat is air and some multiplications won't give the values you posted.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 5

Luis Cazares (1/9/2014)
You might want to review your sample data as it won't give the expected results. Root for ship and boat is air and some multiplications won't give the values you posted.

Thanks for point out my Query Mistake. I have updated the query. Kindly help

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 6

Sean Lange (1/9/2014)
Shanmuga Raj (1/9/2014)
Sean Lange (1/9/2014)
Excellent job posting ddl and sample data. I can't understand what you want for output. I can't seem to figure out the relationship between these tables.
I think that somehow in here you have some sort of hierarchy but I can't get it. SEA Has_Nodes but what are they? It's parent is -1 so I assume it must be the root but there is nothing I can see that would indicate it is a child of that.
Also, in your desired output it is a bit confusing. It seems like AIR has no children but you have nothing for QA, the values of QA in the QC column and nothing again QY. Help me sort out what you are looking for and we can figure this out.
Hi,
Yes. Where the Parent is -1, those records are root values. If no data below, then they donot have child.
I would like to insert my Desired output Image from my machine in the Post, but since i dont know how to do, i am trying to explain my expected output.
The parent data will have either QA or QC
Child will have data in QY.
Hope this clarifies.
You have to remember that we can't see your screen and have no idea what your project is trying to do. You can attach an image if you want. Just scroll down on the page when posting and click the "Edit Attachments" button.
It would go a long way to understanding if you can try to be explicit in your description. Things like "The parent data will have either QA or QC" does not make sense. Which one should it be? What are the business rules?

Parent Data can be in QA or QC, where in the Child data will be in QY in source table.

The result in QY should be displaying child data multiplied against the QA or QC column which has data

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 7

What is the purpose for this project? I saw a duplicate post and responded to it, but this has the feel of a test question for school or a potential employer.

I don't want to provide the whole solution. But, to be helpful:

1. Use a CTE to express the hierarchy as one flat table

2. Self-join the CTE to express the hierarchy into the form of your example; a CASE or two will be needed.

3. The math against the columns should be simple

Thanks

John.

Dwain Camps SSC Guru Points: 86978 More actions · Answer 8

Something like this perhaps?

WITH rCTE AS

(

SELECT b.Mode_ID, b.Mode_info, Mode_Detail=Mode_info, QA, QC, QY, Mode_Sno, b.Has_Nodes

FROM Travel_Quantity a

JOIN Travel_Master b ON a.Mode_id = b.Mode_id

WHERE Mode_Parent_Sno = -1

UNION ALL

SELECT b.Mode_ID, a.Mode_detail, c.Mode_info

,CASE c.Has_Nodes WHEN 'Yes' THEN b.QA ELSE CAST(0 AS NUMERIC(18, 0)) END

,CASE c.Has_Nodes WHEN 'Yes' THEN b.QC ELSE CAST(0 AS NUMERIC(18, 0)) END

,CAST(b.QY * CASE a.QA WHEN 0 THEN a.QC ELSE a.QA END AS NUMERIC(18, 0))

,b.Mode_Sno, c.Has_Nodes

FROM rCTE a

JOIN Travel_Quantity b ON a.Mode_Sno = b.Mode_Parent_Sno

JOIN Travel_Master c ON b.Mode_ID = c.Mode_ID

)

SELECT Mode_info, Mode_Detail, QA, QC, QY

FROM rCTE;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 9

dwain.c (1/9/2014)
Something like this perhaps?
SNIP

See! You gave him the entire solution! 😀 :w00t:

Dwain Camps SSC Guru Points: 86978 More actions · Answer 10

JohnFTamburo (1/9/2014)
dwain.c (1/9/2014)
Something like this perhaps?
SNIP
See! You gave him the entire solution! 😀 :w00t:

Not really. The results set is close but not exact, which means he'll need to figure out what I did to correct it.

Besides, in this particular case, it didn't strike me as a homework problem.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 11

Hi,

Thanks for your SQLQuery !

But, In The QY Column, I am NOT getting values for all chils.

Data is showing only for

CAR BMW 0018

CAR AUDI 0042

Kindly suggest

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 12

Since my Values are NULL, I was not getting the complete result for QY Column.

Instead of

ROADAUDI0084

ROADBMW0036

I need result as.. calculation against the immediate parent only.

ROADAUDI0018

ROADBMW0042

Kindly suggest

Shanmuga Raj Ten Centuries Points: 1178 More actions · Answer 13

dwain.c (1/9/2014)
Something like this perhaps?
WITH rCTE AS
(
SELECT b.Mode_ID, b.Mode_info, Mode_Detail=Mode_info, QA, QC, QY, Mode_Sno, b.Has_Nodes
FROM Travel_Quantity a
JOIN Travel_Master b ON a.Mode_id = b.Mode_id
WHERE Mode_Parent_Sno = -1
UNION ALL
SELECT b.Mode_ID, a.Mode_detail, c.Mode_info
,CASE c.Has_Nodes WHEN 'Yes' THEN b.QA ELSE CAST(0 AS NUMERIC(18, 0)) END
,CASE c.Has_Nodes WHEN 'Yes' THEN b.QC ELSE CAST(0 AS NUMERIC(18, 0)) END
,CAST(b.QY * CASE a.QA WHEN 0 THEN a.QC ELSE a.QA END AS NUMERIC(18, 0))
,b.Mode_Sno, c.Has_Nodes
FROM rCTE a
JOIN Travel_Quantity b ON a.Mode_Sno = b.Mode_Parent_Sno
JOIN Travel_Master c ON b.Mode_ID = c.Mode_ID
)
SELECT Mode_info, Mode_Detail, QA, QC, QY
FROM rCTE;

Thanks, It is working.

In case of I need to have multiplied with only the immediate parent, Kindly suggest me the query

Expected

============

ROAD AUDI 0 0 42

ROAD BMW 0 0 18

Dwain Camps SSC Guru Points: 86978 More actions · Answer 14

Shanmuga Raj (1/9/2014)
dwain.c (1/9/2014)
Something like this perhaps?
WITH rCTE AS
(
SELECT b.Mode_ID, b.Mode_info, Mode_Detail=Mode_info, QA, QC, QY, Mode_Sno, b.Has_Nodes
FROM Travel_Quantity a
JOIN Travel_Master b ON a.Mode_id = b.Mode_id
WHERE Mode_Parent_Sno = -1
UNION ALL
SELECT b.Mode_ID, a.Mode_detail, c.Mode_info
,CASE c.Has_Nodes WHEN 'Yes' THEN b.QA ELSE CAST(0 AS NUMERIC(18, 0)) END
,CASE c.Has_Nodes WHEN 'Yes' THEN b.QC ELSE CAST(0 AS NUMERIC(18, 0)) END
,CAST(b.QY * CASE a.QA WHEN 0 THEN a.QC ELSE a.QA END AS NUMERIC(18, 0))
,b.Mode_Sno, c.Has_Nodes
FROM rCTE a
JOIN Travel_Quantity b ON a.Mode_Sno = b.Mode_Parent_Sno
JOIN Travel_Master c ON b.Mode_ID = c.Mode_ID
)
SELECT Mode_info, Mode_Detail, QA, QC, QY
FROM rCTE;
Thanks, It is working.
In case of I need to have multiplied with only the immediate parent, Kindly suggest me the query
Expected
============
ROAD AUDI 0 0 42
ROAD BMW 0 0 18

This is a pretty complicated query, and as I noted earlier, I think it is important for you to figure out how it is working. To change it to what you need is not difficult, you just need to play with it a little.

Good luck.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St