I'm trying to get an array of invoice objects for each client, but the error I'm getting is when a client has more than one invoice, a new object gets created for that client and it's new invoice. Instead, I would like for each invoice that belongs to the client to be within that client's invoice data. Also not sure how I would combine the total combined invoice to the outer object for "clientInvoiceTotal," combining all of it's invoices from the "invoiceTotal" property.
CREATE PROCEDURE get_report
@userId INT,
@year INT
AS
SELECT
JSON_QUERY((
SELECT
cl.client_name AS clientName,
i.total AS clientInvoiceTotal,
JSON_QUERY((
SELECT
i.invoice_id AS invoiceId,
i.total AS invoiceTotal,
JSON_QUERY((
SELECT
s.service_name AS serviceName,
cis.service_price AS invoiced
FROM
case_identifier_to_service cis
INNER JOIN services s ON s.service_id = cis.service_id
WHERE cis.invoice_id = i.invoice_id
FOR JSON PATH
)) AS serviceData
FROM invoices i WHERE i.invoice_id = c.invoice_id
FOR JSON PATH
)) AS clientData
FROM
cases c
INNER JOIN clients cl ON cl.client_id = c.client_id
INNER JOIN invoices i ON i.invoice_id = c.invoice_id
WHERE c.user_id=@userId AND DATEPART(year, c.date_created) = @year
FOR JSON PATH
)) AS report
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
The data that I SHOULD be getting back is:
{
"report":[
{
"clientName":"Client1",
"clientInvoiceTotal":59500.00,
"clientData":[
{
"invoiceId":100,
"invoiceTotal":59500.00,
"serviceData":[
{
"serviceName":"LDD",
"invoiced":59500.00
}
]
}
]
},
{
"clientName":"Client2",
"clientInvoiceTotal":53150.00,
"clientData":[
{
"invoiceId":101,
"invoiceTotal":8600.00,
"serviceData":[
{
"serviceName":"BCP",
"invoiced":4900.00
},
{
"serviceName":"BRS",
"invoiced":3700.00
}
]
},
{
"invoiceId":102,
"invoiceTotal":44550.00,
"serviceData":[
{
"serviceName":"DDBR",
"invoiced":44550.00
}
]
}
]
}
]
}
But instead, if a client has more than one invoice another object with duplicate client name is added, the data I'm getting back looks like this, which is INCORRECT:
{
"report":[
{
"clientName":"Client1",
"clientInvoiceTotal":59500.00,
"clientData":[
{
"invoiceId":100,
"invoiceTotal":59500.00,
"serviceData":[
{
"serviceName":"LDD",
"invoiced":59500.00
}
]
}
]
},
{
"clientName":"Client2",
"clientInvoiceTotal":8600.00,
"clientData":[
{
"invoiceId":101,
"invoiceTotal":8600.00,
"serviceData":[
{
"serviceName":"BCP",
"invoiced":4900.00
},
{
"serviceName":"BRS",
"invoiced":3700.00
}
]
}
]
},
{
"clientName":"Client2",
"clientInvoiceTotal":44550.00,
"clientData":[
{
"invoiceId":102,
"invoiceTotal":44550.00,
"serviceData":[
{
"serviceName":"DDBR",
"invoiced":44550.00
}
]
}
]
}
]
}