G
Guest
I have the following stored procedure in an sql database
------------------------------------
CREATE PROCEDURE zspQuoteSummary
@dStart DateTime,
@dEnd DateTime
AS
SELECT qtmast.fstatus,
qtmast.festimator,
qtmast.fcompany,
qtmast.fquoteno AS Quote,
qtmast.frevno AS Rev,
qtmast.fquotedate,
qtitem.fenumber AS ItemNumber,
qtitem.fpartno AS Part,
qtitem.fpartrev AS PartRev,
qtpest.fmatlcost + qtpest.ftoolcost + qtpest.flabcost + qtpest.fovhdcost +
qtpest.fsubcost + qtpest.fothrcost + qtpest.fsetupcost AS TotalCost,
qtitem.funetprice AS UnitPrice,
qtitem.festqty AS Qty,
qtitem.funetprice*qtitem.festqty AS Extended,
GM =
CASE
WHEN (qtitem.funetprice*qtitem.festqty) <> 0 THEN
((qtitem.funetprice*qtitem.festqty) - (qtpest.fmatlcost + qtpest.ftoolcost +
qtpest.flabcost + qtpest.fovhdcost + qtpest.fsubcost + qtpest.fothrcost +
qtpest.fsetupcost) )/(qtitem.funetprice*qtitem.festqty)
ELSE
0
END,
EAU =
CASE
WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
SUBSTRING(qtitem.fdescmemo,5,10)
ELSE
0
END,
EAUExtended =
CASE
WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice
ELSE
0
END,
EAUGM =
CASE
WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
CASE
WHEN SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice <> 0 THEN
((SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice) - (qtpest.fmatlcost +
qtpest.ftoolcost + qtpest.flabcost + qtpest.fovhdcost + qtpest.fsubcost +
qtpest.fothrcost + qtpest.fsetupcost)) / (SUBSTRING(qtitem.fdescmemo,5,10)*
qtitem.funetprice)
ELSE
0
END
ELSE
0
END,
'For ' + @dStart + ' thru ' + @dEnd AS Parameters
INTO ztmpQuoteSummary
FROM qtmast INNER JOIN (qtitem INNER JOIN qtpest ON
(qtitem.fpartrev=qtpest.fcpartrev) AND (qtitem.fpartno=qtpest.fpartno) AND
(qtitem.finumber=qtpest.finumber) AND (qtitem.fenumber=qtpest.fenumber) AND
(qtitem.fquoteno=qtpest.fquoteno)) ON qtmast.fquoteno=qtitem.fquoteno
WHERE (((qtmast.fquotedate)>= @dStart And (qtmast.fquotedate)<=@dEnd))
ORDER BY qtmast.fcompany, qtmast.fquoteno, qtmast.frevno;
GO
------------------------------------
CREATE PROCEDURE zspQuoteSummary
@dStart DateTime,
@dEnd DateTime
AS
SELECT qtmast.fstatus,
qtmast.festimator,
qtmast.fcompany,
qtmast.fquoteno AS Quote,
qtmast.frevno AS Rev,
qtmast.fquotedate,
qtitem.fenumber AS ItemNumber,
qtitem.fpartno AS Part,
qtitem.fpartrev AS PartRev,
qtpest.fmatlcost + qtpest.ftoolcost + qtpest.flabcost + qtpest.fovhdcost +
qtpest.fsubcost + qtpest.fothrcost + qtpest.fsetupcost AS TotalCost,
qtitem.funetprice AS UnitPrice,
qtitem.festqty AS Qty,
qtitem.funetprice*qtitem.festqty AS Extended,
GM =
CASE
WHEN (qtitem.funetprice*qtitem.festqty) <> 0 THEN
((qtitem.funetprice*qtitem.festqty) - (qtpest.fmatlcost + qtpest.ftoolcost +
qtpest.flabcost + qtpest.fovhdcost + qtpest.fsubcost + qtpest.fothrcost +
qtpest.fsetupcost) )/(qtitem.funetprice*qtitem.festqty)
ELSE
0
END,
EAU =
CASE
WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
SUBSTRING(qtitem.fdescmemo,5,10)
ELSE
0
END,
EAUExtended =
CASE
WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice
ELSE
0
END,
EAUGM =
CASE
WHEN SUBSTRING(qtitem.fdescmemo,1,4) = UPPER('EAU:') THEN
CASE
WHEN SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice <> 0 THEN
((SUBSTRING(qtitem.fdescmemo,5,10)* qtitem.funetprice) - (qtpest.fmatlcost +
qtpest.ftoolcost + qtpest.flabcost + qtpest.fovhdcost + qtpest.fsubcost +
qtpest.fothrcost + qtpest.fsetupcost)) / (SUBSTRING(qtitem.fdescmemo,5,10)*
qtitem.funetprice)
ELSE
0
END
ELSE
0
END,
'For ' + @dStart + ' thru ' + @dEnd AS Parameters
INTO ztmpQuoteSummary
FROM qtmast INNER JOIN (qtitem INNER JOIN qtpest ON
(qtitem.fpartrev=qtpest.fcpartrev) AND (qtitem.fpartno=qtpest.fpartno) AND
(qtitem.finumber=qtpest.finumber) AND (qtitem.fenumber=qtpest.fenumber) AND
(qtitem.fquoteno=qtpest.fquoteno)) ON qtmast.fquoteno=qtitem.fquoteno
WHERE (((qtmast.fquotedate)>= @dStart And (qtmast.fquotedate)<=@dEnd))
ORDER BY qtmast.fcompany, qtmast.fquoteno, qtmast.frevno;
GO