Combining two SQL statements into One

Transact-SQL
1

I am generating a Crystal Report from VB NET.

I have two reports I would prefer to combine into one report if possible. It is my understanding I cannot run 2 independent SQL queries within the same report.

The full SQL queries currently in the 2 separate reports are as follows:

SQL 1:
DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));
DECLARE @startLoadNumber int = @reportYear * 1000
, @endLoadNumber int = @reportYear * 1000 + 999;

SELECT l.BROKER_AGENCY AS 'BROKER AGENCY'
      , l.AGENT_NAME AS 'AGENT NAME'
      , l.PETS_LOAD_NUMBER AS 'LOAD NUMBER'
FROM Load_Info_Table as l
WHERE l.PETS_LOAD_NUMBER BETWEEN @startLoadNumber
  AND @endLoadNumber
ORDER BY l.PETS_LOAD_NUMBER

SQL 2:
DECLARE @reportYear int = (year(getdate()) - iif(month(getdate()) = 1, 1, 0));
DECLARE @startLoadNumber int = @reportYear * 1000
, @endLoadNumber int = @reportYear * 1000 + 999;

SELECT l.BROKER_AGENCY, COUNT(l.BROKER_AGENCY) as 'TOTAL'
FROM LOAD_INFO_TABLE AS l
WHERE l.PETS_LOAD_NUMBER BETWEEN @startLoadNumber
  AND @endLoadNumber
GROUP BY L.BROKER_AGENCY

SQL 2 would run after SQL 1 in the bottom of the page Report would bring results. Is there a way to combine this into one SQL statement? Hope this is clear.

In a nutshell: I want to run the main report, then count all broker names and give a total # of loads run by broker at the bottom.

2

I think this will do it, although of course I don't have any data to test it with.

SELECT l.BROKER_AGENCY AS 'BROKER AGENCY'
      , COUNT(*) OVER(PARTITION BY BROKER_AGENCY) AS 'TOTAL'
      , l.AGENT_NAME AS 'AGENT NAME'
      , l.PETS_LOAD_NUMBER AS 'LOAD NUMBER'
FROM Load_Info_Table as l
WHERE l.PETS_LOAD_NUMBER BETWEEN @startLoadNumber
  AND @endLoadNumber
ORDER BY l.PETS_LOAD_NUMBER
3

Very close but the COUNT is bringing back only one record. I am expecting 6 returns.

Your query bring:

N / A 1 N / A 2018000
COYOTE 137 NICO SERFILLIPI 2018001
COYOTE 137 NICO SERFILLIPI 2018002
COYOTE 137 NICO SERFILLIPI 2018003
SCHNEIDER 1 PATRICK BASSI 2018026
COYOTE 137 NICO SERFILIPPI 2018027
COYOTE 137 NICO SERFILIPPI 2018028
COYOTE 137 NCO SERFILIPPI 2018029
COYOTE 137 NICO SERFILLIPI 2018030
COYOTE 137 NICO SERFILIPPI 2018031
TQL 12 ANDREW CORCORAN 2018032

I am looking for the following to return at the bottom:

BROKER_AGENCY TOTAL
COYOTE 137
GIX 1
N / A 1
SCHNEIDER 1
TQL 12

I am only getting the first line.

4

Why wouldn't you use Crystal Reports to create the summary data? Since you want this in the page footer - or following the data from the first query - you will need a separate section in the report that is sourced from the main query.

In SSRS (haven't worked with Crystal in 10 years) - this would be accomplished using a separate tablix. That tablix would use the same source as the first query and only show totals.

5

I can do this by embedding a SUB REPORT and will do it this way if there is no quick & dirty way to do this in one SQL query.

6 
SELECT l.BROKER_AGENCY AS 'BROKER AGENCY'
      , (SELECT COUNT(*) FROM Load_Info_Table as I2 WHERE I2.BROKER_AGENCY = I.BROKER_AGENCY AND 
         l.PETS_LOAD_NUMBER BETWEEN @startLoadNumber AND @endLoadNumber
        ) AS 'TOTAL'
      , l.AGENT_NAME AS 'AGENT NAME'
      , l.PETS_LOAD_NUMBER AS 'LOAD NUMBER'
FROM Load_Info_Table as l
WHERE l.PETS_LOAD_NUMBER BETWEEN @startLoadNumber
  AND @endLoadNumber
ORDER BY l.PETS_LOAD_NUMBER