Retrieve Data from three tables by date range

Transact-SQL
1

I have three tables:

Action Table
ApplicationNumber Actions CompletionDate

PermitTable
ApplicationNumber PermitCost IssueDate

Fees Table
ApplicationNumber Amount Date

I have three separate summary reports that work that I would like to combine into one report:

Actions Only:
SELECT
tblDataActions.Actions,
tblDataActions.CompletionDate
FROM
tblDataActions
WHERE
tblDataActions.CompletionDate >= @StartDate
AND tblDataActions.CompletionDate <= @EndDate

PermitCost Only:
SELECT
tblDataPermit.PermitCost,
tblDataPermit.IssueDate
FROM
tblDataPermit
WHERE
tblDataPermit.IssueDate >= @StartDate
AND tblDataPermit.IssueDate <= @EndDate

Fees Only:
SELECT
tblDataFees.Amount,
tblDataFees.Date
FROM
tblDataFees
WHERE
tblDataFees.Date >= @StartDate
AND tblDataFees.Date <= @EndDate

How can I combine them in to one report? Below is my attempt that does not give correct sums. I think the issue is that the application numbers for actions within the time period are not the same as the application numbers for fees & costs in the same time period. I just want to choose a date range and pull out the total number of actions and total sum of costs and fees within that time period. Is it possible to do in one report? The result would be like this:
--Date Range: 10/1/17 - 10/31/17
--Total Actions: 100
--Total Permit Cost: 2,500
--Total fees: 600
Thank you for taking the time to read all of this.

SELECT
tblDataFees.Amount,
tblDataFees.Date,
tblDataActions.Actions,
tblDataActions.CompletionDate,
tblDataPermit.IssueDate,
tblDataPermit.PermitCost
FROM
tblDataPermit
INNER JOIN tblDataFees
ON tblDataPermit.ApplicationNumber = tblDataFees.ApplicationNumber
INNER JOIN tblDataActions
ON tblDataPermit.ApplicationNumber = tblDataActions.ApplicationNumber
WHERE
tblDataActions.CompletionDate >= @StartDate
AND tblDataActions.CompletionDate <= @EndDate
AND tblDataPermit.IssueDate >= @StartDate
AND tblDataPermit.IssueDate <= @EndDate
AND tblDataFees.Date >= @StartDate
AND tblDataFees.Date <= @EndDate

2

use UNION between the three queries

3 
DECLARE @StartDate date
DECLARE @EndDate date

SET @StartDate = '20171001'
SET @EndDate = '20171031'

SELECT actions.*, permits.*, fees.*
FROM (
    SELECT
    COUNT(*) AS ActionCount
    FROM
    dbo.tblDataActions da
    WHERE
    da.CompletionDate >= @StartDate
    AND da.CompletionDate < DATEADD(DAY, 1, @EndDate)
) AS actions
CROSS JOIN (    
    SELECT
    SUM(dp.PermitCost) AS PermitCostTotal
    FROM
    dbo.tblDataPermit dp
    WHERE
    dp.IssueDate >= @StartDate
    AND dp.IssueDate < DATEADD(DAY, 1, @EndDate)
) AS permits
CROSS JOIN (
    SELECT
    SUM(df.Amount) AS AmountTotal
    FROM
    dbo.tblDataFees df
    WHERE
    tf.Date >= @StartDate
    AND tf.Date <= DATEADD(DAY, 1, @EndDate)
) AS fees
4

I added the UNION(s) and get this error:
Cannot convert a char value to money. The char value
has incorrect syntax.

Tried several Web searches and corrections but still get the error.

Any suggestions? Thank you.

5

Scott, when I use your code I get #Error in the table, such as where this expression is:

=Count(IIF(Fields!ActionCount.Value = "Inspection - Pre-Permit", 1, Nothing)) + Count(IIF(Fields!ActionCount.Value = "Inspection - Building", 1, Nothing))

Any thoughts?
Thank you.

6

I can't directly help with the error. Run the SQL separately from SSRS. If the query works, then the issue is somewhere in SSRS, which is a different q.

7

I haven't looked at the code, but in terms of using a UNION on two SELECT statements (which each work if run individually) then the problem is that the Data Type of the columns is not the same for both/all SELECTs in the UNION

e.g. maybe Column-5 in the first SELECT is CHAR and column-5 in the second SELECT is MONEY.

If you make the two select columns "perfect", but accidentally get the columns in a slightly different order, that would have the same effect of course.

So you are probably looking for a MONEY column in one of the SELECTs which has a corresponding CHAR/Varchar/Etc. in one of the other UNION SELECTs

If you do indeed need to have CHAR in one and MONEY in the other, then you need to EXPLICITLY CONVERT the MONEY column to CHAR/Varchar - the default, implicit, conversion will be from Char to Money, which is why you are getting the error - an existing Char value cannot be converted to Money - i.e. the Char doesn't contain a numeric value.

8

Thank you!