December 20, 2013 at 3:41 pm
1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result
===============================================================================
CREATE TABLE and Insert Data
================================================================================
use master
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]
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')
CREATE TABLE [dbo].[Travel_Occurs](
[Load_Id] [int] NULL,
[Mode_Sno] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Parent_Sno] [int] NULL,
[Quantity] [numeric](18, 0) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,1,'AP',-1,4)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,2,'SE',-1,2)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,3,'SP',1,3)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,4,'BT',1,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,5,'RD',-1,3)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,6,'BU',5,10)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,7,'CA',5,20)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,8,'BI',5,15)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,9,'CY',5,2)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,10,'TR',5,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,11,'BM',7,6)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,12,'AI',7,14)
===============================================================================
CREATE #TempTable -- for easy result generation (Optional )
================================================================================
Concept Used ::
Quantity of the High Nodes to be Multiply with Leaf level Data
Mode_Info Mode_Info_Detail Mode_Info_Inculdes Has_Nodes Quantity Calc_Quantity
AIR AIR No 4 4
SEA Yes 2 no display + used for calc only
SEA SHIP No 3 6
SEA BOAT No 5 10
ROAD Yes 3 no display + used for calc only
ROAD BUS No 10 30
ROAD CAR Yes 20 no display + used for calc only
ROAD BIKE No 15 45
ROAD CYCLE No 2 6
ROAD TRAM No 5 15
ROAD CAR BMW No 6 360
ROAD CAR AUDI No 14 840
===============================================================================
Expected Result ================================================================================
Mode_Info Mode_Detail Quantity
Air 4
SEA SHIP 6
SEA BOAT 10
ROAD BUS 30
ROAD BIKE 4
ROAD CYCLE 6
ROAD TRAM 15
ROAD BMW 360
ROAD AUDI 840
An update in my data where we have -1, the quantity will be 0.
Kindly provide me the query
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'AP')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'SE')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'RD')
with cte
as (select load_id
,mode_sno
,mode_id
,mode_parent_sno
,quantity
,mode_id as last_mode_id
from travel_occurs
where mode_parent_sno=-1
union all
select a.load_id
,a.mode_sno
,a.mode_id
,b.mode_parent_sno
,cast(b.quantity*a.quantity as numeric(18,0)) as quantity
,b.mode_id as last_mode_id
from travel_occurs as a
inner join cte as b
on b.load_id=a.load_id
and b.mode_sno=a.mode_parent_sno
where a.mode_parent_sno<>-1
)
select c.mode_info
,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
,b.quantity
from travel_master as a
inner join cte as b
on b.load_id=a.load_id
and b.mode_id=a.mode_id
inner join travel_master as c
on c.load_id=b.load_id
and c.mode_id=b.last_mode_id
where a.has_nodes='No'
order by a.load_id
,b.mode_sno
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'AP')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'SE')
UPDATE Travel_Occurs SET Quantity = 0 WHERE (Mode_Id = N'RD')
December 21, 2013 at 2:33 am
below is the solution
thanks
with cte
as (select load_id
,mode_sno
,mode_id
,mode_parent_sno
,quantity
,mode_id as last_mode_id
from travel_occurs
where mode_parent_sno=-1
union all
select a.load_id
,a.mode_sno
,a.mode_id
,b.mode_parent_sno
,cast(b.quantity*a.quantity as numeric(18,0)) as quantity
,b.mode_id as last_mode_id
from travel_occurs as a
inner join cte as b
on b.load_id=a.load_id
and b.mode_sno=a.mode_parent_sno
where a.mode_parent_sno<>-1
)
select c.mode_info
,case when a.mode_info=c.mode_info then '' else a.mode_info end as mode_detail
,b.quantity
from travel_master as a
inner join cte as b
on b.load_id=a.load_id
and b.mode_id=a.mode_id
inner join travel_master as c
on c.load_id=b.load_id
and c.mode_id=b.last_mode_id
where a.has_nodes='No'
order by a.load_id
,b.mode_sno
December 21, 2013 at 8:54 pm
Thank you for posting your solution.
--Jeff Moden
December 22, 2013 at 1:53 am
Error In My query, I need to have ROAD instead of car.
Expexted
AIR 0
SEA SHIP 0
SEA BOAT 0
ROAD BUS 0
ROAD BIKE 0
ROAD CYCLE 0
ROAD TRAM 0
ROAD BMW 0
ROAD AUDI 0
Current Query
AIR 0
SEA SHIP 0
SEA BOAT 0
ROAD BUS 0
ROAD BIKE 0
ROAD CYCLE 0
ROAD TRAM 0
CAR BMW 0
CAR AUDI 0
Below is the data
delete
FROM [NCGProductReports].[dbo].[Travel_Occurs]
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,1,'AP',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,2,'SE',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,3,'SP',2,3)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,4,'BT',2,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,5,'RD',-1,0)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,6,'BU',5,10)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,7,'CA',5,20)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,8,'BI',5,15)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,9,'CY',5,2)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,10,'TR',5,5)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,11,'BM',7,6)
INSERT INTO [dbo].[Travel_Occurs] ([Load_Id] ,[Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,12,'AI',7,14)
December 22, 2013 at 12:04 pm
Heh... You do make it tough to keep up with changes.
First, let's make the table builds a bit more readable. You can do the same in the future by encapsulating your code in the Code="sql" IFCode shortcuts located just to the left of where you type. You can see the IFCodes I used if you click the "Quote" button just above this post.
I've also taken the time to indent the code and I've added some comments of suggested changes that should be made to the tables (I've not made the changes, I just suggest some changes). I strongly recommend that you read the comments especially since the content of the Has_Nodes column is totally non-intuitive and is actually the opposite of what one would expect in many cases.
--DROP TABLE dbo.Travel_Master,dbo.Travel_Occurs
GO
--===================================================================
-- Create the "Travel_Master" (lookup) table.
-- This is where the long names (Mode_Info) comes from.
--===================================================================
CREATE TABLE dbo.Travel_Master
-- Note that all of these columns should be NOT NULL.
-- NCHAR appears to be serious overkill here.
-- It also needs a PK on ModeID/Load_ID and a UNIQUE index on
-- Mode_Info/Load_ID.
(
Load_ID INT NULL
,Mode_ID NCHAR(2) NULL
,Mode_Info NCHAR(10) NULL
,Has_Nodes NCHAR(3) NULL
)
;
INSERT INTO dbo.Travel_Master
(Load_ID ,Mode_ID ,Mode_Info, Has_Nodes)
-- Note that most of the Has_Nodes values are incorrect
-- and the column should be changed to "IsViewable"
-- and the values should be BIT or TINYINT 1/0.
SELECT 1,'AP','AIR' ,'No' UNION ALL
SELECT 1,'SE','SEA' ,'Yes' UNION ALL
SELECT 1,'SP','SHIP' ,'No' UNION ALL
SELECT 1,'BT','BOAT' ,'No' UNION ALL
SELECT 1,'RD','ROAD' ,'Yes' UNION ALL
SELECT 1,'BU','BUS' ,'No' UNION ALL
SELECT 1,'CA','CAR' ,'Yes' UNION ALL
SELECT 1,'BI','BIKE' ,'No' UNION ALL
SELECT 1,'CY','CYCLE','No' UNION ALL
SELECT 1,'TR','TRAM' ,'No' UNION ALL
SELECT 1,'BM','BMW' ,'No' UNION ALL
SELECT 1,'AI','AUDI' ,'No'
;
--===================================================================
-- Create the "Travel_Occurs" (hierarchical detail) table.
--===================================================================
CREATE TABLE dbo.Travel_Occurs
-- Note that all of these columns should be NOT NULL.
-- NUMERIC(18,0) is serious overkill and should just be an INT.
-- NCHAR appears to be serious overkill here.
-- It also needs a PK on ModeID/Load_ID and a UNIQUE index on
-- ModeID/Mode_Parent_SNo/Load_ID.
(
Load_ID INT NULL
,Mode_SNo INT NULL
,Mode_ID NCHAR(2) NULL
,Mode_Parent_SNo INT NULL
,Quantity NUMERIC(18,0) NULL
)
;
INSERT INTO dbo.Travel_Occurs
(Load_ID, Mode_SNo, Mode_ID, Mode_Parent_SNo, Quantity)
SELECT 1, 1,'AP',-1, 0 UNION ALL
SELECT 1, 2,'SE',-1, 0 UNION ALL
SELECT 1, 3,'SP', 2, 3 UNION ALL
SELECT 1, 4,'BT', 2, 5 UNION ALL
SELECT 1, 5,'RD',-1, 0 UNION ALL
SELECT 1, 6,'BU', 5,10 UNION ALL
SELECT 1, 7,'CA', 5,20 UNION ALL
SELECT 1, 8,'BI', 5,15 UNION ALL
SELECT 1, 9,'CY', 5, 2 UNION ALL
SELECT 1,10,'TR', 5, 5 UNION ALL
SELECT 1,11,'BM', 7, 6 UNION ALL
SELECT 1,12,'AI', 7,14
;
Using your new data and expecting you to change the root level quantities again, here's the code that should solve the whole shootin' match. I also removed some unnecessary columns from the rCTE to make things run a bit faster.
WITH rCTE AS
( --=== Find the root of each tree in the forest
SELECT o.Load_ID
,o.Mode_SNo
,o.Mode_ID
,o.Mode_Parent_SNo
,o.Quantity
,Top_Mode_ID = o.Mode_ID
FROM dbo.Travel_Occurs AS o
WHERE o.Mode_Parent_SNo = (-1) --(-1) is the ID for the root of each tree.
UNION ALL
--===== Find all the branch and leaf level info for each tree.
-- This also multiplies each previous total quantity by the current quantity.
SELECT t.Load_ID
,t.Mode_SNo
,t.Mode_ID
,t.Mode_Parent_SNo
,Quantity = CAST(c.Quantity * t.Quantity AS NUMERIC(18,0)) --Multiply qty from top down
,c.Top_Mode_ID --Remembers the root of each tree
FROM dbo.Travel_Occurs AS t
JOIN rCTE AS c
ON c.Load_ID = t.Load_ID
AND c.Mode_SNo = t.Mode_Parent_SNo
WHERE t.Mode_Parent_SNo <> (-1)
) --=== Return the expanded tree info and the long names from the master table
-- for each detail row as well as the root node for each detail row
SELECT mp.Mode_Info
,Mode_Detail = md.Mode_Info
,Quantity = c.Quantity
FROM rCTE c
JOIN dbo.Travel_Master AS md ON md.Mode_ID = c.Mode_ID
JOIN dbo.Travel_Master AS mp ON mp.Mode_ID = c.Top_Mode_ID
WHERE md.Has_Nodes = 'No' -- This doesn't make any sense because they're mislabeled
ORDER BY Mode_Info, Mode_Detail
;
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply