December 8, 2009 at 10:53 am
I am writing sql for a SSRS report, but it's a SQL question, not ssrs.
I have a table of reasons that I am referencing in my main table. I wrote a sql statement to create a result set that shows a count, by month for each reason.
The tricky part is I'd like to choose all of the rows from my reasons table, even if the count is zero. Right now my results are only coming back with counts of rows that have entries that have been chosen.
I have this:
Home 4
Office 2
Other 3
and I want this:
Friend 0
Home 4
Office 2
Other 3
Relative 0
So that even tho no one has chosen Friend of Relative, I bring back a zero.
Seems like it should be come kind of join, but I have a feeling I'm going to have to write a stored proc to check for null count and return zero if it's null.
December 8, 2009 at 11:08 am
Here is the query:
SELECT COUNT(refTrkVisit.visitID) AS count, refTrkNonAdmitReason.description, Entity.entShortName, MONTH(refTrkVisit.initialEntryDate)
AS Month, refTrkCompetitor.name
FROM refTrkVisit LEFT OUTER JOIN
refTrkNonAdmitReason ON refTrkVisit.nonAdmitReasonID = refTrkNonAdmitReason.nonAdmitReasonID LEFT OUTER JOIN
Entity ON refTrkVisit.entID = Entity.entID LEFT OUTER JOIN
refTrkCompetitor ON refTrkVisit.competitorID = refTrkCompetitor.competitorID AND
refTrkNonAdmitReason.nonAdmitReasonID = refTrkCompetitor.nonAdmitReasonID AND Entity.entID = refTrkCompetitor.entID
WHERE (refTrkVisit.nonAdmitReasonID IS NOT NULL)
GROUP BY refTrkNonAdmitReason.description, Entity.entShortName, MONTH(refTrkVisit.initialEntryDate), refTrkCompetitor.name
ORDER BY Month, Entity.entShortName
this are the tables:
CREATE TABLE [dbo].[refTrkVisit](
[visitID] [int] IDENTITY(1,1) NOT NULL,
[refTrkUserID] [int] NOT NULL,
[entID] [varchar](10) NOT NULL,
[refTypeID] [int] NOT NULL,
[refSourceID] [int] NULL,
[secondaryRefTypeID] [int] NULL,
[secondaryRefSourceID] [int] NULL,
[patientLocTypeID] [int] NOT NULL,
[patientLocID] [int] NULL,
[specialistTypeID] [int] NULL,
[pcpSpecialistID] [int] NULL,
[hospitalPhysicianID] [int] NULL,
[communityPhysicianID] [int] NULL,
[caseManagerID] [int] NULL,
[diagCodeID] [int] NOT NULL,
[payorID] [int] NOT NULL,
[payorSourceID] [int] NULL,
[admisDate] [datetime] NULL,
[nonAdmitReasonID] [int] NULL,
[competitorID] [int] NULL,
[competitorReason] [varchar](250) NULL,
[pendingStatusID] [int] NULL,
[pendingStatus] [varchar](500) NULL,
[tour] [varchar](1) NOT NULL,
[tourDate] [datetime] NULL,
[dateEntered] [datetime] NULL,
[readmit] [varchar](1) NOT NULL,
[dischargeReasonID] [int] NULL,
[dischargeLocID] [int] NULL,
[dischargeCompetitorID] [int] NULL,
[dischargeCompetitorReason] [varchar](500) NULL,
[dischargeDate] [datetime] NULL,
[initialEntryDate] [datetime] NULL,
[updated] [datetime] NOT NULL CONSTRAINT [DF_refTrkVisit_updated] DEFAULT (getdate()),
CONSTRAINT [PK_refTrkVisit] PRIMARY KEY CLUSTERED
(
[visitID] ASC
)
these are the reference tables:
CREATE TABLE [dbo].[refTrkNonAdmitReason](
[nonAdmitReasonID] [int] IDENTITY(1,1) NOT NULL,
[description] [varchar](500) NOT NULL,
[updated] [datetime] NOT NULL CONSTRAINT [DF_refTrkNonAdmitReason_updated] DEFAULT (getdate()),
CONSTRAINT [PK_refTrkNonAdmitReason] PRIMARY KEY CLUSTERED
(
[nonAdmitReasonID] ASC
)
CREATE TABLE [dbo].[refTrkCompetitor](
[competitorID] [int] IDENTITY(1,1) NOT NULL,
[nonAdmitReasonID] [int] NOT NULL,
[entID] [varchar](10) NOT NULL,
[name] [varchar](750) NOT NULL,
[updated] [datetime] NOT NULL CONSTRAINT [DF_refTrkCompetitor_updated] DEFAULT (getdate()),
CONSTRAINT [PK_refTrkCompetitor] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[Entity](
[entID] [varchar](10) NOT NULL,
[entShortName] [varchar](150) NULL,
[entNumericID] [int] NOT NULL,
[orgID] [int] NOT NULL,
[regionID] [int] NOT NULL,
[portID] [int] NOT NULL,
[busTypeID] [int] NOT NULL,
[adpID] [varchar](50) NULL,
[eHealthDataID] [varchar](50) NULL,
[updateDate] [datetime] NULL CONSTRAINT [DF_Entity_updateDate] DEFAULT (getdate()),
[powProID] [int] NULL,
[regionReportingID] [int] NULL,
CONSTRAINT [PK_EntityNEW] PRIMARY KEY CLUSTERED
(
[entID] ASC
)
December 8, 2009 at 11:14 am
Why can't you just use ISNULL to replace the null value with a zero?
_________________________________
seth delconte
http://sqlkeys.com
December 8, 2009 at 11:25 am
Actually, now that I see your query, I think the problem may be resolved by using a FULL OUTER JOIN in place of a LEFT OUTER JOIN.
_________________________________
seth delconte
http://sqlkeys.com
December 8, 2009 at 11:48 am
just for that one table I want to pull all the values for, or in place of all of the left outer joins?
December 8, 2009 at 12:33 pm
shouldn't you just change the first table in the query to be refTrkNonAdmitReason as the base table, and join everything to that in order to get zeros for all reasons?
SELECT
COUNT(refTrkVisit.visitID) AS count,
refTrkNonAdmitReason.description,
Entity.entShortName,
MONTH(refTrkVisit.initialEntryDate) AS Month,
refTrkCompetitor.name
FROM refTrkNonAdmitReason
LEFT OUTER JOIN refTrkVisit
ON refTrkNonAdmitReason.nonAdmitReasonID =refTrkVisit.nonAdmitReasonID
LEFT OUTER JOIN Entity ON refTrkVisit.entID = Entity.entID
LEFT OUTER JOIN refTrkCompetitor
ON refTrkVisit.competitorID = refTrkCompetitor.competitorID
AND refTrkNonAdmitReason.nonAdmitReasonID = refTrkCompetitor.nonAdmitReasonID
AND Entity.entID = refTrkCompetitor.entID
WHERE (refTrkVisit.nonAdmitReasonID IS NOT NULL)
GROUP BY refTrkNonAdmitReason.description, Entity.entShortName, MONTH(refTrkVisit.initialEntryDate), refTrkCompetitor.name
ORDER BY Month, Entity.entShortName
Lowell
December 8, 2009 at 1:30 pm
I thought of that, but no, still same thing. Even tried switching it and using/not using full outer joins, no dice.
SELECT ISNULL(COUNT(refTrkNonAdmitReason.description), 0) AS count, refTrkNonAdmitReason.description, Entity.entShortName,
MONTH(refTrkVisit.initialEntryDate) AS Month
FROM refTrkNonAdmitReason FULL OUTER JOIN
refTrkVisit ON refTrkNonAdmitReason.nonAdmitReasonID = refTrkVisit.nonAdmitReasonID FULL OUTER JOIN
Entity ON refTrkVisit.entID = Entity.entID
WHERE (refTrkVisit.nonAdmitReasonID IS NOT NULL)
GROUP BY refTrkNonAdmitReason.description, Entity.entShortName, MONTH(refTrkVisit.initialEntryDate)
ORDER BY Month, Entity.entShortName
December 9, 2009 at 2:45 am
I think the problem is of the WHERE clause which rules out all the cases when refTrkVisit not exist.
Try this:
SELECT
COUNT(refTrkVisit.visitID) AS count,
refTrkNonAdmitReason.description,
Entity.entShortName,
MONTH(refTrkVisit.initialEntryDate) AS Month,
refTrkCompetitor.name
FROM
refTrkVisit
-- use the right join here to get all Entity Short Name
RIGHT OUTER JOIN Entity ON refTrkVisit.entID = Entity.entID
LEFT OUTER JOIN refTrkNonAdmitReason ON refTrkVisit.nonAdmitReasonID = refTrkNonAdmitReason.nonAdmitReasonID
-- Move the where clause here
AND refTrkVisit.nonAdmitReasonID IS NOT NULL
LEFT OUTER JOIN refTrkCompetitor ON refTrkVisit.competitorID = refTrkCompetitor.competitorID
AND refTrkNonAdmitReason.nonAdmitReasonID = refTrkCompetitor.nonAdmitReasonID
AND Entity.entID = refTrkCompetitor.entID
GROUP BY
refTrkNonAdmitReason.description,
Entity.entShortName,
MONTH(refTrkVisit.initialEntryDate),
refTrkCompetitor.name
ORDER BY
Month,
Entity.entShortName
December 9, 2009 at 7:56 am
That's definitely closer, it gives me one row with a count of zero for each of the choices in the NonAdmitReason table plus a row for each NonAdmitReason that is attached to a legitimate facility(entShortName).
right now it looks like
count, entShortName, description, Month
0 null Chose Other Fac null
0 null Inappropriate amenities null
.... with all of the non admit reasons like that
and then the results of the visits that have a non admit reason.
What I'd like to get in my result is a matrix grid in a report.
So I guess my result set would look like
count, entShortName, description, Month
0 building1 Chose Other Fac 1
5 building1 Inappropriate amenities 1
4 building1 OTHER 1
...
1 building1 Chose Other Fac 2
4 building1 Inappropriate amenities 2
6 building1 OTHER 2
...
1 building2 Chose Other Fac 1
3 building2 Inappropriate amenities 1
0 building2 OTHER 1
...
2 building2 Chose OTher 2
0 building2 Inappropriate amenities 2
6 building2 OTHER 2
Does that make more sense? Thanks to all for the help so far.
December 9, 2009 at 6:30 pm
So we can try using INNER JOIN instead of LEFT JOIN for the NonAdmitReason table.
December 10, 2009 at 7:42 am
Quan Phan (12/9/2009)
So we can try using INNER JOIN instead of LEFT JOIN for the NonAdmitReason table.
adding the inner join takes me back to my original result of only showing 12 rows, one for each nonadmitreason that exists.
SELECT
COUNT(refTrkVisit.visitID) AS count,
refTrkNonAdmitReason.description,
Entity.entShortName,
MONTH(refTrkVisit.initialEntryDate) AS Month,
refTrkCompetitor.name
FROM
refTrkVisit
-- use the right join here to get all Entity Short Name
RIGHT OUTER JOIN Entity ON refTrkVisit.entID = Entity.entID
INNER JOIN refTrkNonAdmitReason ON refTrkVisit.nonAdmitReasonID = refTrkNonAdmitReason.nonAdmitReasonID
-- Move the where clause here
AND refTrkVisit.nonAdmitReasonID IS NOT NULL
LEFT OUTER JOIN refTrkCompetitor ON refTrkVisit.competitorID = refTrkCompetitor.competitorID
AND refTrkNonAdmitReason.nonAdmitReasonID = refTrkCompetitor.nonAdmitReasonID
AND Entity.entID = refTrkCompetitor.entID
GROUP BY
refTrkNonAdmitReason.description,
Entity.entShortName,
MONTH(refTrkVisit.initialEntryDate),
refTrkCompetitor.name
ORDER BY
Month,
Entity.entShortName
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply