Hi all,
Trying to get the max date/time started across ALL tables...
SELECT Operator_Ref, MAX ([Date_Started])
, MAX([Time_Started])
FROM [TempoLive_ABEE].[dbo].[Operator_Usage]
Where Operator_Ref like 'Lynn%'
Group by Operator_Ref
SELECT Operator_Ref,
MAX ([Date_Started])
, MAX([Time_Started])
FROM [TempoLive_ABGlaxo].[dbo].[Operator_Usage]
Where Operator_Ref like 'Lynn%'
Group by Operator_Ref
SELECT Operator_Ref,
MAX ([Date_Started])
, MAX([Time_Started])
FROM [TempoLive_ABFarrGMBH].[dbo].[Operator_Usage]
Where Operator_Ref like 'Lynn%'
Group by Operator_Ref
TIA 🙂
March 4, 2022 at 11:50 am
So you want to loop round multiple DB's pulling in the values for the same query from the same table?
If I need to do this in each database (inc system I would use sp_msforeachdb) but as you wont have the table in the system DBs I would use the below.
Create a global temp table to hold the output, put your per db query in the right place, set the right databases to run this in on the outer select from sys.databases.
Then you can do whatever calculation you like on the ##output table
CREATE TABLE [##Output](
.....
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT
'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'INSERT INTO [##Output] (.....)' + CHAR(13) + CHAR(10) +
/*Put your per DB query in this segment, note this has to be in dynamic SQL so double ' etc etc*/
/*End of per DB query*/
FROM sys.databases
WHERE STATE_DESC = 'ONLINE' AND database_id > 4
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
--select @sql
EXECUTE sp_executesql @SQL
select * from ##Output
drop table ##Output
March 4, 2022 at 11:52 am
For starters, MAX ([Date_Started]), MAX([Time_Started]) needs to be modified to MAX([Date_Started]) + [Time_Started]). The actual calculation will depend on the data types of the 2 fields.
March 4, 2022 at 12:54 pm
Date - Char(8)
Time - Char(6)
SELECT
COALESCE(ABEE.Operator_Ref, ABG.Operator_Ref, GMBH.Operator_Ref) AS Operator_Ref,
CA1.[DateTime_Started]
FROM (
SELECT Operator_Ref,
MAX(CAST([Date_Started] AS datetime) + CAST([Time_Started] AS datetime)) AS [DateTime_Started]
FROM [TempoLive_ABEE].[dbo].[Operator_Usage]
Where Operator_Ref like 'Lynn%'
Group by Operator_Ref
) AS ABEE
FULL OUTER JOIN (
SELECT Operator_Ref,
MAX(CAST([Date_Started] AS datetime) + CAST([Time_Started] AS datetime)) AS [DateTime_Started]
FROM [TempoLive_ABGlaxo].[dbo].[Operator_Usage]
Where Operator_Ref like 'Lynn%'
Group by Operator_Ref
) AS ABG ON ABG.Operator_Ref = ABEE.Operator_Ref
FULL OUTER JOIN (
SELECT Operator_Ref,
MAX(CAST([Date_Started] AS datetime) + CAST([Time_Started] AS datetime)) AS [DateTime_Started]
FROM [TempoLive_ABFarrGMBH].[dbo].[Operator_Usage]
Where Operator_Ref like 'Lynn%'
Group by Operator_Ref
) AS GMBH ON GMBH.Operator_Ref IN ( ABG.Operator_Ref, ABEE.Operator_Ref )
CROSS APPLY (
SELECT MAX(DateTime_Started) AS DateTime_Started
FROM ( VALUES(ABEE.DateTime_Started, ABG.DateTime_Started, GMBH.DateTime_Started) ) AS dates(DateTime_Started)
) AS CA1
ORDER BY COALESCE(ABEE.Operator_Ref, ABG.Operator_Ref, GMBH.Operator_Ref)
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 11, 2022 at 6:08 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply