January 9, 2014 at 10:32 am
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
January 9, 2014 at 10:45 am
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/
January 9, 2014 at 11:42 am
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.
January 9, 2014 at 11:57 am
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/
January 9, 2014 at 11:57 am
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.
January 9, 2014 at 12:28 pm
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
January 9, 2014 at 12:33 pm
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
January 9, 2014 at 3:21 pm
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.
January 9, 2014 at 6:18 pm
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 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
January 9, 2014 at 6:30 pm
dwain.c (1/9/2014)
Something like this perhaps?SNIP
See! You gave him the entire solution! 😀 :w00t:
January 9, 2014 at 6:58 pm
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 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
January 9, 2014 at 10:17 pm
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 0 0 18
CAR AUDI 0 0 42
Kindly suggest
January 9, 2014 at 10:22 pm
Since my Values are NULL, I was not getting the complete result for QY Column.
Instead of
ROAD AUDI 0 0 84
ROAD BMW 0 0 36
I need result as.. calculation against the immediate parent only.
ROAD AUDI 0 0 18
ROAD BMW 0 0 42
Kindly suggest
January 9, 2014 at 10:33 pm
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
January 9, 2014 at 10:52 pm
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 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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply