How to PIVOT the next ROW_NUMBER()How does database indexing work?How can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?How can I do an UPDATE statement with JOIN in SQL?How do I UPDATE from a SELECT in SQL Server?How to Delete using INNER JOIN with SQL Server?How to import an SQL file using the command line in MySQL?
Is it legal for company to use my work email to pretend I still work there?
Assassin's bullet with mercury
Brothers & sisters
I would say: "You are another teacher", but she is a woman and I am a man
What mechanic is there to disable a threat instead of killing it?
How can I tell someone that I want to be his or her friend?
What's the difference between 'rename' and 'mv'?
What killed these X2 caps?
Combinations of multiple lists
Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?
How could indestructible materials be used in power generation?
How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?
Facing a paradox: Earnshaw's theorem in one dimension
What does it mean to describe someone as a butt steak?
Alternative to sending password over mail?
Is there a hemisphere-neutral way of specifying a season?
Can a rocket refuel on Mars from water?
Etiquette around loan refinance - decision is going to cost first broker a lot of money
How much of data wrangling is a data scientist's job?
How to model explosives?
In Romance of the Three Kingdoms why do people still use bamboo sticks when papers are already invented?
intersection of two sorted vectors in C++
What exploit are these user agents trying to use?
What is the PIE reconstruction for word-initial alpha with rough breathing?
How to PIVOT the next ROW_NUMBER()
How does database indexing work?How can I prevent SQL injection in PHP?How do I perform an IF…THEN in an SQL SELECT?How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?How can I do an UPDATE statement with JOIN in SQL?How do I UPDATE from a SELECT in SQL Server?How to Delete using INNER JOIN with SQL Server?How to import an SQL file using the command line in MySQL?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a temp table that is populated by this query:
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
Which then gets me the following into the temp table:
property stayDate addedTimeStamp rowNumber
0053 2020-03-20 2019-03-04 17:10:32.837 1
0053 2020-03-20 2019-03-05 17:10:29.480 2
0053 2020-03-20 2019-03-06 17:10:25.940 3
0053 2020-03-20 2019-03-07 17:10:21.930 4
0100 2020-03-25 2019-03-04 17:10:32.837 1
0100 2020-03-25 2019-03-05 17:10:29.480 2
0100 2020-03-25 2019-03-06 17:10:25.940 3
0100 2020-03-25 2019-03-07 17:10:21.930 4
Out of here I would like to have the property
, stayDate
, addedTimeStamp
, and then the next addedTimeStamp
in the group and if it is the max to just return NULL or whatever...not sure if that makes sense...
What my end goal is to essentially get the following out of that temp table:
property stayDate firstTimeStamp secondTimeStamp
0053 2020-03-20 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0053 2020-03-20 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0053 2020-03-20 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0053 2020-03-20 2019-03-06 17:10:25.940 NULL
0100 2020-03-25 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0100 2020-03-25 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0100 2020-03-25 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0100 2020-03-25 2019-03-06 17:10:25.940 NULL
sql sql-server sql-server-2016
add a comment |
I have a temp table that is populated by this query:
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
Which then gets me the following into the temp table:
property stayDate addedTimeStamp rowNumber
0053 2020-03-20 2019-03-04 17:10:32.837 1
0053 2020-03-20 2019-03-05 17:10:29.480 2
0053 2020-03-20 2019-03-06 17:10:25.940 3
0053 2020-03-20 2019-03-07 17:10:21.930 4
0100 2020-03-25 2019-03-04 17:10:32.837 1
0100 2020-03-25 2019-03-05 17:10:29.480 2
0100 2020-03-25 2019-03-06 17:10:25.940 3
0100 2020-03-25 2019-03-07 17:10:21.930 4
Out of here I would like to have the property
, stayDate
, addedTimeStamp
, and then the next addedTimeStamp
in the group and if it is the max to just return NULL or whatever...not sure if that makes sense...
What my end goal is to essentially get the following out of that temp table:
property stayDate firstTimeStamp secondTimeStamp
0053 2020-03-20 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0053 2020-03-20 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0053 2020-03-20 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0053 2020-03-20 2019-03-06 17:10:25.940 NULL
0100 2020-03-25 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0100 2020-03-25 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0100 2020-03-25 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0100 2020-03-25 2019-03-06 17:10:25.940 NULL
sql sql-server sql-server-2016
add a comment |
I have a temp table that is populated by this query:
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
Which then gets me the following into the temp table:
property stayDate addedTimeStamp rowNumber
0053 2020-03-20 2019-03-04 17:10:32.837 1
0053 2020-03-20 2019-03-05 17:10:29.480 2
0053 2020-03-20 2019-03-06 17:10:25.940 3
0053 2020-03-20 2019-03-07 17:10:21.930 4
0100 2020-03-25 2019-03-04 17:10:32.837 1
0100 2020-03-25 2019-03-05 17:10:29.480 2
0100 2020-03-25 2019-03-06 17:10:25.940 3
0100 2020-03-25 2019-03-07 17:10:21.930 4
Out of here I would like to have the property
, stayDate
, addedTimeStamp
, and then the next addedTimeStamp
in the group and if it is the max to just return NULL or whatever...not sure if that makes sense...
What my end goal is to essentially get the following out of that temp table:
property stayDate firstTimeStamp secondTimeStamp
0053 2020-03-20 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0053 2020-03-20 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0053 2020-03-20 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0053 2020-03-20 2019-03-06 17:10:25.940 NULL
0100 2020-03-25 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0100 2020-03-25 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0100 2020-03-25 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0100 2020-03-25 2019-03-06 17:10:25.940 NULL
sql sql-server sql-server-2016
I have a temp table that is populated by this query:
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
Which then gets me the following into the temp table:
property stayDate addedTimeStamp rowNumber
0053 2020-03-20 2019-03-04 17:10:32.837 1
0053 2020-03-20 2019-03-05 17:10:29.480 2
0053 2020-03-20 2019-03-06 17:10:25.940 3
0053 2020-03-20 2019-03-07 17:10:21.930 4
0100 2020-03-25 2019-03-04 17:10:32.837 1
0100 2020-03-25 2019-03-05 17:10:29.480 2
0100 2020-03-25 2019-03-06 17:10:25.940 3
0100 2020-03-25 2019-03-07 17:10:21.930 4
Out of here I would like to have the property
, stayDate
, addedTimeStamp
, and then the next addedTimeStamp
in the group and if it is the max to just return NULL or whatever...not sure if that makes sense...
What my end goal is to essentially get the following out of that temp table:
property stayDate firstTimeStamp secondTimeStamp
0053 2020-03-20 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0053 2020-03-20 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0053 2020-03-20 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0053 2020-03-20 2019-03-06 17:10:25.940 NULL
0100 2020-03-25 2019-03-04 17:10:32.837 2019-03-05 17:10:29.480
0100 2020-03-25 2019-03-05 17:10:29.480 2019-03-06 17:10:25.940
0100 2020-03-25 2019-03-06 17:10:25.940 2019-03-07 17:10:21.930
0100 2020-03-25 2019-03-06 17:10:25.940 NULL
sql sql-server sql-server-2016
sql sql-server sql-server-2016
edited Mar 9 at 0:03
Vladimir Baranov
23.1k52965
23.1k52965
asked Mar 8 at 23:31
FemmerFemmer
296
296
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You want lead()
, but you don't need a subquery:
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)), 4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS firstTimeStamp,
LEAD(added_timestamp) OVER (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
Thank you! This works perfectly!
– Femmer
Mar 11 at 14:16
add a comment |
This is a perfect example to use the LEAD
window function
LEAD(firstTimeStamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
You can put LEAD
in your main query and you can remove ROW_NUMBER
if you don't need it elsewhere.
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber, att.secondTimeStamp
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber,
LEAD(added_timestamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
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%2f55072414%2fhow-to-pivot-the-next-row-number%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You want lead()
, but you don't need a subquery:
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)), 4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS firstTimeStamp,
LEAD(added_timestamp) OVER (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
Thank you! This works perfectly!
– Femmer
Mar 11 at 14:16
add a comment |
You want lead()
, but you don't need a subquery:
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)), 4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS firstTimeStamp,
LEAD(added_timestamp) OVER (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
Thank you! This works perfectly!
– Femmer
Mar 11 at 14:16
add a comment |
You want lead()
, but you don't need a subquery:
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)), 4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS firstTimeStamp,
LEAD(added_timestamp) OVER (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
You want lead()
, but you don't need a subquery:
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)), 4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS firstTimeStamp,
LEAD(added_timestamp) OVER (PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
answered Mar 9 at 4:20
Gordon LinoffGordon Linoff
794k37318421
794k37318421
Thank you! This works perfectly!
– Femmer
Mar 11 at 14:16
add a comment |
Thank you! This works perfectly!
– Femmer
Mar 11 at 14:16
Thank you! This works perfectly!
– Femmer
Mar 11 at 14:16
Thank you! This works perfectly!
– Femmer
Mar 11 at 14:16
add a comment |
This is a perfect example to use the LEAD
window function
LEAD(firstTimeStamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
You can put LEAD
in your main query and you can remove ROW_NUMBER
if you don't need it elsewhere.
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber, att.secondTimeStamp
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber,
LEAD(added_timestamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
add a comment |
This is a perfect example to use the LEAD
window function
LEAD(firstTimeStamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
You can put LEAD
in your main query and you can remove ROW_NUMBER
if you don't need it elsewhere.
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber, att.secondTimeStamp
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber,
LEAD(added_timestamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
add a comment |
This is a perfect example to use the LEAD
window function
LEAD(firstTimeStamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
You can put LEAD
in your main query and you can remove ROW_NUMBER
if you don't need it elsewhere.
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber, att.secondTimeStamp
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber,
LEAD(added_timestamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
This is a perfect example to use the LEAD
window function
LEAD(firstTimeStamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
You can put LEAD
in your main query and you can remove ROW_NUMBER
if you don't need it elsewhere.
SELECT att.property, att.stayDate, att.addedTimeStamp, att.rowNumber, att.secondTimeStamp
FROM
(
SELECT RIGHT('000' + CAST(att.property AS VARCHAR(4)),4) AS property,
CAST(att.stay_date AS DATE) AS stayDate,
CAST(added_timestamp AS DATETIME) AS addedTimeStamp,
ROW_NUMBER() OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS rowNumber,
LEAD(added_timestamp) OVER
(PARTITION BY property, stay_date ORDER BY added_timestamp) AS secondTimeStamp
FROM dbo.tb_rm_portal_attention_days att
WHERE att.revenue_initiative = 'Test'
) att
edited Mar 9 at 0:08
answered Mar 8 at 23:58
Vladimir BaranovVladimir Baranov
23.1k52965
23.1k52965
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%2f55072414%2fhow-to-pivot-the-next-row-number%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