How to append data to the previous row
-
That was going to be one of my other tests (and still will be to just to see what happens on different machines). Like I said, this has turned out to be a really interesting thread. Thanks for all the code and the testing, Jonathan.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It is nice to see so many posts that offering different solutions, as well as the performance analysis.
Regarding the real case scenario in my situation, I would expect to see about a few hundreds of events that could be activated/deactivated, and normally it shouldn't have more than three rows per event, i.e. Inactivate first, then reactivate, and finally inactivated.
However, I would definitely following Jonathan's sample test code to run it in my environment. It is a valuable learning experience.
Again, thank you all for your suggestions and advices.
-Jay
- jay-125866 wrote:
It is nice to see so many posts that offering different solutions, as well as the performance analysis.
Regarding the real case scenario in my situation, I would expect to see about a few hundreds of events that could be activated/deactivated, and normally it shouldn't have more than three rows per event, i.e. Inactivate first, then reactivate, and finally inactivated.
However, I would definitely following Jonathan's sample test code to run it in my environment. It is a valuable learning experience.
Again, thank you all for your suggestions and advices.
-Jay
I'm just getting off of work and haven't tried the code yet but, looking at the code that Jonathan wrote to create a substantial set of test data, I have to say he did an awesome job. It DOES require the fnTally function. If you don't have that or one like it that you could substitute for, you can get a version at the following URL. Heh... I can vouch for the guy who wrote it. 😀
https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just for fun I tried cutting and pasting the original question into ChatGPT, this is what it came up with. As far as I can tell it works.
You can use a self-join and conditional aggregation to achieve the desired result:
SELECT t1.EventId,
MAX(CASE WHEN t1.EventStatus = 'InActive' THEN t1.EventStatus END) AS InactiveEvent,
MAX(CASE WHEN t1.EventStatus = 'InActive' THEN t1.UpdatedDate END) AS InactivateDate,
MAX(CASE WHEN t2.EventStatus = 'ReActive' THEN t2.EventStatus END) AS ReactiveEvent,
MAX(CASE WHEN t2.EventStatus = 'ReActive' THEN t2.UpdatedDate END) AS ReactivateDate
FROM #tbSourceData t1
LEFT JOIN #tbSourceData t2
ON t1.EventId = t2.EventId
AND t1.RowNum + 1 = t2.RowNum
GROUP BY t1.EventId, t1.RowNum
HAVING MAX(CASE WHEN t1.EventStatus = 'InActive' THEN t1.EventStatus END) = 'InActive'____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Can you post the actual question that you asked it? I'm curious as to how much it needed to know.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
Can you post the actual question that you asked it? I'm curious as to how much it needed to know.
Literally all of the original question starting with "I have a table" and ending with the sample data, "Insert Into #tbSourceData..."
Try it yourself.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 - Jeff Moden wrote:
Can you post the actual question that you asked it? I'm curious as to how much it needed to know.
I just tried pasting the entire question, including the "Hi" and it gave me this answer:
SELECT t1.EventId,
MAX(CASE WHEN t1.EventStatus = 'InActive' THEN t1.EventStatus END) AS InactiveEvent,
MAX(CASE WHEN t1.EventStatus = 'InActive' THEN t1.UpdatedDate END) AS InactivateDate,
MAX(CASE WHEN t2.EventStatus = 'ReActive' THEN t2.EventStatus END) AS ReactiveEvent,
MAX(CASE WHEN t2.EventStatus = 'ReActive' THEN t2.UpdatedDate END) AS ReactivateDate
FROM #tbSourceData t1
LEFT JOIN #tbSourceData t2
ON t1.EventId = t2.EventId AND t1.RowNum = t2.RowNum - 1
GROUP BY t1.EventId, t1.RowNum
ORDER BY t1.EventId, t1.RowNum;I then said "can you indent the answer?" and it gave me this:
SELECT t1.EventId,
MAX(CASE WHEN t1.EventStatus = 'InActive' THEN t1.EventStatus END) AS InactiveEvent,
MAX(CASE WHEN t1.EventStatus = 'InActive' THEN t1.UpdatedDate END) AS InactivateDate,
MAX(CASE WHEN t2.EventStatus = 'ReActive' THEN t2.EventStatus END) AS ReactiveEvent,
MAX(CASE WHEN t2.EventStatus = 'ReActive' THEN t2.UpdatedDate END) AS ReactivateDate
FROM #tbSourceData t1
LEFT JOIN #tbSourceData t2
ON t1.EventId = t2.EventId AND t1.RowNum = t2.RowNum - 1
GROUP BY t1.EventId, t1.RowNum
ORDER BY t1.EventId, t1.RowNum;I then said "can you solve it using LEAD?"
Its answer:
SELECT EventId,
EventStatus AS InactiveEvent,
UpdatedDate AS InactivateDate,
LEAD(EventStatus) OVER (PARTITION BY EventId ORDER BY UpdatedDate) AS ReactiveEvent,
LEAD(UpdatedDate) OVER (PARTITION BY EventId ORDER BY UpdatedDate) AS ReactivateDate
FROM #tbSourceData
WHERE EventStatus = 'InActive'
ORDER BY EventId, UpdatedDate;It's not quite correct but give it a few more releases it will be doing common program tasks for us.
There is a certain amount of randomness built into its answers so if you ask it the same question at a later date it might give you a different answer and a different way of solving the solution.
- Jonathan AC Roberts wrote:
There is a certain amount of randomness built into its answers so if you ask it the same question at a later date it might give you a different answer and a different way of solving the solution.
Thanks, Jonathan. I have the same issue with people I work with. 😀 😀 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The next question should be, "How can that be optimized for performance"?
I'm actually impressed with its responses that you posted above. Impressed enough where I'm not actually going to try it. It'll also be interesting to see how it reacts to people that don't actually know how to ask such questions (as we've seen before) but I'm not going to be the one to test that.
As a bit of a sidebar, it's interesting that people asking such questions of it will also probably be the first to be replaced by it. 😀 Me? I could have retired 5 years ago... 😀 The reason I haven't is because this kind of stuff is good for the brain. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
On second thought, I DO have a couple of interesting questions to ask it to see how it does... especially when it comes to answers that actually preform well. And, yeah... I'm going to add it's answer to the testing I do on this using Jonathan's excellent data generator he built for this thread.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm officially "dead" with ChatGPT. It never asked me for a password when I first setup my login. I logged out and told it I forgot my password when I tried to log back in. I said it sent me instructions to my email address. I never got such an email and, yes, I checked the spam locker. I asked to resend. Still nothing.
While I was in, I asked it how to count from 1 to a million in t-sql. It gave me the usual answer of using a recursive CTE. It didn't include a MAXRecursion option and so it failed. I asked how to do it without an rCTE. It said to use a numbers table and then provided the same wrong rCTE to build the numbers table and without an index.
I also asked it to convert and adjacency list to nested sets. It provided a recursive CTE that kinda looked right but it didn't create the left or right bowers correctly. I asked it to do it without an rCTE and it produced the old push-stack code but it didn't create the bowers correctly either. On the rCTE method, it also created and accidental Cross Join in the rCTE.
I've not yet had a chance to evaluate the code that it wrote for the problem on this thread.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
I'm officially "dead" with ChatGPT. It never asked me for a password when I first setup my login. I logged out and told it I forgot my password when I tried to log back in. I said it sent me instructions to my email address. I never got such an email and, yes, I checked the spam locker. I asked to resend. Still nothing.
While I was in, I asked it how to count from 1 to a million in t-sql. It gave me the usual answer of using a recursive CTE. It didn't include a MAXRecursion option and so it failed. I asked how to do it without an rCTE. It said to use a numbers table and then provided the same wrong rCTE to build the numbers table and without an index.
I also asked it to convert and adjacency list to nested sets. It provided a recursive CTE that kinda looked right but it didn't create the left or right bowers correctly. I asked it to do it without an rCTE and it produced the old push-stack code but it didn't create the bowers correctly either. On the rCTE method, it also created and accidental Cross Join in the rCTE.
I've not yet had a chance to evaluate the code that it wrote for the problem on this thread.
I asked it "generate numbers 1 to a million in t-sql"
It came back with this:
WITH Nums AS (
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Nums
WHERE Num < 1000000
)
SELECT Num
FROM Nums
OPTION (MAXRECURSION 0);I then asked "Do it using the same method Jeff Moden would use"
It returned this (I've screenshot it just in case you don't believe it):
I then told it master..spt_values only had 2553 rows then this:
So eventually with a bit of prodding it arrived at a good answer.
-
I figured out how to get back in and it had saved my questions and answers.
Here was my first question and it's answer... note the lack of the MAXRECURSION option...
Then I asked it the same question but how to do it without a CTE.. notice anything familiar? {headdesk}
So, I asked the same question but asked it not to use a CTE or a numbers table... are you as impressed with the WHERE clause as I was? {headdesk}{headdesk}{headdesk}
It was definitely in the ball park but it couldn't find the pitcher's mound. 😀 I have to admit that I'm impressed with the technology but I'm concerned that people that don't know things (other than testing, why would they be asking) are going to take this information as gospel rather than remembering the disclaimers in the user agreement (that I read top to bottom)
The really good part about all this is it will help keep interviews of candidates applying for SQL positions really short.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You should have just asked it "How would Jeff Moden do it" - LOL
- Jonathan AC Roberts wrote:
I then asked "Do it using the same method Jeff Moden would use"
It returned this (I've screenshot it just in case you don't believe it):
BWAAA-HAAA-HAAA!!!! I just read what it returned. That's hilarious (I think it was just telling you what your wanted to hear by using the name you supplied because I've used that table maybe just a couple of times in my life) and humbling at the same time. Thanks for posting it. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply