Richard Quadling
asked on
Multiple aggregations in a single result set.
Hi.
I'm in the process of converting a series of reports from Sage Retrieve 4GL to MSSQL/Crystal.
The reports in Sage are built by code (i.e. read a row from the table, add values to temporary storage/totals), rather than SQL (the 4GL db is NOT SQL).
Here are the tables in use.
Head Offices (HO.Code, HO.Name)
Customers (CU.Code, CU.Name, CU.HOCode) CU.HOCode links to HO.Code
Banks (PB.Code, PB.Period) PB.Code links to CU.Code
Tickets (CT.Ticket, CT.Period, CT.Code) CT.Period links to PB.Period and CT.Code links to CU.Code
TicketCasings (TC.Ticket, TC.Casing, TC.Grade) TC.Ticket links to CT.Ticket, TC.Grade links to lookup GR.Grade
Grade (GR.Grade, GR.Summary)
Orders (BO.Order, BO.Period, BO.Code) BO.Period links to PB.Period and BO.Code links to BO.Code
OrderCasings(BOC.Order, BOC.Casing) BOC.Order links to BO.Order
The problem I am having is trying to get SUM()s across 2 different streams. I don't think it is possible.
If it makes any sense, here is the stored procedure I'm using.
The ticket totals are all correct, but the order totals are all wrong.
I think I need to use a temp table and populate it just like I do in the 4GL code.
Or at least use a subselect for the ticket totals. Hmmm.
Any ideas?
Richard.
I'm in the process of converting a series of reports from Sage Retrieve 4GL to MSSQL/Crystal.
The reports in Sage are built by code (i.e. read a row from the table, add values to temporary storage/totals), rather than SQL (the 4GL db is NOT SQL).
Here are the tables in use.
Head Offices (HO.Code, HO.Name)
Customers (CU.Code, CU.Name, CU.HOCode) CU.HOCode links to HO.Code
Banks (PB.Code, PB.Period) PB.Code links to CU.Code
Tickets (CT.Ticket, CT.Period, CT.Code) CT.Period links to PB.Period and CT.Code links to CU.Code
TicketCasings (TC.Ticket, TC.Casing, TC.Grade) TC.Ticket links to CT.Ticket, TC.Grade links to lookup GR.Grade
Grade (GR.Grade, GR.Summary)
Orders (BO.Order, BO.Period, BO.Code) BO.Period links to PB.Period and BO.Code links to BO.Code
OrderCasings(BOC.Order, BOC.Casing) BOC.Order links to BO.Order
The problem I am having is trying to get SUM()s across 2 different streams. I don't think it is possible.
If it makes any sense, here is the stored procedure I'm using.
The ticket totals are all correct, but the order totals are all wrong.
I think I need to use a temp table and populate it just like I do in the 4GL code.
Or at least use a subselect for the ticket totals. Hmmm.
Any ideas?
Richard.
CREATE PROCEDURE [dbo].[raq_SingleCustomerSterlingBankReport]
@Year int,
@Month int,
@Customer CHAR(15)
AS
DECLARE @Period INT
SELECT @Period = (@Year - 1900) * 12 + @Month
SELECT
-- Head Office
LTRIM(RTRIM(HO.CustomerCode)) AS [Head Office Code], HO.Name AS [Head Office], HO.AddressLines_1 AS [Head Office Address Line 1], HO.AddressLines_2 AS [Head Office Address Line 2], HO.AddressLines_3 AS [Head Office Address Line 3], HO.AddressLines_4 AS [Head Office Address Line 4], HO.Postcode AS [Head Office Postcode], HO.Telephone AS [Head Office Telephone], HO.Facsimile AS [Head Office Fax],
-- Customer
LTRIM(RTRIM(CU.CustomerCode)) AS [Customer Code], CU.Name AS [Customer], CU.AddressLines_1 AS [Customer Address Line 1], CU.AddressLines_2 AS [Customer Address Line 2], CU.AddressLines_3 AS [Customer Address Line 3], CU.AddressLines_4 AS [Customer Address Line 4], CU.Postcode AS [Customer Postcode], CU.Telephone AS [Customer Telephone], CU.Facsimile AS [Customer Fax],
-- Period Bank Totals
PB.Period, PB.BankClosedOn, PB.Accepted, PB.Ordered, PB.Purchased,
PB.SterlingAdjustments, PB.SterlingClose, PB.SterlingMovement, PB.SterlingOpen, PB.SterlingOrders, PB.SterlingPayments, PB.SterlingScrap, PB.SterlingTickets,
PB.UnitsAccepted, PB.UnitsAdjust, PB.UnitsClose, PB.UnitsCollected, PB.UnitsOpen, PB.UnitsRejected, PB.UnitsSupplied, PB.UnitsValueAccepted, PB.UnitsValueAdjust, PB.UnitsValueClose, PB.UnitsValueOpen, PB.UnitsValueRejected, PB.UnitsValueSupplied,
-- Shared Casing Size Code
ISNULL(BOC.CasingSizeCode, TC.CasingSizeCode) AS 'CasingSizeCode',
-- Collection Tickets
CT.TicketNumber,
COUNT(TC.UniqueID) AS [Casings Collected],
SUM(CASE WHEN ISNULL(AC.SummaryCode, '') = 'P' THEN 1 ELSE 0 END) AS [Premium Qty Accepted],
SUM(CASE WHEN ISNULL(AC.SummaryCode, '') = 'P' THEN TC.Price ELSE 0 END) AS [Premium Value Accepted],
SUM(CASE WHEN ISNULL(AC.SummaryCode, '') = 'S' THEN 1 ELSE 0 END) AS [Standard Qty Accepted],
SUM(CASE WHEN ISNULL(AC.SummaryCode, '') = 'S' THEN TC.Price ELSE 0 END) AS [Standard Value Accepted],
SUM(CASE WHEN ISNULL(AC.SummaryCode, '') = 'E' THEN 1 ELSE 0 END) AS [Euromould Qty Accepted],
SUM(CASE WHEN ISNULL(AC.SummaryCode, '') = 'E' THEN TC.Price ELSE 0 END) AS [Euromould Value Accepted],
SUM(CASE WHEN ISNULL(TC.RejectCode, '') <> '' THEN 1 ELSE 0 END) AS [Rejected Qty],
SUM(CASE WHEN ISNULL(TC.RejectCode, '') <> '' THEN - TC.Price ELSE 0 END) AS [Rejected Value],
-- Bank Orders
COUNT(BOC.UniqueID) AS [Casings Supplied],
SUM(CASE WHEN ISNULL(BOC.Grade, '') = 'P' THEN BOC.SuppliedFromStock ELSE 0 END) AS [Premium Qty Supplied],
SUM(CASE WHEN ISNULL(BOC.Grade, '') = 'P' THEN BOC.Value ELSE 0 END) AS [Premium Value Supplied],
SUM(CASE WHEN ISNULL(BOC.Grade, '') = 'S' THEN BOC.SuppliedFromStock ELSE 0 END) AS [Standard Qty Supplied],
SUM(CASE WHEN ISNULL(BOC.Grade, '') = 'S' THEN BOC.Value ELSE 0 END) AS [Standard Value Supplied],
SUM(CASE WHEN ISNULL(BOC.Grade, '') = 'E' THEN BOC.SuppliedFromStock ELSE 0 END) AS [Euromould Qty Supplied],
SUM(CASE WHEN ISNULL(BOC.Grade, '') = 'E' THEN BOC.Value ELSE 0 END) AS [Euromould Value Supplied]
FROM
-- Head Office / Customers
(
Customers HO
INNER JOIN
Customers CU
ON
HO.HeadOfficeFlag = 1 -- Is a Head Office
AND
CU.ShortName <> 'CLOSED' -- Is not closed
AND
CU.PayFrequency <> 6 -- Is not daily clearance
AND
(
CU.Bank = 2 -- Sterling Bank
OR
CU.Bank = 4 -- Mixed Bank
)
AND
HO.CustomerCode = CU.HeadOfficeCode
AND
CU.CustomerCode = @Customer
)
-- Period Bank Totals
LEFT OUTER JOIN
PeriodBanks PB
ON
PB.CustomerCode = CU.CustomerCode
AND
PB.Period = @Period
-- Merged Orders and Tickets
LEFT OUTER JOIN
(
(
BankOrders BO
INNER JOIN
BankOrdersCasings BOC
ON
BO.UniqueID = BOC.ParentID
AND
BO.OrderPeriod = @Period
)
FULL OUTER JOIN
(
CollectionTickets CT
INNER JOIN
TicketCasings TC
ON
CT.TicketStatus = 11 -- Comleted Ticket
AND
CT.HandledAsScrap <> 1 -- Not a scrap ticket
AND
CT.UniqueID = TC.ParentID
AND
TC.IOCCode <> 'N' -- Casing was received
AND
TC.CasingBank <> 1 -- Casing was not unit bank (mixed bank only)
AND
CT.TicketPeriod = @Period
LEFT OUTER JOIN
AcceptanceCodes AC
ON
TC.AcceptCode = AC.AcceptCode
)
ON
BO.CustomerCode = CT.CustomerCode
AND
BO.OrderPeriod = CT.TicketPeriod
AND
BOC.CasingSizeCode = TC.CasingSizeCode
)
ON
CU.CustomerCode = ISNULL(BO.CustomerCode, CT.CustomerCode)
-- AND
-- PB.Period = ISNULL(BO.OrderPeriod, CT.TicketPeriod)
GROUP BY
CU.CustomerCode, CU.AddressLines_1, CU.AddressLines_2, CU.AddressLines_3, CU.AddressLines_4, CU.CustomerCode, CU.Facsimile, CU.Name, CU.Postcode, CU.Telephone,
HO.CustomerCode, HO.AddressLines_1, HO.AddressLines_2, HO.AddressLines_3, HO.AddressLines_4, HO.CustomerCode, HO.Facsimile, HO.Name, HO.Postcode, HO.Telephone,
PB.Accepted, PB.BankClosedOn, PB.Ordered, PB.Period, PB.Purchased,
PB.SterlingAdjustments, PB.SterlingClose, PB.SterlingMovement, PB.SterlingOpen, PB.SterlingOrders, PB.SterlingPayments, PB.SterlingScrap, PB.SterlingTickets,
PB.UnitsAccepted, PB.UnitsAdjust, PB.UnitsClose, PB.UnitsCollected, PB.UnitsOpen, PB.UnitsRejected, PB.UnitsSupplied, PB.UnitsValueAccepted, PB.UnitsValueAdjust, PB.UnitsValueClose, PB.UnitsValueOpen, PB.UnitsValueRejected, PB.UnitsValueSupplied,
CT.TicketNumber,
TC.CasingSizeCode,
BOC.CasingSizeCode
ORDER BY
CasingSizeCode,
TicketNumber
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad you were able to get it working....
: )
: )
ASKER
The original 4gl app has no SQL. Instead, I had to use a temp table to gather all the data for the reporting. This takes around 10 mins.
With the new SQL db and the above SP, I can get the whole job done in under 60 seconds. And that includes using PHP to talk to Crystal Reports to produce PDF documents!
These last 2 weeks have given me a new lease of life with Crystal. I'm getting to like it.
With the new SQL db and the above SP, I can get the whole job done in under 60 seconds. And that includes using PHP to talk to Crystal Reports to produce PDF documents!
These last 2 weeks have given me a new lease of life with Crystal. I'm getting to like it.
ASKER
I was trying to sum AND merge data at the same time.
I actually wanted to merge pre-summed data.
Here is the working stored procedure.
Thanks for looking.
Open in new window