Values as per rules

Question

Values as per rules

Shanmuga Raj

Ten Centuries

Points: 1178
More actions
January 9, 2014 at 2:27 pm

#284258

Hi
I need to Return values with hierarchy 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 4 posts - 1 through 3 (of 3 total)

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

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 1

Hi. This example looks like a test of some kind. I think that it is unfair to your school or potential employer to ask us to do the work for you. If the former, you won't learn. If the latter, you are asking us to help you to demonstrate knowledge you don't have.

The most help I am willing to offer is that you should be using a join and a group, and I am being generous by supplying that much.

Thanks

John.

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

John,

It seems to be more than that as it depends on a hierarchy. This is a duplicate post and it was originally posted here.

http://www.sqlservercentral.com/Forums/Topic1529451-391-1.aspx

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

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 3

Thanks, Luis. I only saw it here. I do know that there is much more than a single join and group, but I still suspect that this is a test question we are being asked to solve for the OP.

Thanks

John.