SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recursive Common Table Expression data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 01/10/2014 :  17:23:12  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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

Edited by - shanmugaraj on 01/11/2014 19:07:42

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 01/10/2014 :  23:29:03  Show Profile  Reply with Quote
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
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 01/11/2014 :  02:28:46  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 01/12/2014 :  01:33:10  Show Profile  Reply with Quote
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
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 01/12/2014 :  02:48:34  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000