August 11, 2014 at 2:51 pm
hi,
I apologize in advance for not following "Forum Etiquette: How to post data/code on a forum to get the best help."
The view I am trying to create (crvKCLPRInsJob) is using another view (brvPRInsJob).
I can provide a sample data, that's easy. I couldn't figure out how to get all the other info required.
I created the view below.
I created the following custom fields.
IncCode723Hours
IncCode748Hours
GrossEarn723
GrossEarn748
can I reuse the above fields to do more calculation within the same select statement? See below.
TotalHours=IncCode723Hours+IncCode748Hours,
TotalGross=GrossEarn723+GrossEarn748,
EligibleEarningsBenefitsIncTax=case when TotalHours = 0 then 0 else ((crvKCLPRInsJob.Hours/TotalHours)*TotalGross)
below is the complete script.
USE [VPSample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view [dbo].[crvKCLPRInsJob]
as
SELECT brvPRInsJob.PRCo, brvPRInsJob.PRGroup, brvPRInsJob.Employee, brvPRInsJob.TimeCardEarn, brvPRInsJob.AddonEarn, brvPRInsJob.Hours,
brvPRInsJob.Rate, brvPRInsJob.InsCode, brvPRInsJob.Job, brvPRInsJob.JCCo, brvPRInsJob.PREndDate, JCJM.Description,
IncCode723Hours=SUM(case when brvPRInsJob.InsCode='723' then brvPRInsJob.Hours else 0 end),
IncCode748Hours=SUM(case when brvPRInsJob.InsCode='748' then brvPRInsJob.Hours else 0 end),
GrossEarn723=SUM(case when brvPRInsJob.InsCode='723' then brvPRInsJob.TimeCardEarn else 0 end),
GrossEarn748=SUM(case when brvPRInsJob.InsCode='748' then brvPRInsJob.TimeCardEarn else 0 end),
TotalHours=IncCode723Hours+IncCode748Hours,
TotalGross=GrossEarn723+GrossEarn748,
EligibleEarningsBenefitsIncTax=case when TotalHours = 0 then 0 else ((crvKCLPRInsJob.Hours/TotalHours)*TotalGross)
FROM brvPRInsJob brvPRInsJob
LEFT OUTER JOIN PREH PREH ON (brvPRInsJob.PRCo=PREH.PRCo) AND (brvPRInsJob.Employee=PREH.Employee)
LEFT OUTER JOIN JCJM JCJM on brvPRInsJob.Job = JCJM.Job
GROUP BY brvPRInsJob.PRCo, brvPRInsJob.PRGroup, brvPRInsJob.Employee, brvPRInsJob.TimeCardEarn, brvPRInsJob.AddonEarn, brvPRInsJob.Hours, JCJM.Description,
brvPRInsJob.Rate, brvPRInsJob.InsCode, brvPRInsJob.Job, brvPRInsJob.JCCo, brvPRInsJob.PREndDate
GO
August 11, 2014 at 3:32 pm
Not in T-SQL. What you're trying to do is inherit the alias, which doesn't work that way. What you want to do is put your first calculations into a subselect, then perform further stuff on top. Example:
SELECT
Quantity,
Price,
FullValue = Quantity*Price
FROM
(SELECT
PurchaseQuantity*StockSplits AS Quantity,
(OriginalPrice+MarkUp)*PAR AS Price
FROM
Sometable
) AS drv
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 11, 2014 at 3:55 pm
Using a CTE is basically the same but in different order. I prefer this approach but it's just personal preference.
ALTER VIEW [dbo].[crvKCLPRInsJob]
AS
WITH CTE AS(
SELECT
brvPRInsJob.PRCo
,brvPRInsJob.PRGroup
,brvPRInsJob.Employee
,brvPRInsJob.TimeCardEarn
,brvPRInsJob.AddonEarn
,brvPRInsJob.Hours
,brvPRInsJob.Rate
,brvPRInsJob.InsCode
,brvPRInsJob.Job
,brvPRInsJob.JCCo
,brvPRInsJob.PREndDate
,JCJM.Description
,IncCode723Hours = SUM(CASE
WHEN brvPRInsJob.InsCode = '723'
THEN brvPRInsJob.Hours
ELSE 0
END)
,IncCode748Hours = SUM(CASE
WHEN brvPRInsJob.InsCode = '748'
THEN brvPRInsJob.Hours
ELSE 0
END)
,GrossEarn723 = SUM(CASE
WHEN brvPRInsJob.InsCode = '723'
THEN brvPRInsJob.TimeCardEarn
ELSE 0
END)
,GrossEarn748 = SUM(CASE
WHEN brvPRInsJob.InsCode = '748'
THEN brvPRInsJob.TimeCardEarn
ELSE 0
END)
FROM brvPRInsJob brvPRInsJob
LEFT OUTER JOIN PREH PREH ON (brvPRInsJob.PRCo = PREH.PRCo)
AND (brvPRInsJob.Employee = PREH.Employee)
LEFT OUTER JOIN JCJM JCJM ON brvPRInsJob.Job = JCJM.Job
GROUP BY brvPRInsJob.PRCo
,brvPRInsJob.PRGroup
,brvPRInsJob.Employee
,brvPRInsJob.TimeCardEarn
,brvPRInsJob.AddonEarn
,brvPRInsJob.Hours
,JCJM.Description
,brvPRInsJob.Rate
,brvPRInsJob.InsCode
,brvPRInsJob.Job
,brvPRInsJob.JCCo
,brvPRInsJob.PREndDate
)
SELECT PRCo
,PRGroup
,Employee
,TimeCardEarn
,AddonEarn
,Hours
,Rate
,InsCode
,Job
,JCCo
,PREndDate
,Description
,IncCode723Hours
,IncCode748Hours
,GrossEarn723
,GrossEarn748
,TotalHours = IncCode723Hours + IncCode748Hours
,TotalGross = GrossEarn723 + GrossEarn748
,EligibleEarningsBenefitsIncTax = CASE
WHEN (IncCode723Hours + IncCode748Hours) = 0
THEN 0
ELSE ((crvKCLPRInsJob.Hours / (IncCode723Hours + IncCode748Hours)) * (GrossEarn723 + GrossEarn748))
END
FROM CTE
August 12, 2014 at 6:50 am
thank you guys for your quick response.
If I create the CTE, Do you guys know if the CTE will work in SAP Crystal reports?
August 13, 2014 at 11:55 am
Luis Cazares (8/11/2014)
Using a CTE is basically the same but in different order. I prefer this approach but it's just personal preference.
ALTER VIEW [dbo].[crvKCLPRInsJob]
AS
WITH CTE AS(
SELECT
brvPRInsJob.PRCo
,brvPRInsJob.PRGroup
,brvPRInsJob.Employee
,brvPRInsJob.TimeCardEarn
,brvPRInsJob.AddonEarn
,brvPRInsJob.Hours
,brvPRInsJob.Rate
,brvPRInsJob.InsCode
,brvPRInsJob.Job
,brvPRInsJob.JCCo
,brvPRInsJob.PREndDate
,JCJM.Description
,IncCode723Hours = SUM(CASE
WHEN brvPRInsJob.InsCode = '723'
THEN brvPRInsJob.Hours
ELSE 0
END)
,IncCode748Hours = SUM(CASE
WHEN brvPRInsJob.InsCode = '748'
THEN brvPRInsJob.Hours
ELSE 0
END)
,GrossEarn723 = SUM(CASE
WHEN brvPRInsJob.InsCode = '723'
THEN brvPRInsJob.TimeCardEarn
ELSE 0
END)
,GrossEarn748 = SUM(CASE
WHEN brvPRInsJob.InsCode = '748'
THEN brvPRInsJob.TimeCardEarn
ELSE 0
END)
FROM brvPRInsJob brvPRInsJob
LEFT OUTER JOIN PREH PREH ON (brvPRInsJob.PRCo = PREH.PRCo)
AND (brvPRInsJob.Employee = PREH.Employee)
LEFT OUTER JOIN JCJM JCJM ON brvPRInsJob.Job = JCJM.Job
GROUP BY brvPRInsJob.PRCo
,brvPRInsJob.PRGroup
,brvPRInsJob.Employee
,brvPRInsJob.TimeCardEarn
,brvPRInsJob.AddonEarn
,brvPRInsJob.Hours
,JCJM.Description
,brvPRInsJob.Rate
,brvPRInsJob.InsCode
,brvPRInsJob.Job
,brvPRInsJob.JCCo
,brvPRInsJob.PREndDate
)
SELECT PRCo
,PRGroup
,Employee
,TimeCardEarn
,AddonEarn
,Hours
,Rate
,InsCode
,Job
,JCCo
,PREndDate
,Description
,IncCode723Hours
,IncCode748Hours
,GrossEarn723
,GrossEarn748
,TotalHours = IncCode723Hours + IncCode748Hours
,TotalGross = GrossEarn723 + GrossEarn748
,EligibleEarningsBenefitsIncTax = CASE
WHEN (IncCode723Hours + IncCode748Hours) = 0
THEN 0
ELSE ((crvKCLPRInsJob.Hours / (IncCode723Hours + IncCode748Hours)) * (GrossEarn723 + GrossEarn748))
END
FROM CTE
thank you Luis I was able to create the view. Its not working the way I think it should or want it to. I have the following data below.
I am pulling all the data into Crystal reports. I am getting individual totals for InsCode 723 and 748
example: if we look at employee # 12, in Crystal Reports I am getting one EligibleEarningsBenefitsIncTax total for each line. so Employee #12 has 6 lines therefore I will get 6 totals. I only want one total. how can i do this?
this might be a question for crystal reports experts but i am not sure which section to post this question into.
INSERT INTO #crvKCLPRInsJob
(PRGroup, PRCo, Emp, InsCode, TimeCardEarn, Hours, Rate, Job, PREndDate, IncCode723Hours, IncCode748Hours, GrossEarn723, GrossEarn748, TotalHours, TotalGross, EligibleEarningsBenefitsIncTax)
SELECT '2' '1' '12' '723' '189.91' '0' '0.0455' '' '41826' '0' '0' '189.91' '0' '0' '189.91' '0' UNION ALL
SELECT '2' '1' '12' '723' '297.41' '0' '0.0455' '' '41833' '0' '0' '297.41' '0' '0' '297.41' '0' UNION ALL
SELECT '2' '1' '12' '723' '206.76' '6' '0.0455' '115 - 85' '41826' '6' '0' '206.76' '0' '6' '206.76' '206.76' UNION ALL
SELECT '2' '1' '12' '748' '310.14' '9' '0.1831' '115 - 85' '41833' '0' '9' '0' '310.14' '9' '310.14' '310.14' UNION ALL
SELECT '2' '1' '12' '748' '895.96' '26' '0.1831' '115 - 85' '41826' '0' '26' '0' '895.96' '26' '895.96' '895.96' UNION ALL
SELECT '2' '1' '12' '723' '1619.62' '43' '0.0455' '115 - 85' '41833' '43' '0' '1619.62' '0' '43' '1619.62' '1619.62' UNION ALL
SELECT '2' '1' '14' '723' '195.33' '0' '0.0455' '' '41826' '0' '0' '195.33' '0' '0' '195.33' '0' UNION ALL
SELECT '2' '1' '14' '723' '283.97' '0' '0.0455' '' '41833' '0' '0' '283.97' '0' '0' '283.97' '0' UNION ALL
SELECT '2' '1' '14' '723' '250.51' '6.5' '0.0455' '115 - 85' '41833' '6.5' '0' '250.51' '0' '6.5' '250.51' '250.51' UNION ALL
SELECT '2' '1' '14' '748' '1136.94' '28.5' '0.1831' '115 - 85' '41826' '0' '28.5' '0' '1136.94' '28.5' '1136.94' '1136.94' UNION ALL
SELECT '2' '1' '14' '748' '1464.53' '36.5' '0.1831' '115 - 85' '41833' '0' '36.5' '0' '1464.53' '36.5' '1464.53' '1464.53' UNION ALL
SELECT '2' '1' '15' '723' '152.64' '0' '0.0455' '' '41826' '0' '0' '152.64' '0' '0' '152.64' '0' UNION ALL
SELECT '2' '1' '15' '723' '189.41' '0' '0.0455' '' '41833' '0' '0' '189.41' '0' '0' '189.41' '0' UNION ALL
SELECT '2' '1' '15' '723' '67.76' '2' '0.0455' '146 - 05' '41826' '2' '0' '67.76' '0' '2' '67.76' '67.76' UNION ALL
SELECT '2' '1' '15' '723' '101.64' '3' '0.0455' '146 - 05' '41826' '3' '0' '101.64' '0' '3' '101.64' '101.64' UNION ALL
SELECT '2' '1' '15' '723' '135.52' '4' '0.0455' '146 - 05' '41833' '4' '0' '135.52' '0' '4' '135.52' '135.52' UNION ALL
SELECT '2' '1' '15' '723' '220.22' '6.5' '0.0455' '146 - 05' '41833' '6.5' '0' '220.22' '0' '6.5' '220.22' '220.22' UNION ALL
SELECT '2' '1' '15' '723' '694.54' '20.5' '0.0455' '146 - 05' '41826' '20.5' '0' '694.54' '0' '20.5' '694.54' '694.54' UNION ALL
SELECT '2' '1' '15' '723' '745.36' '22' '0.0455' '146 - 05' '41833' '22' '0' '745.36' '0' '22' '745.36' '745.36' UNION ALL
SELECT '2' '1' '19' '723' '150.05' '0' '0.0455' '' '41826' '0' '0' '150.05' '0' '0' '150.05' '0' UNION ALL
SELECT '2' '1' '19' '723' '188.11' '0' '0.0455' '' '41833' '0' '0' '188.11' '0' '0' '188.11' '0' UNION ALL
SELECT '2' '1' '19' '748' '851.24' '26' '0.1831' '115 - 85' '41826' '0' '26' '0' '851.24' '26' '851.24' '851.24' UNION ALL
SELECT '2' '1' '19' '748' '1096.79' '33.5' '0.1831' '115 - 85' '41833' '0' '33.5' '0' '1096.79' '33.5' '1096.79' '1096.79' UNION ALL
SELECT '2' '1' '20' '723' '152.7' '0' '0.0455' '' '41826' '0' '0' '152.7' '0' '0' '152.7' '0' UNION ALL
SELECT '2' '1' '20' '723' '199.18' '0' '0.0455' '' '41833' '0' '0' '199.18' '0' '0' '199.18' '0' UNION ALL
SELECT '2' '1' '20' '723' '266.56' '8' '0.0455' '' '41833' '8' '0' '266.56' '0' '8' '266.56' '266.56' UNION ALL
SELECT '2' '1' '20' '748' '866.32' '26' '0.1831' '115 - 85' '41826' '0' '26' '0' '866.32' '26' '866.32' '866.32' UNION ALL
SELECT '2' '1' '20' '748' '899.64' '27' '0.1831' '115 - 85' '41833' '0' '27' '0' '899.64' '27' '899.64' '899.64' UNION ALL
SELECT '2' '1' '27' '723' '201.24' '0' '0.0455' '' '41826' '0' '0' '201.24' '0' '0' '201.24' '0' UNION ALL
SELECT '2' '1' '27' '723' '276.79' '0' '0.0455' '' '41833' '0' '0' '276.79' '0' '0' '276.79' '0' UNION ALL
SELECT '2' '1' '27' '723' '84.7' '2' '0.0455' '115 - 78' '41826' '2' '0' '84.7' '0' '2' '84.7' '84.7' UNION ALL
SELECT '2' '1' '27' '723' '169.4' '4' '0.0455' '115 - 78' '41826' '4' '0' '169.4' '0' '4' '169.4' '169.4' UNION ALL
SELECT '2' '1' '27' '723' '190.58' '4.5' '0.0455' '115 - 78' '41826' '4.5' '0' '190.58' '0' '4.5' '190.58' '190.58' UNION ALL
SELECT '2' '1' '27' '723' '370.56' '8.5' '0.0455' '115 - 78' '41833' '8.5' '0' '370.56' '0' '8.5' '370.56' '370.56' UNION ALL
SELECT '2' '1' '27' '723' '444.68' '10' '0.0455' '115 - 78' '41833' '10' '0' '444.68' '0' '10' '444.68' '444.68' UNION ALL
SELECT '2' '1' '27' '723' '709.36' '16.5' '0.0455' '115 - 78' '41826' '16.5' '0' '709.36' '0' '16.5' '709.36' '709.36' UNION ALL
SELECT '2' '1' '27' '723' '836.41' '19.5' '0.0455' '115 - 78' '41833' '19.5' '0' '836.41' '0' '19.5' '836.41' '836.41' UNION ALL
SELECT '2' '1' '28' '723' '279.07' '0' '0.0455' '' '41826' '0' '0' '279.07' '0' '0' '279.07' '0' UNION ALL
SELECT '2' '1' '28' '723' '349.71' '0' '0.0455' '' '41833' '0' '0' '349.71' '0' '0' '349.71' '0' UNION ALL
SELECT '2' '1' '28' '748' '1659.67' '35.5' '0.1831' '115 - 85' '41826' '0' '35.5' '0' '1659.67' '35.5' '1659.67' '1659.67' UNION ALL
SELECT '2' '1' '28' '748' '2148.47' '46' '0.1831' '115 - 85' '41833' '0' '46' '0' '2148.47' '46' '2148.47' '2148.47' UNION ALL
SELECT '1' '1' '75' '723' '62.57' '0' '0.0455' '' '41826' '0' '0' '62.57' '0' '0' '62.57' '0' UNION ALL
SELECT '1' '1' '75' '723' '62.57' '0' '0.0455' '' '41833' '0' '0' '62.57' '0' '0' '62.57' '0' UNION ALL
SELECT '1' '1' '75' '723' '1000' '40' '0.0455' '125 - 97' '41826' '40' '0' '1000' '0' '40' '1000' '1000' UNION ALL
SELECT '1' '1' '75' '723' '1000' '40' '0.0455' '125 - 97' '41833' '40' '0' '1000' '0' '40' '1000' '1000' UNION ALL
SELECT '1' '1' '177' '723' '62.57' '0' '0.0455' '' '41826' '0' '0' '62.57' '0' '0' '62.57' '0' UNION ALL
SELECT '1' '1' '177' '723' '62.57' '0' '0.0455' '' '41833' '0' '0' '62.57' '0' '0' '62.57' '0' UNION ALL
SELECT '1' '1' '177' '723' '1000' '40' '0.0455' '125 - 98' '41826' '40' '0' '1000' '0' '40' '1000' '1000' UNION ALL
SELECT '1' '1' '177' '723' '1000' '40' '0.0455' '125 - 98' '41833' '40' '0' '1000' '0' '40' '1000' '1000' UNION ALL
SELECT '1' '1' '179' '723' '1093.72' '40' '0.0455' '' '41826' '40' '0' '1093.72' '0' '40' '1093.72' '1093.72' UNION ALL
SELECT '1' '1' '179' '723' '1093.72' '40' '0.0455' '' '41833' '40' '0' '1093.72' '0' '40' '1093.72' '1093.72' UNION ALL
SELECT '1' '1' '186' '723' '101.67' '0' '0.0455' '' '41826' '0' '0' '101.67' '0' '0' '101.67' '0' UNION ALL
SELECT '1' '1' '186' '723' '1139.65' '28' '0.0455' '' '41826' '28' '0' '1139.65' '0' '28' '1139.65' '1139.65' UNION ALL
SELECT '1' '1' '186' '723' '1729.75' '40' '0.0455' '' '41833' '40' '0' '1729.75' '0' '40' '1729.75' '1729.75' UNION ALL
SELECT '1' '1' '186' '723' '488.43' '12' '0.0455' '126 - 00' '41826' '12' '0' '488.43' '0' '12' '488.43' '488.43' UNION ALL
SELECT '1' '1' '201' '723' '0.53' '0' '0.0455' '' '41826' '0' '0' '0.53' '0' '0' '0.53' '0' UNION ALL
SELECT '1' '1' '201' '723' '0.53' '0' '0.0455' '' '41833' '0' '0' '0.53' '0' '0' '0.53' '0' UNION ALL
SELECT '1' '1' '201' '723' '2.66' '0' '0.0455' '' '41826' '0' '0' '2.66' '0' '0' '2.66' '0' UNION ALL
SELECT '1' '1' '201' '723' '2.66' '0' '0.0455' '' '41833' '0' '0' '2.66' '0' '0' '2.66' '0' UNION ALL
SELECT '1' '1' '201' '723' '8.83' '0' '0.0455' '' '41826' '0' '0' '8.83' '0' '0' '8.83' '0' UNION ALL
SELECT '1' '1' '201' '723' '8.83' '0' '0.0455' '' '41833' '0' '0' '8.83' '0' '0' '8.83' '0' UNION ALL
SELECT '1' '1' '201' '723' '16.09' '0' '0.0455' '' '41826' '0' '0' '16.09' '0' '0' '16.09' '0' UNION ALL
SELECT '1' '1' '201' '723' '16.09' '0' '0.0455' '' '41833' '0' '0' '16.09' '0' '0' '16.09' '0' UNION ALL
SELECT '1' '1' '201' '723' '127.5' '0' '0.0455' '' '41826' '0' '0' '127.5' '0' '0' '127.5' '0' UNION ALL
SELECT '1' '1' '201' '723' '127.5' '0' '0.0455' '' '41833' '0' '0' '127.5' '0' '0' '127.5' '0' UNION ALL
SELECT '1' '1' '201' '723' '510' '8' '0.0455' '' '41826' '16' '0' '1020' '0' '16' '1020' '510' UNION ALL
SELECT '1' '1' '201' '723' '510' '8' '0.0455' '' '41833' '8' '0' '510' '0' '8' '510' '510' UNION ALL
SELECT '1' '1' '201' '723' '1530' '24' '0.0455' '14-P007' '41826' '24' '0' '1530' '0' '24' '1530' '1530' UNION ALL
SELECT '1' '1' '201' '723' '2040' '32' '0.0455' '14-P007' '41833' '32' '0' '2040' '0' '32' '2040' '2040'
August 13, 2014 at 12:00 pm
You need to remove brvPRInsJob.InsCode from your GROUP BY clause.
August 13, 2014 at 12:54 pm
Luis Cazares (8/13/2014)
You need to remove brvPRInsJob.InsCode from your GROUP BY clause.
if you are talking about Group by in crystal reports, I have not used brvPRInsJob.InsCode it to Group BY.
if you are talking about removing it from the View/CTE in SQL. I have tried it and I am getting the error below.
Msg 8120, Level 16, State 1, Procedure crvKCLPRInsJob, Line 13
Column 'brvPRInsJob.InsCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
August 13, 2014 at 1:27 pm
tnthunder (8/13/2014)
Luis Cazares (8/13/2014)
You need to remove brvPRInsJob.InsCode from your GROUP BY clause.if you are talking about Group by in crystal reports, I have not used brvPRInsJob.InsCode it to Group BY.
if you are talking about removing it from the View/CTE in SQL. I have tried it and I am getting the error below.
Msg 8120, Level 16, State 1, Procedure crvKCLPRInsJob, Line 13
Column 'brvPRInsJob.InsCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I was referring to the view.
I didn't notice that you have it in your SELECT column list. Do you need it? If so, what should you show when you have both rows?
August 15, 2014 at 7:36 am
Luis Cazares (8/13/2014)
I was referring to the view.I didn't notice that you have it in your SELECT column list. Do you need it? If so, what should you show when you have both rows?
I need it cause it has the insurance codes which i need to display in my report.
with the below Script, I am getting duplicate records.
how do I eliminate/ fix the duplicate record?
USE [Server]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter VIEW [dbo].[crvKCLPRInsJob]
AS
WITH CTE AS(
SELECT
brvPRInsJob.PRCo
,brvPRInsJob.PRGroup
,brvPRInsJob.Employee
,brvPRInsJob.TimeCardEarn
,brvPRInsJob.AddonEarn
,brvPRInsJob.Hours as brvPRInsJob_Hours
,brvPRInsJob.Rate
,brvPRInsJob.InsCode
,brvPRInsJob.InsState
,brvPRInsJob.Job
,brvPRInsJob.JCCo
,brvPRInsJob.PREndDate
,JCJM.Description as JobDescription
,PRIA.DLCode
,PRIA.Earnings
,PRIA.EligibleAmt
,PRIA.Hours as PRIA_Hours
,IncCode723Hours = SUM(CASE
WHEN brvPRInsJob.InsCode = '723'
THEN brvPRInsJob.Hours
ELSE 0
END)
,IncCode748Hours = SUM(CASE
WHEN brvPRInsJob.InsCode = '748'
THEN brvPRInsJob.Hours
ELSE 0
END)
,GrossEarn723 = SUM(CASE
WHEN brvPRInsJob.InsCode = '723'
THEN brvPRInsJob.TimeCardEarn
ELSE 0
END)
,GrossEarn748 = SUM(CASE
WHEN brvPRInsJob.InsCode = '748'
THEN brvPRInsJob.TimeCardEarn
ELSE 0
END)
FROM brvPRInsJob brvPRInsJob
LEFT OUTER JOIN PREH PREH ON (brvPRInsJob.PRCo = PREH.PRCo)
AND (brvPRInsJob.Employee = PREH.Employee)
LEFT OUTER JOIN JCJM JCJM ON brvPRInsJob.Job = JCJM.Job
LEFT OUTER JOIN PRIA PRIA ON (((brvPRInsJob.PRCo=PRIA.PRCo) AND (brvPRInsJob.PRGroup=PRIA.PRGroup)) AND (brvPRInsJob.PREndDate=PRIA.PREndDate)) AND (brvPRInsJob.Employee=PRIA.Employee)
GROUP BY brvPRInsJob.PRCo
,brvPRInsJob.PRGroup
,brvPRInsJob.Employee
,brvPRInsJob.TimeCardEarn
,brvPRInsJob.AddonEarn
,brvPRInsJob.Hours
,brvPRInsJob.Rate
,brvPRInsJob.InsCode
,brvPRInsJob.InsState
,brvPRInsJob.Job
,brvPRInsJob.JCCo
,brvPRInsJob.PREndDate
,JCJM.Description
,PRIA.DLCode
,PRIA.Earnings
,PRIA.EligibleAmt
,PRIA.Hours
)
SELECT PRCo
,PRGroup
,Employee
,TimeCardEarn
,AddonEarn
,brvPRInsJob_Hours
,Rate
,InsCode
,InsState
,Job
,JCCo
,PREndDate
,JobDescription
,DLCode
,Earnings
,EligibleAmt
,PRIA_Hours
,IncCode723Hours
,IncCode748Hours
,GrossEarn723
,GrossEarn748
,TotalHours = IncCode723Hours + IncCode748Hours
,TotalGross = GrossEarn723 + GrossEarn748
,EligibleEarningsBenefitsIncTax = CASE
WHEN (IncCode723Hours + IncCode748Hours) = 0
THEN 0
ELSE ((brvPRInsJob_Hours / (IncCode723Hours + IncCode748Hours)) * (GrossEarn723 + GrossEarn748))
END
FROM CTE
GO
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply