Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How To Return Zero If No Rows Found Expand / Collapse
Author
Message
Posted Wednesday, June 25, 2014 2:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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

Post #1585779
Posted Wednesday, June 25, 2014 3:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 AM
Points: 5,488, Visits: 10,339
What do you mean by "rows with no data"? Please will you provide some examples?

John
Post #1585786
Posted Wednesday, June 25, 2014 3:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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
Post #1585789
Posted Wednesday, June 25, 2014 4:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 AM
Points: 5,488, Visits: 10,339
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
Post #1585802
Posted Wednesday, June 25, 2014 4:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,533, Visits: 7,101
Quick question, why the nolock hint?
Post #1585815
Posted Wednesday, June 25, 2014 5:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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
Post #1585828
Posted Wednesday, June 25, 2014 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1585841
Posted Wednesday, June 25, 2014 6:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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.
Post #1585853
Posted Wednesday, June 25, 2014 6:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:33 AM
Points: 856, Visits: 1,506
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.







Post #1585863
Posted Wednesday, June 25, 2014 6:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:47 AM
Points: 62, Visits: 224
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]
Post #1585868
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse