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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Complex Problem: Joining two distinct databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattboy_slim
Yak Posting Veteran

67 Posts

Posted - 08/16/2013 :  12:14:38  Show Profile  Reply with Quote
I need to join data from multiple databases (on the same server). That in itself doesn’t seem to be inherently difficult. However, I have a CTE that iterates down to get all the children of a specified group. Below I have two distinct queries. The first one has the proper lineage and the second one gets the proper data. What I don’t understand is how to combine these two using the lineage of the first query.

Query 1
This is the lineage I need (where f_locationID = some ID passed from the web app). This works as desired and gives me all the machines that belong to the location or machines that belong to children of the location. However, the data I need for these machines resides in another database entirely.

quote:
;with cte_assets as (
select a.f_locationid, a.f_locationparent, a.f_locationname, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [db_assets].[dbo].[tb_locations] a where f_locationID = '130' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,a.f_locationname
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
 )
 select f_assetnetbiosname as 'Computer Name'
 from cte_assets c
 JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID
 ORDER BY f_assetname DESC




Query 2

This is the data I need. The CTE here can be discarded as the lineage was set up merely as an example, and is not what I need. See the results window below to see the data. However, I’d like that data back out for the lineage from above (where ‘Computer Name’ from query 2 = ‘Computer Name’ from query 1).


quote:
;with rCTE as (
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
  			  ,a.f_itemname
 			  ,0 as [lev]
 			  ,convert(varchar(30), '0_' + convert(varchar(10), f_itemid)) lineage
 	   from [db_reports].[dbo].[tb_locationsmachines] a
 	   where f_itemid = '1308' 
 	   UNION ALL
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
 			  ,a.f_itemname
 			  ,c.[lev] + 1
 			  ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_itemid))
from   rCTE c
join   [db_reports].[dbo].[tb_locationsmachines] a
on a.f_itemparentid = c.f_itemid
)
--below is what I need, not the lineage above
SELECT f_computername as 'Computer Name', 
  SUM(f_sessionlength) AS 'Total Session Time', 
  COUNT(*) as 'Sessions', 
  COUNT(*)/60 as 'Average Sessions per Day', 
  CAST(SUM(f_sessionlength) / 3600E / COUNT(*) as DECIMAL(18,2)) as 'Average Session Length (Hours)'
from rCTE c
JOIN [db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.f_itemname
GROUP BY f_computername
Order By 'Sessions' DESC, f_computername ASC



The common field between the two tables is ‘Computer Name’. I think I need a CTE inside a CTE, but no matter what I try I cannot get it to work.

Any guidance is greatly appreciated, sincerely.

Thanks in advance,
Matt

Lamprey
Flowing Fount of Yak Knowledge

4347 Posts

Posted - 08/16/2013 :  12:23:42  Show Profile  Reply with Quote
I'm having a hard time following without sample data and expected output, but youc an "chain" CTEs together if that's what you are trying to do. For example, if you take your first sample and make the query that consumes teh cte a cte itself:
;with cte_assets as (
select a.f_locationid, a.f_locationparent, a.f_locationname, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [db_assets].[dbo].[tb_locations] a where f_locationID = '130' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,a.f_locationname
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
 ),
 cte_a AS 
 (
	 select f_assetnetbiosname as 'Computer Name'
	 from cte_assets c
	 JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID
	 ORDER BY f_assetname DESC
 ),
 ...
Go to Top of Page

mattboy_slim
Yak Posting Veteran

67 Posts

Posted - 08/16/2013 :  17:02:02  Show Profile  Reply with Quote
Thank you. It took me a while to compile a sample set of data, but here it is:

Script 1:
quote:
USE [_db_assets]
GO
/****** Object:  Table [dbo].[tb_locations]    Script Date: 08/16/2013 15:46:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_locations](
	[f_locationID] [int] IDENTITY(1,1) NOT NULL,
	[f_locationparent] [int] NULL,
	[f_locationname] [varchar](64) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tb_locations] ON
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (7, 0, N'Memphis, TN')
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (11, 7, N'Building 1')
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (12, 7, N'Building 2')
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (13, 7, N'Building 3')
SET IDENTITY_INSERT [dbo].[tb_locations] OFF
/****** Object:  Table [dbo].[tb_assets]    Script Date: 08/16/2013 15:46:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_assets](
	[f_assettagID] [int] IDENTITY(10001,1) NOT NULL,
	[f_assetnetbiosname] [varchar](36) NULL,
	[f_assetlocation] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tb_assets] ON
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11001, N'MACHINE-A', 11)
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11002, N'MACHINE-B', 12)
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11003, N'MACHINE-C', 13)
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11004, N'MACHINE-D', 13)
SET IDENTITY_INSERT [dbo].[tb_assets] OFF




Script 2:
quote:
USE [_db_reports]
GO
/****** Object:  Table [dbo].[tb_sessions]    Script Date: 08/16/2013 15:57:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_sessions](
	[f_ID] [int] IDENTITY(1,1) NOT NULL,
	[f_sessiondate] [datetime] NULL,
	[f_sessionlength] [int] NULL,
	[f_computername] [varchar](36) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tb_sessions] ON
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560193, CAST(0x0000A21A00000000 AS DateTime), 2767, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560079, CAST(0x0000A21A00000000 AS DateTime), 1884, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5559979, CAST(0x0000A21A00000000 AS DateTime), 388, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560128, CAST(0x0000A21A00000000 AS DateTime), 2601, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563101, CAST(0x0000A21A00000000 AS DateTime), 1168, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563141, CAST(0x0000A21A00000000 AS DateTime), 327, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563175, CAST(0x0000A21A00000000 AS DateTime), 564, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560943, CAST(0x0000A21A00000000 AS DateTime), 4515, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560580, CAST(0x0000A21A00000000 AS DateTime), 5363, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560831, CAST(0x0000A21A00000000 AS DateTime), 2694, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5562887, CAST(0x0000A21A00000000 AS DateTime), 3012, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563547, CAST(0x0000A21A00000000 AS DateTime), 4360, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5562701, CAST(0x0000A21A00000000 AS DateTime), 256, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563572, CAST(0x0000A21A00000000 AS DateTime), 224, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563641, CAST(0x0000A21A00000000 AS DateTime), 2575, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563679, CAST(0x0000A21A00000000 AS DateTime), 8980, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563807, CAST(0x0000A21A00000000 AS DateTime), 1128, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563831, CAST(0x0000A21A00000000 AS DateTime), 7051, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563755, CAST(0x0000A21A00000000 AS DateTime), 1845, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563848, CAST(0x0000A21A00000000 AS DateTime), 2603, N'MACHINE-B')
SET IDENTITY_INSERT [dbo].[tb_sessions] OFF
/****** Object:  Table [dbo].[tb_locationsmachines]    Script Date: 08/16/2013 15:57:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_locationsmachines](
	[f_itemID] [int] IDENTITY(1,1) NOT NULL,
	[f_itemtype] [nvarchar](50) NULL,
	[f_itemparentID] [int] NOT NULL,
	[f_itemname] [nvarchar](50) NULL,
 CONSTRAINT [PK_tb_locationsmachines] PRIMARY KEY CLUSTERED 
(
	[f_itemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tb_locationsmachines] ON
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1062, N'group', 1010, N'Memphis, TN')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1067, N'machine', 1308, N'MACHINE-A')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1068, N'machine', 1316, N'MACHINE-B')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1069, N'machine', 1318, N'MACHINE-C')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1070, N'machine', 1308, N'MACHINE-D')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1308, N'group', 1062, N'Building 1')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1316, N'group', 1062, N'Building 2')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1318, N'group', 1062, N'Building 3')
SET IDENTITY_INSERT [dbo].[tb_locationsmachines] OFF




Updated SQL to match:
quote:
--this is the lineage I need where f_locationID = some ID passed from the web app
--note the machines in the first result window are what I need data for
;with cte_assets as (
select a.f_locationid, a.f_locationparent, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [_db_assets].[dbo].[tb_locations] a where f_locationID = '7' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [_db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
 )
 select f_assetnetbiosname as 'Computer Name'
 from cte_assets c
 JOIN [_db_assets].[dbo].[tb_assets] ass on ass.f_assetlocation = c.f_locationID
 ORDER BY f_assetnetbiosname ASC
 




--this is the data I need, so the lineage here is only for an example
;with rCTE as (
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
  			  ,a.f_itemname
 			  ,0 as [lev]
 			  ,convert(varchar(30), '0_' + convert(varchar(10), f_itemid)) lineage
 	   from [_db_reports].[dbo].[tb_locationsmachines] a
 	   where f_itemid = '1062' 
 	   UNION ALL
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
 			  ,a.f_itemname
 			  ,c.[lev] + 1
 			  ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_itemid))
from   rCTE c
join   [_db_reports].[dbo].[tb_locationsmachines] a
on a.f_itemparentid = c.f_itemid
)

SELECT f_computername as 'Computer Name', 
 SUM(f_sessionlength) AS 'Total Session Time', 
 COUNT(*) as 'Sessions', 
 COUNT(*)/60 as 'Average Sessions per Day', 
 CAST(SUM(f_sessionlength) / 3600E / COUNT(*) as DECIMAL(18,2)) as 'Average Session Length (Hours)'
from rCTE c
JOIN [_db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.f_itemname
where DATEDIFF(d, f_sessiondate, getdate()) < 60
GROUP BY f_computername
Order By f_computername ASC, 'Sessions' DESC



Again, thank you. Sincerely.

Edited by - mattboy_slim on 08/16/2013 17:40:34
Go to Top of Page

mattboy_slim
Yak Posting Veteran

67 Posts

Posted - 08/16/2013 :  17:39:33  Show Profile  Reply with Quote
Using the scripts above to import a small sample set and using the script below those, the output should be as follows. I want only the bottom results (circled in red), but need to use the lineage/hierarchy from the top part of the sql. In other words, I need to join the "Session" data for the PCs in the second part of the query to the hierarchy from the first part of the query


Thanks,
Matt
Go to Top of Page

mattboy_slim
Yak Posting Veteran

67 Posts

Posted - 08/16/2013 :  17:55:23  Show Profile  Reply with Quote
I got it! I read your reply and just stopped trying to go down my own path and did as you said. Here is the resultant query that works!


quote:
;with cte_assets as (
select a.f_locationid, a.f_locationparent, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [_db_assets].[dbo].[tb_locations] a where f_locationID = '7' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [_db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
),
cte_a AS 
(
	select f_assetnetbiosname as 'Computer Name'
	from cte_assets c
	JOIN [_db_assets].[dbo].[tb_assets] ass on ass.f_assetlocation = c.f_locationID
)
 
SELECT [Computer Name] as 'Computer Name', 
 SUM(f_sessionlength) AS 'Total Session Time', 
COUNT(*) as 'Sessions', 
COUNT(*)/60 as 'Average Sessions per Day', 
CAST(SUM(f_sessionlength) / 3600E / COUNT(*) as DECIMAL(18,2)) as 'Average Session Length (Hours)'
from cte_a c
JOIN [_db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.[Computer Name]
GROUP BY [Computer Name]
Order By [Computer Name] ASC, 'Sessions' DESC



Thanks for the push off the cliff - I was off in left field myself.

Edited by - mattboy_slim on 08/16/2013 17:56:53
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.12 seconds. Powered By: Snitz Forums 2000