January 5, 2016 at 7:22 am
Hi,
I got a report that runs annually and it grabs data using the following query:
SELECT SUM(a.Amount) AS TRAmount FROM (
SELECT
LineID,
InvoiceNumber,
LineDescription,
Amount,
LineNumber
FROM tblInvoiceLine
WHERE InvoiceNumber IN (
SELECT
InvoiceNumber
FROM tblVesselInvoice
WHERE MovementsID IN (
SELECT
MovementsID
FROM tblVesselMovements
WHERE CONVERT(DATETIME, ArrivalDate, 103)
BETWEEN '2014-01-01' AND '2014-12-30' AND VesselID IN (
SELECT
VesselID
FROM tblVesselDetails
WHERE VesselTypeCode = 'TR')
) AND IsCancelled = 0
) AND LineNumber = 9
) a
So that bit would retrieve an amount of charges made during 2014 (date needs to be converted since its stored as string).
This query "happens" about 35 times, one per every VesselTypeCode:
WHERE VesselTypeCode = 'TR'
This is bizarre, the code C# is actually querying (almost) the same 35 times and storing the results in variables that later get printed on the report. I would like to do something more database driven, I'm not sure if a cursor is the way to go... (?)
January 5, 2016 at 7:46 am
Unless I have missed something is this just a simple grouping? If so does the below work - you may need to fix any syntax errors as I created it quickly
SELECT
VesD.VesselTypeCode
,SUM(InvL.Amount) as Amount
FROM tblInvoiceLine InvL
join tblVessleInvoice VesI
on InvL.InvoiceNumber = VesI.InvoiceNumber
join tblVesselMovements VesM
on VesI.MovementId = VesM.MovementID
join tblVesselDetails VesD
on VesM.VesselID = VesD.VesselID
where CONVERT(DATETIME, VesM.ArrivalDate, 103) BETWEEN '2014-01-01' AND '2014-12-30'
and VesI.IsCancelled = 0
and Invl.LineNumber = 9
Group by VesD.VesselTypeCode
January 5, 2016 at 8:42 am
This could be a safer approach to prevent unwanted duplicate rows.
WITH cteVessels AS(
--Change this code to something that won't need the DISTINCT
SELECT DISTINCT VesselTypeCode
FROM tblVesselDetails
)
SELECT VesselTypeCode,
a.TRAmount
FROM cteVessels v
CROSS APPLY(
SELECT SUM( Amount) AS TRAmount
FROM tblInvoiceLine
WHERE InvoiceNumber IN (
SELECT InvoiceNumber
FROM tblVesselInvoice
WHERE MovementsID IN (
SELECT MovementsID
FROM tblVesselMovements
WHERE CONVERT(DATETIME, ArrivalDate, 103) BETWEEN '2014-01-01' AND '2014-12-30'
AND VesselID IN (
SELECT VesselID
FROM tblVesselDetails
WHERE VesselTypeCode = v.VesselTypeCode)
)
AND IsCancelled = 0
)
AND LineNumber = 9
) a
January 5, 2016 at 11:04 am
Luis Cazares (1/5/2016)
This could be a safer approach to prevent unwanted duplicate rows.
Ah! This one worked quite prefectly... What would be the best way of repeating this part 9 times (one per LineNumber)
CROSS APPLY
(
SELECT SUM(Amount) AS TRAmount
FROM tblInvoiceLine
WHERE InvoiceNumber IN
(
SELECT InvoiceNumber
FROM tblVesselInvoice
WHERE MovementsID IN
(
SELECT MovementsID
FROM tblVesselMovements
WHERE CONVERT(DATETIME, ArrivalDate, 103) BETWEEN '2014-01-01' AND '2014-12-30' AND VesselID IN
(
SELECT VesselID
FROM tblVesselDetails
WHERE VesselTypeCode = v.VesselTypeCode
)
) AND IsCancelled = 0
) AND LineNumber = 9
) a
I know If I actually copy-paste it, change the alias and include it on top of the original query it will work out but I was thinking of a fancier way?,
Thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply