Creating a Group Total with group totals along side

Question

Creating a Group Total with group totals along side

Weegee2017

SSC Eights!

Points: 994
More actions
June 12, 2019 at 12:36 pm

#3652720

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?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

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;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Weegee2017 SSC Eights! Points: 994 More actions · Answer 2

Brilliant Phil.

That works for me.

Thank you very much