Return Results With 0 Count

The follow SQL works great but it doesn't return a row for agencies that don't have any records for a particular date. I need to have all agencies in the result set even if they don't have records for the dates in question.

Thanks!


DECLARE  @ReportMode INT,
         @startDate  DATETIME,
         @endDate    DATETIME,
         @agencyID   VARCHAR(100),
         @divisionID VARCHAR(10);

SET @ReportMode = 2;

SET @startDate = '2009-01-01';

SET @endDate = '2008-12-31';

SET @divisionID = '4';

SET @agencyID = '0';

SELECT   agency_id,
         agency_name,
         Count(DISTINCT ti.id) tran_total,
         CASE @ReportMode
           WHEN 3
           THEN Left(Convert(VARCHAR(10),dreceived,120),4)
           WHEN 2
           THEN Left(Convert(VARCHAR(10),dreceived,120),7)
           ELSE Left(Convert(VARCHAR(10),dreceived,120),10)
         END AS reportdatestr
FROM     transactions_incoming ti
         INNER JOIN agencies a ON ti.agency_id = a.id
         LEFT JOIN Splitorderids(@agencyID) aid ON a.id = aid.orderid
         LEFT JOIN Splitorderids(@divisionID) did ON a.agency_division_id = did.orderid
WHERE    ti.dreceived BETWEEN @startDate AND @endDate
         AND (@agencyID = '0'
               OR aid.orderid IS NOT NULL)
         AND (@divisionID = '0'
               OR did.orderid IS NOT NULL)
GROUP BY agency_id,
         agency_name,
         CASE @ReportMode
           WHEN 3
           THEN Left(Convert(VARCHAR(10),dreceived,120),4)
           WHEN 2
           THEN Left(Convert(VARCHAR(10),dreceived,120),7)
           ELSE Left(Convert(VARCHAR(10),dreceived,120),10)
         END
Order by
      reportdatestr,agency_name

Open in new window

jgoodaleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Change INNER JOIN to LEFT OUTER JOIN and give it a try..
Change
FROM     transactions_incoming ti
         INNER JOIN agencies a ON ti.agency_id = a.id
to
FROM     transactions_incoming ti
         LEFT JOIN agencies a ON ti.agency_id = a.id
0
jgoodaleAuthor Commented:
So revised statement returns same results:
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you provide some sample records for the current SELECT query along with Expected result sets so that I can understand your requirement better.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Rajkumar GsSoftware EngineerCommented:
Can you please paste query in main comment area?

Since I am from mobile, can't read code-snipplet fully

I will also try to help you

Raj
0
jgoodaleAuthor Commented:
agency_id   agency_name   tran_total    reportdatestr
61      ANAHEIM PD      299      2009-01
412      CHP Baldwin Park      448      2009-01
57      CHP LA Central       714      2009-01
372      CHP LA South      471      2009-01
367      CHP LA West      330      2009-01
63      CHP Oceanside      203      2009-01
330      CHP Rancho Cucamonga      275      2009-01

This would be a valid dataset but the problem is there is another agency "CHP Altadena" that doesn't have any transactions during this time period so they don't show in the results.

Here is the query below:

DECLARE  @ReportMode INT,
         @startDate  DATETIME,
         @endDate    DATETIME,
         @agencyID   VARCHAR(100),
         @divisionID VARCHAR(10);

SET @ReportMode = 2;

SET @startDate = '2009-01-01';

SET @endDate = '2008-12-31';

SET @divisionID = '4';

SET @agencyID = '0';

SELECT   agency_id,
         agency_name,
         Count(DISTINCT ti.id) tran_total,
         CASE @ReportMode
           WHEN 3
           THEN Left(Convert(VARCHAR(10),dreceived,120),4)
           WHEN 2
           THEN Left(Convert(VARCHAR(10),dreceived,120),7)
           ELSE Left(Convert(VARCHAR(10),dreceived,120),10)
         END AS reportdatestr
FROM     transactions_incoming ti
         INNER JOIN agencies a ON ti.agency_id = a.id
         LEFT JOIN Splitorderids(@agencyID) aid ON a.id = aid.orderid
         LEFT JOIN Splitorderids(@divisionID) did ON a.agency_division_id = did.orderid
WHERE    ti.dreceived BETWEEN @startDate AND @endDate
         AND (@agencyID = '0'
               OR aid.orderid IS NOT NULL)
         AND (@divisionID = '0'
               OR did.orderid IS NOT NULL)
GROUP BY agency_id,
         agency_name,
         CASE @ReportMode
           WHEN 3
           THEN Left(Convert(VARCHAR(10),dreceived,120),4)
           WHEN 2
           THEN Left(Convert(VARCHAR(10),dreceived,120),7)
           ELSE Left(Convert(VARCHAR(10),dreceived,120),10)
         END
Order by
      reportdatestr,agency_name
0
Rajkumar GsSoftware EngineerCommented:
Try modify the query like

...FROM     transactions_incoming ti
         INNER JOIN agencies a ON ti.agency_id = a.id
         LEFT JOIN...

to

...FROM agencies a
LEFT JOIN    transactions_incoming ti ON ti.agency_id = a.id
         LEFT JOIN...

Raj
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware EngineerCommented:
If you use INNER JOIN, only those records matches the JOIN condition will be returned.

LEFT OUTER JOIN will always SELECTs all records from LEFT side table will NULLs for those unmatched records from second table

Raj
0
jgoodaleAuthor Commented:
I have tried changing the join on the agencies to left outer but that doesn't work as well. I believe the problem is the query should revolve around the agencies and not the transactions. When the left outer join is performed there is no transactions on the date in question so the agencies are not getting bound.
0
Rajkumar GsSoftware EngineerCommented:
Yes. I agree.

Another way - you can keep this query as it is. Then...

SELECT * FROM agencies a
LEFT JOIN () b ON a.agency_id = b.agency_Id

Raj
0
jgoodaleAuthor Commented:
I tried your result the problem is the result set has null for total and for the date. How do I get the results to group with the correct date and show 0 instead of null

agency_id      agency_name      tran_total      reportdatestr
73      Alameda County Sheriff      NULL      NULL
72      Antioch PD      NULL      NULL
71      Berkeley PD      NULL      NULL
0
Rajkumar GsSoftware EngineerCommented:
Try use ISNULL to convert NULLs to 0

eg:- ISNULL(urColumn, 0) AS urColumn

Raj
0
Rajkumar GsSoftware EngineerCommented:
>> ...but it doesn't return a row for agencies that don't have any records for a particular date <<

Do you mean show all DISTINCT dates from second table against agencies even if there is NO record, with Total = 0 ?

Or with all dates of calender?

Raj
0
jgoodaleAuthor Commented:
I guess the best way to put it would be date of calendar, didn't think about like that but that is exactly the way I want to report the data.
0
Rajkumar GsSoftware EngineerCommented:
When I am back to SQL Server machine, I will try for sure, since it is some more complex - I think. I would like to post working query.

Hope other experts may help you before me.

Raj
0
Anthony PerkinsCommented:
>>I need to have all agencies in the result set even if they don't have records for the dates in question.<<
Then all you have to do is query against a table containing all the dates covered.  Let me know if you need an example.
0
jgoodaleAuthor Commented:
I could use an example. Thanks!
0
Anthony PerkinsCommented:
Do the following steps (Step 1 and 2 are a one time deal and do not need to be repeated):

1.  Build a table to hold the dates and call it Dates.
      CREATE TABLE Dates(DateValue smalldatetime PRIMARY KEY)
2. Populate it with a reasonable range of dates.  For example:
DECLARE @CurrDate smalldatetime

SET @CurrDate = '20100101'
WHILE @CurrDate <= '20201231'
      BEGIN
            INSERT Dates(DateValue) VALUES(@CurrDate)
            SET @CurrDate = DATEADD(DAY, 1, @CurrDate)
      END
3. Build your query something like this:
DECLARE @ReportMode integer,
    @startDate datetime,
    @endDate datetime,
    @agencyID varchar(100),
    @divisionID varchar(10) ;

SELECT  @ReportMode = 2,
        @startDate = '2009-01-01',
        @endDate = '2008-12-31',
        @divisionID = '4',
        @agencyID = '0'

SELECT  t.agency_id,
		t.agency_name,
		t.tran_total,
		d.DateValue reportdatestr
FROM    Dates d
        LEFT JOIN (
			SELECT	a.agency_id,
                    a.agency_name,
                    COUNT(DISTINCT ti.id) tran_total,
                    CASE @ReportMode
						WHEN 3 THEN LEFT(CONVERT(VARCHAR(10), ti.dreceived, 120), 4)
						WHEN 2 THEN LEFT(CONVERT(VARCHAR(10), ti.dreceived, 120), 7)
						ELSE LEFT(CONVERT(VARCHAR(10), ti.dreceived, 120), 10)
                    END AS reportdatestr
           FROM     agencies a
                    Left JOIN transactions_incoming ti ON a.id = ti.agency_id AND ti.dreceived BETWEEN @startDate AND @endDate
                    LEFT JOIN Splitorderids(@agencyID) aid ON a.id = aid.orderid
                    LEFT JOIN Splitorderids(@divisionID) did ON a.agency_division_id = did.orderid
           WHERE    (@agencyID = '0' OR aid.orderid IS NOT NULL)
                    AND (@divisionID = '0' OR did.orderid IS NOT NULL)
           GROUP BY a.id,
                    a.agency_name,
                    CASE @ReportMode
						WHEN 3 THEN LEFT(CONVERT(VARCHAR(10), ti.dreceived, 120), 4)
						WHEN 2 THEN LEFT(CONVERT(VARCHAR(10), ti.dreceived, 120), 7)
						ELSE LEFT(CONVERT(VARCHAR(10), ti.dreceived, 120), 10)
                    END
                  ) t ON d.DateValue = t.reportdatestr
ORDER BY t.reportdatestr,
        t.agency_name

Open in new window

0
Anthony PerkinsCommented:
I believe the comment at http:#a29969240 is a correct solution to the question.
0
jgoodaleAuthor Commented:
Here is the final working query:

            Begin
                  SELECT
                        agency_id,
                        agency_name,
                        Avg(tat) as tat,
                        Convert(date,CAST(Month(dReceived) as varchar(2)) + '/' + CAST(Day(dReceived) as varchar(2)) + '/' + Cast(Year(dReceived) as varchar(4)),101) as dReceived
                  FROM (      
                        SELECT
                              a.[id] as agency_id,
                              a.agency_name,
                              COUNT(tro.id) as total,
                              Convert(date,CAST(Month(A.Thedate) as varchar(2)) + '/' + CAST(Day(A.Thedate) as varchar(2)) + '/' + Cast(Year(A.Thedate) as varchar(4)),101) as dReceived,
                              IsNull(DATEDIFF(d,ti.dReceived,tro.dReceived),0) as tat      
                        FROM
                              (select * from agencies AS A,
                                     (select distinct convert(datetime,troDate.dReceived) as Thedate from transactions_outgoing troDate where troDate.dReceived between @startDate and @endDate) as D
                                    WHERE A.[id] IN (
                                                       SELECT ID FROM (
                                                        SELECT A.[ID], '0' as temp_val
                                                        FROM agencies  
                                                        UNION ALL  
                                                        SELECT OrderId, '1'  
                                                        from splitOrderIDs(@agencyID)) temp where temp_val = CASE when @agencyID= '0' then '0' else '1' end)
                                                 AND
                                           A.[agency_division_id] IN (
                                                      
                                                SELECT agency_division_id FROM (
                                                  SELECT A.[agency_division_id] , '0' as temp_val
                                                  FROM agencies  
                                                  UNION ALL  
                                                  SELECT OrderId, '1'  
                                                  from splitOrderIDs(@divisionID)) temp where temp_val = CASE when @divisionID= '0' then '0' else '1' end)
                               ) as a
                        LEFT OUTER JOIN transactions_outgoing tro on a.id=tro.agency_id AND tro.dReceived = A.thedate
                        LEFT OUTER JOIN transactions_incoming ti ON ( tro.transaction_num = ti.transaction_num
                                                                                            AND ti.dreceived = (SELECT MIN(dreceived)
                                                                                                                         FROM   transactions_incoming bb
                                                                                                                         WHERE  bb.dreceived <= tro.dreceived
                                                                                                                                    AND bb.transaction_num = tro.transaction_num) )
                        GROUP BY
                              a.id,
                              a.agency_name,
                              tro.dReceived,
                              ti.dReceived,
                              Year(A.Thedate),
                              Month(A.Thedate),
                              Day(A.Thedate)
                  ) as a
                  GROUP BY
                        agency_id,
                        agency_name,
                        Year(dReceived),
                        Month(dReceived),
                        Day(dReceived)
                  ORDER BY
                        dReceived,
                        agency_name      
            End
0
jgoodaleAuthor Commented:
I'm trying to give full points to RajkumarGS but I'm doing something wrong
0
Anthony PerkinsCommented:
Next time please provide feedback and do not abandon the question.

Thanks.
0
Anthony PerkinsCommented:
The author is trying to award points to RajkumarGS.
0
Anthony PerkinsCommented:
Now you can award points to RajkumarGS.  If you cannot figure it out an EE Moderator will.
0
jgoodaleAuthor Commented:
Here is the final working query:

            Begin
                  SELECT
                        agency_id,
                        agency_name,
                        Avg(tat) as tat,
                        Convert(date,CAST(Month(dReceived) as varchar(2)) + '/' + CAST(Day(dReceived) as varchar(2)) + '/' + Cast(Year(dReceived) as varchar(4)),101) as dReceived
                  FROM (      
                        SELECT
                              a.[id] as agency_id,
                              a.agency_name,
                              COUNT(tro.id) as total,
                              Convert(date,CAST(Month(A.Thedate) as varchar(2)) + '/' + CAST(Day(A.Thedate) as varchar(2)) + '/' + Cast(Year(A.Thedate) as varchar(4)),101) as dReceived,
                              IsNull(DATEDIFF(d,ti.dReceived,tro.dReceived),0) as tat      
                        FROM
                              (select * from agencies AS A,
                                     (select distinct convert(datetime,troDate.dReceived) as Thedate from transactions_outgoing troDate where troDate.dReceived between @startDate and @endDate) as D
                                    WHERE A.[id] IN (
                                                       SELECT ID FROM (
                                                        SELECT A.[ID], '0' as temp_val
                                                        FROM agencies  
                                                        UNION ALL  
                                                        SELECT OrderId, '1'  
                                                        from splitOrderIDs(@agencyID)) temp where temp_val = CASE when @agencyID= '0' then '0' else '1' end)
                                                 AND
                                           A.[agency_division_id] IN (
                                                     
                                                SELECT agency_division_id FROM (
                                                  SELECT A.[agency_division_id] , '0' as temp_val
                                                  FROM agencies  
                                                  UNION ALL  
                                                  SELECT OrderId, '1'  
                                                  from splitOrderIDs(@divisionID)) temp where temp_val = CASE when @divisionID= '0' then '0' else '1' end)
                               ) as a
                        LEFT OUTER JOIN transactions_outgoing tro on a.id=tro.agency_id AND tro.dReceived = A.thedate
                        LEFT OUTER JOIN transactions_incoming ti ON ( tro.transaction_num = ti.transaction_num
                                                                                            AND ti.dreceived = (SELECT MIN(dreceived)
                                                                                                                         FROM   transactions_incoming bb
                                                                                                                         WHERE  bb.dreceived <= tro.dreceived
                                                                                                                                    AND bb.transaction_num = tro.transaction_num) )
                        GROUP BY
                              a.id,
                              a.agency_name,
                              tro.dReceived,
                              ti.dReceived,
                              Year(A.Thedate),
                              Month(A.Thedate),
                              Day(A.Thedate)
                  ) as a
                  GROUP BY
                        agency_id,
                        agency_name,
                        Year(dReceived),
                        Month(dReceived),
                        Day(dReceived)
                  ORDER BY
                        dReceived,
                        agency_name      
            End
0
jgoodaleAuthor Commented:
Thank you, I'm still new to this website.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.