| Author |
 Topic  |
|
|
Spica66
Starting Member
23 Posts |
 Posted - 08/06/2012 : 11:11:02
|
I have the following query in a stored procedure:
ALTER PROCEDURE [dbo].[frc_Top25byFRCcode]
@startDate AS DATETIME = null,
@endDate AS DATETIME = null,
@NFClaimStatus int = null
AS
DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @NFCS INT
SET @SD = @startDate
SET @ED = @endDate
SET @NFCS = @NFClaimStatus
BEGIN
SELECT
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,
((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT
FROM dbo.NFWARRANTYCLAIMS AS c
INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = @NFCS AND (transDate BETWEEN @SD AND @ED)
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID
INNER JOIN NFWARRANTYTABLE AS t ON t.WARRANTYNUMBER = c.WARRANTYNUMBER
INNER JOIN (
SELECT *
FROM NFWARRANTYCORRECTIONTABLE
WHERE NFCORRECTIONSTATUS <> 7
)
AS w ON w.CLAIMID = h.NFCLAIMID
INNER JOIN NFWARRANTYPARTS AS p ON p.NFWARRANTYPARTID = w.NFWARRANTYPARTID
ORDER BY w.WCCDESCRIPTION
END
It generates the table shown below:
WCC Description Amount
02.09.07 - Tires 170.00
02.09.07 - Tires 350.00
02.09.07 - Tires 285.00
02.09.07 - Tires 190.00
03.19.08 - Paint 130.00
03.19.08 - Paint 280.00
03.19.08 - Paint 285.00
06.01.05 - Sink 150.00
06.01.05 - Sink 250.00
06.01.05 - Sink 85.00
06.01.05 - Sink 150.00
I am trying to get the SQL statement to SUM the amounts for the grouped WCC Description and sort based on the totals for the group, highest total first:
02.09.07 - Tires 805.00
03.19.08 - Paint 695.00
06.01.05 - Sink 635.00
I thought I could SUM(AMOUNT) and GROUP BY(WCCDESCRIPTION), but I can't figure out where in my code to do those two things. No matter what combination I try, it gives me a Syntax Error.
ALTER PROCEDURE [dbo].[frc_Top25byFRCcode]
@startDate AS DATETIME = null,
@endDate AS DATETIME = null,
@NFClaimStatus int = null
AS
DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @NFCS INT
SET @SD = @startDate
SET @ED = @endDate
SET @NFCS = @NFClaimStatus
BEGIN
SELECT
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,
Select SUM(Amount) From
(((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT) x
FROM dbo.NFWARRANTYCLAIMS AS c
INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = @NFCS AND (transDate BETWEEN @SD AND @ED)
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID
INNER JOIN NFWARRANTYTABLE AS t ON t.WARRANTYNUMBER = c.WARRANTYNUMBER
INNER JOIN (
SELECT *
FROM NFWARRANTYCORRECTIONTABLE
WHERE NFCORRECTIONSTATUS <> 7
)
AS w ON w.CLAIMID = h.NFCLAIMID
INNER JOIN NFWARRANTYPARTS AS p ON p.NFWARRANTYPARTID = w.NFWARRANTYPARTID
GROUP BY w.WCCDESCRIPTION
END
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 08/06/2012 : 11:19:40
|
ALTER PROCEDURE [dbo].[frc_Top25byFRCcode]
@startDate AS DATETIME = null,
@endDate AS DATETIME = null,
@NFClaimStatus int = null
AS
DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @NFCS INT
SET @SD = @startDate
SET @ED = @endDate
SET @NFCS = @NFClaimStatus
BEGIN
SELECT
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,
SUM(ROUND(ISNULL(i.PRICE*i.QTY,0),2)
+
ROUND(ISNULL(AMOUNT,0),2)
+ (W.HOURS*W.PRICE)) as AMOUNT
FROM dbo.NFWARRANTYCLAIMS AS c
INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = @NFCS AND (transDate BETWEEN @SD AND @ED)
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID
INNER JOIN NFWARRANTYTABLE AS t ON t.WARRANTYNUMBER = c.WARRANTYNUMBER
INNER JOIN (
SELECT *
FROM NFWARRANTYCORRECTIONTABLE
WHERE NFCORRECTIONSTATUS <> 7
)
AS w ON w.CLAIMID = h.NFCLAIMID
INNER JOIN NFWARRANTYPARTS AS p ON p.NFWARRANTYPARTID = w.NFWARRANTYPARTID
INNER JOIN NFWARRANTYCLAIMSITEMS i
ON i.CLAIMID = w.CLAIMID
AND i.CORRECTIONCODE = w.CORRECTIONCODE
INNER JOIN NFWARRANTYMISCCOSTS m
ON m.CLAIMID = w.CLAIMID
AND m.CORRECTIONCODE = w.CORRECTIONCODE
GROUP BY w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION
ORDER BY AMOUNT DESC
END
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Spica66
Starting Member
23 Posts |
Posted - 08/06/2012 : 11:32:18
|
Wow!! Thank you for that quick response. I was just coming back to add that I was also hoping to get the count of the rows that made up the group, and you had already posted. Any thoughts on getting the counts as well?
|
|
|
|
Spica66
Starting Member
23 Posts |
Posted - 08/06/2012 : 11:59:53
|
I am afraid that your proposed code did not work. There are $28,000 worth of Paint Repairs, but your query returned $344,000.
The amount calculation is as follows:
((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT
Some of that code disappeared from your response.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 08/06/2012 : 12:24:10
|
quote:
Originally posted by Spica66
I am afraid that your proposed code did not work. There are $28,000 worth of Paint Repairs, but your query returned $344,000.
The amount calculation is as follows:
((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT
Some of that code disappeared from your response.
nope
i've replaced subqueries with joins. see the full suggestion and last two joins
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 08/06/2012 : 12:25:17
|
quote:
Originally posted by Spica66
I am afraid that your proposed code did not work. There are $28,000 worth of Paint Repairs, but your query returned $344,000.
The amount calculation is as follows:
((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT
Some of that code disappeared from your response.
also reg your total issue i cant suggest anything unless i see how data is. so if you can post the data from tables then i may be able to help
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Is it possible to sort by Group Totals
