Recursive Common Table Expression data

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

Recursive Common Table Expression data

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-10 : 17:23:12

With my new data, need result for below scenario ( use CTE )
´`````````````
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
) ON [PRIMARY]

===============================
Insert DATA TABLE 1
===============================

INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'AP' ,'AIR' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FL' ,'FLIGHT' ,'Yes')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'FP' ,'FLIGHT-Pas' ,'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,'RD' ,'ROAD' ,'No')
INSERT INTO [dbo].[Travel_Master] ([Load_Id] ,[Mode_Id] ,[Mode_Info] ,[Has_Nodes]) VALUES ( 1,'BU' ,'BUS' ,'No')

===============================
Insert DATA TABLE 2
===============================
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'1' ,'AP' ,'-1','4' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'2' ,'FL' ,'1','2' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'3' ,'FP' ,'2','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'4' ,'SE' ,'-1','0' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'5' ,'SP' ,'4','1' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'6' ,'RD' ,'-1','6' )
INSERT INTO [Travel_Quantity]([Load_Id],[Mode_Sno],[Mode_Id],[Mode_Parent_Sno],[QA]) VALUES ( 1,'7' ,'BU' ,'6','3' )

========
RULE
========

1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.
From the available result,

2.HasNodes = Yes, Display Parent and 1st Node
3.HasNodes = No, Display Parent Only.

================
Expected Result
================
Mode_Info |Mode_Detail |QA
AIR | |4
AIR |FLIGHT |2
ROAD | |6

===============
My Query
=============

need help on learing CTE To apply for HasNodes Rules.

;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
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.QA Parent_QA ,T1.Has_Nodes
from MyCTE1 T1
where Mode_Parent_Sno = - 1 and QA <> 0 --and t1.Has_Nodes ='Yes'

UNION ALL

select MyCTE2.Mode_Info, T.Mode_Info as Mode_Detail, T.Mode_Parent_Sno, T.Mode_Sno,T.QA, MyCTE2.QA ,T.Has_Nodes
from MyCTE2
JOIN MyCTE1 T on MyCTE2.Mode_Sno = T.Mode_Parent_Sno
)
select Mode_Info,Mode_Detail,QA ,Has_Nodes
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 : 23:29:03

WHy not keep all related questions in same thread?


SELECT ModeInfo,ModeDetails,QA
FROM
(
SELECT COALESCE(m1.Mode_Info,m.Mode_Info) AS ModeInfo,CASE WHEN m1.Mode_Info IS NULL THEN '' ELSE m.Mode_Info END AS ModeDetails,m.Has_Nodes,
q.QA,q1.QA AS ParentQA,SUM(CASE WHEN m1.Mode_Info IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY COALESCE(m1.Mode_Info,m.Mode_Info)) AS Cnt
FROM [Travel_Master] m
INNER JOIN [Travel_Quantity] q
ON q.Load_Id = m.Load_Id
AND q.Mode_Id = m.Mode_Id
LEFT JOIN [Travel_Quantity] q1
ON q1.Mode_Sno = q.Mode_Parent_Sno
LEFT JOIN [Travel_Master] m1
ON q1.Load_Id = m1.Load_Id
AND q1.Mode_Id = m1.Mode_Id
)t
WHERE Cnt > 0 
AND QA>0 
AND  ( Has_Nodes='Yes' OR  ModeDetails = '')

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-11 : 02:28:46

Sorry, I was thinking it will be considered as same source data, wherein my soruce data is changed for the rules.

The result which i get is not as per expected. HasNodes = Yes Condition is only to be considered for Parent Node.
Consider i Change the Value as below,

UPDATE Travel_Quantity SET QA = 0 WHERE (Mode_Id = N'AP')
UPDATE Travel_Quantity SET QA = 2 WHERE (Mode_Id = N'SE')
Now I need the result to show as

================
Expected Result
================
Mode_Info |Mode_Detail |QA
SEA| |2
SEA|SHIP |1
ROAD | |6

========
RULE
========

1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.
From the avaliable result,

2.HasNodes = Yes, Display Parent and 1st Node
3.HasNodes = No, Display Parent Only.

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-12 : 01:33:10

quote:
Originally posted by shanmugaraj

Sorry, I was thinking it will be considered as same source data, wherein my soruce data is changed for the rules.

The result which i get is not as per expected. HasNodes = Yes Condition is only to be considered for Parent Node.
Consider i Change the Value as below,

UPDATE Travel_Quantity SET QA = 0 WHERE (Mode_Id = N'AP')
UPDATE Travel_Quantity SET QA = 2 WHERE (Mode_Id = N'SE')
Now I need the result to show as

================
Expected Result
================
Mode_Info |Mode_Detail |QA
SEA| |2
SEA|SHIP |1
ROAD | |6

========
RULE
========

1.QA of Parent Row value Greater than 0- Consider Parent and 1st Node.
From the avaliable result,

2.HasNodes = Yes, Display Parent and 1st Node
3.HasNodes = No, Display Parent Only.

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

did you try solution i provided in other forums?


SELECT ModeInfo,ModeDetails,ParentQA
FROM
(
SELECT COALESCE(m1.Mode_Info,m.Mode_Info) AS ModeInfo,CASE WHEN m1.Mode_Info IS NULL THEN '' ELSE m.Mode_Info END AS ModeDetails,m1.Has_Nodes,
COALESCE(q1.QA,q.QA) AS ParentQA,SUM(CASE WHEN m1.Mode_Info IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY COALESCE(m1.Mode_Info,m.Mode_Info)) AS Cnt
FROM [Travel_Master] m
INNER JOIN [Travel_Quantity] q
ON q.Load_Id = m.Load_Id
AND q.Mode_Id = m.Mode_Id
LEFT JOIN [Travel_Quantity] q1
ON q1.Mode_Sno = q.Mode_Parent_Sno
LEFT JOIN [Travel_Master] m1
ON q1.Load_Id = m1.Load_Id
AND q1.Mode_Id = m1.Mode_Id
)t
WHERE Cnt > 0 
AND ParentQA>0   
AND  ( Has_Nodes='Yes' 
OR  ModeDetails = '')

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

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-12 : 02:48:34

Thanks. It is working,
I was looking solution using CTE , since i want to have more combinations based on Has_nodes.
I would like to learn more on Recursive CTE..
Below is my query from my study which returns value for QA <> 0,

But i need help on learing CTE To apply for HasNodes Rules.

;with MyCTE1 as (
select
CONVERT(nvarchar(MAX),RTRIM(T2.Mode_Info)) Mode_Info, T1.Mode_Parent_Sno, T1.Mode_Sno, T1.QA ,T2.Has_Nodes
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.QA Parent_QA ,T1.Has_Nodes
from MyCTE1 T1
where Mode_Parent_Sno = - 1 and QA <> 0 --and t1.Has_Nodes ='Yes'

UNION ALL

select MyCTE2.Mode_Info, T.Mode_Info as Mode_Detail, T.Mode_Parent_Sno, T.Mode_Sno,T.QA, MyCTE2.QA ,T.Has_Nodes
from MyCTE2
JOIN MyCTE1 T on MyCTE2.Mode_Sno = T.Mode_Parent_Sno
)
select Mode_Info,Mode_Detail,QA ,Has_Nodes
from MyCTE2
order by Mode_Info,Mode_Detail

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources