Complicated logic of group by and partition
I have the table as per the script below.
The data that I want finally is shown in the screenshot.
The logic that is to be implemented is :
If SUM(FPR_QTY) > QPA, Use QPA without summing it up.
Else, use FPR_QTY.
Eg explanation: For the first 4 rows, TOT_FPR > QPA, so I just need 1.
While for rest 4 rows, TOT_FPR < QPA, i need to use TOT_FPR.
So, ultimately, I want 21 against each record.
Please let me know if my explanation is not clear.
create table #TEMP
(QPA int
,FPR_QTY int
, key1 varchar(2)
, key2 varchar(10)
)
insert into #TEMP values
(1,1,'K1','kk1')
,(1,0,'k1','kk1')
,(1,1,'k1','kk1')
,(1,0,'k1','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
from #TEMP
sql
sql-server asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
add a comment |
I have the table as per the script below.
The data that I want finally is shown in the screenshot.
The logic that is to be implemented is :
If SUM(FPR_QTY) > QPA, Use QPA without summing it up.
Else, use FPR_QTY.
Eg explanation: For the first 4 rows, TOT_FPR > QPA, so I just need 1.
While for rest 4 rows, TOT_FPR < QPA, i need to use TOT_FPR.
So, ultimately, I want 21 against each record.
Please let me know if my explanation is not clear.
create table #TEMP
(QPA int
,FPR_QTY int
, key1 varchar(2)
, key2 varchar(10)
)
insert into #TEMP values
(1,1,'K1','kk1')
,(1,0,'k1','kk1')
,(1,1,'k1','kk1')
,(1,0,'k1','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
from #TEMP
sql
sql-server asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
You seem to have forgotten to post some of the details here. What is TOT_FPR? And what is FINAL?
– Sean Lange
Nov 14 '18 at 15:07
@SeanLange TOT_FPR I am calculating the Total fpr qty(See my last select which has partition by clause).Final is what I want the output to be(derived column with the logic).
– WhoamI
Nov 14 '18 at 15:09
OK I see TOT_FPR but FINAL makes no sense to me. Where does 21 come from? I can't find any logical way to come up with that.
– Sean Lange
Nov 14 '18 at 15:11
2
Where does the 21 come from? should it be 20?
– Dohsan
Nov 14 '18 at 15:11
For first 4 records, because the key1 is same and QPA for each record is less than TOT_FPR, I just want to add 1 once. For rest 4 records, because the key1 is same and TOT_FPR<QPA, I want the TOT_FPR for all those 4 records. Now, because the entire dataset key2 is same, my desired output is 20 + 1 against each record.
– WhoamI
Nov 14 '18 at 15:12
add a comment |
I have the table as per the script below.
The data that I want finally is shown in the screenshot.
The logic that is to be implemented is :
If SUM(FPR_QTY) > QPA, Use QPA without summing it up.
Else, use FPR_QTY.
Eg explanation: For the first 4 rows, TOT_FPR > QPA, so I just need 1.
While for rest 4 rows, TOT_FPR < QPA, i need to use TOT_FPR.
So, ultimately, I want 21 against each record.
Please let me know if my explanation is not clear.
create table #TEMP
(QPA int
,FPR_QTY int
, key1 varchar(2)
, key2 varchar(10)
)
insert into #TEMP values
(1,1,'K1','kk1')
,(1,0,'k1','kk1')
,(1,1,'k1','kk1')
,(1,0,'k1','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
from #TEMP
sql
sql-server asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
I have the table as per the script below.
The data that I want finally is shown in the screenshot.
The logic that is to be implemented is :
If SUM(FPR_QTY) > QPA, Use QPA without summing it up.
Else, use FPR_QTY.
Eg explanation: For the first 4 rows, TOT_FPR > QPA, so I just need 1.
While for rest 4 rows, TOT_FPR < QPA, i need to use TOT_FPR.
So, ultimately, I want 21 against each record.
Please let me know if my explanation is not clear.
create table #TEMP
(QPA int
,FPR_QTY int
, key1 varchar(2)
, key2 varchar(10)
)
insert into #TEMP values
(1,1,'K1','kk1')
,(1,0,'k1','kk1')
,(1,1,'k1','kk1')
,(1,0,'k1','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
,(50,5,'k2','kk1')
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
from #TEMP
sql
sql-server sql
sql-server asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
asked Nov 14 '18 at 15:02
WhoamIWhoamI
162129
162129
You seem to have forgotten to post some of the details here. What is TOT_FPR? And what is FINAL?
– Sean Lange
Nov 14 '18 at 15:07
@SeanLange TOT_FPR I am calculating the Total fpr qty(See my last select which has partition by clause).Final is what I want the output to be(derived column with the logic).
– WhoamI
Nov 14 '18 at 15:09
OK I see TOT_FPR but FINAL makes no sense to me. Where does 21 come from? I can't find any logical way to come up with that.
– Sean Lange
Nov 14 '18 at 15:11
2
Where does the 21 come from? should it be 20?
– Dohsan
Nov 14 '18 at 15:11
For first 4 records, because the key1 is same and QPA for each record is less than TOT_FPR, I just want to add 1 once. For rest 4 records, because the key1 is same and TOT_FPR<QPA, I want the TOT_FPR for all those 4 records. Now, because the entire dataset key2 is same, my desired output is 20 + 1 against each record.
– WhoamI
Nov 14 '18 at 15:12
add a comment |
You seem to have forgotten to post some of the details here. What is TOT_FPR? And what is FINAL?
– Sean Lange
Nov 14 '18 at 15:07
@SeanLange TOT_FPR I am calculating the Total fpr qty(See my last select which has partition by clause).Final is what I want the output to be(derived column with the logic).
– WhoamI
Nov 14 '18 at 15:09
OK I see TOT_FPR but FINAL makes no sense to me. Where does 21 come from? I can't find any logical way to come up with that.
– Sean Lange
Nov 14 '18 at 15:11
2
Where does the 21 come from? should it be 20?
– Dohsan
Nov 14 '18 at 15:11
For first 4 records, because the key1 is same and QPA for each record is less than TOT_FPR, I just want to add 1 once. For rest 4 records, because the key1 is same and TOT_FPR<QPA, I want the TOT_FPR for all those 4 records. Now, because the entire dataset key2 is same, my desired output is 20 + 1 against each record.
– WhoamI
Nov 14 '18 at 15:12
You seem to have forgotten to post some of the details here. What is TOT_FPR? And what is FINAL?
– Sean Lange
Nov 14 '18 at 15:07
You seem to have forgotten to post some of the details here. What is TOT_FPR? And what is FINAL?
– Sean Lange
Nov 14 '18 at 15:07
@SeanLange TOT_FPR I am calculating the Total fpr qty(See my last select which has partition by clause).Final is what I want the output to be(derived column with the logic).
– WhoamI
Nov 14 '18 at 15:09
@SeanLange TOT_FPR I am calculating the Total fpr qty(See my last select which has partition by clause).Final is what I want the output to be(derived column with the logic).
– WhoamI
Nov 14 '18 at 15:09
OK I see TOT_FPR but FINAL makes no sense to me. Where does 21 come from? I can't find any logical way to come up with that.
– Sean Lange
Nov 14 '18 at 15:11
OK I see TOT_FPR but FINAL makes no sense to me. Where does 21 come from? I can't find any logical way to come up with that.
– Sean Lange
Nov 14 '18 at 15:11
2
2
Where does the 21 come from? should it be 20?
– Dohsan
Nov 14 '18 at 15:11
Where does the 21 come from? should it be 20?
– Dohsan
Nov 14 '18 at 15:11
For first 4 records, because the key1 is same and QPA for each record is less than TOT_FPR, I just want to add 1 once. For rest 4 records, because the key1 is same and TOT_FPR<QPA, I want the TOT_FPR for all those 4 records. Now, because the entire dataset key2 is same, my desired output is 20 + 1 against each record.
– WhoamI
Nov 14 '18 at 15:12
For first 4 records, because the key1 is same and QPA for each record is less than TOT_FPR, I just want to add 1 once. For rest 4 records, because the key1 is same and TOT_FPR<QPA, I want the TOT_FPR for all those 4 records. Now, because the entire dataset key2 is same, my desired output is 20 + 1 against each record.
– WhoamI
Nov 14 '18 at 15:12
add a comment |
1 Answer
1
active
oldest
votes
Here you go...I was able to write the query within two selects. I wish I could accept my own answer as it is most simplest and will run without failure.
select * ,SUM(IIF(TOT_FPR>QPA,IIF(QPA_IND = 1,QPA,0),FPR_QTY)) OVER (PARTITION BY key2) FINAL
from
(
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
,ROW_NUMBER() OVER (PARTITION BY key1 order by QPA) AS QPA_IND
from #TEMP
)T
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
);
);
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%2fstackoverflow.com%2fquestions%2f53303157%2fcomplicated-logic-of-group-by-and-partition%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
Here you go...I was able to write the query within two selects. I wish I could accept my own answer as it is most simplest and will run without failure.
select * ,SUM(IIF(TOT_FPR>QPA,IIF(QPA_IND = 1,QPA,0),FPR_QTY)) OVER (PARTITION BY key2) FINAL
from
(
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
,ROW_NUMBER() OVER (PARTITION BY key1 order by QPA) AS QPA_IND
from #TEMP
)T
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
add a comment |
Here you go...I was able to write the query within two selects. I wish I could accept my own answer as it is most simplest and will run without failure.
select * ,SUM(IIF(TOT_FPR>QPA,IIF(QPA_IND = 1,QPA,0),FPR_QTY)) OVER (PARTITION BY key2) FINAL
from
(
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
,ROW_NUMBER() OVER (PARTITION BY key1 order by QPA) AS QPA_IND
from #TEMP
)T
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
add a comment |
Here you go...I was able to write the query within two selects. I wish I could accept my own answer as it is most simplest and will run without failure.
select * ,SUM(IIF(TOT_FPR>QPA,IIF(QPA_IND = 1,QPA,0),FPR_QTY)) OVER (PARTITION BY key2) FINAL
from
(
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
,ROW_NUMBER() OVER (PARTITION BY key1 order by QPA) AS QPA_IND
from #TEMP
)T
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
Here you go...I was able to write the query within two selects. I wish I could accept my own answer as it is most simplest and will run without failure.
select * ,SUM(IIF(TOT_FPR>QPA,IIF(QPA_IND = 1,QPA,0),FPR_QTY)) OVER (PARTITION BY key2) FINAL
from
(
select *
,SUM(FPR_QTY) OVER (PARTITION BY key1) AS TOT_FPR
,ROW_NUMBER() OVER (PARTITION BY key1 order by QPA) AS QPA_IND
from #TEMP
)T
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
answered Nov 14 '18 at 22:05
WhoamIWhoamI
162129
162129
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53303157%2fcomplicated-logic-of-group-by-and-partition%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
You seem to have forgotten to post some of the details here. What is TOT_FPR? And what is FINAL?
– Sean Lange
Nov 14 '18 at 15:07
@SeanLange TOT_FPR I am calculating the Total fpr qty(See my last select which has partition by clause).Final is what I want the output to be(derived column with the logic).
– WhoamI
Nov 14 '18 at 15:09
OK I see TOT_FPR but FINAL makes no sense to me. Where does 21 come from? I can't find any logical way to come up with that.
– Sean Lange
Nov 14 '18 at 15:11
2
Where does the 21 come from? should it be 20?
– Dohsan
Nov 14 '18 at 15:11
For first 4 records, because the key1 is same and QPA for each record is less than TOT_FPR, I just want to add 1 once. For rest 4 records, because the key1 is same and TOT_FPR<QPA, I want the TOT_FPR for all those 4 records. Now, because the entire dataset key2 is same, my desired output is 20 + 1 against each record.
– WhoamI
Nov 14 '18 at 15:12