July 26, 2012 at 9:49 am
I must be overlooking something, I'm sure. But I have stared at it so long and it is just not jumping out at me. Using the code below, my row_count field is not populating in the pivot. Not sure why. Any help will be greatly appreciated.
Thanks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
BEGIN
DECLARE @BeginDtesmalldatetime = NULL
DECLARE @EndDtesmalldatetime = NULL
DECLARE @PRIORBeginDtesmalldatetime
DECLARE @PRIOREndDtesmalldatetime
IF @EndDte IS NULL
BEGIN
SET @EndDte = CONVERT(date, GETDATE())
END
IF @BeginDte IS NULL
BEGIN
SET @BeginDte = DATEADD(d, -7, @EndDte)
END
SET @PRIOREndDte = DATEADD(d, -7, @EndDte)
SET @PRIORBeginDte = DATEADD(d, -7, @PRIOREndDte)
END
IF OBJECT_ID(N'tempdb.dbo.#TMP') > 0
BEGIN
DROP TABLE #TMP
END
CREATE TABLE #TMP
(
ServerNamenvarchar(100),
DBNamenvarchar(255),
CaseNamenvarchar(255),
ActiveStatusnvarchar(25),
CreatedDatedate,
TableNamenvarchar(255),
Row_Countbigint
)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-19','Document',32946)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-20','File',NULL)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-20','Document',NULL)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-21','Document',32946)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-21','File',75)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-21','AuditRecord',11150001)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-22','Document',32946)
INSERT INTO #TMP (ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count)
VALUES('Server20','DB16','Case11509','Test Cases','2012-07-22','File',NULL)
SELECT * FROM #TMP
SELECT DISTINCT
ServerName,
DBName,
CaseName,
ActiveStatus,
CreatedDate,
ISNULL(AuditRecCount, 0) AS 'AuditRecCount',
ISNULL(DocumentRecCount, 0) AS 'DocumentRecCount',
ISNULL(FileRecCount, 0) AS 'FileRecCount'
FROM (
SELECT ServerName, DBName, CaseName, ActiveStatus, CreatedDate, TableName, Row_Count
FROM #TMP
) A
PIVOT
(MAX(Row_Count)
FOR TableName
IN (AuditRecCount, DocumentRecCount, FileRecCount)
) AS PVT
ORDER BY CreatedDate
July 26, 2012 at 10:21 am
You're looking for tables name AuditRecCount, DocumentRecCount, and FileRecCount, but the tables are named AuditRecord, Document, and File respectively. Since the names don't match, there are no values to populate your counts.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 26, 2012 at 10:41 am
Thank you so much!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply