March 26, 2019 at 7:11 am
Greetings everyone!
I'm trying to write a query that sets up several rows as column values instead, similar to a pivot, though I've run into a bit of a an issue.
The data looks similar to this:
UserID, UserName, Workstation ID, LoginDate, Success/Fail
The data is spread across 3 tables: UserTable, WorkstationTable, LoginHistory
I want to get the previous 12 login attempts, and if there's isn't 12, to fill in with default data (to keep an export to a CSV consistent).
The way I went about it is like this:
select act.userid,
rtrim(ltrim(act2.username)),
isnull(stuff(
(
select top 12 ',' + left(convert(varchar,b.login_date,120),10) + ',' + isnull(convert(varchar,b.success,120),0)
from LoginHist b
cross apply (select top 1 success, login_date from LoginHist where login_date = b.login_date) tb6
WHERE b.userid = log.userid
order by b.login_date desc
for xml path('')
),1,1,''),',0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0,,0') as [LoginData]
from UserAccts act
join LoginTbl log on log.userid = act.userid
join AcctTbl2 on act2.userid = act.userid
order by act.account_no asc
This works up until there's not enough of a history for the logins, in which case will throw off the formatting of the CSV file to not have the same number of fields per row. What can I do to check the records returned and format the [LoginData] to match the appropriate fields? If this isn't the best method, please let me know and point me to the right direction. I attempted to use PIVOT without success, and this gave me the closest result I need. I can do this through an application, but would prefer to keep it strictly SQL if possible.
Thanks in advance!
March 26, 2019 at 8:08 am
Without any test data to work with, and for both scenarios (you have enough rows, and, you don't), there's not a lot of incentive for anyone to try and figure this out, as they have no way to test your scenario without doing a heck of a lot of work. Please provide create table scripts and inserts for the sample data. Help us help you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 26, 2019 at 9:05 am
It's cool, I figured it out 🙂
Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
March 26, 2019 at 10:00 am
Dra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
Then maybe you didn't need help so much as a push to take care of more of the problem on your own. What you may fail to realize is that most contributors here want to be sure their solution can actually be tested before they provide it. That's not something anyone would have been able to do with the code you posted, as they don't have your data. Or any sample data representative of the domain of the problem. Imagine trying to solve your problem, and not having access to any of your data, and not having your knowledge of the data and tables. If you think that's an attitude worthy of criticism, then yeah, maybe you should be elsewhere. After all, take a look at the time between your post and mine - an hour... a virtual eternity around here. Most problems with posted code AND sample data are quick fixes in less time. You really do have to help us help you. Maybe, just maybe, I pushed you to help yourself enough to solve it. I can take comfort in that...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 26, 2019 at 10:15 am
Dra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
So you expect us to not only volunteer our time and expertise to give you free working code, you expect us also to create and set up test data for your issue? Even though all we're asking you to do is "fill in a few dates and numbers" of data that you already have in a table and we don't? Wow.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2019 at 12:39 pm
Dra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
Also forum etiquette would have you post your solution should others have a similar problem or issue.
March 26, 2019 at 1:49 pm
sgmunson - Tuesday, March 26, 2019 10:00 AMDra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
Then maybe you didn't need help so much as a push to take care of more of the problem on your own. What you may fail to realize is that most contributors here want to be sure their solution can actually be tested before they provide it. That's not something anyone would have been able to do with the code you posted, as they don't have your data. Or any sample data representative of the domain of the problem. Imagine trying to solve your problem, and not having access to any of your data, and not having your knowledge of the data and tables. If you think that's an attitude worthy of criticism, then yeah, maybe you should be elsewhere. After all, take a look at the time between your post and mine - an hour... a virtual eternity around here. Most problems with posted code AND sample data are quick fixes in less time. You really do have to help us help you. Maybe, just maybe, I pushed you to help yourself enough to solve it. I can take comfort in that...
I've been working on the issue for the last few days - figuring I might have missed something with the functions I was using, I figured I'd seek the aid of better eyes than my own. I get it that folks want to be sure their code works properly before offering a solution, but I wasn't asking for a rewrite or a "hai gais, plz do dis fur me kthnx?!"
"What can I do to check the records returned and format the [LoginData] to match the appropriate fields? If this isn't the best method, please let me know and point me to the right direction. I attempted to use PIVOT without success, and this gave me the closest result I need. I can do this through an application, but would prefer to keep it strictly SQL if possible."
And no, your response didn't "push me to help myself". I came back to post that I had a solution, and then saw the most condescding, elitest comments ever. No reason to give yourself assumed credit when it's certainly not due. Egoism seems to be the norm around here.
ScottPletcher - Tuesday, March 26, 2019 10:15 AMDra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
So you expect us to not only volunteer our time and expertise to give you free working code, you expect us also to create and set up test data for your issue? Even though all we're asking you to do is "fill in a few dates and numbers" of data that you already have in a table and we don't? Wow.
Clearly you're so self important that you assumed I wanted free working code. As quoted above:
"What can I do to check the records returned and format the [LoginData] to match the appropriate fields? If this isn't the best method, please let me know and point me to the right direction. I attempted to use PIVOT without success, and this gave me the closest result I need. I can do this through an application, but would prefer to keep it strictly SQL if possible."
That doesn't merit any code, just some suggestions to try. I'm sorry I work with classified info and had to make it as ambiguous as possible. Didn't realize writing down a handful of numbers (user id, workstation id, date, and a 1 or 0 to show a success/fail). CLEARLY that requires soooooooooooooooooooooooooooooooooo much work and effort that it warrants attitude like yours. I'm willing to bet you'd never speak like this to a person in real life, so keep your ego in check and maybe you'll be a better (and possibly happier) person for it.
Lynn Pettis - Tuesday, March 26, 2019 12:39 PMDra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
Also forum etiquette would have you post your solution should others have a similar problem or issue.
Had I been shown some etiquette to begin with, I'd have posted the solution.
I mean, there's definitely a reason why I've only made 2 threads (and maybe a total of 4 posts, including the ones here) in 12 years.
March 26, 2019 at 2:07 pm
Dra-489663 - Tuesday, March 26, 2019 1:49 PMsgmunson - Tuesday, March 26, 2019 10:00 AMDra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
Then maybe you didn't need help so much as a push to take care of more of the problem on your own. What you may fail to realize is that most contributors here want to be sure their solution can actually be tested before they provide it. That's not something anyone would have been able to do with the code you posted, as they don't have your data. Or any sample data representative of the domain of the problem. Imagine trying to solve your problem, and not having access to any of your data, and not having your knowledge of the data and tables. If you think that's an attitude worthy of criticism, then yeah, maybe you should be elsewhere. After all, take a look at the time between your post and mine - an hour... a virtual eternity around here. Most problems with posted code AND sample data are quick fixes in less time. You really do have to help us help you. Maybe, just maybe, I pushed you to help yourself enough to solve it. I can take comfort in that...
I've been working on the issue for the last few days - figuring I might have missed something with the functions I was using, I figured I'd seek the aid of better eyes than my own. I get it that folks want to be sure their code works properly before offering a solution, but I wasn't asking for a rewrite or a "hai gais, plz do dis fur me kthnx?!"
"What can I do to check the records returned and format the [LoginData] to match the appropriate fields? If this isn't the best method, please let me know and point me to the right direction. I attempted to use PIVOT without success, and this gave me the closest result I need. I can do this through an application, but would prefer to keep it strictly SQL if possible."
And no, your response didn't "push me to help myself". I came back to post that I had a solution, and then saw the most condescding, elitest comments ever. No reason to give yourself assumed credit when it's certainly not due. Egoism seems to be the norm around here.
ScottPletcher - Tuesday, March 26, 2019 10:15 AMDra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
So you expect us to not only volunteer our time and expertise to give you free working code, you expect us also to create and set up test data for your issue? Even though all we're asking you to do is "fill in a few dates and numbers" of data that you already have in a table and we don't? Wow.
Clearly you're so self important that you assumed I wanted free working code. As quoted above:
"What can I do to check the records returned and format the [LoginData] to match the appropriate fields? If this isn't the best method, please let me know and point me to the right direction. I attempted to use PIVOT without success, and this gave me the closest result I need. I can do this through an application, but would prefer to keep it strictly SQL if possible."That doesn't merit any code, just some suggestions to try. I'm sorry I work with classified info and had to make it as ambiguous as possible. Didn't realize writing down a handful of numbers (user id, workstation id, date, and a 1 or 0 to show a success/fail). CLEARLY that requires soooooooooooooooooooooooooooooooooo much work and effort that it warrants attitude like yours. I'm willing to bet you'd never speak like this to a person in real life, so keep your ego in check and maybe you'll be a better (and possibly happier) person for it.
Lynn Pettis - Tuesday, March 26, 2019 12:39 PMDra-489663 - Tuesday, March 26, 2019 9:05 AMIt's cool, I figured it out 🙂Also I disagree with a "heck of a lot of work" as it's filling in a few dates and a few numbers.
And if this is the common attitude towards new posters, I'll just stay off this site.
Also forum etiquette would have you post your solution should others have a similar problem or issue.
Had I been shown some etiquette to begin with, I'd have posted the solution.
I mean, there's definitely a reason why I've only made 2 threads (and maybe a total of 4 posts, including the ones here) in 12 years.
Here is the problem, without the DDL, sample data, expected results we can't provide you with a solution. Given that you haven't posted your solution, we can't tell you if there may be a better a way to accomplish what your are doing.
Looking at your code I have no real idea what it is doing nor do I have a way to run it to see what it is doing. How could I help you if I don't fully understand the problem you are experiencing? This isn't about ego on our part, it is about trying to provide the best help possible and that can only happen if you help us help you.
March 26, 2019 at 2:43 pm
Lynn Pettis - Tuesday, March 26, 2019 2:07 PMHere is the problem, without the DDL, sample data, expected results we can't provide you with a solution. Given that you haven't posted your solution, we can't tell you if there may be a better a way to accomplish what your are doing.Looking at your code I have no real idea what it is doing nor do I have a way to run it to see what it is doing. How could I help you if I don't fully understand the problem you are experiencing? This isn't about ego on our part, it is about trying to provide the best help possible and that can only happen if you help us help you.
Lynn, I wasn't referring to you about the ego comments, so I apologize if you feel otherwise.
I thought my goal and issue was fairly straight forward:
"I'm trying to write a query that sets up several rows as column values instead, similar to a pivot, though I've run into a bit of a an issue.
.....
I want to get the previous 12 login attempts, and if there's isn't 12, to fill in with default data (to keep an export to a CSV consistent)."
That's really the only important part. The code (albeit a bit sloppy due to having to mask actual table and field names) showed that I use using the STUFF function on an XML formatted string to place the date and login success in a comma separated string (and if the string was null, to insert the CSV string to keep it consistent). The problem came with there not being a total of 12 login attempts, but say 6 or 11, then it wouldn't format the string properly.
I would've HAPPILY explained in more details, but the attitude given was complete BS. If that's the way new members are treated, this isn't a place I want to visit on a regular basis, if ever, after this thread runs its course.
March 26, 2019 at 3:07 pm
Dra-489663 - Tuesday, March 26, 2019 2:43 PMLynn Pettis - Tuesday, March 26, 2019 2:07 PMHere is the problem, without the DDL, sample data, expected results we can't provide you with a solution. Given that you haven't posted your solution, we can't tell you if there may be a better a way to accomplish what your are doing.Looking at your code I have no real idea what it is doing nor do I have a way to run it to see what it is doing. How could I help you if I don't fully understand the problem you are experiencing? This isn't about ego on our part, it is about trying to provide the best help possible and that can only happen if you help us help you.
Lynn, I wasn't referring to you about the ego comments, so I apologize if you feel otherwise.
I thought my goal and issue was fairly straight forward:
"I'm trying to write a query that sets up several rows as column values instead, similar to a pivot, though I've run into a bit of a an issue.
.....
I want to get the previous 12 login attempts, and if there's isn't 12, to fill in with default data (to keep an export to a CSV consistent)."That's really the only important part. The code (albeit a bit sloppy due to having to mask actual table and field names) showed that I use using the STUFF function on an XML formatted string to place the date and login success in a comma separated string (and if the string was null, to insert the CSV string to keep it consistent). The problem came with there not being a total of 12 login attempts, but say 6 or 11, then it wouldn't format the string properly.
I would've HAPPILY explained in more details, but the attitude given was complete BS. If that's the way new members are treated, this isn't a place I want to visit on a regular basis, if ever, after this thread runs its course.
Again, looking at the code you posted, I don't see how it would work. And again, with nothing to run it against I can't see what it actually does. Had you posted the DDL for the tables, some sample data representative of the problem, and the expected results you would have gotten a thought out and working piece of code that, if not completely usable in your environment, would have helped you develop a solution.
I also don't think Steve said anything that could be construed to be arrogant or offensive. Perhaps he could have stated things differently, but then we all can at times.
Also, the original query doesn't match the table names you mention arlier in the post.
March 26, 2019 at 3:20 pm
I think this will work, but it is COMPLETELY UNTESTED. I took out one of your ISNULLs, because I don't think it is necessary with this change, and I also took out a CROSS APPLY, because, again, I don't think it was necessary. I added a derived table that ensured that there were at least 12 records for the TOP clause, although some of them had NULL data.
select
act.userid,
rtrim(ltrim(act2.username)),
stuff
(
(
select top 12 ',' + COALESCE(left(convert(varchar,b.login_date,120),10), '') + ',' + isnull(convert(varchar,b.success,120),0)
from
(
SELECT b.login_date, b.success, 1 AS sort
FROM LoginHist b
WHERE b.userid = log.userid
UNION ALL
SELECT NULL, NULL, v.sort
FROM ( VALUES(2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2) ) v(sort)
) b
order by b.sort, b.login_date desc
for xml path('')
),1,1,'') as [LoginData]
from UserAccts act
join LoginTbl log on log.userid = act.userid
join AcctTbl2 on act2.userid = act.userid
order by act.account_no asc
The 2 in the VALUES clause is used for the sort to ensure that actual records sort before dummy records.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply