Author |
Topic |
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2013-08-16 : 12:14:38
|
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 1This 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)) lineagefrom [db_assets].[dbo].[tb_locations] a where f_locationID = '130' UNION ALLselect 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 2This 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 cjoin [db_reports].[dbo].[tb_locationsmachines] aon a.f_itemparentid = c.f_itemid)--below is what I need, not the lineage aboveSELECT 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 cJOIN [db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.f_itemnameGROUP BY f_computernameOrder 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
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-08-16 : 12:23:42
|
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)) lineagefrom [db_assets].[dbo].[tb_locations] a where f_locationID = '130' UNION ALLselect 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 ), ... |
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2013-08-16 : 17:02:02
|
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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tb_locations]( [f_locationID] [int] IDENTITY(1,1) NOT NULL, [f_locationparent] [int] NULL, [f_locationname] [varchar](64) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[tb_locations] ONINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tb_assets]( [f_assettagID] [int] IDENTITY(10001,1) NOT NULL, [f_assetnetbiosname] [varchar](36) NULL, [f_assetlocation] [int] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[tb_assets] ONINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [dbo].[tb_sessions] ONINSERT [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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOSET IDENTITY_INSERT [dbo].[tb_locationsmachines] ONINSERT [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)) lineagefrom [_db_assets].[dbo].[tb_locations] a where f_locationID = '7' UNION ALLselect 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 cjoin [_db_reports].[dbo].[tb_locationsmachines] aon 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 cJOIN [_db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.f_itemnamewhere DATEDIFF(d, f_sessiondate, getdate()) < 60GROUP BY f_computernameOrder By f_computername ASC, 'Sessions' DESC
Again, thank you. Sincerely. |
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2013-08-16 : 17:39:33
|
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 queryThanks,Matt |
|
|
mattboy_slim
Yak Posting Veteran
72 Posts |
Posted - 2013-08-16 : 17:55:23
|
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)) lineagefrom [_db_assets].[dbo].[tb_locations] a where f_locationID = '7' UNION ALLselect 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 cJOIN [_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. |
|
|
|
|
|