I'm unable to create a Summary dataset with stats. PLease help me.
Parameter 1: @Division
Parameter 2: @Datasetname
Parameter 3: @StartTime
Parameter 4: @EndTime
Dataset 1:
Select * from Monitoring..VelocityDatasetCategory
Dataset 2:
Select Distinct DatasetName,DatasetID,divisionID from Monitoring..VelocityDataset nolock order by DatasetName
Dataset 3:
SELECT A.DatasetID, A.DatasetName, A.BatchNumber, A.BatchStartTime, A.BatchEndTime, A.Runtime, A.DeltaCount, A.DeltaSizeInMB,A.StatusID FROM Monitoring..VelocityDatasetSLASummary AS A INNER JOIN Monitoring..VelocityDataset AS B ON A.DatasetName = B.DatasetALias WHERE (A.BatchStartTime >= @BatchStartTime) AND (A.BatchEndTime <= CONVERT(date, DATEADD(dd, 1, @BatchEndTime), 101)) ORDER BY A.BatchNumber DESC
Dataset 4: This is where I'm having issue.
IF OBJECT_ID('TEMPDB..#SummaryTable')IS NOT NULL DROP TABLE #SummaryTable CREATE TABLE #SummaryTable (DatasetName sysname, DatasetID INT,BatchStartTime datetime,BatchEndTime datetime,StatusID INT,IsScheduled INT) Insert INTO #SummaryTable Select A.DatasetName,A.DatasetID,A.BatchStartTime,A.BatchEndTime,A.StatusID,B.IsScheduled FROM Monitoring..VelocityDatasetSLASummary A (NOLOCK) INNER JOIN Monitoring..VelocityDataset B ON A.DatasetName = B.DatasetALias WHERE (A.BatchStartTime >= @BatchStartTime) AND (A.BatchEndTime <= CONVERT(date, DATEADD(dd, 1, @BatchEndTime), 101)) --and B.DatasetID = 21 IF (Select top 1 IsScheduled FROM #SummaryTable) = 0 BEGIN IF OBJECT_ID('TEMPDB..#Scheduled')IS NOT NULL DROP TABLE #Scheduled CREATE TABLE #Scheduled ([Status] Varchar(20),[BatchCount] Float,DatasetID INT) Insert INTO #Scheduled SELECT 'Total Count' as [Status], COUNT(1), (Select top 1 DatasetID from #SummaryTable) from #SummaryTable UNION ALL Select 'Success Count' as [Status],COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable where statusID = 1 Union All Select 'Failure Count' as [Status],COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable where statusID = 2 Union All Select 'Late Start' as [Status], COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable where statusID = 4 Union ALL Select 'OUT OF CYCLE (N/A)' as [Status],0,(Select top 1 DatasetID from #SummaryTable) Union ALL SELECT 'Total Expected' as [Status], COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable Union all Select 'NO SHOW (N/A)' as [Status],0,(Select top 1 DatasetID from #SummaryTable) Select * from #Scheduled Union All Select 'Success Percentage' as [Status], 100 - (Cast((((Select [BatchCount] from #Scheduled where Status = 'Failure Count') + (Select [BatchCount] from #Scheduled where Status = 'Late Start'))/(Select [BatchCount] from #Scheduled where Status = 'Total Count')) as decimal(10,2))* 100 ),(Select top 1 DatasetID from #SummaryTable) Union all Select 'Overall Success Percentage' as [Status], 100 - (Cast((((Select [BatchCount] from #Scheduled where Status = 'Failure Count') + (Select [BatchCount] from #Scheduled where Status = 'Late Start'))/(Select [BatchCount] from #Scheduled where Status = 'Total Expected')) as decimal(10,2))* 100 ),(Select top 1 DatasetID from #SummaryTable) END ELSE BEGIN PRINT N'Once above Non- schdeduled Query is sucessful, I shall start building for Scheduled datasets.' END
End result of Dataset 4 should look like this
Status BatchCount DatasetID
Total Count 34 21
Success Count 21 21
Failure Count 12 21
Late Start 1 21
OUT OF CYCLE (N/A) 0 21
Total Expected 34 21
NO SHOW (N/A) 0 21
Success Percentage 62 21
Overall Success Percentage 62 21
- Moved by Allen Li - MSFTMicrosoft contingent staff 8 hours 4 minutes ago SSRS
- Edited by Vinnydrums 2 hours 39 minutes ago