Creating a Group Total with group totals along side

  • Weegee2017

    SSC Eights!

    Points: 933

    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?

     

     

  • Phil Parkin

    SSC Guru

    Points: 243596

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Weegee2017

    SSC Eights!

    Points: 933

    Brilliant Phil.

    That works for me.

    Thank you very much

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

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