| Author |
Topic |
|
H
Starting Member
23 Posts |
 Posted - 2015-04-21 : 13:17:50
|
| I need help with following problem.Table ANameService_DateAssigned_toTable AJohn Smith 3/1/2015 3Jane Doe 3/12/2015 2Apple Brown 4/1/2015 1,3 -------Table BEmpidEmp user id Emp first nameEmp last name Table B1 AJ Patel 2 Jay Thakkur3 Brown MikeIf see in Table A – JohnSmith case is assigned to Brown Mike BUT Apple Brown case is assigned to Aj patel and Brown Mike What I need is Name Service_date Assigned_to, Emp_id, emp firstname, emp last name John Smith 3/1/2015 3 3 AJ Patel Jane doe 3/12/2015 2 2 Jay ThakkurApple Brown 4/1/2015 1,3 1,3 AJ, Brown Patel, Mike Is it possible ?ThanksH |
|
|
H
Starting Member
23 Posts |
Posted - 2015-04-21 : 17:21:19
|
| Let me make it more clearI need Name, service_date, Assigned_to from table A and empid, emp first name, emp lastname from table BIf more than 1 emp are assigned to Name then I need both emp name. (as above example for Apple brown 2 emp are assigned)I will also need to find out which name does not have any emp assigned --- I think that is easy but if it has more than 1 emp and they are comma seperated -- I am unable to separate out id and bring in emp name from table B. If there is only one emp assigned my query runs fine but if it has more than 1 then I am getting error. There can be more than 2 emp assigned to a name in some casesNote: I will have more fields on table A and table B but I just need this fields Name Service Date Assigned _To Empid emp firstname emp lastname |
 |
|
|
H
Starting Member
23 Posts |
Posted - 2015-04-22 : 09:25:36
|
| Any one can help please I need this report ThanksH |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-22 : 09:37:16
|
| Pleas post the query you have worked up so far. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-22 : 09:43:19
|
| Just noticed that your Table A has an Assigned_to column that appears to be a varchar. (How else could it hold '1,3'?)This is not good design and will make queries much more difficultAssigned_to should be an integer and an FK to Table B's Empid column. So, you would have two rows in table a for Apple Brown, one with Assigned_to = 1 and a second row with Assigned_to = 3 |
|
|
|
H
Starting Member
23 Posts |
Posted - 2015-04-22 : 17:35:46
|
| I have to ask programmer who has designed this table I am just trying to create a report assigned_to filed / column hold id from other able What it does it on front end it assigned 2 user (employee) to a Patient (name) user can be on different department so 1 user does certain task related to that Patient and another user does other task so we need to have both user attached to that Name (patient).Now manager wants a report to know which patient has user assigned and which does not (that is simple)select a.assigned_to, a.name, b.firstname, b.lastname from tableA a left join table bon a.assigned_to = b.empidwhere a.assigned_to not like '%,%'Above query runs fine Other report is what user are assigned to a patient So patient A can have user A, user B, User C assigned Hope this help I know may be something in coding (powerbuilder) that is populating 2 integer or more than 2 integers on that assigned_to filed /column |
|
|
|
H
Starting Member
23 Posts |
Posted - 2015-04-22 : 17:43:42
|
| Sorry missed to mention that Query (on previous post) gives list of patient who has just 1 assigned used with user name, I can change where clause and can get list of patient without user assigned Just need to know what should be done to get name of patient who has more than 1 user along with user name. (I can get name of patient with more than 1 user but do not know how to get name from other table)If for 2 user - I get 2 line item that should be work as well, Like in example for Apple Brown one with assigned user 1 and other line with assigned user 3ThanksHiral |
|
|
|
H
Starting Member
23 Posts |
Posted - 2015-04-22 : 17:44:20
|
| Thanks Gbritton |
|
|
|
H
Starting Member
23 Posts |
Posted - 2015-04-23 : 13:27:21
|
| Do you all think creating a split function might help?Or procedure needs to be createdThanksHiral |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-23 : 13:58:39
|
| You will need a split function, I believe. Here's a great one: http://www.sqlservercentral.com/articles/Tally+Table/72993/ |
|
|
|
H
Starting Member
23 Posts |
Posted - 2015-04-24 : 17:23:38
|
| Thanks GBrittonI tried following query it works but it takes lot of time as we have lot of data on those tables and it will grow, any trick to make it run faster Anyone has any idea --- Please see query and let me know how can I fixed that select a.name, a. service_date, b.empfirstname, b.emplastname from tablealeft join tableb bon b.empid in (select row_value from tablea cross apply sa_split_list (assigned_to, ',')where assigned_to like '%,%') |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-25 : 05:34:34
|
| We have changed the SPLIT functions we use over the years as new techniques (and features in new SQL versions) have become available. What I definitely think is that there is no "one size fits all" function.We have different functions (even if the internal logic is the same) to return INT and STRING splits, and again different if the Source is VARCHAR(8000) or VARCHAR(MAX).If the columns being split contain [on average] very few "pieces", or a "Lot" of pieces, we use different Split functions as some perform better on Narrow fields, some better on Wider ones. (Just checking my Source Code I have 8 different Split functions ...)First up I would create a benchmark (with static, hard-wired, [representative!!] data so that the tests are repeatable) and trial various SPLIT functions to find the one that works best for you. The one that gbritton linked to performs well for me on narrow columns, but mediocre on wide columns.Assuming that your b.empid is INT I would definitely create sa_split_list() to return INT rather than VARCHAR columns.You should also consider how "123,456,,789" would handle the blank case (and also ",123,456,789" and "123,456,789,") |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-25 : 09:44:42
|
| depends on how you define narrow and wide, of course, but IIRC Jeff Moden says that his splitter is not optimized for varchar(max) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-26 : 05:13:44
|
| Yeah, I'm splitting hairs, this splitter performs very well and on my travels I come across lots of really shocking ones which I can easily replace with "anything" and make myself look good in the eyes of my clients!There is a bit in the article that says that if the length of the elements heads for 20 characters, and the number of elements (within the input string to be split) heads for more than 20, the performance drops off. The article also indicates that the narrative is from the original article but the code has been replaced with a more souped-up version, so it might be that the analysis of performance is not representative of the latest code version.My test was with a range of lengths of input strings, the largest being just short of 8,000 characters, and performance dropped off significantly (twice as slow as the best alternative, although still "very fast" compared to most other splitters) at that point.My personal view is that Splitters are usually used in frequently run code. Some sort of List provided by a front end application which needs to be converted to rows, perhaps in a temporary table, and then joined to some other table(s). Shaving even a few MS off the run-time is a significant saving over the lifetime of the application for maybe an hour's development time, so worth putting some effort into finding a likely "best candidate" function.There was a lengthy thread on Splitters here ([link], many years ago, and when that first came up I reviewed the one we used (lifted from one of Celko's books and using a Tally table, which I had presumed was invincible!) and I found that its performance was, in fact, dreadful so we replaced it. In fact it was at that time that my experiments suggested that having different functions for INT / VARCHAR outputs (and indeed for a VARCHAR(MAX) input) were worthwhile and we changed our source code accordingly.Reading the article you linked to there is talk that a single-line Table Value Function performs significantly better than a Multi-line one. My functions are multi-line as I have some error handling and "optional" parameters (governing what to do with "empty" elements, and handling non-single-character delimiters) so looks like I can save some MS by revisiting my functions. At the very least I should be able to convert the single-character-delimiter (which at a guess is 99.999% of all my splitting!!) to a single-line-TVF and have a different (set of) function for delimiters of 2+ characters.I did also try the linked function with a pre-created Tally table, and it looked like it performed a bit better, so that's another option. |
|
|
|
|
SQL Query