Better way to do running sum with Grand Total??

[sparkbyte]

I modified a view I had built to include a Grand total but would like to know if there is a better way?

With cte_TodaysRecords(EmployeeFullName, BoxNumber, FileNumber, TrackingDate)
	As (
		SELECT     
			EmployeeFullName = Case
					When e.EmployeeMI = '' Then e.EmployeeLN + ', ' + e.EmployeeFN
					When e.EmployeeMI Is Null Then e.EmployeeLN + ', ' + e.EmployeeFN
					When e.EmployeeMI <> '' Then e.EmployeeLN + ', ' + e.EmployeeFN + ' ' + e.EmployeeMI
				End,
			a.BoxNumber As [Tracking Number], 
			a.FileNumber As [File Number], 
			a.TrackingDate As [Tracking Date and Time]
		FROM	dbo.tblTrackingTable a
			INNER JOIN dbo.tblEmployee e ON a.EmployeeID = e.EmployeeID
		WHERE     (a.TrackingDate 
				BETWEEN DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0) 
				AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))) 
				AND (a.EmployeeID IS NOT NULL) 
				AND (a.FileNumber <> '') 
				AND (a.BoxNumber <> '') 
				AND (a.FileNumber <> '.BOX.END.') 
				AND (a.TrackingDate IS NOT NULL)
			),
cte_Totals([Employee Full Name], [Total Tracking Numbers], [Total Folders Scanned])
	As (
		Select 
			Cast(EmployeeFullName As VarChar(35)) As [Employee Full Name], 
			Count(Distinct BoxNumber) As [Total Tracking Numbers],
			Count(FileNumber) As [Total Folders Scanned]
		From cte_TodaysRecords
			Group By EmployeeFullName
		)
	
Select 
		T.[Employee Full Name],
		T.[Total Tracking Numbers], 
		T.[Total Folders Scanned],
			Case When [Employee Full Name] = (Select Top 1 [Employee Full Name] from cte_Totals 
												Order By [Employee Full Name] Desc)
			Then (Select Sum([Total Folders Scanned]) From cte_Totals)
			Else 0
			End As 'Grand Total - Folders'
From cte_Totals T

Thanks

John Fuhrman

 

[markros]

Take a look at CUBE/ROLLUP clauses for GROUP BY (and Grouping sets if you're dealing with SQL 2008) in the GROUP BY clause in BOL

PluralSight Learning Library

 

[sparkbyte]

I had tried doing this with cube but could not seem to get as good of performance as using cte.

How can this be rewritten to use cube?


Thanks

John Fuhrman

 

[markros]

I don't think you need to use CUBE, may be rollup instead.
If you want some further assistance, please post DDL of the table(s) involved, insert statements and desired output.

PluralSight Learning Library

 

[sparkbyte]

OK. Rather than using #temp tables I used TempDB to create everything under for testing. The script will create everything including the indexes and one USP I use to get the user logon ID split apart. (Domain/UserID)

If you are not on a domain you may need to alter that a bit.

The main insert loops and inserts 25000 rows of random false UPS tracking numbers. The other Insert for the Employee table is self explanitory.

This is pretty long.

USE [tempdb]

/*****************************************************************

	Create UDF for seperating the username and domain.

******************************************************************/
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[fn_Split](@String varchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0

BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@String)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@String,@INDEX - 1)
ELSE
SELECT @SLICE = @String
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @String = RIGHT(@String,LEN(@String) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@String) = 0 BREAK
END
Return
END


/*****************************************************************

	Create Primary Transaction table (tblTrackingTable)

******************************************************************/
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTrackingTable](
	[Tracking_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[EmployeeID] [varchar](50) NULL,
	[MachineName] [varchar](20) NULL,
	[BoxNumber] [varchar](45) NOT NULL,
	[FileNumber] [varchar](25) NOT NULL,
	[TrackingDate] [datetime] NULL,
	[Reship] [bit] NULL,
	[BoxNumberOriginal] [varchar](50) NULL,
	[TrackingYear]  AS (datepart(year,[TrackingDate])),
	[TrackingMonth]  AS (datepart(month,[TrackingDate])),
	[TrackingDay]  AS (datepart(day,[TrackingDate])),
	[TrackingNumberPrefix]  AS (
								case when upper(left([BoxNumber],(2)))='1Z' 
									then CONVERT([varchar](2),upper(left([BoxNumber],(2))),0) 
									else CONVERT([varchar](3),left([BoxNumber],patindex('%[^a-z.]%',[BoxNumber]+'1')-(1)),0) 
								end),
	[TrackingNumberAct]  AS (
								case when upper(left([BoxNumber],(2)))='1Z' 
								then upper(substring([BoxNumber],(3),(6)))  
								end),
	[TrackingNumberShipping]  AS (
								case when upper(left([BoxNumber],(2)))='1Z' 
								then CONVERT([int],upper(substring([BoxNumber],(9),(2))),0)  
								end),
	[TrackingNumberParsel]  AS (
								case when upper(left([BoxNumber],(2)))='1Z' 
								then CONVERT([int],upper(substring([BoxNumber],(11),(8))),0)  
								end),
 CONSTRAINT [PK_tblTrackingTable] PRIMARY KEY CLUSTERED 
(
	[Tracking_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

/*****************************************************************

	Create Indexes Primary Transaction table (tblTrackingTable)

******************************************************************/

GO

CREATE NONCLUSTERED INDEX [IDX_Lookup1] ON [dbo].[tblTrackingTable] 
(
	[BoxNumber] ASC
)
INCLUDE ( [FileNumber]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
	SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IDX_Lookup2] ON [dbo].[tblTrackingTable] 
(
	[TrackingDate] ASC
)
INCLUDE ( [BoxNumber],
[FileNumber]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/*****************************************************************

	Create Employee table (tblEmployee)

******************************************************************/

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEmployee](
	[EmployeePK] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[EmployeeID] [nvarchar](15) NOT NULL,
	[EmployeeFN] [nvarchar](50) NULL,
	[EmployeeMI] [nvarchar](1) NULL,
	[EmployeeLN] [nvarchar](50) NULL,
	[EmployeeDept] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
	[EmployeePK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [CK_tblEmployeeID] UNIQUE NONCLUSTERED 
(
	[EmployeePK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

/*****************************************************************

	Create Indexes for the Employee table (tblEmployee)

******************************************************************/
GO

CREATE NONCLUSTERED INDEX [idx_Main_EmployeeLoopup] ON [dbo].[tblEmployee] 
(
	[EmployeeID] ASC,
	[EmployeeFN] ASC,
	[EmployeeMI] ASC,
	[EmployeeLN] ASC,
	[EmployeeDept] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [idx_SurnameLookups] ON [dbo].[tblEmployee] 
(
	[EmployeeLN] ASC,
	[EmployeeFN] ASC,
	[EmployeeMI] ASC,
	[EmployeeID] ASC,
	[EmployeeDept] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--------------------------------------------------------------
--
--	Pupulate the Tracking Table with random data
--		Will Insert 25000 Rows
--
--------------------------------------------------------------
Go
Use tempdb
Go

DECLARE @Intveral INT
SET @Intveral = 25000
WHILE (@Intveral > 0)
BEGIN

	DECLARE @EmployeeID VarChar(10) -- Users Network Logon ID to Domain
		Set @EmployeeID = (Select cast(Items As VarChar(10)) As LoginID from fn_Split(SUSER_SNAME(),'\') Where ID = 2)
    
	DECLARE @MachineName  VarChar(15) -- Workstation Network ID
		Set @MachineName = Host_Name()

    DECLARE @LogonName varchar(10) -- Logged on user interting the new record
		Set @LogonName = SUSER_SNAME()
	
	DECLARE @BoxNumber Varchar(25)
		Set	@BoxNumber = '1Z1A123B' + '03' + RIGHT(REPLICATE('0',8) + CONVERT(VARCHAR(100),CAST(RAND() * 100000000 AS INT)),8) 

	DECLARE @FileNumber Varchar(10)
		Set	@FileNumber = 'A' + RIGHT(REPLICATE('0',8) + CONVERT(VARCHAR(100),CAST(RAND() * 100000000 AS INT)),8)

INSERT INTO [dbo].[tblTrackingTable]
           ([EmployeeID]
           ,[MachineName]
           ,[BoxNumber]
           ,[FileNumber]
           ,[TrackingDate]
           ,[Reship]
           ,[BoxNumberOriginal])
     VALUES
           (@EmployeeID,
           @MachineName,
           @BoxNumber,
           @FileNumber,
           GETDATE(),
           '',
           '')
SET @Intveral = @Intveral - 1

End
GO


--------------------------------------------------------------
--
--	Pupulate the Employee Table with Generic data
--
--------------------------------------------------------------
DECLARE @EmployeeID VarChar(10) -- Users Network Logon ID to Domain
	Set @EmployeeID = (
		Select cast(Items As VarChar(10)) As LoginID 
				from fn_Split(SUSER_SNAME(),'\') Where ID = 2)

INSERT INTO [dbo].[tblEmployee]
           ([EmployeeID],[EmployeeFN],[EmployeeMI]
           ,[EmployeeLN],[EmployeeDept])
     VALUES
           (@EmployeeID,'John','F','Fuhrman','IT')

INSERT INTO [dbo].[tblEmployee]
           ([EmployeeID],[EmployeeFN],[EmployeeMI]
           ,[EmployeeLN],[EmployeeDept])
     VALUES
           ('jlsmith','Jane','L','Smith','QC')
INSERT INTO [dbo].[tblEmployee]
           ([EmployeeID],[EmployeeFN],[EmployeeMI]
           ,[EmployeeLN],[EmployeeDept])
     VALUES
           ('JFDoe','John','F','Doe','IT')
INSERT INTO [dbo].[tblEmployee]
           ([EmployeeID],[EmployeeFN],[EmployeeMI]
           ,[EmployeeLN],[EmployeeDept])
     VALUES
           ('RTMartin','Ricky','T','Martin','Mailroom')
INSERT INTO [dbo].[tblEmployee]
           ([EmployeeID],[EmployeeFN],[EmployeeMI]
           ,[EmployeeLN],[EmployeeDept])
     VALUES
           ('HKRogers','Heidi','K','Rogers','QC')
INSERT INTO [dbo].[tblEmployee]
           ([EmployeeID],[EmployeeFN],[EmployeeMI]
           ,[EmployeeLN],[EmployeeDept])
     VALUES
           ('TLManning','Todd','L','Manning','Mailroom')


--------------------------------------------------------------
--
--	Get rid of everything
--
--------------------------------------------------------------

--USE tempdb
--Go
--Drop Table dbo.tblEmployee
--Drop Table dbo.tblTrackingTable
--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Split]') 
--	AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
--DROP FUNCTION [dbo].[fn_Split]
--

Current script per above

USE tempdb
GO

;With cte_TodaysRecords(EmployeeFullName, BoxNumber, FileNumber, TrackingDate)
	As (

		SELECT     
			EmployeeFullName = Case
					When e.EmployeeMI = '' Then e.EmployeeLN + ', ' + e.EmployeeFN
					When e.EmployeeMI Is Null Then e.EmployeeLN + ', ' + e.EmployeeFN
					When e.EmployeeMI <> '' Then e.EmployeeLN + ', ' + e.EmployeeFN + ' ' + e.EmployeeMI
				End,
			a.BoxNumber As [Tracking Number], 
			a.FileNumber As [File Number], 
			a.TrackingDate As [Tracking Date and Time]
		FROM	tblTrackingTable a
			INNER JOIN tblEmployee e ON a.EmployeeID = e.EmployeeID
		

		WHERE     (a.TrackingDate 
				BETWEEN DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0) 
				AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))) 
				AND (a.EmployeeID IS NOT NULL) 
				AND (a.FileNumber <> '') 
				AND (a.BoxNumber <> '') 
				AND (a.FileNumber <> '.BOX.END.') 
				AND (a.TrackingDate IS NOT NULL)
			),
cte_Totals([Employee Full Name], [Total Tracking Numbers], [Total Folders Scanned])
	As (
		Select 
			Cast(EmployeeFullName As VarChar(35)) As [Employee Full Name], 
			Count(Distinct BoxNumber) As [Total Tracking Numbers],
			Count(FileNumber) As [Total Folders Scanned]
		From cte_TodaysRecords
			Group By EmployeeFullName
		)
	
Select 
		T.[Employee Full Name],
		T.[Total Tracking Numbers], 
		T.[Total Folders Scanned],
			Case When [Employee Full Name] = (Select Top 1 [Employee Full Name] from cte_Totals 
												Order By [Employee Full Name] Desc)
			Then (Select Sum([Total Folders Scanned]) From cte_Totals)
			Else 0
			End As 'Grand Total - Folders'
From cte_Totals T

Expected output should be something like this.
[tt]
Employee Full Name Total Tracking Numbers Total Folders Scanned Grand Total - Folders
----------------------------------- ---------------------- --------------------- ---------------------
Fuhrman, John F 24995 25000 25000
[/tt]



Thanks

John Fuhrman