Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query for performance report(Counting records) 1

Status
Not open for further replies.
sparkbyte

sparkbyte

Technical User
Joined
Sep 20, 2002
Messages
879
Location
US
I have a query that gets me the number of total folders scanned for the current day and I need to add a column that also totals the number of tracking numbers for each person.

Here is the current query
Code: 
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_Rollup(EmployeeFullName, [Total Folders Scanned])
	As (
Select 
	EmployeeFullName, 
	Count(FileNumber) As [Total Folders Scanned]
 From cte_TodaysRecords
Group By EmployeeFullName
with Rollup)
Select * From cte_Rollup Where EmployeeFullName Is Not Null

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 79 ms, elapsed time = 91 ms.

SQL Server Execution Times:
CPU time = 296 ms, elapsed time = 285 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

6,798,298 Total Rows in Table dbo.tblTrackingTable
Click to expand...

Getting great performance from this query thanks to gmmastros taking the time to explain indexing in an understandable manner.

THANKS George!!!!


Thanks

John Fuhrman
 
Sort by date Sort by votes
Come on not even a hint on how to do this??

Thanks

John Fuhrman
 
Upvote 0 Downvote
Often times, when you don't get an answer to a question (within a reasonable time), it's because the question is a bit confusing.

Can you post sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
OK, Here are Temp Tables that match my real tables and bogus sample data.

Code: 
CREATE TABLE #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])),
 CONSTRAINT [PK_tblTrackingTable] PRIMARY KEY CLUSTERED ([Tracking_ID] ASC))
 
 GO
 
 CREATE TABLE #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))

GO

INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0161697594','T69856985','2011-02-17 11:01:15','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0161697594','A12345678','2011-02-17 11:00:36','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0161697594','A11111111','2011-02-17 11:00:35','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0161697594','A22222222','2011-02-17 11:00:34','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0163757382','T11111111','2011-02-17 10:59:22','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0163757382','A32323232','2011-02-17 10:59:13','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0163757382','A98989898','2011-02-17 10:59:10','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('ktgoodye','DCNRCCIS038035','1Z1A123R0369411918','T25252525','2011-02-17 10:58:05','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('ktgoodye','DCNRCCIS038035','1Z1A123R0369411918','A58585858','2011-02-17 10:57:45','','')
INSERT INTO #tblTrackingTable
           ([EmployeeID],[MachineName],[BoxNumber],[FileNumber],[TrackingDate],[Reship],[BoxNumberOriginal])
     VALUES('dljackso','DCNRCCIS038029','1Z1A123R0162566974','T56985698','2011-02-17 10:57:36','','')

GO

INSERT INTO #tblEmployee
           ([EmployeeID],[EmployeeFN],[EmployeeMI],[EmployeeLN],[EmployeeDept])
     VALUES ('dljackso','David','L','Jackson','Mailroom')
INSERT INTO #tblEmployee
           ([EmployeeID],[EmployeeFN],[EmployeeMI],[EmployeeLN],[EmployeeDept])
     VALUES ('ktgoodye','Kyle','T','Goodyear','Processing')

GO

The Output from the above query is like this.
[tt]
EmployeeFullName Total Folders Scanned
------------------- ---------------------
Goodyear, Kyle T 2
Jackson, David L 8
[/tt]

What I would like it to be.
[tt]
EmployeeFullName Total Folders Scanned Total Box Numbers
------------------- --------------------- ----------------------
Goodyear, Kyle T 2 1
Jackson, David L 8 3
[/tt]

Thanks

John Fuhrman
 
Upvote 0 Downvote
try this:

Code: 
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_Rollup(EmployeeFullName, [Total Folders Scanned][!],[Total Box Numbers][/!])
    As (
Select 
    EmployeeFullName, 
    Count(FileNumber) As [Total Folders Scanned][!],
    Count(Distinct BoxNumber) As [Total Box Numbers][/!]
 From cte_TodaysRecords
Group By EmployeeFullName
[!]--with Rollup
)[/!]
Select * From cte_Rollup Where EmployeeFullName Is Not Null

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
That did it.

I had tried the [red]Count(Distinct BoxNumber) As [Total Box Numbers][/red] but for some reason could not get it to work.

Guess, I must have been looking at it too long.......


The new indexes and the rewrite of the script has made major improvements. 15 to 20 seconds down to this.

[tt]
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 9884, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblEmployee'. Scan count 4751, logical reads 9502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblTrackingTable'. Scan count 1, logical reads 14292, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 372 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
[/tt]

Now that [red]ROCKS!![/red]

Thanks

John Fuhrman
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
904
Chriss Miller
C
gbaughma
  • Locked
  • Question Question
SQL Performance
Replies
11
Views
1K
pjw001
pjw001
peac3
  • Locked
  • Question Question
Avoid double counting
Replies
16
Views
2K
peac3
peac3
WomanPro
  • Locked
  • Question Question
Insert into query adds records to unreadable format, how do I change that?
Replies
5
Views
813
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question Question
Error in Select query
Replies
14
Views
1K
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top