SUM HOURS BY PAY CODESDROP PARTITION strategy using LIST-REF composite partitioningWhat must be in place to...
Have any astronauts or cosmonauts died in space?
Will linear voltage regulator step up current?
Can you wish for more wishes from an Efreeti bound to service via an Efreeti Bottle?
Relation between roots and coefficients - manipulation of identities
How can a kingdom keep the secret of a missing monarch from the public?
How to know if I am a 'Real Developer'
Why don't reads from /dev/zero count as I/O?
Coloring in a multirow
Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash
How should I ship cards?
What are Holorydmachines?
What does “to the numbers” mean in landing clearance?
Discouraging missile alpha strikes
Can a planet be tidally unlocked?
Define function that behaves almost identically to Mathematica function
What does an unprocessed RAW file look like?
Can "ee" appear in Latin?
I hate taking lectures, can I still survive in academia?
How can changes in personality/values of a person who turned into a vampire be explained?
Exploding Numbers
Why is Bernie Sanders maximum accepted donation on actblue $5600?
Is layered encryption more secure than long passwords?
Why is the meaning of kanji 閑 "leisure"?
Why do we divide Permutations to get to Combinations?
SUM HOURS BY PAY CODES
DROP PARTITION strategy using LIST-REF composite partitioningWhat must be in place to validate a XMLTYPE against a schema?Table redirect / filter / trigger on selectCannot change service name for OracleInstalling Oracle 11gR2 on Oracle Linux 6 - error possibly related to database (unique) nameComparing values between two tablesOracle 11g Auditing and Collecting the SQL Text with AUDIT_TRAIL=OSHow should I store a large number of dynamically generated heterogeneous tables?How to know when did a blocking session start and end?Eclipse data source explorer not showing Inserted table records (Oracle)
I am trying to total pay hours based from what is in a PAY_SUM_GRP. I have a query that will create a new line for each PAY_SUM_GRP for an employee, but I want just one record per employee with different columns for the PAY_SUM_GRPs.
Here is my code that creates separate records:
SELECT PAYTIME.DESCRIPTION AS PAY_TYPE_NAME,
CASE WHEN PAYTIME.GROUP = '101'
THEN SUM(PAYTIME.HOURS) END AS REG_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '075'
THEN SUM(PAYTIME.HOURS) END AS PTO_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '114'
THEN SUM(PAYTIME.HOURS) END AS DIFF_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP NOT IN ('101', '075', '114')
THEN SUM(PAYTIME.HOURS) END AS MISC_HOURS,
FROM ....
AND I GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 1.25 55.65
2356 Bi-Wkly 64.98 2892.91
2356 Bi-Wkly 20.13 35.85
2356 Bi-Wkly 0 2.65
I WANT TO GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 64.98 1.25 20.13 0 2987.06
oracle-11g-r2
edited 14 hours ago
Randi Vertongen
2,866721
asked 14 hours ago
user3900798user3900798
1
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am trying to total pay hours based from what is in a PAY_SUM_GRP. I have a query that will create a new line for each PAY_SUM_GRP for an employee, but I want just one record per employee with different columns for the PAY_SUM_GRPs.
Here is my code that creates separate records:
SELECT PAYTIME.DESCRIPTION AS PAY_TYPE_NAME,
CASE WHEN PAYTIME.GROUP = '101'
THEN SUM(PAYTIME.HOURS) END AS REG_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '075'
THEN SUM(PAYTIME.HOURS) END AS PTO_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '114'
THEN SUM(PAYTIME.HOURS) END AS DIFF_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP NOT IN ('101', '075', '114')
THEN SUM(PAYTIME.HOURS) END AS MISC_HOURS,
FROM ....
AND I GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 1.25 55.65
2356 Bi-Wkly 64.98 2892.91
2356 Bi-Wkly 20.13 35.85
2356 Bi-Wkly 0 2.65
I WANT TO GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 64.98 1.25 20.13 0 2987.06
oracle-11g-r2
edited 14 hours ago
Randi Vertongen
2,866721
asked 14 hours ago
user3900798user3900798
1
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Please show your entire select statement as what you have in the group by section is critical to understanding what is happening. The reason I ask is because to me it appears that your group by is incorrect and if you fix that it would fix the problem. I am guessing you are including paytime.group when it is not needed.
– Joe W
14 hours ago
add a comment |
I am trying to total pay hours based from what is in a PAY_SUM_GRP. I have a query that will create a new line for each PAY_SUM_GRP for an employee, but I want just one record per employee with different columns for the PAY_SUM_GRPs.
Here is my code that creates separate records:
SELECT PAYTIME.DESCRIPTION AS PAY_TYPE_NAME,
CASE WHEN PAYTIME.GROUP = '101'
THEN SUM(PAYTIME.HOURS) END AS REG_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '075'
THEN SUM(PAYTIME.HOURS) END AS PTO_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '114'
THEN SUM(PAYTIME.HOURS) END AS DIFF_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP NOT IN ('101', '075', '114')
THEN SUM(PAYTIME.HOURS) END AS MISC_HOURS,
FROM ....
AND I GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 1.25 55.65
2356 Bi-Wkly 64.98 2892.91
2356 Bi-Wkly 20.13 35.85
2356 Bi-Wkly 0 2.65
I WANT TO GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 64.98 1.25 20.13 0 2987.06
oracle-11g-r2
edited 14 hours ago
Randi Vertongen
2,866721
asked 14 hours ago
user3900798user3900798
1
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I am trying to total pay hours based from what is in a PAY_SUM_GRP. I have a query that will create a new line for each PAY_SUM_GRP for an employee, but I want just one record per employee with different columns for the PAY_SUM_GRPs.
Here is my code that creates separate records:
SELECT PAYTIME.DESCRIPTION AS PAY_TYPE_NAME,
CASE WHEN PAYTIME.GROUP = '101'
THEN SUM(PAYTIME.HOURS) END AS REG_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '075'
THEN SUM(PAYTIME.HOURS) END AS PTO_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP = '114'
THEN SUM(PAYTIME.HOURS) END AS DIFF_HOURS,
CASE WHEN PAYTIME.GROUP_SUM_GRP NOT IN ('101', '075', '114')
THEN SUM(PAYTIME.HOURS) END AS MISC_HOURS,
FROM ....
AND I GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 1.25 55.65
2356 Bi-Wkly 64.98 2892.91
2356 Bi-Wkly 20.13 35.85
2356 Bi-Wkly 0 2.65
I WANT TO GET:
EMPLOYEE PAY_TYPE_NAME REG_HRS PTO_HRS DIFF_HRS MISC_HRS DOLLARS
2356 Bi-Wkly 64.98 1.25 20.13 0 2987.06
oracle-11g-r2
oracle-11g-r2
edited 14 hours ago
Randi Vertongen
2,866721
asked 14 hours ago
user3900798user3900798
1
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 14 hours ago
Randi Vertongen
2,866721
asked 14 hours ago
user3900798user3900798
1
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 14 hours ago
Randi Vertongen
2,866721
edited 14 hours ago
Randi Vertongen
2,866721
edited 14 hours ago
Randi Vertongen
2,866721
2,866721
asked 14 hours ago
user3900798user3900798
1
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 14 hours ago
user3900798user3900798
1
asked 14 hours ago
user3900798user3900798
1
1
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
user3900798 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Please show your entire select statement as what you have in the group by section is critical to understanding what is happening. The reason I ask is because to me it appears that your group by is incorrect and if you fix that it would fix the problem. I am guessing you are including paytime.group when it is not needed.
– Joe W
14 hours ago
add a comment |
2
Please show your entire select statement as what you have in the group by section is critical to understanding what is happening. The reason I ask is because to me it appears that your group by is incorrect and if you fix that it would fix the problem. I am guessing you are including paytime.group when it is not needed.
– Joe W
14 hours ago
2
2
Please show your entire select statement as what you have in the group by section is critical to understanding what is happening. The reason I ask is because to me it appears that your group by is incorrect and if you fix that it would fix the problem. I am guessing you are including paytime.group when it is not needed.
– Joe W
14 hours ago
Please show your entire select statement as what you have in the group by section is critical to understanding what is happening. The reason I ask is because to me it appears that your group by is incorrect and if you fix that it would fix the problem. I am guessing you are including paytime.group when it is not needed.
– Joe W
14 hours ago
add a comment |
1 Answer
1
active
oldest
votes
It looks like you are trying to PIVOT your data.
First thing you need to do is set the value for a single column such that THAT column can be used to map the aggregated value to the pivoted Column Name.
Normally, you would just use GROUP_SUM_GRP to determine the Column Name post PIVOT. However, the problem you have is the requirement to "group all other GROUP_SUM_GRP values into MISC_HOURS".
In this case, just set "all other values of GROUP_SUM_GRP" to a static value:
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS" -- encase Reserved Words in double quotes
from paytime p -- etc
Once the data is in the correct format, you can PIVOT it
with data as (
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS"
from paytime p -- etc
)
select *
from data
pivot (
sum( "HOURS" )
for group_sum_grp in ('101' as "REG_HOURS"
,'075' as "PTO_HOURS"
,'114' as "DIFF_HOURS"
,'---' as "MISC_HOURS"
)
);
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
I don't think this is needed as I am guessing the group by that is being used is incorrect and we can't know for sure until they have provided it. The select that was provided would work as long as they are only grouping by employee number and pay_type.
– Joe W
13 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
user3900798 is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230315%2fsum-hours-by-pay-codes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It looks like you are trying to PIVOT your data.
First thing you need to do is set the value for a single column such that THAT column can be used to map the aggregated value to the pivoted Column Name.
Normally, you would just use GROUP_SUM_GRP to determine the Column Name post PIVOT. However, the problem you have is the requirement to "group all other GROUP_SUM_GRP values into MISC_HOURS".
In this case, just set "all other values of GROUP_SUM_GRP" to a static value:
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS" -- encase Reserved Words in double quotes
from paytime p -- etc
Once the data is in the correct format, you can PIVOT it
with data as (
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS"
from paytime p -- etc
)
select *
from data
pivot (
sum( "HOURS" )
for group_sum_grp in ('101' as "REG_HOURS"
,'075' as "PTO_HOURS"
,'114' as "DIFF_HOURS"
,'---' as "MISC_HOURS"
)
);
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
I don't think this is needed as I am guessing the group by that is being used is incorrect and we can't know for sure until they have provided it. The select that was provided would work as long as they are only grouping by employee number and pay_type.
– Joe W
13 hours ago
add a comment |
It looks like you are trying to PIVOT your data.
First thing you need to do is set the value for a single column such that THAT column can be used to map the aggregated value to the pivoted Column Name.
Normally, you would just use GROUP_SUM_GRP to determine the Column Name post PIVOT. However, the problem you have is the requirement to "group all other GROUP_SUM_GRP values into MISC_HOURS".
In this case, just set "all other values of GROUP_SUM_GRP" to a static value:
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS" -- encase Reserved Words in double quotes
from paytime p -- etc
Once the data is in the correct format, you can PIVOT it
with data as (
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS"
from paytime p -- etc
)
select *
from data
pivot (
sum( "HOURS" )
for group_sum_grp in ('101' as "REG_HOURS"
,'075' as "PTO_HOURS"
,'114' as "DIFF_HOURS"
,'---' as "MISC_HOURS"
)
);
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
I don't think this is needed as I am guessing the group by that is being used is incorrect and we can't know for sure until they have provided it. The select that was provided would work as long as they are only grouping by employee number and pay_type.
– Joe W
13 hours ago
add a comment |
It looks like you are trying to PIVOT your data.
First thing you need to do is set the value for a single column such that THAT column can be used to map the aggregated value to the pivoted Column Name.
Normally, you would just use GROUP_SUM_GRP to determine the Column Name post PIVOT. However, the problem you have is the requirement to "group all other GROUP_SUM_GRP values into MISC_HOURS".
In this case, just set "all other values of GROUP_SUM_GRP" to a static value:
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS" -- encase Reserved Words in double quotes
from paytime p -- etc
Once the data is in the correct format, you can PIVOT it
with data as (
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS"
from paytime p -- etc
)
select *
from data
pivot (
sum( "HOURS" )
for group_sum_grp in ('101' as "REG_HOURS"
,'075' as "PTO_HOURS"
,'114' as "DIFF_HOURS"
,'---' as "MISC_HOURS"
)
);
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
It looks like you are trying to PIVOT your data.
First thing you need to do is set the value for a single column such that THAT column can be used to map the aggregated value to the pivoted Column Name.
Normally, you would just use GROUP_SUM_GRP to determine the Column Name post PIVOT. However, the problem you have is the requirement to "group all other GROUP_SUM_GRP values into MISC_HOURS".
In this case, just set "all other values of GROUP_SUM_GRP" to a static value:
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS" -- encase Reserved Words in double quotes
from paytime p -- etc
Once the data is in the correct format, you can PIVOT it
with data as (
select e.employee, p.pay_type_name
,case
when p.group_sum_grp not in ( '101','075','114' ) then '---'
else p.group_sum_grp
end group_sum_grp
,p."HOURS"
from paytime p -- etc
)
select *
from data
pivot (
sum( "HOURS" )
for group_sum_grp in ('101' as "REG_HOURS"
,'075' as "PTO_HOURS"
,'114' as "DIFF_HOURS"
,'---' as "MISC_HOURS"
)
);
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
answered 14 hours ago
Michael KutzMichael Kutz
2,0871111
2,0871111
I don't think this is needed as I am guessing the group by that is being used is incorrect and we can't know for sure until they have provided it. The select that was provided would work as long as they are only grouping by employee number and pay_type.
– Joe W
13 hours ago
add a comment |
I don't think this is needed as I am guessing the group by that is being used is incorrect and we can't know for sure until they have provided it. The select that was provided would work as long as they are only grouping by employee number and pay_type.
– Joe W
13 hours ago
I don't think this is needed as I am guessing the group by that is being used is incorrect and we can't know for sure until they have provided it. The select that was provided would work as long as they are only grouping by employee number and pay_type.
– Joe W
13 hours ago
I don't think this is needed as I am guessing the group by that is being used is incorrect and we can't know for sure until they have provided it. The select that was provided would work as long as they are only grouping by employee number and pay_type.
– Joe W
13 hours ago
add a comment |
user3900798 is a new contributor. Be nice, and check out our Code of Conduct.
user3900798 is a new contributor. Be nice, and check out our Code of Conduct.
user3900798 is a new contributor. Be nice, and check out our Code of Conduct.
user3900798 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230315%2fsum-hours-by-pay-codes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Please show your entire select statement as what you have in the group by section is critical to understanding what is happening. The reason I ask is because to me it appears that your group by is incorrect and if you fix that it would fix the problem. I am guessing you are including paytime.group when it is not needed.
– Joe W
14 hours ago