September 27, 2018 at 10:32 am
In table UserData, it accumulates rows for users every time there is some sort of transaction. Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records than this. Any help in getting started would be appreciated! XLS file attached with data set sample.
CustNo CancelDate ID TimeStamp
---------- --------------- --------- ------------------
ABC NULL 2097 9/1/2018 (assume any hour / minute)
ABC NULL 2100 9/3/2018
ABC 9/4/2018 2147 9/4/2018
ABC 9/4/2018 2301 9/7/2018
ABC 9/4/2018 2423 9/15/2018
September 27, 2018 at 10:53 am
mnovosel - Thursday, September 27, 2018 10:32 AMIn table UserData, it accumulates rows for users every time there is some sort of transaction. Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records than this. Any help in getting started would be appreciated!CustNo CancelDate ID TimeStamp
---------- --------------- --------- ------------------
ABC NULL 2097 9/1/2018 (assume any hour / minute)
ABC NULL 2100 9/3/2018
ABC 9/4/2018 2147 9/4/2018
ABC 9/4/2018 2301 9/7/2018
ABC 9/4/2018 2423 9/15/2018
Some combination of LAG() and LEAD() should get this done for you. Can you post the data in consumable format (ie, one that can be cut, pasted and executed in SSMS)? Someone will help with the exact code required if you do this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 27, 2018 at 11:42 am
Phil Parkin - Thursday, September 27, 2018 10:53 AMmnovosel - Thursday, September 27, 2018 10:32 AMIn table UserData, it accumulates rows for users every time there is some sort of transaction. Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records than this. Any help in getting started would be appreciated!CustNo CancelDate ID TimeStamp
---------- --------------- --------- ------------------
ABC NULL 2097 9/1/2018 (assume any hour / minute)
ABC NULL 2100 9/3/2018
ABC 9/4/2018 2147 9/4/2018
ABC 9/4/2018 2301 9/7/2018
ABC 9/4/2018 2423 9/15/2018Some combination of LAG() and LEAD() should get this done for you. Can you post the data in consumable format (ie, one that can be cut, pasted and executed in SSMS)? Someone will help with the exact code required if you do this.
Thanks Phil, went ahead and attached an XLS file.
September 27, 2018 at 11:47 am
mnovosel - Thursday, September 27, 2018 11:42 AMThanks Phil, went ahead and attached an XLS file.
Text file would be better, as most people here won't open Excel files from 'strangers' ... just in case.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 27, 2018 at 12:22 pm
Worked my way through it and looks like I've got the solution more or less. Thanks Phil for the Lead function...never used it before. This more or less does what I'm looking for and might help others... The 1/1/1900 date is to later be able to compare dates (NULLS are allowed but they can't be compared to a date field). The 9/9/1900 date is the last record for each customer and will be disregarded.
;WITH CTE_LEAD
AS
(
select ID
, custno
, solddate
, CASE WHEN cancelDate IS NULL THEN '1/1/1900' ELSE CancelDate END as CancelDate
, LastSaved
, CASE WHEN LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) IS NULL THEN '1/1/1900'
ELSE LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) END as LeadCancel
from moxyholding
where CustNo in ('E591625627', 'E590673801')
)
SELECT *
FROM CTE_LEAD
WHERE cancelDate <> LeadCancel and LeadCancel <> '9/9/1900'
order by custno, id
September 27, 2018 at 12:23 pm
Phil Parkin - Thursday, September 27, 2018 11:47 AMmnovosel - Thursday, September 27, 2018 11:42 AMThanks Phil, went ahead and attached an XLS file.Text file would be better, as most people here won't open Excel files from 'strangers' ... just in case.
I see, no worries. I posted the solution I came up with using Lead and a CTE.
September 27, 2018 at 1:00 pm
mnovosel - Thursday, September 27, 2018 12:22 PMWorked my way through it and looks like I've got the solution more or less. Thanks Phil for the Lead function...never used it before. This more or less does what I'm looking for and might help others... The 1/1/1900 date is to later be able to compare dates (NULLS are allowed but they can't be compared to a date field). The 9/9/1900 date is the last record for each customer and will be disregarded.
;WITH CTE_LEAD
AS
(
select ID
, custno
, solddate
, CASE WHEN cancelDate IS NULL THEN '1/1/1900' ELSE CancelDate END as CancelDate
, LastSaved
, CASE WHEN LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) IS NULL THEN '1/1/1900'
ELSE LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) END as LeadCancel
from moxyholding
where CustNo in ('E591625627', 'E590673801')
)SELECT *
FROM CTE_LEAD
WHERE cancelDate <> LeadCancel and LeadCancel <> '9/9/1900'
order by custno, id
Just a couple of nitpicky things but overall good job.
1) CTEs don't start with a semicolon. The semicolon is a terminator not a beginninator. If you read the documentation for CTEs it states that the previous statement must be terminated with a semicolon. We ahve seen people having problems with creating views where the select statement uses a CTE and the create view fails because they have preceded the WITH with a semicolon. You really need to get into the habit of terminating your SQL statements with semicolons. The THROW statement is like the WITH for a CTE in that it requires the previous statement to be terminated with a semicolon. Also, the MERGE statement must be terminated with a semicolon. Just a good habit to start.
2) Your dates in the code. You really should use either the YYYY-MM-DD or YYYYMMDD format for your date strings. The first may still fail depending on the settings for your dates (mdy or dmy) and the date type you are using, but the latter will always convert correctly.
September 27, 2018 at 5:09 pm
mnovosel - Thursday, September 27, 2018 10:32 AMIn table UserData, it accumulates rows for users every time there is some sort of transaction. Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records than this. Any help in getting started would be appreciated! XLS file attached with data set sample.CustNo CancelDate ID TimeStamp
---------- --------------- --------- ------------------
ABC NULL 2097 9/1/2018 (assume any hour / minute)
ABC NULL 2100 9/3/2018
ABC 9/4/2018 2147 9/4/2018
ABC 9/4/2018 2301 9/7/2018
ABC 9/4/2018 2423 9/15/2018
Please post DDL and follow ANSI/ISO standards when asking for help.
September 27, 2018 at 5:10 pm
mnovosel - Thursday, September 27, 2018 10:32 AMIn table UserData, it accumulates rows for users every time there is some sort of transaction. Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records than this. Any help in getting started would be appreciated! XLS file attached with data set sample.CustNo CancelDate ID TimeStamp
---------- --------------- --------- ------------------
ABC NULL 2097 9/1/2018 (assume any hour / minute)
ABC NULL 2100 9/3/2018
ABC 9/4/2018 2147 9/4/2018
ABC 9/4/2018 2301 9/7/2018
ABC 9/4/2018 2423 9/15/2018
>> In table UserData, it accumulates rows for users every time there is some sort of transaction. <<
Where is the DDL for this table? Why do you think that this is a good, precise, clear name that describes what you've got? I find it to be vague and useless.
>> Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records [sic] returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records [sic] than this. <<
Rows are nothing whatsoever like records. Also, a spreadsheet is nothing like a relational table. Then your picture doesn't even use the correct date format; the only one allowed in ANSI/ISO standard SQL is "yyyy-mm-dd" and not the local dialect you published. There is also no such thing as a generic "id" or a generic "timestamp" in RDBMS. These are called attribute properties and they have to be attach to a particular entity.
>> Any help in getting started would be appreciated! XLS file attached with data set sample.<<
Because you were too lazy to post DDL, you expect us to go and open up a file on the Internet. Do you often open up files from people that you don't know when you're on the Internet? I hope not!
Why don't you try posting this again and follow basic forum and netiquette?
Please post DDL and follow ANSI/ISO standards when asking for help.
September 27, 2018 at 6:22 pm
jcelko212 32090 - Thursday, September 27, 2018 5:10 PMmnovosel - Thursday, September 27, 2018 10:32 AMIn table UserData, it accumulates rows for users every time there is some sort of transaction. Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records than this. Any help in getting started would be appreciated! XLS file attached with data set sample.CustNo CancelDate ID TimeStamp
---------- --------------- --------- ------------------
ABC NULL 2097 9/1/2018 (assume any hour / minute)
ABC NULL 2100 9/3/2018
ABC 9/4/2018 2147 9/4/2018
ABC 9/4/2018 2301 9/7/2018
ABC 9/4/2018 2423 9/15/2018>> In table UserData, it accumulates rows for users every time there is some sort of transaction. <<
Where is the DDL for this table? Why do you think that this is a good, precise, clear name that describes what you've got? I find it to be vague and useless.
>> Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records [sic] returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records [sic] than this. <<
Rows are nothing whatsoever like records. Also, a spreadsheet is nothing like a relational table. Then your picture doesn't even use the correct date format; the only one allowed in ANSI/ISO standard SQL is "yyyy-mm-dd" and not the local dialect you published. There is also no such thing as a generic "id" or a generic "timestamp" in RDBMS. These are called attribute properties and they have to be attach to a particular entity.
>> Any help in getting started would be appreciated! XLS file attached with data set sample.<<
Because you were too lazy to post DDL, you expect us to go and open up a file on the Internet. Do you often open up files from people that you don't know when you're on the Internet? I hope not!
Why don't you try posting this again and follow basic forum and netiquette?
Hi Joe, first off my apologies for not following the proper netiquette here for you. Phil told me to attach a file and so I did. Now had you read juuuust a wee bit further you'd see that the issue was addressed. But thank you for your "words of wisdom".
So I wasn't lazy to post a DDL, just made a mistake. Issue has been resolved too. But thanks Joe, it's good to encounter feedback like yours which is amazeballs. You clearly had some frustration to get out and I'm glad you did !
September 27, 2018 at 6:29 pm
Lynn Pettis - Thursday, September 27, 2018 1:00 PMmnovosel - Thursday, September 27, 2018 12:22 PMWorked my way through it and looks like I've got the solution more or less. Thanks Phil for the Lead function...never used it before. This more or less does what I'm looking for and might help others... The 1/1/1900 date is to later be able to compare dates (NULLS are allowed but they can't be compared to a date field). The 9/9/1900 date is the last record for each customer and will be disregarded.
;WITH CTE_LEAD
AS
(
select ID
, custno
, solddate
, CASE WHEN cancelDate IS NULL THEN '1/1/1900' ELSE CancelDate END as CancelDate
, LastSaved
, CASE WHEN LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) IS NULL THEN '1/1/1900'
ELSE LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) END as LeadCancel
from moxyholding
where CustNo in ('E591625627', 'E590673801')
)SELECT *
FROM CTE_LEAD
WHERE cancelDate <> LeadCancel and LeadCancel <> '9/9/1900'
order by custno, idJust a couple of nitpicky things but overall good job.
1) CTEs don't start with a semicolon. The semicolon is a terminator not a beginninator. If you read the documentation for CTEs it states that the previous statement must be terminated with a semicolon. We ahve seen people having problems with creating views where the select statement uses a CTE and the create view fails because they have preceded the WITH with a semicolon. You really need to get into the habit of terminating your SQL statements with semicolons. The THROW statement is like the WITH for a CTE in that it requires the previous statement to be terminated with a semicolon. Also, the MERGE statement must be terminated with a semicolon. Just a good habit to start.
2) Your dates in the code. You really should use either the YYYY-MM-DD or YYYYMMDD format for your date strings. The first may still fail depending on the settings for your dates (mdy or dmy) and the date type you are using, but the latter will always convert correctly.
Thank you Lynn! I was partly pulling apart someone else's stored procedure and I noticed that at this company they seem to all have a semi colon there rather than on the same line as the declare for the variable so I just kept it in tune with what's there but valid point and I'll ask for any future procedures if that should change. Same for the dates, valid point and since I'm temporarily helping out with a few reports I've just worked using the formatting they have previously But I'll look into changing that since it's the better way to go. Thanks again!
September 28, 2018 at 6:54 am
mnovosel - Thursday, September 27, 2018 6:22 PMHi Joe, first off my apologies for not following the proper netiquette here for you. Phil told me to attach a file and so I did. Now had you read juuuust a wee bit further you'd see that the issue was addressed. But thank you for your "words of wisdom".So I wasn't lazy to post a DDL, just made a mistake. Issue has been resolved too. But thanks Joe, it's good to encounter feedback like yours which is amazeballs. You clearly had some frustration to get out and I'm glad you did !
I'm pretty sure you've got some well deserved sarcasm for your "buddy" Joe in there, but don't be happy that he "had some frustration to get out"... He never runs out, and does this to pretty much everyone. Yep, it's annoying, and most of the time, he completely misses the point, too. Why he continues to remain a member of this forum continues to remain a mystery to me. He so frequently violates the very standards he helped create, that hypocrite is the least appellation he's deserving of. I'm pretty sure that there a quite a few considerably less PC appellations he'd be assigned if the entire forum save him were to meet in person and discuss the topic. Best course of action is to just ignore him.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 28, 2018 at 7:55 am
mnovosel - Thursday, September 27, 2018 12:22 PMWorked my way through it and looks like I've got the solution more or less. Thanks Phil for the Lead function...never used it before. This more or less does what I'm looking for and might help others... The 1/1/1900 date is to later be able to compare dates (NULLS are allowed but they can't be compared to a date field). The 9/9/1900 date is the last record for each customer and will be disregarded.
;WITH CTE_LEAD
AS
(
select ID
, custno
, solddate
, CASE WHEN cancelDate IS NULL THEN '1/1/1900' ELSE CancelDate END as CancelDate
, LastSaved
, CASE WHEN LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) IS NULL THEN '1/1/1900'
ELSE LEAD(CancelDate, 1, '9/9/1900') OVER (PARTITION BY Custno ORDER BY custno, id) END as LeadCancel
from moxyholding
where CustNo in ('E591625627', 'E590673801')
)SELECT *
FROM CTE_LEAD
WHERE cancelDate <> LeadCancel and LeadCancel <> '9/9/1900'
order by custno, id
You should not use dates in the past (1900-09-09) for unknown future dates. You are better off using a far off future data. Common dates are (9999-12-30, 9999-01-01, and 9000-01-01). That way both the known and unknown dates can be handled the same way. Otherwise you have to make exceptions for dates that represent unknown future dates.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 28, 2018 at 8:35 am
mnovosel - Thursday, September 27, 2018 6:22 PMjcelko212 32090 - Thursday, September 27, 2018 5:10 PMmnovosel - Thursday, September 27, 2018 10:32 AMIn table UserData, it accumulates rows for users every time there is some sort of transaction. Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records than this. Any help in getting started would be appreciated! XLS file attached with data set sample.CustNo CancelDate ID TimeStamp
---------- --------------- --------- ------------------
ABC NULL 2097 9/1/2018 (assume any hour / minute)
ABC NULL 2100 9/3/2018
ABC 9/4/2018 2147 9/4/2018
ABC 9/4/2018 2301 9/7/2018
ABC 9/4/2018 2423 9/15/2018>> In table UserData, it accumulates rows for users every time there is some sort of transaction. <<
Where is the DDL for this table? Why do you think that this is a good, precise, clear name that describes what you've got? I find it to be vague and useless.
>> Looking to pull in this instance the two rows any time the CancelDate changed. In this instance, the two records [sic] returned should be the row with ID 2100 and 2147. Each CustNo can have more or fewer records [sic] than this. <<
Rows are nothing whatsoever like records. Also, a spreadsheet is nothing like a relational table. Then your picture doesn't even use the correct date format; the only one allowed in ANSI/ISO standard SQL is "yyyy-mm-dd" and not the local dialect you published. There is also no such thing as a generic "id" or a generic "timestamp" in RDBMS. These are called attribute properties and they have to be attach to a particular entity.
>> Any help in getting started would be appreciated! XLS file attached with data set sample.<<
Because you were too lazy to post DDL, you expect us to go and open up a file on the Internet. Do you often open up files from people that you don't know when you're on the Internet? I hope not!
Why don't you try posting this again and follow basic forum and netiquette?
Hi Joe, first off my apologies for not following the proper netiquette here for you. Phil told me to attach a file and so I did. Now had you read juuuust a wee bit further you'd see that the issue was addressed. But thank you for your "words of wisdom".
So I wasn't lazy to post a DDL, just made a mistake. Issue has been resolved too. But thanks Joe, it's good to encounter feedback like yours which is amazeballs. You clearly had some frustration to get out and I'm glad you did !
No frustration on his part, that's just Mr. Celko at his warmest. And yes, I am sure you were being sarcastic.
October 9, 2018 at 1:23 pm
Phil told me to attach a file and so I did.
Phil did not tell you to attach a file 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply