Combining two fields into one

I have a small problem.
In my query I am calling a field of status and a field of statusyearcount. I
need to get these into one cell in a table, called statusyearcount.
ie. Status = C
StatusYearCount = 10
Need to show C10 in the table.
I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
getting an "incorrect format string".
Im gathering it because one is an int and the other varchar. Can somebody
please point me in the right direction here.
Thanks muchly.You can cast the Field.
=Fields!Status.Value + Int(Fields!StatusYearCount.Value)
--
| Thread-Topic: Combining two fields into one
| thread-index: AcWo+8ArbHZHrGD+TI2fppOvcbIgrg==| X-WBNR-Posting-Host: 202.175.143.143
| From: "=?Utf-8?B?TmF0IEpvaG5zb24=?="
<NatJohnson@.discussions.microsoft.com>
| Subject: Combining two fields into one
| Date: Wed, 24 Aug 2005 15:32:46 -0700
| Lines: 18
| Message-ID: <3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51089
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a small problem.
|
| In my query I am calling a field of status and a field of
statusyearcount. I
| need to get these into one cell in a table, called statusyearcount.
|
| ie. Status = C
| StatusYearCount = 10
|
| Need to show C10 in the table.
|
| I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I
keep
| getting an "incorrect format string".
|
| Im gathering it because one is an int and the other varchar. Can
somebody
| please point me in the right direction here.
|
| Thanks muchly.
|
||||This will do it:
=Fields!Status.Value & CStr(Fields!StatusYearCount.Value)
CStr converts integer to string, allowing succesful concatenation.
GeoSynch
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com...
>I have a small problem.
> In my query I am calling a field of status and a field of statusyearcount. I
> need to get these into one cell in a table, called statusyearcount.
> ie. Status = C
> StatusYearCount = 10
> Need to show C10 in the table.
> I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
> getting an "incorrect format string".
> Im gathering it because one is an int and the other varchar. Can somebody
> please point me in the right direction here.
> Thanks muchly.
>

Combining two fields

Hey everyone, I am very new to SQL Reporting, I usually work with Crystal. So I think this question is very simple. I have a field and in the expression box it says

=Sum(Fields!EstRev.Value)

Now I have another field that I want to combine in the above box, no fancy calcs or anything I just want it to appear in the same area.

=Sum(Fields!OrderCount.Value)

Basically I do not want to add the two fields together arthritically I just want them numbers to appear on the bottom of each other.

I have tried an AND Operator and that didnt work. Please help, very simple questions. It has to be very simple to just combine these two in to one field on the report?

You can do something like this:

=Sum(Fields!EstRev.Value)&" "&Sum(Fields!OrderCount.Value) <- this will show something like "23 432"

or

=Sum(Fields!EstRev.Value)&Chr(13)&Chr(10)&Sum(Fields!OrderCount.Value) <- this will show something like

23

432

Maciej

|||

Maciej

I normally do this Cstr(sum(fields!EstRev.value)) - converting number to string values

Hammer

Combining Time with a Date

Let's say that I have two tables, one of which has a StartDate field (say,
TableA) as well as a foreign key reference to TableB which has a field
called BeginTime. Suppose that values in TableA.StartDate field have varying
dates together with a time of 00:00:00.000 (as it's a datetime field) and
TableB.BeginTime fields have varying time values (e.g. 07:00:00.000). NOTE:
TableB.BeginTime field is currently an nvarchar field.
What I want to do is compare the current date (using GETDATE()) to the
datetime value that results from combining TableA.StartDate with
TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare the
current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
determine if the difference between them is greater than a certain # of
minutes. How could I do this using SQL?Bob
Look at DATEDIFF system function.
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Let's say that I have two tables, one of which has a StartDate field (say,
> TableA) as well as a foreign key reference to TableB which has a field
> called BeginTime. Suppose that values in TableA.StartDate field have
varying
> dates together with a time of 00:00:00.000 (as it's a datetime field) and
> TableB.BeginTime fields have varying time values (e.g. 07:00:00.000).
NOTE:
> TableB.BeginTime field is currently an nvarchar field.
> What I want to do is compare the current date (using GETDATE()) to the
> datetime value that results from combining TableA.StartDate with
> TableB.BeginTime. For example, if TableA.StartDate = "03/29/2005
> 00:00:00.000", and TableB.BeginTime = "07:00:00.000", I want to compare
the
> current date to "03/29/2005 07:00:00.000". I will ultimately be trying to
> determine if the difference between them is greater than a certain # of
> minutes. How could I do this using SQL?
>|||I am familiar with the DATEDIFF function, but that will not combine separate
DATE and TIME values together to give me a DATETIME. That's the piece I'm
missing here.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u1zyYHDNFHA.3076@.tk2msftngp13.phx.gbl...
> Bob
> Look at DATEDIFF system function.
>
> "BobRoyAce" <bob@.decisioncritical.com> wrote in message
> news:%23byP4xCNFHA.3844@.TK2MSFTNGP14.phx.gbl...
> varying
> NOTE:
> the
>|||Since you express datetime as a string, it is just a matter of building a st
ring expression which
can safely be converted to datetime. I didn't follow your first post, but le
ts assume that one value
is datetime and the other is a string:
DECLARE @.a datetime, @.b nvarchar(40)
SET @.a = getdate()
SET @.b = '07:00:00'
SELECT CAST(CONVERT(char(8), @.a, 112) + ' ' + @.b AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"BobRoyAce" <bob@.decisioncritical.com> wrote in message
news:%23mQ5VNDNFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am familiar with the DATEDIFF function, but that will not combine separat
e DATE and TIME values
>together to give me a DATETIME. That's the piece I'm missing here.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:u1zyYHDNFHA.3076@.tk2
msftngp13.phx.gbl...
>|||What's the 112 for?|||>> What's the 112 for?
It is the argument for CONVERT to return the ISO format( yymmdd ) for dates
represented as a string. See the topic CAST and CONVERT in SQL Server Books
Online.
Anithsqlsql

Combining subquery results into one field

Hello there

I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow:

bookingrequestion - bookingrequestid, startdate, enddate

roomsbooked-id, bookingrequestid, roomname

i'm basically trying to use the following query

select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms

i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.

thanks

I would look at returning two results to a dataset then creating a relationship between them. Displaying the results is pretty easy using nested repeaters:http://gridviewguy.com/ArticleDetails.aspx?articleID=185

Combining numeric fields

Hello,
I use the following line in my sql view to bring 2 numeric fields together,
as one field (I'm using this to populate a listbox in vb.net).
This all works great, but I need to have the value 0 show up as 0.0. Right
now, it shows up as 0.
CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
Any suggestions?
TIA!
amberOn Tue, 20 Sep 2005 15:30:04 -0700, amber wrote:

>Hello,
>I use the following line in my sql view to bring 2 numeric fields together,
>as one field (I'm using this to populate a listbox in vb.net).
>This all works great, but I need to have the value 0 show up as 0.0. Right
>now, it shows up as 0.
>CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
>CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
>Any suggestions?
Hi amber,
Use the STR function instead of CAST. See the documentation in Books
Online for details on usage.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If the original data type of those two columns is integer (int, bigint,
smallint or tinyint) then cast it as decimal before casting it to varchar.
Look into data types in Books Online for more details, or provide DDL and
some sample data and please explain what exactly your goal is.
ML|||Thank you!
That fixed it!
Amber

Combining Multiple Values into One Field

I have two tables called tblName and tblType.

tblName
Name Type
Mike 1
Frank 2
Robert 2
Edward 3
David 4
Albert 4

I want the output to look like this in the tblType table:

tblType
Type Name
1 Mike
2 Frank, Robert
3 Edward
4 David, Albert
5

When I execute the following vb code all I get is the last value from the tblName table:

strSQL = "UPDATE tblType "
strSQL = strSQL & "SET tblType.Name = tblType.Name + " ', ' + "
strSQL = strSQL & "tblName.Name "
strSQL = strSQL & "FROM tblName, tblType "
strSQL = strSQL & "WHERE tblName.type = tblType.type;"

Any suggestions?

ThanksUsing COALESCE to Build Comma-Delimited String (http://sqlteam.com/item.asp?ItemID=2368)

rudy
http://r937.com/|||Thanks Rudy, I knew there was some way of accomplishing this. I went to the link you posted and gave the COALESCE Function a quick read but is there a way of substituting the comma for another value such as a vbCRLF?

Thanks,

Mark|||is there a way of substituting the comma for another value such as a vbCRLF
don't see why not -- didja try it?|||Rudy,
Thanks for your post. I was unable to make the query do what I initally wanted it to do. I was able to accomplish what I wanted by using two nested recordsets. It takes a little longer to complete but it does what I want it to do.

Thanks!

Mark

Combining Fields to string

I have a function that takes a field with values separated by commas within the field and splits them to multiple rows.

Example:
Field - Interior
Value - abc,def,efg,ghi

Output:
ID Item
1 abc
2 def
3 efg
etc

This is working great thanks to help that I received on here.

Now I am combining multiple fields to a string.
Example:
Field1: abc, def
Field2: ghi, jkl

using

SELECT (Field1 + ',' + Field2) From ....

This is working great unless there is a field that has a NULL value. Then I get a NULL result.

Is there an easy way to only put the fields with value into my string and leave out the NULL fields? Some have one NULL field, some have multiple. I just need to get the string to work and get only the fields that have values.

Any suggestions are always appreciated.It has been resolved on another post.

THANKS!!|||

Quote:

Originally Posted by rpeacock

It has been resolved on another post.

THANKS!!

I am having the same problem - can you tell me what other post solved the issue?

Thanks in advance

RIP

Combining date field and time field in a column

SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please reply to me at Babies001@.yahoo.co
ThankWhat datatypes are you using for storing your date and time values? If using
strings, then concatenate them and use CAST or CONVERT functions to convert
the concatenated value into a datetime value.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"TM" <anonymous@.discussions.microsoft.com> wrote in message
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com...
SELECT RequireDate + ' ' + RequireTime AS date
FROM IN_Header
My Database:
Date Time
28/03/2004 01:34:09PM
After run SQL statement, my result become:
26/03/2004 01:34:09PM
Why my date minus two day? so what should i need to do? Urgent please reply
to me at Babies001@.yahoo.com
Thanks|||Regarding the Question, my database fiel
Field DataTyp
Date DateTim
Time DateTim
So my result become
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
What the code for convert the date and time together and my date will not minus two day
Can adding the source code inside
Thank
-- Narayana Vyas Kondreddi wrote: --
What datatypes are you using for storing your date and time values? If usin
strings, then concatenate them and use CAST or CONVERT functions to conver
the concatenated value into a datetime value
--
HTH
Vyas, MVP (SQL Server
http://vyaskn.tripod.com
Is .NET important for a database professional
http://vyaskn.tripod.com/poll.ht
"TM" <anonymous@.discussions.microsoft.com> wrote in messag
news:E6A4D56F-97F0-4E97-AD2D-3348504128DF@.microsoft.com..
SELECT RequireDate + ' ' + RequireTime AS dat
FROM IN_Heade
My Database
Date Tim
28/03/2004 01:34:09P
After run SQL statement, my result become
26/03/2004 01:34:09P
Why my date minus two day? so what should i need to do? Urgent please repl
to me at Babies001@.yahoo.co
Thanksqlsql

Combining a stored procedure and a table in one Dataset?

Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?

Thanks.

Brad

If you mean a SQL style join at the dataset level then the answer is no.

You could always use a batch of SQL instead of a simple select and use temporary tables within the query to perform the JOIN. Even better create a stored procedure to do it.

If the data is coming from different databases/platforms then could use linked servers.

sqlsql

Combining a stored procedure and a table in one Dataset?

Is it possible to combine a stored procedure result set and a table into one dataset? For example, if I have a stored procedure with field "TradeID", and a table with "TradeID", can I join the them in a dataset?

Thanks.

Brad

If you mean a SQL style join at the dataset level then the answer is no.

You could always use a batch of SQL instead of a simple select and use temporary tables within the query to perform the JOIN. Even better create a stored procedure to do it.

If the data is coming from different databases/platforms then could use linked servers.

Combining 3 datasource controls with t-sql

Hello everyone,

I'm trying to get a count of 3 different types on the same field. For Example, let's use Gender as the field with these options: Male, Female, Not Given. What I'm wanting to do is retrieve a count for each type. What I have so far is: SELECT COUNT(Gender) WHERE Gender = 'Male' and I have to duplicate this in 3 different data controls. I would like, however, to have one datasource control with a statement along the lines of:

SELECT ( SELECT COUNT(Gender) FROM Users WHERE Gender='Male), SELECT COUNT(Gender) FROM Users WHERE Gender='Female', SELECT COUNT(Gender) WHERE Gender='NotGiven' )

From Users

Or something to that effect. Any suggestions?

Thank you greatly for your help,

Mark

SELECTSUM(CASEWHEN Gender='Male'THEN 1ELSE 0END)as MaleCount,

SUM(CASEWHEN Gender='Female'THEN 1ELSE 0END)as FemaleCount,

SUM(CASEWHEN Gender='NotGiven'Or GenderISNULLTHEN 1ELSE 0END)as NotGivenCount

FROM Users

|||

This ended up working for me: SELECT Count_1 = (SELECT COUNT(Gender) FROM User WHERE Gender='male'), Count_2 = (SELECT COUNT(Gender) FROM Users WHERE Gender='female') and then access Count_1 and Count_2

Thanks for your help.. I will mark yours as the answer because it looks like it would work too.

sqlsql

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?
Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>
|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...[vbc
ol=seagreen]
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will b
e
> the non-null field?
>
>[/vbcol]|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine text field with ntext field

select textfield + N'-' + ntextfield as myfield from tbl
results with error
how i can combine text field with ntext field ?
thanksHi
+ is not a valid operator when using ntext. Your result could be 4GB wide,
make sure that it is necessarily to have datatypes this large.
John
"Sam" <focus10@.zahav.net.il> wrote in message
news:uGlE%238buFHA.3792@.TK2MSFTNGP10.phx.gbl...
> select textfield + N'-' + ntextfield as myfield from tbl
> results with error
> how i can combine text field with ntext field ?
> thanks
>|||Hi,
See the old post from Aaron:-
http://groups.google.com/group/micr...c0c3714d2c912a9
Thanks
Hari
SQL Server MVP
"Sam" <focus10@.zahav.net.il> wrote in message
news:uGlE%238buFHA.3792@.TK2MSFTNGP10.phx.gbl...
> select textfield + N'-' + ntextfield as myfield from tbl
> results with error
> how i can combine text field with ntext field ?
> thanks
>sqlsql

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO

12/22/2006

2

12:48:45 PM

9

12/22/2006

16

5:40:55 AM

1

12/22/2006

16

12:03:59 PM

2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.I was suggested to try this out:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

I'll run it after I use DTwizard to export the data into my table. I should also mention I have no PKs defined, just a FK that references Id from a table called Employees. I'm thinking it's best to define ID and Date and Time as PKs.|||

As far as SQL Server is concerned, it will be far easier over time to work with and deal with date/time data if it is stored as one column. I recommend combining the two.

One primary reason is that the combined column will allow easier datetime comparisions and searches.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Would it be better to combine the Date & Time fields into one
column? If so, how?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.drurjen (jfontecha@.gmail.com) writes:

Quote:

Originally Posted by

Good morning.
>
I am importing an XLS file into one of my tables. The fields are:
>
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
>
When I do the import, I get the following:
>
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
Here are my doubts:
>
1. Would it be better to combine the Date & Time fields into one
column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

Quote:

Originally Posted by

2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.

Combine results into one field

If I do a basic query
select * from grouping where code='12345'
I get the results
(fields are code,result)
12345 aaaaa
12345 assas
12345 f5fgh
I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
just want one result. Possible?
*** Sent via Developersdex http://www.examnotes.net ***http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:eUn4NjrTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> If I do a basic query
> select * from grouping where code='12345'
> I get the results
> (fields are code,result)
> 12345 aaaaa
> 12345 assas
> 12345 f5fgh
>
> I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
> just want one result. Possible?
> *** Sent via Developersdex http://www.examnotes.net ***

Combine multiple lines for one record into one comma-delimted field

Here is a query I have: It's long, so bear with me.
Scroll to the bottom to see what question I have, please.
This is a SQL 2003 db. In the select statement I am only asking for 2
fields which in reality, I am asking for about 40 (hence the long FROM
section)
SELECT v_basic_booking_data_ODBC.ptt_last_name,
dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
FROM dbo.v_basic_case_rec_data INNER JOIN
dbo.v_CRA_BO_01_Journaling_Data
v_CRA_BO_01_Journaling_Data ON
dbo.v_basic_case_rec_data.cr_urn =
v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
dbo.v_CRA_cpt_code ON
dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
JOIN
reportuser.v_BKA_02_Booking_Data
v_BKA_02_Booking_Data INNER JOIN
dbo.v_basic_booking_data_ODBC
v_basic_booking_data_ODBC ON
v_BKA_02_Booking_Data.book_urn =
v_basic_booking_data_ODBC.book_urn INNER JOIN
dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
JOIN
dbo.book_audref book_audref ON
v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
v_CRA_BO_01_Journaling_Data.cr_urn =
book_audref.cr_urn
WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
'2006-03-10 00:00:00', 102)) AND
(v_basic_booking_data_ODBC.book_date <
CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
ORDER BY v_basic_booking_data_ODBC.room_descr,
v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
I get results like
lname cptcode
DOE 1111111
DOE 4343445
SMITH 5456544
RALF 4789008
what I want is for Doe's line to read
DOE 111111,4343445
How can I do this with my extensive query above?
Thank you.
*** Sent via Developersdex http://www.examnotes.net ***Joe , since you have mot posetd DDL+ sample data I did some testing on my
own. Just be aware that the below approach isn't reliable and shoud be
avoided ,instead do such reports on the client side
create table w
(
id int not null,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||In my experience of trying it, dynamic columns in SQL is horrible, and
I've only achieved it with some messy Dynamic SQL. What are you
outputting to? if it's going out to a web app or something then I'd
suggest using the c# (or whatever language) to clean up your result set
there. SQL is more geared to obtaining rather than formatting data.
Cheers
Will|||http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Combine fields

I have two fields that I would like to combine into 1 field is this possible.
Example: Document # 555, Doc Description ABCD in the 3 field I would like 555-ABCD.
Is that possible in SQL Server thankscould you provide us some info as outlined in the hint sticky that's on top of the board?|||Select DocNoc, DocDesc from DocTable (take these to fields and insert it into one field in the same table.
Insert into DocNumDesc

How would I go about creating this syntax. Thanks|||SELECT DocNoc+DocDesc

??|||Okay is the correct syntax:

INSERT INTO DocNumDesc
SELECT DocNoc +DocDesc
FROM DocTable


Thanks