Return values with hierachy sum - URGENT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return values with hierachy sum - URGENT

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-09 : 12:03:29

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 multiplied against Its roots.
Where the Parent is -1, those records are root values. If no data below, then they don't have child.

The parent data will have either QA or QC
Child will have data in QY.
========================
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

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-09 : 20:07:25

What have you come up with so far?

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-10 : 00:34:22

I have the query now.. I need to remove the following childs and display only the immediate parent and Child. Kindly suggest me the query
====================================
Expected to remove the rows
====================================

ROAD AUDI 0 0 84
ROAD BMW 0 0 36

;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info,T1.Mode_Parent_Sno,T1.Mode_Sno,T1.QA,T1.QC,T1.QY

from [Travel_Quantity] T1
left join [Travel_Master] T2 on T1.Mode_Id = T2.Mode_Id
)
,MyCTE2 as (
select T1.Mode_Info, CONVERT(NVARCHAR(MAX),'') as Mode_Detail,T1.Mode_Parent_Sno,T1.Mode_Sno,T1.QA,T1.QC,T1.QY, T1.QA Parent_QA , T1.QC Parent_QC, 1 as Lev
from MyCTE1 T1
where Mode_Parent_Sno = -1

UNION ALL

select MyCTE2.Mode_Info, T.Mode_Info,T.Mode_Parent_Sno,T.Mode_Sno,T.QA,T.QC,T.QY, MyCTE2.QA, MyCTE2.QC, Lev + 1
from MyCTE2
JOIN MyCTE1 T on MyCTE2.Mode_Sno = T.Mode_Parent_Sno
)
select Mode_Info,Mode_Detail,QA,QC, ((QY*Parent_QA) + (QY*Parent_QC)) * (Lev - 1) QY--, Lev
from MyCTE2
order by Mode_Info,Mode_Detail

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:33:57

Answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190673

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources