begin transaction
;WITH
-- -----------------------------------------------
-- Calcualtes sum of active taxes
-- for each of Tax Groups
-- -----------------------------------------------
Taxes as
(
SELECT tg.nTaxGroupID as nTaxGroupID,
tg.cName as TaxGroupName,
Sum(isNull(ti.nPercent,0)) as TaxPercent
FROM TaxGroups tg
LEFT JOIN TaxGroupsToTaxItems tgi on tg.nTaxGroupID = tgi.nTaxGroupID
LEFT JOIN TaxItems ti on tgi.nTaxItemID = ti.nTaxItemID
WHERE tg.lActive = 1
GROUP BY tg.nTaxGroupID,
tg.cName
),
-- -----------------------------------------------
-- calculates total cost(?) per Job
-- -----------------------------------------------
JDS as
(
SELECT nJobHeaderID,
Sum(nAmount * nQuantity) as TotalAmount
FROM JobDetail
GROUP BY nJobHeaderID
),
-- -----------------------------------------------
-- concatenates all Customer's phone numbers
-- into one string (LF delimited)
-- -----------------------------------------------
P as
(
SELECT nCustomerID,
Stuff(PhoneInfo, 1, 1, '') AS PhoneInfo
-- Personally I prefer Stuff for removing first few characters,
-- but substring is O.K. too. There's no any performance advantage
-- for one or another, as I know
-- p.s. Substring(<exp>,2,<somebignumbereg4000>) would do the job too
-- you don't need to calculate Len(<exp>)
FROM ( SELECT InnerData.nCustomerID,
( SELECT Char(10) + P.cPhoneNumber + ' - ' + rTrim(PT.cType)
-- Char(10) - LineFeed or ManualLineBreak
-- should do the job, probably you don't need CRLF
FROM PhoneNumbers P
INNER JOIN PhoneType PT on P.nPhoneTypeID = PT.nPhoneTypeID
WHERE P.nCustomerID = InnerData.nCustomerID
FOR XML PATH('') -- this does string concatenation
) as PhoneInfo
FROM ( SELECT DISTINCT nCustomerID
FROM PhoneNumbers
) as InnerData
) as OuterData
)
SELECT J.nRouteHeaderID as nRouteHeaderID,
JH.nCustomerID as nCustomerID,
JH.nJobHeaderID as JobHeaderID,
J.ScheduleTime as ScheduleTime,
JH.cContact as Contact,
J.SplitAmount as SplitAmount,
vJ.cAddr1 as Address1,
vJ.cAddr2 as Address2,
vJ.cCity as City,
vJ.cState as State,
vJ.cZip as Zip,
vJ.JobDescription as JobDescription,
JH.nDiscountPercent/100 * JDS.TotalAmount
as DiscountAmount,
JH.nAmount as JobTotal,
T.TaxGroupName as TaxType,
T.TaxPercent as TaxPercent,
J.PermanentNote as PermanentNote,
P.PhoneInfo as PhoneInfo
FROM @Jobs J
INNER JOIN dbo.JobHeader JH on J.nJobHeaderID = JH.nJobHeaderID
LEFT JOIN v_JobAddress vJ on J.nJobHeaderID = vJ.nJobHeaderID
LEFT JOIN Taxes T on JH.nTaxGroupID = T.nTaxGroupID
LEFT JOIN JDS JDS on JH.nJobHeaderID = JDS.nJobHeaderID
LEFT JOIN P P on JH.[nCustomerID] = P.[nCustomerID]
ORDER BY J.IdField -- Used for order but not retrieved
-- Job Details
select J.nJobHeaderID as JobID, JD.nQuantity as Quantity, JD.cDescription as Description, JD.nAmount as Amount
from @Jobs J
INNER JOIN dbo.JobDetail JD on J.nJobHeaderID = JD.nJobHeaderID
-- All notes combined
select J.nRouteHeaderID, NN.mNote as RouteNote from @Jobs J
INNER JOIN (select N.nID as nCustomerID, mNote from Notes N
INNER JOIN NoteCategories NC on N.nNoteCategoryID = NC.nNoteCategoryID
and NC.cDescription = 'Customer') NN
ON J.nCustomerID = NN.nCustomerID
UNION ALL
select J.nRouteHeaderID, J.PermanentNote as RouteNote from @Jobs J
where J.PermanentNote IS NOT NULL and cast(J.PermanentNote as varchar(max)) <> ''
UNION ALL
select J.nRouteHeaderID, NN.mNote as RouteNote from @Jobs J
INNER JOIN (select N.nID as nJobHeaderID, mNote from Notes N
INNER JOIN NoteCategories NC on N.nNoteCategoryID = NC.nNoteCategoryID and NC.cDescription = 'JobHeader') NN
ON J.nJobHeaderID = NN.nJobHeaderID
commit transaction
