Values as per rules

Question

Post reply

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: 6330 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: 183686 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://qa.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: 6330 More actions · Answer 3

SQLBlimp

SSCertifiable

Points: 6330

January 9, 2014 at 3:13 pm

#1678999

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.