I am trying to create a query for running on Microsoft Server SQL and I’m not really sure how to do it, as I think it requires nesting 1 or more queries within a query. I’m quite new to SQL and would appreciate any assistance that could be provided.
I have 3 tables:
Report table - this consists of transactions, and 1 or more transactions may link to the same report ID.
Transactions table - this consists of disbursements, and 1 or more disbursements may link to the same transaction ID.
Disbursements include an Amount column and a Status column, in addition to a disbursements ID.
I can’t quite figure out how I can tally up all the Amount column within for all disbursements that relate to a report id (joining the disbursements to transactions, and transactions to reports) and with the following conditions:
-disbursement status is “Submitted”
-transaction status is “Accepted”
If anyone could help me with this, it would be greatly appreciated.
I would like the query to show each Report ID once, and show the total Amount of disbursements.
SELECT reports.id
, SUM(disbursements.amount) AS disbursed_amt
FROM reports
INNER
JOIN transactions
ON transactions.xxx = reports.yyy
AND transactions.status = 'Accepted'
INNER
JOIN disbursements
ON disbursements.aaa = transactions.bbb
AND disbursements.status = 'Submitted'
GROUP
BY reports.id
i wasn’t sure about the column names for the join columns
Here’s the full code I am working with. You will note that I have had to comment out the sum() line, and the group by line. If either of those are not commented out, then I get the following error:
Msg 8120, Level 16, State 1, Line 8
Column ‘ReportStage_RV.C_Date’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
declare @p_Start_Date datetime = '2022-08-01'
, @p_End_Date datetime = '2023-07-31'
select
cast(RepStage.C_Date as DATE) as Report_Date,
Rep.ReportId,
case Rep.ReportTypeCd
when 24003 then 'STR'
when 24004 then 'UTR'
end as Type,
FacName.Name as Facility,
MP.MasterPatronId as Patron_ID,
P.Surname,
P.GivenName
--sum(Dis.Amount) as Amount
from Report_RV Rep
left join ReportStage_RV RepStage on Rep.ReportId = RepStage.ReportId
left join Patron_RV P on Rep.PatronId = P.PatronId
left join MasterPatron_RV MP on P.MasterPatronId = MP.MasterPatronId
left join FacilityName_RV FacName on Rep.FacilityId = FacName.FacilityId
left join Transaction_RV Trans on Rep.ReportId = Trans.ReportId
left join Disposition_RV Dis on Trans.TransactionId = Dis.TransactionId
and Dis.ReasonMethodType = 'Method'
where Rep.ReportTypeCd in (24003,24004)
and RepStage.C_Date between @p_Start_Date and @p_End_Date
--group by Rep.ReportId
SQL Server requires that you group by any selected column that is not part of an aggregate function (like sum). Add all the other columns you are selecting to your group by column.
declare @p_Start_Date datetime = '2022-08-01'
, @p_End_Date datetime = '2023-07-31'
select
cast(RepStage.C_Date as DATE) as Report_Date,
Rep.ReportId,
case Rep.ReportTypeCd
when 24003 then 'STR'
when 24004 then 'UTR'
end as Type,
FacName.Name as Facility,
MP.MasterPatronId as Patron_ID,
P.Surname,
P.GivenName
sum(Dis.Amount) as Amount
from Report_RV Rep
left join ReportStage_RV RepStage on Rep.ReportId = RepStage.ReportId
left join Patron_RV P on Rep.PatronId = P.PatronId
left join MasterPatron_RV MP on P.MasterPatronId = MP.MasterPatronId
left join FacilityName_RV FacName on Rep.FacilityId = FacName.FacilityId
left join Transaction_RV Trans on Rep.ReportId = Trans.ReportId
left join Disposition_RV Dis on Trans.TransactionId = Dis.TransactionId and Dis.ReasonMethodType = 'Method'
where
Rep.ReportTypeCd in (24003,24004)
and RepStage.C_Date between @p_Start_Date and @p_End_Date
group by
RepStage.C_Date,
Rep.ReportId,
Rep.ReportTypeCd,
FacName.Name,
MP.MasterPatronId,
P.Surname,
P.GivenName