I received some wonderful assistance from a fellow SO user yesterday (see here ) which allowed me to make a great deal of progress towards my goal. Now I'm attempting to establish whether the magical addition that was suggested can be embedded into an existing query that is producing xml output.
The existing query is as follows:
PROCEDURE [dbo].[CreateLandingPurchaseOrderDetails]
-- Add the parameters for the stored procedure here
@startDate DATE,
@endDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT (SELECT
Contacts.ContactId AS '@ContactId',
VesselOwner AS '@Owner',
FORMAT(SUM(LandingDetails.Quantity * LandingDetails.UnitPrice), 'N2') AS '@Owed',
SocietyMemberships.WeeklyDeductionRate AS '@WeeklyDeductionRate',
SocietyMemberships.FromMinimumReturn AS '@FromMinimumReturn',
Deductions.DeductionRate AS '@DeductionRate',
(SELECT DISTINCT
ld1.ProductId AS '@ProductId',
FORMAT(AVG(ld1.UnitPrice), 'N2') AS '@Cost',
FORMAT(SUM(ld1.Quantity), 'N2') AS '@Quantity'
FROM LandingDetails ld1
INNER JOIN dbo.LandingHeaders lh1
ON ld1.LandingId = lh1.LandingId
WHERE Posted = 0
AND lh1.VesselOwner = LandingHeaders.VesselOwner
GROUP BY ld1.ProductId
FOR XML PATH ('Products'), TYPE)
FROM dbo.LandingDetails
INNER JOIN dbo.LandingHeaders
ON LandingDetails.LandingId = LandingHeaders.LandingId
INNER JOIN dbo.Vessels
ON LandingHeaders.VesselId = Vessels.VesselId
INNER JOIN dbo.Contacts
ON Vessels.OwnerId = Contacts.ContactId
INNER JOIN dbo.SocietyMemberships
ON Contacts.SocietyId = SocietyMemberships.SocietyId
INNER JOIN dbo.Deductions
ON Vessels.DeductionId = Deductions.DeductionId
WHERE LandingHeaders.Posted = 0
AND LandingDate1 BETWEEN @startDate AND @endDate
GROUP BY ContactId,
VesselOwner,
SocietyMemberships.WeeklyDeductionRate,
SocietyMemberships.FromMinimumReturn,
Deductions.DeductionRate
ORDER BY ContactId
FOR XML PATH ('Owner'), TYPE)
FOR XML PATH ('PurchaseOrders'), TYPE
END
This Produces xml output along these lines;
<PurchaseOrders>
<Owner ContactId="39" Owner="Paul Joy" Owed="1,609.39" WeeklyDeductionRate="10.00" FromMinimumReturn="110.00" DeductionRate="0.0150">
<Products ProductId="33" Cost="5.00" Quantity="0.40" />
<Products ProductId="34" Cost="1.80" Quantity="0.90" />
<Products ProductId="41" Cost="2.30" Quantity="1.30" />
I would like to add one more attribute to the Owner element ( TotalDeductions ). From the previous question that I asked I can see how it's possible for the sql to calculate the information that I was after in order to produce the Total deductions field. However adding that logic to the FOR XML query has proved elusive. I'm loathe to say it can't be done on the grounds that it seems there is little if anything that can't be done in SQL with a little lateral thought.
If I simply cut and paste the case part of the query to my FOR XML query the compiler points out that Owed isn't a valid name. That I understand. However if I simply embed the owner part of the for xml in the following fashion;
WITH cte AS (
'embed the part of the FOR XML producing the owner element here
)
SELECT
ContactId,
Owed,
WeeklyDeductionRate,
FromMinimumReturn,
DeductionRate,
CASE
WHEN Owed - (Owed * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn
THEN Owed * DeductionRate + WeeklyDeductionRate
ELSE Owed * DeductionRate END
AS TotalDeductions
FROM cte
Then leaving aside the compile errors for the moment I'd not be producing the xml that I'm after.
Have I finally found something that actually can't be done in SQL, or have I simply missed the obvious 'lateral thought' that I should have had?
Thanks
I think inserting the original case expression at the correct place should work. Try this:
PROCEDURE [dbo].[CreateLandingPurchaseOrderDetails]
-- Add the parameters for the stored procedure here
@startDate DATE, @endDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT (
SELECT
Contacts.ContactId AS '@ContactId',
LandingHeaders.VesselOwner AS '@Owner',
FORMAT(SUM(LandingDetails.Quantity * LandingDetails.UnitPrice), 'N2') AS '@Owed',
SocietyMemberships.WeeklyDeductionRate AS '@WeeklyDeductionRate',
SocietyMemberships.FromMinimumReturn AS '@FromMinimumReturn',
Deductions.DeductionRate AS '@DeductionRate',
CASE
WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn
THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate
END AS '@TotalDeductions',
(SELECT DISTINCT
ld1.ProductId AS '@ProductId',
FORMAT(AVG(ld1.UnitPrice), 'N2') AS '@Cost',
FORMAT(SUM(ld1.Quantity), 'N2') AS '@Quantity'
FROM LandingDetails ld1
INNER JOIN dbo.LandingHeaders lh1
ON ld1.LandingId = lh1.LandingId
WHERE Posted = 0
AND lh1.VesselOwner = LandingHeaders.VesselOwner
GROUP BY ld1.ProductId
FOR XML PATH ('Products'), TYPE)
FROM dbo.LandingDetails
INNER JOIN dbo.LandingHeaders
ON LandingDetails.LandingId = LandingHeaders.LandingId
INNER JOIN dbo.Vessels
ON LandingHeaders.VesselId = Vessels.VesselId
INNER JOIN dbo.Contacts
ON Vessels.OwnerId = Contacts.ContactId
INNER JOIN dbo.SocietyMemberships
ON Contacts.SocietyId = SocietyMemberships.SocietyId
INNER JOIN dbo.Deductions
ON Vessels.DeductionId = Deductions.DeductionId
WHERE LandingHeaders.Posted = 0
AND LandingDate1 BETWEEN @startDate AND @endDate
GROUP BY ContactId,
LandingHeaders.VesselOwner,
SocietyMemberships.WeeklyDeductionRate,
SocietyMemberships.FromMinimumReturn,
Deductions.DeductionRate
ORDER BY ContactId
FOR XML PATH ('Owner'), TYPE)
FOR XML PATH ('PurchaseOrders'), TYPE
END
The query can probably be improved but without table definitions and some sample data it's a bit difficult to test it.
Check out this SQL Fiddle for a slightly tweaked version that should give the same result.