Return Results With 0 Count

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

So revised statement returns same results:

Can you provide some sample records for the current SELECT query along with Expected result sets so that I can understand your requirement better.

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

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

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

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.

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

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

Try use ISNULL to convert NULLs to 0

eg:- ISNULL(urColumn, 0) AS urColumn

Raj

>> ...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

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.

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

>>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.

I could use an example. Thanks!

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

Select allOpen in new window

I believe the comment at http:#a29969240 is a correct solution to the question.

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

I'm trying to give full points to RajkumarGS but I'm doing something wrong

Next time please provide feedback and do not abandon the question.

Thanks.

The author is trying to award points to RajkumarGS.

Now you can award points to RajkumarGS.  If you cannot figure it out an EE Moderator will.

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

Thank you, I'm still new to this website.