Concatenation Of Patient Name Question

Jul 16, 2007

I have a report built that returns patient information. My code to concatenate the patient name fields (last name, first name, middle name and surname) work fine unless the middle name or surname fields are null, then it returns the concatenated patient name field as null. The code is posted below. Is there an easy method to determine if the field is null and then apply the correct logic to concatenate the name with the elements that are not null?





ltrim(rtrim(srm.patients.patient_lname))

+ ', '

+ ltrim(rtrim(srm.patients.patient_fname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_mname))

+ ' '

+ ltrim(rtrim(srm.patients.patient_sname))

as SRM_PatientName









View 6 Replies


ADVERTISEMENT

Patient Query... Pls Help

Aug 27, 2007



Hi Guys,


I have got a table of patients where each patient has got a unique patient number.There are different services which a patient can undertake such as resthome, dementia etc.

Here is the sample data

Service patientNo startDate EndDate

resthome 12 01/04/2003 03/05/2003

resthome 13 12/9/2004 13/10/2006

Dementia 44 12/08/2002 13/01/2004

dementia 12 05/05/2003 06/12/2006

................................. ............ ................................

Each patient has got a start date when he started undertaking that service and end date when the service finished .

Now I need to count total patients for each catagory but here is the main issue....

A patient can move from one servcice to another but cant be in two services at the same time and I need to count the most recent service which the patient is currently enrolled in..



How do I do that????

Pls help me guys as you have always done:wow:



Thanks

View 2 Replies View Related

Calculate Sum And Average - Patient Information For All Nurses

Oct 8, 2013

I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patient information for all the Nurses.

I need to calculate Number of patients per Nurse and Average number of patients per day per nurse.

We need to calculate Average Number of patients per day per nurse = Total Patients per nurse/No.of unique days they worked

I need my report as like this.

DistinctNurse No.ofPatients AvgNo.ofpatients PerDay
Tina 100 25
Sony 50 16.6

How to get the result as above.

View 11 Replies View Related

Store Proc - How To Get Patient Status As Output To Form

Dec 15, 2013

I am using below code to get patient status as an out put to my form. not sure whats happening but each time I run this its not executing my last "IF" if set to "N" show me N if not show me "Y" but it is by passing my first "IF" condition and jumps to last?

The column alerts_ind shows only Y or N in the table patient_status.

The table patient_status_mstr show the description of the patient which "discharged". All I want to do is if the patient is flagged with "discharge" the columns "alerts_ind" shows "Y". but something wrong? below is the code.

Alter PROCEDURE GBCheckPatientStatus (@enc_id varchar(36), @data_ind Char(1) OUTPUT)

as
begin
declare
@alerts_ind char(1);
select @alerts_ind =pm.alerts_ind

[Code] ....

View 5 Replies View Related

SQL Server 2008 :: How To Subtract Dates From Previous Date On Same Patient

Jun 22, 2015

I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.

create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')

[code]....

View 9 Replies View Related

Concatenation

Jun 12, 2008

I need to concatenate two date fields so they appear in a drop-down list like this:   8:00 AM - 10:00 AM
I'm using MS SQL 2005 and my query looks like this:  SELECT ClinicTimesID, ClinicID, (CTStartTime + ' - ' + CTEndTime) AS TimeSlot FROM Clinics_Times WHERE (ClinicID = 1) and I get this error:  Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can I strip out the date so that only the time appears or is it even possible to concatenate these fields?  If so, how?
Thanks!

View 1 Replies View Related

Concatenation

Sep 8, 2005

Hello all, I'm trying to combine two columns of data into a third column using a formula on the thrid column. Each of the columns could contain nulls and each of the columns could contain padding after or before the data. I'm trying to use the following formula yet SQL is throwing an error. Can someone provide another set of eyes to check this out? ISNULL(LTRIM(RTRIM([user_Define_4a])),’’) + ISNULL(LTRIM(RTRIM([user_Define_1])),’’) Thanks

View 2 Replies View Related

DTS Concatenation - Please Help

Nov 17, 1999

I am importing via DTS a .csv file. I have 2 issues:

-One field from the source needs to be inserted into two existing fields.

-Two fields from the source, First_Name and Last_Name need to be
concatenated to a destination field, Full_Name.

I do not know VB but I do know SQL.

Can anyone help?

View 1 Replies View Related

Concatenation

Mar 7, 2007

Hi. I want to make a query that concatenate the address of the person with a comma inbetween and neglecting the null value.

table - pspersonaldata
dcno name streetbldg1 streetbldg2 streetbldg3
1 jon santol1 null santol3
2 mike mangga1 mangga2 null
3 jay langka1 langka2 langka3
4 joy null buko2 buko3
5 jean null null null

expected output is:

name address
jon santol1, santol3
mike mangga1, mangga2
jay langka1, langka2, langka3
joy buko2, buko3
jean null

thanks.
-Ron-

View 14 Replies View Related

Concatenation

Oct 3, 2007

I am completely new to SQL and I have some syntax questions. I am trying to concatenate 4 fields and some padded constants to form a new key field to perform joins. The result should be a twelve character field without spaces. My problem is that the current fields use spaces as place holders and I need to replace the spaces with ‘0’.

[RD_ID] nvarchar length 5
[RDWY_ID] nvarchar length 1
[MLGE_TYPE] nvarchar length 1
[OVLAP_MLGE_CD] nvarchar length 1

Concatenate 12 characters

1st ‘0’ (constant)
2nd, 3rd, and 4th, from [RD_ID] (without the suffix)
5th and 6th from [RD_ID] suffix or replace spaces with ‘00’
7th 1 or 2 from [RDWY_ID]
8th Z from [MLGE_TYPE] or replace space with ‘0’
9th 1 – 9 from [OVLAP_MLGE_CD] or replace space with ‘0’
10th ‘S’ (constant)
11th ‘0’ (constant)
12th ‘0’ (constant)

Results should resemble
0001CQ100S00 or 000100100S00

This is the query I used in Access.

LRS: "0" & IIf((Len(LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])))=3),LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])) & "00",LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID]))) & [HITIS2_TVCLEAR2]![DIR_RDWY_ID] & IIf([HITIS2_TVCLEAR2]![MLGE_TYPE]=" ",0,[HITIS2_TVCLEAR2]![MLGE_TYPE]) & IIf([HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]=" ",0,[HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]) & "S00"

Thanks for any help.

View 6 Replies View Related

Concatenation

Feb 27, 2008



My file is in this format

Col1 Col2 Col3
---------------------------------------------------------------------------
Andrews S 93845877712 P
Sylvia 9999876669 J
Bill K 7657654677 L

I need the output like this
Col1 Col2 Col3
--------------------------------------------------------------------------
AndrewsS 93845877712 P
Sylvia 99999876669 J
BillK 76576546677 L

The character on the left of Col2 has to be joined to Col1 and character on the right of col2 has to be joined to Col3. Can anybody suggest a query for this.

Thanks,

View 3 Replies View Related

Concatenation

Apr 8, 2008



I need to concatenate the strings of the grouped result set in my report. Lets say if I have

ID Text
-----------------
1 text1
1 text2
1 text3


and if I am grouping by ID, the resulted text should be "text, text2, text3". Any ideas on this? Its kind of emergency

Thanks
Dinesh

View 5 Replies View Related

Dynamic SQL - Concatenation!

May 1, 2008

Hi All,
I've been trying to create a dynamic query using the 'Like' clause and '%'.
my code snippet looks like this:
while (@@FETCH_STATUS = 0)begin   set @likeString = @likeString + ' item_Text LIKE ''%'+@word+'%'' OR '   fetch next from theLike   into @word end
 
-- strip off last ORset @likeString = ltrim(rtrim(substring(@likeString, 0, (len(@likeString) - 3))))-- ================================================-- create query to find keywords in the index and store in temp table-- ================================================set @query = 'INSERT into #resulttable (itemcount, item_id) SELECT COUNT(d.item_id), d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id 'set @query = @query +' WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE ( 'set @query = rtrim(@query) + @likeStringset @query = @query + ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC'
The problem is the @query string gets truncated. My question is how to get the quotes around '%' variables to work in a string?
Thanks for any help!
regards
Davej

View 4 Replies View Related

SQL And Proper Concatenation Within VB

Jun 9, 2008

Hi all - I have posted inquiries on this rather vexing issue before, so I apologize in advance for revisting this. I am trying to create the code to add the parameters for two CheckBoxLists together. One CheckBoxList allows users to choose a group of Customers by Area Code, the other "CBL" allows users to select Customers by a type of Category that these Customers are grouped into. When a user selects Customers via one or the other CBL, I have no problems. If, however, the user wants to get all the Customers from one or more Area Codes who ALSO may or may not be members of one or more Categories; I have had trouble trying to create the proper SQL. What I have so far:Protected Sub btn_CustomerSearchCombined_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombined.Click        Dim CSC_SqlString As String = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Category.Category FROM Customers INNER JOIN Category ON Customers.CategoryID = Category.CategoryID WHERE "        Dim ACItem As ListItem        Dim CATItem As ListItem        For Each ACItem In cbl_CustomersearchAREA.Items            If ACItem.Selected Then                CSC_SqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR "            End If        Next        CSC_SqlString &= "' AND " <-- this is the heart of my problem, I believe        For Each CATItem In cbl_CustomersearchCAT.Items            If CATItem.Selected Then                CSC_SqlString &= "Customers.CategoryID = '" & CATItem.Value & "' OR "            End If        Next        CSC_SqlString = Left(CSC_SqlString, Len(CSC_SqlString) - 4)        CSC_SqlString &= "ORDER By Categories.Category"        sql_CustomersearchGrid.SelectCommand = CSC_SqlString    End SubAny help on this is much appreciated, many thanks -- 

View 5 Replies View Related

Problem With Concatenation In SP

Sep 10, 2004

Hi,
I'm trying to mak emy query dynamic such that depending upon certain conditions, the conditions in the WHERE clause change. I have my SP as shown below and it doesn't seem to work correctly this way and seems like it is not even taking it as a condition. Please advise on what is going wrong here. I'm building the @Condition variable dynamically and appending it to the where clause below. Any help wud be greatly appreciated.

Thanks


CREATE PROCEDURE dbo.CabsSchedule_ViewSchedule
(
@SiteCode smallint = 0,
@YearMonth int = NULL,
@ByYearMonth bit = 1
)
AS

DECLARE @tempYearMonth int
DECLARE @Condition varchar(1000)
SET @tempYearMonth = 0

IF @YearMonth IS NULL OR @YearMonth = 0
BEGIN
EXECUTE CabsSchedule_GetYearMonth @tempYearMonth, @YearMonth OUTPUT
END

IF @ByYearMonth = 0
BEGIN
DECLARE @Year int
DECLARE @Month int
SET @Year = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),1,4) AS INT)
SET @Month = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),5,2) AS INT)
SET @Condition = ' DATEPART ([month], FullDate) = ' + CAST(@Month AS VARCHAR(2)) + ' AND DATEPART ([year], FullDate) = ' + CAST(@Year AS VARCHAR(4)) + ' AND '
END
ELSE
BEGIN
SET @Condition = ' YearMonth = ' + CAST(@YearMonth AS VARCHAR(6)) + ' AND '
END

SELECT BillPeriod =
CASE
WHEN BillPeriod = 32 THEN 'NB'
WHEN BillPeriod = 33 THEN 'Holiday'
ELSE Convert(nVarChar(7), BillPeriod)
END,
WorkDay =
CASE
WHEN WorkDay = -1 THEN ''
WHEN WorkDay = 0 THEN 'Holiday'
ELSE Convert(nVarchar(7), WorkDay)
END,
JulianDate, CalendarDay, CalDayBillRcvd, Remarks, FullDate
FROMdbo.CabsSchedule
WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode
GO

View 7 Replies View Related

SELECT CONCATENATION

Aug 28, 2000

I'm trying to implement a system where I want to use something like a group by with a concatenation clause.

e.g.

Table 1.
ID Field
1 null
2 null

Table 2.
ID Value
1 AAA
1 BBB
2 CCC
2 DDD
2 EEE

Using one query I would like to transform Table 1 to

ID Field
1 AAABBB
2 CCCDDDEEE

Is there any way of doing this ? At the moment I'm using two cursors to accomplish this task, but this is not really efficient.

Maurice v/d Zwaan

View 2 Replies View Related

Concatenation Problem

Dec 18, 2007

i am having data something like this:
number starting point ending point
1 ABC DEF
2 DEF ABC
1 PQR STU
2 STU PQR
1 ABC DEF
2 DEF PQR
3 PQR ABC

and i want it to be like this in my new table based on number column:
CONCATENATED
ABC/DEF/ABC
PQR/STU/PQR
ABC/DEF/PQR/ABC

can anyone suggest me how to write a query for this one?

View 3 Replies View Related

Concatenation Of Two Columns

Jun 16, 2004

Hi all,

I am trying to concatenate two columns First_Name and Last_Name to display as Name in a View. I used the following statement but the result only shows the First_Name.

Select First_Name + Last_Name as Name from Address;

How do i combine the two columns??

SQL 2000 running on Win 2000

Thanks in advance.

View 2 Replies View Related

Interger Concatenation

Dec 15, 2004

Hey,

Does anyone know how to do integer concatenation using sql?

Say for instance I have 2004 and 12, I need 200412 and not 2016.

Can this be done?

Thanks

View 12 Replies View Related

Concatenation Of Text

Feb 24, 2005

I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?

View 7 Replies View Related

NULL && Concatenation

Mar 23, 2006

I am using the following sql statement to concatenate fields from a sql server in my query.

SELECT RTRIM(title) + ' ' + RTRIM(fname) + ' ' + RTRIM(lname) AS name, id FROM contact

2 questions:

1. How can I avoid a Null name field resulting from either fname or lname being Null? One Null field in the contatenation yields a Null field, even though the other field is not Null.

2. Does concatentation in the sql statement reduce performance significanlty?

View 5 Replies View Related

Concatenation In Query

Apr 6, 2006

Hi all,

I am using concatenation in Query in Sql Server like,

Select Column1 + ' bla bla ' + Column2 as MyColumn from MyTable

So, here any secruity issure occur or not.... because some one tell to me.. d'not use Concetenation in query bcz it is not secure, worst in performance and helpfull in SQL injection.......
any idea about that ??

Thanks
Sajjad

View 10 Replies View Related

String Concatenation

Jun 19, 2006

Hi,

Why does this result produce 'Null' and not the expected string of 10 B's?

The var @SecurityString is a VarChar Type.

WHILE @LoopCount <= 10

BEGIN
SET @SecurityString = @SecurityString + 'B'
SET @LoopCount = @LoopCount + 1
END
SELECT @SecurityString AS SecurityCode

Any pointers would be a great help, thanks.

View 3 Replies View Related

String Concatenation

Jan 23, 2004

create procedure ChangePassword(@sUser char(20),@sPassword char(20))
as
begin
execute immediate 'GRANT CONNECT TO ' + @sUser + ' IDENTIFIED BY ' + @sPassword
grant execute on ChangePassword to public
end

I m getting syntax error at '+' sign. I saw in BOL and it is exactly the same.
Can nyone help me out?


Thx

View 2 Replies View Related

String Concatenation

Jan 26, 2004

create procedure CheckSQLErrors( @TheCode integer, @TheState integer, @Routine varchar(40), @Help varchar(40))
as
begin
{ call LogMsg('SQLA',@Routine,@Help,'sqlstate=' + @TheState +
', sqlcode=' + @TheCode) }
end


I m getting this error.
"Incorrect syntax near + "


+ is used for string concatenation. I tried to use CAST to convert
@TheState and @TheCode variables to varchar but did not work. Can you help me out?



FYI
LogMsg is a sproc

create procedure
dbo.LogMsg( @aAppName varchar(18), @aRoutine varchar(20), @aType varchar(5), @aMsg varchar(255))
as
begin
insert into MessageLog(strAppName,strRoutineName,strType,strMe ssage) values(
@aAppName,@aRoutine,@aType,@aMsg)
end
GO




Thks

View 3 Replies View Related

Concatenation But With A Format...

Apr 15, 2004

Hi Consider a table with a decimal and a string varibale.

DecString
3fred
23bill

I need to concatenate them but with leading zeros such as
03Fred
23Bill

Ideas please ??

Gerry

View 3 Replies View Related

Concatenation Problem

May 20, 2008

Hi, i have this query that is perfect on SQL2005 but in SQL2000 it gives me the error: "Column 'D.RevenueCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


SELECT SUBSTRING(D.RevenueCode, 5, 2) + ' ' +
(SELECT A.sdp_des
FROM vw_UNI_se_girev A
WHERE A.sdp = SUBSTRING(D.RevenueCode, 5, 2)
AND Cast(A.Comp AS Integer) = D.CompanyCode) AS RevenueGrpItem,

SUM(nSign * isNull(ItemAmount, 0)) AS GrandTotal

FROM SIC_Invoice_Header AS H, vw_SIC_Invoice_Detail AS D
WHERE H.CompanyCode = 1
AND H.StockCategory = 'INV'
AND H.CompanyCode = D.CompanyCode
AND H.StockCategory = D.StockCategory
AND H.SaleType = D.SaleType
AND H.TransactionType = D.TransactionType
AND H.SerialNo = D.SerialNo
AND D.RevenueCode is not NULL

GROUP BY D.CompanyCode, SUBSTRING(D.RevenueCode, 5, 2)
ORDER BY SUBSTRING(D.RevenueCode, 5, 2)



It appears that when I remove the + and put a comma it works correctly but that's not what I want !

Any suggestions?

Thanks in advance

Laurent

View 16 Replies View Related

String Concatenation

Feb 21, 2006

I'm trying to concatenate 3 columns into 1. Can someone provide me w/ the syntax?? :) thx

View 1 Replies View Related

Concatenation Of Strings

Mar 10, 2008

Guys,

I have following table where I need to concatenate varchar column.

For example

IDCOMMENT
__________________
1JOHN SMITH
1 SURRENDER
1TO COPS

I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

View 5 Replies View Related

Concatenation Getting Truncated

May 24, 2006

Hello,Using SQL SERVER 2000I have 4 columns with varchar(80) each that I want to concatenate.When I look at the result, it only gives me 256 characters. What am Imissing on my code?Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+rtrim(line4))[color=blue]>From tableOne[/color]GoThank you for your input.Edgar

View 3 Replies View Related

Trouble With Concatenation

Jan 23, 2008



I am trying to write a batch script that basically goes like this:

select customer_num +' '+ customer_code+'|'+customer_name+'|'+customer_country
from customer

and what I think it should spit out is:
ABCD 123|ABC company|USA

but I am getting the following error:
Syntax error converting the nvarchar value 'ABCD' to a column of data type int.

customer_num is nvarchar
customer_code is int
customer_name is nvarchar
customer_country is nvarchar

View 1 Replies View Related

Concatenation Aggregate

Nov 17, 2007

Are we actually going to get a string concatenation aggregate in SQL Server 2008?? I am so sick of having to write a UDF for every concatenation I want to do. I am well aware of all the methods available to do it now, and they are all junk, and the performance is horrible. I'm sure it is a challenging problem, and there are known pitfalls, but its time to address these.

The notion that it is the responsibility of the front end to do the concatenation is pure bs. You don't dump 10,000 rows to your report server when you only need to display 1000, just so you can concatenate one lousy column.


I want to be able to write:

SELECT CustName, SUM(ProductPrice), CONCAT(ProductName, [my separator], ORDER OVER ProductName ASC)
FROM Orders

And I'm not going to stop complaining til I get it! (-;



View 4 Replies View Related

Concatenation Of Columns.

Feb 22, 2008

My table looks like this.
Col1 Col2
-------------------------------------------------
Andrew 1 0394588893
Charles 6 0495869457
Carmon 5 9878957874

But, I want the result like this.
Col1 Col2
---------------------------------------------------
Andrew 10394588893
Charles 60495869457
Carmon 59878957874

The last digit in the col1 has to be joined as the first digit in the col2. How to write this query?.


View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved