May 19, 2016 at 11:31 am
Hi everybody,
I need a T-sql query to return the existing table data return as a Tree View structure.
Below is the sample schema and sample data
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PatDet](
[PID] [int] NULL,
[VisitType] [char](2) NULL,
[VisitStartDate] [date] NULL,
[VisitEndDate] [date] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1001, N'V1', CAST(N'2009-10-10' AS Date), CAST(N'2009-10-10' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1001, N'V1', CAST(N'2011-08-26' AS Date), CAST(N'2011-08-26' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1001, N'V2', CAST(N'2012-11-20' AS Date), CAST(N'2012-11-20' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1001, N'V3', CAST(N'2013-02-28' AS Date), CAST(N'2013-02-28' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1001, N'V4', CAST(N'2013-03-02' AS Date), CAST(N'2013-03-02' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1002, N'V1', CAST(N'2010-06-21' AS Date), CAST(N'2010-06-21' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1002, N'V1', CAST(N'2010-07-29' AS Date), CAST(N'2010-07-29' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1002, N'V1', CAST(N'2011-04-11' AS Date), CAST(N'2011-04-11' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1002, N'V1', CAST(N'2011-04-20' AS Date), CAST(N'2011-04-20' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1002, N'V1', CAST(N'2013-04-07' AS Date), CAST(N'2013-04-07' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1003, N'V2', CAST(N'2009-12-11' AS Date), CAST(N'2009-12-11' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1003, N'V1', CAST(N'2011-04-30' AS Date), CAST(N'2011-04-30' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1003, N'V4', CAST(N'2011-05-13' AS Date), CAST(N'2011-05-13' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1003, N'V3', CAST(N'2012-11-06' AS Date), CAST(N'2012-11-06' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1003, N'V2', CAST(N'2013-03-27' AS Date), CAST(N'2013-03-27' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1004, N'V2', CAST(N'2009-07-17' AS Date), CAST(N'2009-07-17' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1004, N'V3', CAST(N'2010-01-21' AS Date), CAST(N'2010-01-21' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1004, N'V5', CAST(N'2010-03-19' AS Date), CAST(N'2010-03-19' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1004, N'V2', CAST(N'2010-11-08' AS Date), CAST(N'2010-11-08' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1005, N'V2', CAST(N'2009-12-09' AS Date), CAST(N'2009-12-09' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1005, N'V2', CAST(N'2011-06-23' AS Date), CAST(N'2011-06-23' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1005, N'V2', CAST(N'2011-11-21' AS Date), CAST(N'2011-11-21' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1005, N'V1', CAST(N'2012-05-21' AS Date), CAST(N'2012-05-21' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1005, N'V5', CAST(N'2012-05-25' AS Date), CAST(N'2012-05-25' AS Date))
INSERT [dbo].[PatDet] ([PID], [VisitType], [VisitStartDate], [VisitEndDate]) VALUES (1005, N'V3', CAST(N'2013-12-29' AS Date), CAST(N'2013-12-29' AS Date))
Expected output :
1st Visit2nd Visit3rd Visit
V1-3
v1-2
V1-1
V2-1
V2-2
V1-1
V4-1
V2-1
V5-1
V3-1
V2-1
Where V1, V2, etc are the Visit Type and the adjacent figures are count of PID.
1st Visit, 2nd Visit are derived based on the VisitStartDate (Ascending order).
Hope I am clear on this. Please suggest me an optimized way of getting the expected output using T-SQL.
Thanks in advance
May 19, 2016 at 12:17 pm
This type of report is usually best left to the presentation layer. It's possible to do this in T-SQL, but it's not the best tool for the job. Here is an approach that will give you what you want--except for minor formatting. (I did change your table to a table variable.)
[h2]Data setup[/h2]
DECLARE @PatDev TABLE (
PID INT,
VisitType NCHAR(2),
VisitStartDate DATE,
VisitEndDate DATE)
INSERT @PatDev
VALUES
(1001, N'V1', CAST(N'2009-10-10' AS Date), CAST(N'2009-10-10' AS Date))
,(1001, N'V1', CAST(N'2011-08-26' AS Date), CAST(N'2011-08-26' AS Date))
,(1001, N'V2', CAST(N'2012-11-20' AS Date), CAST(N'2012-11-20' AS Date))
,(1001, N'V3', CAST(N'2013-02-28' AS Date), CAST(N'2013-02-28' AS Date))
,(1001, N'V4', CAST(N'2013-03-02' AS Date), CAST(N'2013-03-02' AS Date))
,(1002, N'V1', CAST(N'2010-06-21' AS Date), CAST(N'2010-06-21' AS Date))
,(1002, N'V1', CAST(N'2010-07-29' AS Date), CAST(N'2010-07-29' AS Date))
,(1002, N'V1', CAST(N'2011-04-11' AS Date), CAST(N'2011-04-11' AS Date))
,(1002, N'V1', CAST(N'2011-04-20' AS Date), CAST(N'2011-04-20' AS Date))
,(1002, N'V1', CAST(N'2013-04-07' AS Date), CAST(N'2013-04-07' AS Date))
,(1003, N'V2', CAST(N'2009-12-11' AS Date), CAST(N'2009-12-11' AS Date))
,(1003, N'V1', CAST(N'2011-04-30' AS Date), CAST(N'2011-04-30' AS Date))
,(1003, N'V4', CAST(N'2011-05-13' AS Date), CAST(N'2011-05-13' AS Date))
,(1003, N'V3', CAST(N'2012-11-06' AS Date), CAST(N'2012-11-06' AS Date))
,(1003, N'V2', CAST(N'2013-03-27' AS Date), CAST(N'2013-03-27' AS Date))
,(1004, N'V2', CAST(N'2009-07-17' AS Date), CAST(N'2009-07-17' AS Date))
,(1004, N'V3', CAST(N'2010-01-21' AS Date), CAST(N'2010-01-21' AS Date))
,(1004, N'V5', CAST(N'2010-03-19' AS Date), CAST(N'2010-03-19' AS Date))
,(1004, N'V2', CAST(N'2010-11-08' AS Date), CAST(N'2010-11-08' AS Date))
,(1005, N'V2', CAST(N'2009-12-09' AS Date), CAST(N'2009-12-09' AS Date))
,(1005, N'V2', CAST(N'2011-06-23' AS Date), CAST(N'2011-06-23' AS Date))
,(1005, N'V2', CAST(N'2011-11-21' AS Date), CAST(N'2011-11-21' AS Date))
,(1005, N'V1', CAST(N'2012-05-21' AS Date), CAST(N'2012-05-21' AS Date))
,(1005, N'V5', CAST(N'2012-05-25' AS Date), CAST(N'2012-05-25' AS Date))
,(1005, N'V3', CAST(N'2013-12-29' AS Date), CAST(N'2013-12-29' AS Date))
[h2]Query[/h2]
;
WITH pd AS (
SELECT PID, pd.VisitType, ROW_NUMBER() OVER(PARTITION BY pd.PID ORDER BY pd.VisitStartDate) AS rn
FROM @PatDev pd
)
, pd_pvt AS (
SELECT pd.PID
,MAX(CASE WHEN rn = 1 THEN pd.VisitType END) AS Visit1
,MAX(CASE WHEN rn = 2 THEN pd.VisitType END) AS Visit2
,MAX(CASE WHEN rn = 3 THEN pd.VisitType END) AS Visit3
FROM pd
GROUP BY pd.PID
)
SELECT CASE WHEN GROUPING(pp.Visit2) = 1 THEN pp.Visit1 END AS Visit1, CASE WHEN GROUPING(pp.Visit3) = 1 THEN pp.Visit2 END AS Visit2, pp.Visit3, COUNT(pp.PID) AS cnt
FROM pd_pvt pp
GROUP BY ROLLUP(pp.Visit1, pp.Visit2, pp.Visit3)
HAVING GROUPING(pp.Visit1) = 0
ORDER BY GROUPING(pp.Visit1) DESC, pp.Visit1, GROUPING(pp.Visit2) DESC, pp.Visit2, GROUPING(pp.Visit3) DESC, pp.Visit3
Make sure that you understand EVERYTHING that this query is doing before deploying it to your server. You need to be able to support your code, and you can't do that if you don't understand what it is doing.
Drew
[edited to separate data setup from the main query.]
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 20, 2016 at 1:32 am
This type of report is usually best left to the presentation layer. It's possible to do this in T-SQL, but it's not the best tool for the job.
I completely agree with your opinion. But there are some constraints from Front End and hence I need to resort to T-SQL.
But in case if I had to leave to Presentation Layer to handle the visualization, what should be the Ideal way to give the data? I mean is it sufficient to give the table data as data source or I have to write any query to be able to get the data as expected?
Your solution solves the purpose. Thanks a lot!
I am thinking to create a query which will generate the output in the following way
Probably by creating an Indexed View Keeping the Node Id as Unique, I would get reasonable performance.
I guess the following output can be used by any Front End language to visualize the tree with minimum effort and maximum performance.
NodeIdVisitTypePatientCntParentNodeIdLevelID
1V12NULL1
2V23NULL1
11V1212
12V1122
13V2122
14V3122
111V11113
112V21113
113V41123
114V21133
115V51143
Looking forward for your comments/suggestions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply