Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How To Return Zero If No Rows Found


How To Return Zero If No Rows Found

Author
Message
Teee-SQL
Teee-SQL
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 229
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.OperationalTypeAlternateKey AS [OperationalTypeAlternateKey]
,ot.OperationalType AS [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
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7446 Visits: 15134
What do you mean by "rows with no data"? Please will you provide some examples?

John
Teee-SQL
Teee-SQL
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 229
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-6C626D750814 Assault / Fighting 18-Apr-14 1
AFC5554E-64C5-E311-A6AC-6C626D750814 Enquiry 18-Apr-14 1
68C5554E-64C5-E311-A6AC-6C626D750814 Fire Alarm 18-Apr-14 0
A1C5554E-64C5-E311-A6AC-6C626D750814 Inspections 18-Apr-14 3
B1C5554E-64C5-E311-A6AC-6C626D750814 Theft - Snatch & Grab 18-Apr-14 1
61C5554E-64C5-E311-A6AC-6C626D750814 Bank Escorts Code 13 18-Apr-14 0
A2C5554E-64C5-E311-A6AC-6C626D750814 AWOL 18-Oct-13 0
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7446 Visits: 15134
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
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6733 Visits: 17711
Quick question, why the nolock hint?
Cool
Teee-SQL
Teee-SQL
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 229
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 :-)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19025
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?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Teee-SQL
Teee-SQL
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 229
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.
twin.devil
twin.devil
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2597
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.
Teee-SQL
Teee-SQL
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 229
The Select below returns the same results as the one I first posted. Just rows with values for the filtered dates

SELECT
ot.OperationalTypeAlternateKey AS [OperationalTypeAlternateKey]
,ot.OperationalType AS [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]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search