Can i add columns including LAG,Lead to get least and recent days from the givens in one row
-2
I have the below two tables :
- Adjustment Transaction:
- Time Day lookup:
- The desired output :
My question is can we use LAG & Lead to get the fist/last borrow date in one row same as desired output?
I have figured out the below but it did not work:
SELECT [Mobile Number]
,sum(CASE WHEN Adjustment_Code = 'CRE' then COALESCE ([Adjustment_Amount], 0)else 0 END) as "Total Amount Borrowed",
sum(CASE WHEN Adjustment_Code = 'DEB' then COALESCE(abs([Adjustment_Amount]), 0)else 0 END) as "Total Amount Payback"
,COUNT(CASE WHEN Adjustment_Code = 'CRE' THEN 1 ELSE NULL END) AS [Total TRX Borrowed]
,COUNT(CASE WHEN Adjustment_Code = 'DEB' THEN 1 ELSE NULL END) AS [Total TRX Payback]
, (select LEAD(Full_Date, 5, 0) OVER (PARTITION By adjustment_code ORDER BY date_key) AS Lead_2
from [Adjustment Transaction] )
FROM dbo.[Adjustment Transaction] INNER JOIN dbo.[Time Day Lookup] ON dbo.[Adjustment Transaction].Date_Key = dbo.[Time Day Lookup].Date_Key
group by [Mobile Number],Full_Date
sql-server edited Nov 25 '18 at 4:43
kit
1,1063816
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
add a comment |
-2
I have the below two tables :
- Adjustment Transaction:
- Time Day lookup:
- The desired output :
My question is can we use LAG & Lead to get the fist/last borrow date in one row same as desired output?
I have figured out the below but it did not work:
SELECT [Mobile Number]
,sum(CASE WHEN Adjustment_Code = 'CRE' then COALESCE ([Adjustment_Amount], 0)else 0 END) as "Total Amount Borrowed",
sum(CASE WHEN Adjustment_Code = 'DEB' then COALESCE(abs([Adjustment_Amount]), 0)else 0 END) as "Total Amount Payback"
,COUNT(CASE WHEN Adjustment_Code = 'CRE' THEN 1 ELSE NULL END) AS [Total TRX Borrowed]
,COUNT(CASE WHEN Adjustment_Code = 'DEB' THEN 1 ELSE NULL END) AS [Total TRX Payback]
, (select LEAD(Full_Date, 5, 0) OVER (PARTITION By adjustment_code ORDER BY date_key) AS Lead_2
from [Adjustment Transaction] )
FROM dbo.[Adjustment Transaction] INNER JOIN dbo.[Time Day Lookup] ON dbo.[Adjustment Transaction].Date_Key = dbo.[Time Day Lookup].Date_Key
group by [Mobile Number],Full_Date
edited Nov 25 '18 at 4:43
kit
1,1063816
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
Welcome to stackoverflow. Please format your code as code (start lines of code with 4 spaces) this will improve readability and make it easier for users to answer.
– Simon.S.A.
Nov 25 '18 at 4:38
Not sure if I understand your question fully. Can you not use MIN(DATE) OVER (PARTITION BY MOBILE_NUMBER) AS FIRST_BORROW_DATE, MAX(DATE) OVER (PARTITION BY (MOBILE_NUMBER) AS LAST_BORROW_DATE. I feel your requirement is simple but the explanation is not very clear.
– WhoamI
Nov 25 '18 at 5:37
1
Check out How To Ask and the importance of a Minimal, Complete, Verifiable Example. Also, avoid pictures of code... After reading, Start Here to edit your question as needed, though you'll probably figure out the answer once you start formatting your code properly.
– Eric Brandt
Nov 25 '18 at 8:18
@wahoaml i have a test , requesting to do the desired output with LAG & LEAD
– Mahmoud Abd El Raouf
Nov 25 '18 at 12:41
add a comment |
-2
-2
-2
I have the below two tables :
- Adjustment Transaction:
- Time Day lookup:
- The desired output :
My question is can we use LAG & Lead to get the fist/last borrow date in one row same as desired output?
I have figured out the below but it did not work:
SELECT [Mobile Number]
,sum(CASE WHEN Adjustment_Code = 'CRE' then COALESCE ([Adjustment_Amount], 0)else 0 END) as "Total Amount Borrowed",
sum(CASE WHEN Adjustment_Code = 'DEB' then COALESCE(abs([Adjustment_Amount]), 0)else 0 END) as "Total Amount Payback"
,COUNT(CASE WHEN Adjustment_Code = 'CRE' THEN 1 ELSE NULL END) AS [Total TRX Borrowed]
,COUNT(CASE WHEN Adjustment_Code = 'DEB' THEN 1 ELSE NULL END) AS [Total TRX Payback]
, (select LEAD(Full_Date, 5, 0) OVER (PARTITION By adjustment_code ORDER BY date_key) AS Lead_2
from [Adjustment Transaction] )
FROM dbo.[Adjustment Transaction] INNER JOIN dbo.[Time Day Lookup] ON dbo.[Adjustment Transaction].Date_Key = dbo.[Time Day Lookup].Date_Key
group by [Mobile Number],Full_Date
edited Nov 25 '18 at 4:43
kit
1,1063816
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
I have the below two tables :
- Adjustment Transaction:
- Time Day lookup:
- The desired output :
My question is can we use LAG & Lead to get the fist/last borrow date in one row same as desired output?
I have figured out the below but it did not work:
SELECT [Mobile Number]
,sum(CASE WHEN Adjustment_Code = 'CRE' then COALESCE ([Adjustment_Amount], 0)else 0 END) as "Total Amount Borrowed",
sum(CASE WHEN Adjustment_Code = 'DEB' then COALESCE(abs([Adjustment_Amount]), 0)else 0 END) as "Total Amount Payback"
,COUNT(CASE WHEN Adjustment_Code = 'CRE' THEN 1 ELSE NULL END) AS [Total TRX Borrowed]
,COUNT(CASE WHEN Adjustment_Code = 'DEB' THEN 1 ELSE NULL END) AS [Total TRX Payback]
, (select LEAD(Full_Date, 5, 0) OVER (PARTITION By adjustment_code ORDER BY date_key) AS Lead_2
from [Adjustment Transaction] )
FROM dbo.[Adjustment Transaction] INNER JOIN dbo.[Time Day Lookup] ON dbo.[Adjustment Transaction].Date_Key = dbo.[Time Day Lookup].Date_Key
group by [Mobile Number],Full_Date
edited Nov 25 '18 at 4:43
kit
1,1063816
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
edited Nov 25 '18 at 4:43
kit
1,1063816
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
edited Nov 25 '18 at 4:43
kit
1,1063816
edited Nov 25 '18 at 4:43
kit
1,1063816
edited Nov 25 '18 at 4:43
kit
1,1063816
1,1063816
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
asked Nov 25 '18 at 4:23
Mahmoud Abd El RaoufMahmoud Abd El Raouf
12
12
Welcome to stackoverflow. Please format your code as code (start lines of code with 4 spaces) this will improve readability and make it easier for users to answer.
– Simon.S.A.
Nov 25 '18 at 4:38
Not sure if I understand your question fully. Can you not use MIN(DATE) OVER (PARTITION BY MOBILE_NUMBER) AS FIRST_BORROW_DATE, MAX(DATE) OVER (PARTITION BY (MOBILE_NUMBER) AS LAST_BORROW_DATE. I feel your requirement is simple but the explanation is not very clear.
– WhoamI
Nov 25 '18 at 5:37
1
Check out How To Ask and the importance of a Minimal, Complete, Verifiable Example. Also, avoid pictures of code... After reading, Start Here to edit your question as needed, though you'll probably figure out the answer once you start formatting your code properly.
– Eric Brandt
Nov 25 '18 at 8:18
@wahoaml i have a test , requesting to do the desired output with LAG & LEAD
– Mahmoud Abd El Raouf
Nov 25 '18 at 12:41
add a comment |
Welcome to stackoverflow. Please format your code as code (start lines of code with 4 spaces) this will improve readability and make it easier for users to answer.
– Simon.S.A.
Nov 25 '18 at 4:38
Not sure if I understand your question fully. Can you not use MIN(DATE) OVER (PARTITION BY MOBILE_NUMBER) AS FIRST_BORROW_DATE, MAX(DATE) OVER (PARTITION BY (MOBILE_NUMBER) AS LAST_BORROW_DATE. I feel your requirement is simple but the explanation is not very clear.
– WhoamI
Nov 25 '18 at 5:37
1
Check out How To Ask and the importance of a Minimal, Complete, Verifiable Example. Also, avoid pictures of code... After reading, Start Here to edit your question as needed, though you'll probably figure out the answer once you start formatting your code properly.
– Eric Brandt
Nov 25 '18 at 8:18
@wahoaml i have a test , requesting to do the desired output with LAG & LEAD
– Mahmoud Abd El Raouf
Nov 25 '18 at 12:41
Welcome to stackoverflow. Please format your code as code (start lines of code with 4 spaces) this will improve readability and make it easier for users to answer.
– Simon.S.A.
Nov 25 '18 at 4:38
Welcome to stackoverflow. Please format your code as code (start lines of code with 4 spaces) this will improve readability and make it easier for users to answer.
– Simon.S.A.
Nov 25 '18 at 4:38
Not sure if I understand your question fully. Can you not use MIN(DATE) OVER (PARTITION BY MOBILE_NUMBER) AS FIRST_BORROW_DATE, MAX(DATE) OVER (PARTITION BY (MOBILE_NUMBER) AS LAST_BORROW_DATE. I feel your requirement is simple but the explanation is not very clear.
– WhoamI
Nov 25 '18 at 5:37
Not sure if I understand your question fully. Can you not use MIN(DATE) OVER (PARTITION BY MOBILE_NUMBER) AS FIRST_BORROW_DATE, MAX(DATE) OVER (PARTITION BY (MOBILE_NUMBER) AS LAST_BORROW_DATE. I feel your requirement is simple but the explanation is not very clear.
– WhoamI
Nov 25 '18 at 5:37
1
1
Check out How To Ask and the importance of a Minimal, Complete, Verifiable Example. Also, avoid pictures of code... After reading, Start Here to edit your question as needed, though you'll probably figure out the answer once you start formatting your code properly.
– Eric Brandt
Nov 25 '18 at 8:18
Check out How To Ask and the importance of a Minimal, Complete, Verifiable Example. Also, avoid pictures of code... After reading, Start Here to edit your question as needed, though you'll probably figure out the answer once you start formatting your code properly.
– Eric Brandt
Nov 25 '18 at 8:18
@wahoaml i have a test , requesting to do the desired output with LAG & LEAD
– Mahmoud Abd El Raouf
Nov 25 '18 at 12:41
@wahoaml i have a test , requesting to do the desired output with LAG & LEAD
– Mahmoud Abd El Raouf
Nov 25 '18 at 12:41
add a comment |
1 Answer
1
active
oldest
votes
0
Maybe this which uses Null value is eliminated by an aggregate or other SET operation to our advantage.
mobilenumber datekey amount code
------------ ----------- ----------- ----
1 1 10 cre
1 1 10 cre
1 2 10 cre
1 2 10 deb
1 3 10 deb
1 3 10 cre
(6 row(s) affected)
id date
----------- -----------------------
1 2017-01-01 00:00:00
2 2017-01-02 00:00:00
3 2017-01-03 00:00:00
(3 row(s) affected)
select t.mobilenumber,
sum(case when code = 'cre' then amount else 0 end) sumcre,
sum(case when code = 'deb' then amount else 0 end) sumdeb,
min(date) mindate,
max(date) maxdate
from
(
select t.*,d.date from t join dimtime d on d.id = t.datekey and t.code = 'cre'
union
select t.*,null from t join dimtime d on d.id = t.datekey and t.code = 'deb'
) t
group by t.mobilenumber
mobilenumber sumcre sumdeb mindate maxdate
------------ ----------- ----------- ----------------------- -----------------------
1 30 20 2017-01-01 00:00:00 2017-01-03 00:00:00
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
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
});
}
});
draft saved
draft discarded
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%2f53464633%2fcan-i-add-columns-including-lag-lead-to-get-least-and-recent-days-from-the-given%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
0
Maybe this which uses Null value is eliminated by an aggregate or other SET operation to our advantage.
mobilenumber datekey amount code
------------ ----------- ----------- ----
1 1 10 cre
1 1 10 cre
1 2 10 cre
1 2 10 deb
1 3 10 deb
1 3 10 cre
(6 row(s) affected)
id date
----------- -----------------------
1 2017-01-01 00:00:00
2 2017-01-02 00:00:00
3 2017-01-03 00:00:00
(3 row(s) affected)
select t.mobilenumber,
sum(case when code = 'cre' then amount else 0 end) sumcre,
sum(case when code = 'deb' then amount else 0 end) sumdeb,
min(date) mindate,
max(date) maxdate
from
(
select t.*,d.date from t join dimtime d on d.id = t.datekey and t.code = 'cre'
union
select t.*,null from t join dimtime d on d.id = t.datekey and t.code = 'deb'
) t
group by t.mobilenumber
mobilenumber sumcre sumdeb mindate maxdate
------------ ----------- ----------- ----------------------- -----------------------
1 30 20 2017-01-01 00:00:00 2017-01-03 00:00:00
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
add a comment |
0
Maybe this which uses Null value is eliminated by an aggregate or other SET operation to our advantage.
mobilenumber datekey amount code
------------ ----------- ----------- ----
1 1 10 cre
1 1 10 cre
1 2 10 cre
1 2 10 deb
1 3 10 deb
1 3 10 cre
(6 row(s) affected)
id date
----------- -----------------------
1 2017-01-01 00:00:00
2 2017-01-02 00:00:00
3 2017-01-03 00:00:00
(3 row(s) affected)
select t.mobilenumber,
sum(case when code = 'cre' then amount else 0 end) sumcre,
sum(case when code = 'deb' then amount else 0 end) sumdeb,
min(date) mindate,
max(date) maxdate
from
(
select t.*,d.date from t join dimtime d on d.id = t.datekey and t.code = 'cre'
union
select t.*,null from t join dimtime d on d.id = t.datekey and t.code = 'deb'
) t
group by t.mobilenumber
mobilenumber sumcre sumdeb mindate maxdate
------------ ----------- ----------- ----------------------- -----------------------
1 30 20 2017-01-01 00:00:00 2017-01-03 00:00:00
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
add a comment |
0
0
0
Maybe this which uses Null value is eliminated by an aggregate or other SET operation to our advantage.
mobilenumber datekey amount code
------------ ----------- ----------- ----
1 1 10 cre
1 1 10 cre
1 2 10 cre
1 2 10 deb
1 3 10 deb
1 3 10 cre
(6 row(s) affected)
id date
----------- -----------------------
1 2017-01-01 00:00:00
2 2017-01-02 00:00:00
3 2017-01-03 00:00:00
(3 row(s) affected)
select t.mobilenumber,
sum(case when code = 'cre' then amount else 0 end) sumcre,
sum(case when code = 'deb' then amount else 0 end) sumdeb,
min(date) mindate,
max(date) maxdate
from
(
select t.*,d.date from t join dimtime d on d.id = t.datekey and t.code = 'cre'
union
select t.*,null from t join dimtime d on d.id = t.datekey and t.code = 'deb'
) t
group by t.mobilenumber
mobilenumber sumcre sumdeb mindate maxdate
------------ ----------- ----------- ----------------------- -----------------------
1 30 20 2017-01-01 00:00:00 2017-01-03 00:00:00
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
Maybe this which uses Null value is eliminated by an aggregate or other SET operation to our advantage.
mobilenumber datekey amount code
------------ ----------- ----------- ----
1 1 10 cre
1 1 10 cre
1 2 10 cre
1 2 10 deb
1 3 10 deb
1 3 10 cre
(6 row(s) affected)
id date
----------- -----------------------
1 2017-01-01 00:00:00
2 2017-01-02 00:00:00
3 2017-01-03 00:00:00
(3 row(s) affected)
select t.mobilenumber,
sum(case when code = 'cre' then amount else 0 end) sumcre,
sum(case when code = 'deb' then amount else 0 end) sumdeb,
min(date) mindate,
max(date) maxdate
from
(
select t.*,d.date from t join dimtime d on d.id = t.datekey and t.code = 'cre'
union
select t.*,null from t join dimtime d on d.id = t.datekey and t.code = 'deb'
) t
group by t.mobilenumber
mobilenumber sumcre sumdeb mindate maxdate
------------ ----------- ----------- ----------------------- -----------------------
1 30 20 2017-01-01 00:00:00 2017-01-03 00:00:00
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
answered Nov 25 '18 at 11:47
P.SalmonP.Salmon
7,7822415
7,7822415
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53464633%2fcan-i-add-columns-including-lag-lead-to-get-least-and-recent-days-from-the-given%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
Welcome to stackoverflow. Please format your code as code (start lines of code with 4 spaces) this will improve readability and make it easier for users to answer.
– Simon.S.A.
Nov 25 '18 at 4:38
Not sure if I understand your question fully. Can you not use MIN(DATE) OVER (PARTITION BY MOBILE_NUMBER) AS FIRST_BORROW_DATE, MAX(DATE) OVER (PARTITION BY (MOBILE_NUMBER) AS LAST_BORROW_DATE. I feel your requirement is simple but the explanation is not very clear.
– WhoamI
Nov 25 '18 at 5:37
1
Check out How To Ask and the importance of a Minimal, Complete, Verifiable Example. Also, avoid pictures of code... After reading, Start Here to edit your question as needed, though you'll probably figure out the answer once you start formatting your code properly.
– Eric Brandt
Nov 25 '18 at 8:18
@wahoaml i have a test , requesting to do the desired output with LAG & LEAD
– Mahmoud Abd El Raouf
Nov 25 '18 at 12:41