June 12, 2019 at 12:36 pm
Hi there
I have a set of three tables that i wish to report group totals on
However I am having trouble aggregating data into 1 summary line
I have the followng t-sql
If OBJECT_ID(N'tempdb..#RunControlClient', N'U') IS NOT NULL DROP TABLE #RunControlClient
If OBJECT_ID(N'tempdb..#SQLServerDatabase', N'U') IS NOT NULL DROP TABLE #SQLServerDatabase
CREATE TABLE [#Run](
[RunID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseID] [int] NOT NULL,
[RunDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_RunRun] PRIMARY KEY CLUSTERED
(
[RunID] 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
CREATE TABLE [#RunControlClient](
[RunID] [int] NOT NULL,
[SourceProviderID] [smallint] NOT NULL,
[LoadTypeID] [smallint] NOT NULL,
[ClientURN] [int] NOT NULL,
[ClientShortName] [nvarchar](255) NOT NULL,
[ClientFullName] [nvarchar](255) NOT NULL,
[TakeOnEffectiveDate] [datetime] NOT NULL,
CONSTRAINT [PK_RunControlClient] PRIMARY KEY CLUSTERED
(
[RunID] ASC,
[SourceProviderID] 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
CREATE TABLE [#SQLServerDatabase](
[DatabaseID] [int] NOT NULL,
[ServerID] [int] NOT NULL,
[DatabaseName] [nvarchar](255) NOT NULL,
[DatabaseUse] [nvarchar](255) NOT NULL,
[Username] [nvarchar](100) NULL,
[Production] [bit] NOT NULL,
[LinkedDatabaseID] [int] NULL,
CONSTRAINT [PK_SQLServerDatabase] PRIMARY KEY CLUSTERED
(
[DatabaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [#Run] ON
GO
INSERT [#Run] ([RunID], [DatabaseID], [RunDateTime]) VALUES (461, 8, CAST(N'2018-10-25T10:39:35.040' AS DateTime))
GO
INSERT [#Run] ([RunID], [DatabaseID], [RunDateTime]) VALUES (462, 8, CAST(N'2018-10-25T13:27:26.470' AS DateTime))
GO
INSERT [#Run] ([RunID], [DatabaseID], [RunDateTime]) VALUES (463, 8, CAST(N'2018-10-26T14:53:34.187' AS DateTime))
GO
INSERT [#Run] ([RunID], [DatabaseID], [RunDateTime]) VALUES (464, 8, CAST(N'2018-10-30T09:44:12.203' AS DateTime))
GO
INSERT [#Run] ([RunID], [DatabaseID], [RunDateTime]) VALUES (465,11, CAST(N'2018-10-30T14:27:27.813' AS DateTime))
GO
INSERT [#Run] ([RunID], [DatabaseID], [RunDateTime]) VALUES (466, 11, CAST(N'2018-12-12T10:44:40.670' AS DateTime))
GO
SET IDENTITY_INSERT [#Run] OFF
GO
INSERT [#RunControlClient] ([RunID], [SourceProviderID], [LoadTypeID], [ClientURN], [ClientShortName], [ClientFullName], [TakeOnEffectiveDate]) VALUES (461, 3, 1, 5936, N'Aston Martin Lagonda', N'Aston Martin Lagonda Global Holdings plc', CAST(N'2018-10-03T00:00:00.000' AS DateTime))
GO
INSERT [#RunControlClient] ([RunID], [SourceProviderID], [LoadTypeID], [ClientURN], [ClientShortName], [ClientFullName], [TakeOnEffectiveDate]) VALUES (462, 3, 1, 5936, N'Aston Martin Lagonda', N'Aston Martin Lagonda Global Holdings plc', CAST(N'2018-10-03T00:00:00.000' AS DateTime))
GO
INSERT [#RunControlClient] ([RunID], [SourceProviderID], [LoadTypeID], [ClientURN], [ClientShortName], [ClientFullName], [TakeOnEffectiveDate]) VALUES (463, 3, 1, 5936, N'Aston Martin Lagonda', N'Aston Martin Lagonda Global Holdings plc', CAST(N'2018-10-03T00:00:00.000' AS DateTime))
GO
INSERT [#RunControlClient] ([RunID], [SourceProviderID], [LoadTypeID], [ClientURN], [ClientShortName], [ClientFullName], [TakeOnEffectiveDate]) VALUES (464, 3, 1, 5936, N'Aston Martin Lagonda', N'Aston Martin Lagonda Global Holdings plc', CAST(N'2018-10-03T00:00:00.000' AS DateTime))
GO
INSERT [#RunControlClient] ([RunID], [SourceProviderID], [LoadTypeID], [ClientURN], [ClientShortName], [ClientFullName], [TakeOnEffectiveDate]) VALUES (465, 3, 1, 5936, N'Aston Martin Lagonda', N'Aston Martin Lagonda Global Holdings plc', CAST(N'2018-10-03T00:00:00.000' AS DateTime))
GO
INSERT [#RunControlClient] ([RunID], [SourceProviderID], [LoadTypeID], [ClientURN], [ClientShortName], [ClientFullName], [TakeOnEffectiveDate]) VALUES (466, 3, 1, 5936, N'Aston Martin Lagonda', N'Aston Martin Lagonda Global Holdings plc', CAST(N'2018-10-03T00:00:00.000' AS DateTime))
GO
INSERT [#SQLServerDatabase] ([DatabaseID], [ServerID], [DatabaseName], [DatabaseUse], [Username], [Production], [LinkedDatabaseID]) VALUES (1, 1, N'RTO_DEV_A01', N'RTO', N'GraysoD', 0, 19)
GO
INSERT [#SQLServerDatabase] ([DatabaseID], [ServerID], [DatabaseName], [DatabaseUse], [Username], [Production], [LinkedDatabaseID]) VALUES (5, 1, N'RTO_DEV_A02', N'RTO', NULL, 0, 19)
GO
INSERT [#SQLServerDatabase] ([DatabaseID], [ServerID], [DatabaseName], [DatabaseUse], [Username], [Production], [LinkedDatabaseID]) VALUES (8, 1, N'RTO_DEV_A03', N'RTO', N'UK1\TindalS', 0, 20)
GO
INSERT [#SQLServerDatabase] ([DatabaseID], [ServerID], [DatabaseName], [DatabaseUse], [Username], [Production], [LinkedDatabaseID]) VALUES (11, 1, N'RTO_PRD_A01', N'RTO', N'UK1\hedgesm', 1, 19)
GO
I wrote this query
SELECT rcc.ClientShortName + ' (' + CAST(rcc.ClientURN AS VARCHAR(20)) + ')' AS 'ClientName'
,rcc.ClientURN
,COUNT(1) 'TotalRuns'
,count(substring( d.DatabaseName,5,3))
,substring( d.DatabaseName,5,3)
FROM #RunControlClient rcc
join #run r on rcc.RunID = r.RunID
join #SQLServerDatabase d on r.DatabaseID = d.DatabaseID
where rcc.ClientURN = 5936
GROUP BY rcc.ClientShortName, rcc.ClientURN
,DatabaseName
ORDER BY rcc.ClientShortName ASC
But want to summarise as follows
ClientName ClientURN TotalRuns
Aston Martin Lagonda (5936) 5936 6
Instead
ClientName ClientURN TotalRuns Dev PRD
Aston Martin Lagonda (5936) 5936 6 4 2
How can I do this please?
June 12, 2019 at 1:55 pm
Try this:
DROP TABLE IF EXISTS #Run;
DROP TABLE IF EXISTS #RunControlClient;
DROP TABLE IF EXISTS #SQLServerDatabase;
CREATE TABLE #Run
(
RunID INT NOT NULL PRIMARY KEY CLUSTERED
,DatabaseID INT NOT NULL
,RunDateTime DATETIME NOT NULL
);
CREATE TABLE #RunControlClient
(
RunID INT NOT NULL
,SourceProviderID SMALLINT NOT NULL
,LoadTypeID SMALLINT NOT NULL
,ClientURN INT NOT NULL
,ClientShortName NVARCHAR(255) NOT NULL
,ClientFullName NVARCHAR(255) NOT NULL
,TakeOnEffectiveDate DATETIME NOT NULL
,
PRIMARY KEY CLUSTERED (
RunID ASC
,SourceProviderID ASC
)
);
CREATE TABLE #SQLServerDatabase
(
DatabaseID INT NOT NULL PRIMARY KEY CLUSTERED
,ServerID INT NOT NULL
,DatabaseName NVARCHAR(255) NOT NULL
,DatabaseUse NVARCHAR(255) NOT NULL
,Username NVARCHAR(100) NULL
,Production BIT NOT NULL
,LinkedDatabaseID INT NULL
);
INSERT #Run
(
RunID
,DatabaseID
,RunDateTime
)
VALUES
(461, 8, CAST(N'2018-10-25T10:39:35.040' AS DATETIME))
,(462, 8, CAST(N'2018-10-25T13:27:26.470' AS DATETIME))
,(463, 8, CAST(N'2018-10-26T14:53:34.187' AS DATETIME))
,(464, 8, CAST(N'2018-10-30T09:44:12.203' AS DATETIME))
,(465, 11, CAST(N'2018-10-30T14:27:27.813' AS DATETIME))
,(466, 11, CAST(N'2018-12-12T10:44:40.670' AS DATETIME));
INSERT #RunControlClient
(
RunID
,SourceProviderID
,LoadTypeID
,ClientURN
,ClientShortName
,ClientFullName
,TakeOnEffectiveDate
)
VALUES
(461, 3, 1, 5936, N'Aston Martin Landa', N'Aston Martin Landa Global Holdings plc'
,CAST(N'2018-10-03T00:00:00.000' AS DATETIME))
,(462, 3, 1, 5936, N'Aston Martin Landa', N'Aston Martin Landa Global Holdings plc'
,CAST(N'2018-10-03T00:00:00.000' AS DATETIME))
,(463, 3, 1, 5936, N'Aston Martin Landa', N'Aston Martin Landa Global Holdings plc'
,CAST(N'2018-10-03T00:00:00.000' AS DATETIME))
,(464, 3, 1, 5936, N'Aston Martin Landa', N'Aston Martin Landa Global Holdings plc'
,CAST(N'2018-10-03T00:00:00.000' AS DATETIME))
,(465, 3, 1, 5936, N'Aston Martin Landa', N'Aston Martin Landa Global Holdings plc'
,CAST(N'2018-10-03T00:00:00.000' AS DATETIME))
,(466, 3, 1, 5936, N'Aston Martin Landa', N'Aston Martin Landa Global Holdings plc'
,CAST(N'2018-10-03T00:00:00.000' AS DATETIME));
INSERT #SQLServerDatabase
(
DatabaseID
,ServerID
,DatabaseName
,DatabaseUse
,Username
,Production
,LinkedDatabaseID
)
VALUES
(1, 1, N'RTO_DEV_A01', N'RTO', N'GraysoD', 0, 19)
,(5, 1, N'RTO_DEV_A02', N'RTO', NULL, 0, 19)
,(8, 1, N'RTO_DEV_A03', N'RTO', N'UK1\TindalS', 0, 20)
,(11, 1, N'RTO_PRD_A01', N'RTO', N'UK1\hedgesm', 1, 19);
SELECT rcc.ClientShortName
,TotalRuns = COUNT(1)
,Dev = COUNT(IIF(ssd.Production = 0, 1, NULL))
,Prod = COUNT(IIF(ssd.Production = 1, 1, NULL))
FROM #RunControlClient rcc
JOIN #Run r
ON r.RunID = rcc.RunID
JOIN #SQLServerDatabase ssd
ON ssd.DatabaseID = r.DatabaseID
GROUP BY rcc.ClientShortName;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 12, 2019 at 2:06 pm
Brilliant Phil.
That works for me.
Thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply