Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query

Author  Topic 

H
Starting Member

23 Posts

Posted - 2015-04-21 : 13:17:50
I need help with following problem.
Table A
Name
Service_Date
Assigned_to

Table A
John Smith 3/1/2015 3
Jane Doe 3/12/2015 2
Apple Brown 4/1/2015 1,3
-------
Table B
Empid
Emp user id
Emp first name
Emp last name

Table B
1 AJ Patel
2 Jay Thakkur
3 Brown Mike

If 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 Thakkur
Apple Brown 4/1/2015 1,3 1,3 AJ, Brown Patel, Mike


Is it possible ?

Thanks
H

H
Starting Member

23 Posts

Posted - 2015-04-21 : 17:21:19
Let me make it more clear
I need
Name, service_date, Assigned_to from table A and empid, emp first name, emp lastname from table B
If 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 cases

Note: 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
Go to Top of Page

H
Starting Member

23 Posts

Posted - 2015-04-22 : 09:25:36
Any one can help please I need this report

Thanks
H
Go to Top of Page

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.
Go to Top of Page

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 difficult

Assigned_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
Go to Top of Page

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 b
on a.assigned_to = b.empid
where 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
Go to Top of Page

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 3

Thanks
Hiral
Go to Top of Page

H
Starting Member

23 Posts

Posted - 2015-04-22 : 17:44:20
Thanks Gbritton
Go to Top of Page

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 created

Thanks
Hiral
Go to Top of Page

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/
Go to Top of Page

H
Starting Member

23 Posts

Posted - 2015-04-24 : 17:23:38
Thanks GBritton

I 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 tablea
left join tableb b
on b.empid in (select row_value from tablea cross apply sa_split_list (assigned_to, ',')where assigned_to like '%,%')
Go to Top of Page

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,")
Go to Top of Page

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)
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -