June 25, 2014 at 2:52 am
Hi All
I have a report that needs to return a count of zero for the rows that have no data, I have tried to use the Left Outer Join but my where clause is excluding the rows with no data and I need to filter the report with the Year, day and Month.
I'm not sure what I'm missing please see script below, any help would be appreciated.
The date filters are from different table(dimDate), not sure how to include them in the #tmpOperationalTypes join as filters
ALTER PROCEDURE [dbo].[spcAdvancedComparisonDateDWReport]
@Year varchar(4000) = '',
@Day varchar(28) = '',
@Month varchar(28) = '',
@Locations varchar(4000) = '',
@OperationalTypes varchar(4000) = '',
@IncludeInactiveLocations bit = 0,
@IncludeInactiveOperationalTypes bit = 0,
@userid uniqueidentifier = null
AS
set nocount on
BEGIN
--Operational Types
--return all rows from this table, count of zero if there's no data
CREATE TABLE #tmpOperationalTypes (OperationalTypeAlternateKey uniqueidentifier, OperationalType nvarchar(400), OperationalTypeKey int)
INSERT INTO #tmpOperationalTypes(OperationalTypeAlternateKey, OperationalType, OperationalTypeKey)
SELECT ot.OperationalTypeAlternateKey, ot.OperationalType, OperationalTypeKey
FROM dimOperationalType ot
IF (@OperationalTypes <> '')
Delete
FROM #tmpOperationalTypes
WHERE OperationalTypeAlternateKey NOT IN (SELECT Value FROM dbo.fnSplitString(@OperationalTypes, ','))
--Locations
Create Table #tmpLocations(LocationAlternateKey uniqueidentifier, LocationFullAlias Varchar(400), LocationKey int )
INSERT INTO #tmpLocations(LocationAlternateKey, LocationFullAlias, LocationKey )
SELECT LocationAlternateKey, LocationFullAlias, LocationKey
FROM dimLocation
If (@Locations <> '')
Delete
From #tmpLocations
WHERE LocationAlternateKey NOT IN (SELECT Value FROM dbo.fnSplitString(@Locations, ','))
--Inactive Locations
If @IncludeInactiveLocations = 0
BEGIN
delete
From #tmpLocations
Where LocationAlternateKey IN (Select LocationAlternateKey From dimLocation Where IsActive = 0 )
END
--Inactive Operational Types
If @IncludeInactiveOperationalTypes = 0
BEGIN
delete
From #tmpOperationalTypes
Where OperationalTypeAlternateKey IN (Select OperationalTypeAlternateKey From dimOperationalType Where IsActive = 0 )
END
SELECT
ot.OperationalTypeAlternateKeyAS [OperationalTypeAlternateKey]
,ot.OperationalTypeAS [Entry Type]
,Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear) AS [Date]
,COUNT(*)AS [Total]
FROM
fctOperationalEntry f with (nolock)
INNER JOIN dimDate d with (nolock) ON f.ReportedDateKey = d.DateKey
INNER JOIN #tmpLocations tl with (nolock) ON f.LocationKey = tl.LocationKey
LEFT OUTER JOIN #tmpOperationalTypes ot with (nolock) ON f.OperationalTypeKey = ot.OperationalTypeKey --return all rows from this table, count of zero if there's no data
WHERE d.DateYear IN (SELECT Value FROM dbo.fnSplitString(@Year, ','))
AND d.DateDayOfMonth = @Day
AND d.DateMonthName = @Month
GROUP BY ot.OperationalTypeAlternateKey, ot.OperationalType, Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear)
ORDER BY [Entry Type]
DROP TABLE #tmpOperationalTypes
DROP TABLE #tmpLocations
END
Thanks
June 25, 2014 at 3:22 am
What do you mean by "rows with no data"? Please will you provide some examples?
John
June 25, 2014 at 3:37 am
Here's the required output, at the moment the rows with 0 counts are not returned:
For example If I'm running the report for the dates below, I would like to see everything in the temp table even if there's no count for it
Exec [spcAdvancedComparisonDateDWReport] @Year = '2011,2012,2013,2014', @Day = '18', @Month= 'April'
OperationalTypeAlternateKey Entry Type Date Total
9FC5554E-64C5-E311-A6AC-6C626D750814Assault / Fighting18-Apr-14 1
AFC5554E-64C5-E311-A6AC-6C626D750814Enquiry 18-Apr-14 1
68C5554E-64C5-E311-A6AC-6C626D750814Fire Alarm 18-Apr-14 0
A1C5554E-64C5-E311-A6AC-6C626D750814Inspections 18-Apr-14 3
B1C5554E-64C5-E311-A6AC-6C626D750814Theft - Snatch & Grab 18-Apr-14 1
61C5554E-64C5-E311-A6AC-6C626D750814Bank Escorts Code 13 18-Apr-14 0
A2C5554E-64C5-E311-A6AC-6C626D750814AWOL 18-Oct-13 0
June 25, 2014 at 4:06 am
If you want to see everything from #tmpOperationalTypes, you need to put it on the left hand side of your LEFT JOIN (or change the LEFT JOIN to a RIGHT JOIN). It's hard to know whether that's what you're after, though, without seeing any sample data.
John
June 25, 2014 at 4:38 am
Quick question, why the nolock hint?
😎
June 25, 2014 at 5:14 am
The script was inherited, I'm not sure why the no lock hint was used especially on temp tables and the others is data warehouse tables that are used by other reports, so I guess it was used to avoid any locking while the other reports are running 🙂
June 25, 2014 at 5:38 am
Your query has four output columns:
SELECT
ot.OperationalTypeAlternateKey AS [OperationalTypeAlternateKey]
,ot.OperationalType AS [Entry Type]
,Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear) AS [Date]
,COUNT(*) AS [Total]
FROM
If you're returning a single row with a count of 0 when there are no matching rows for the given set of parameters, what would you like to see returned in the first two columns of the output?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2014 at 6:18 am
I would like to see the OperationalTypeKey and the OperationalTypeName for those columns with no counts, the dates can be defaulted to the filter date, just to show that there was no data for those rows in those dates.
June 25, 2014 at 6:34 am
ChrisM@Work (6/25/2014)
Your query has four output columns:SELECT
ot.OperationalTypeAlternateKey AS [OperationalTypeAlternateKey]
,ot.OperationalType AS [Entry Type]
,Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear) AS [Date]
,COUNT(*) AS [Total]
FROM
If you're returning a single row with a count of 0 when there are no matching rows for the given set of parameters, what would you like to see returned in the first two columns of the output?
+1,
your base table should be #tmpOperationalTypes as per the 1st two columns are suggesting in SELECT clause. Date dimension is Inner join with fctOperationalEntry table and following is your where clause.
WHERE d.DateYear IN (SELECT Value FROM dbo.fnSplitString(@Year, ','))
AND d.DateDayOfMonth = @Day
AND d.DateMonthName = @Month
this will eliminate any possible combination.
June 25, 2014 at 6:45 am
The Select below returns the same results as the one I first posted. Just rows with values for the filtered dates
SELECT
ot.OperationalTypeAlternateKeyAS [OperationalTypeAlternateKey]
,ot.OperationalTypeAS [Entry Type]
,Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear) AS [Date]
,COUNT(*)AS [Total]
FROM
#tmpOperationalTypes ot
LEFT OUTER JOIN fctOperationalEntry f ON ot.OperationalTypeKey = f.OperationalTypeKey
INNER JOIN dimDate d with (nolock) ON f.ReportedDateKey = d.DateKey
INNER JOIN #tmpLocations tl ON f.LocationKey = tl.LocationKey
WHERE d.DateYear IN (SELECT Value FROM dbo.fnSplitString(@Year, ','))
AND d.DateDayOfMonth = @Day
AND d.DateMonthName = @Month
GROUP BY ot.OperationalTypeAlternateKey, ot.OperationalType, Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear)
ORDER BY [Entry Type]
June 25, 2014 at 8:01 am
i have adjusted the query
Note: i have commented the Order By Clause, because i do not know "[Entry Type]" from which table. To test the query result and performance of the query.
SELECT ot.OperationalTypeAlternateKey AS [OperationalTypeAlternateKey]
, ot.OperationalType AS [Entry Type]
, Convert(Varchar, DateDayOfMonth)+' '+DateMonthName + ' '+ Convert(Varchar,DateYear) AS [Date]
, ISNULL(A.[Total],0) AS [Total]
FROM #tmpOperationalTypes ot
Cross join dimDate d with (nolock)
Left join (select f.OperationalTypeKey, f.ReportedDateKey, count(*) AS [Total]
from fctOperationalEntry f
INNER JOIN #tmpLocations tl ON f.LocationKey = tl.LocationKey
Group by f.OperationalTypeKey, f.ReportedDateKey
) A on A.OperationalTypeKey = ot.OperationalTypeKey
AND A.ReportedDateKey = d.DateKey
WHERE d.DateYear IN (SELECT Value FROM dbo.fnSplitString(@Year, ','))
AND d.DateDayOfMonth = @Day
AND d.DateMonthName = @Month
--ORDER BY [Entry Type]
Hope it helps.
June 25, 2014 at 8:18 am
This works perfectly, I can't thank you enough 🙂 🙂
The Order by clause if from the Outer query, it's working fine when included as well.
June 25, 2014 at 8:58 am
Glad to help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply