Concatenate Date & Time

I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
nvarchar(8) type. I have 2 problems.
1. I need a way to concatenate the 2 fields into 1 datetime field with a
select statement
2.In my example data below, you can see that dtTime is in a "military" time
format. Is there a way within SQL to convert it to a normal time format with
the AM/PM?
Any ideas or help would be greatly appreciated.
Example of Data ******************
dtDate dtTime
---
3/14/2006 12:00:00 AM 01:21:57
3/15/2006 12:00:00 AM 14:42:53Why are these separate? Anyway, try this, untested...
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
Of course, this will only work if all of your dtTime values are valid times.
Since you chose NVARCHAR for some reason, this is an extra hassle to
validate / constrain.
"Scott Bailey" <sbailey@.mileslumber.com> wrote in message
news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
>nvarchar(8) type. I have 2 problems.
> 1. I need a way to concatenate the 2 fields into 1 datetime field with a
> select statement
> 2.In my example data below, you can see that dtTime is in a "military"
> time format. Is there a way within SQL to convert it to a normal time
> format with the AM/PM?
> Any ideas or help would be greatly appreciated.
>
> Example of Data ******************
> dtDate dtTime
> ---
> 3/14/2006 12:00:00 AM 01:21:57
> 3/15/2006 12:00:00 AM 14:42:53
>|||Your code returns just the date part like:
2006-03-14
Can you modify it to display the date and time? Also, can you have the whole
result converted to datetime format?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||One last note, I didn't create this db, I just inherited it or I would never
have split the Date and Time into different fields.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||thank you. it works.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ug$RJsAZGHA.1228@.TK2MSFTNGP02.phx.gbl...
>I was basing it on this:
> SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
> Which returns:
> --
> 2006-04-19 7:40:33 PM
> Maybe it will work better like this:
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
> I would put this into a view so you don't have to repeat this calculation
> everywhere.
> If that still doesn't yield the correct results, then please post DDL and
> sample data so we can actually try and reproduce your issue.
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
>

concatenate a text data type

Hi,
How to concatenate a text data type value with another text data type
value or varchar data type value.
Regards
KrishnaKrishna (krishna_hot@.hotmail.com) writes:
> How to concatenate a text data type value with another text data type
> value or varchar data type value.
You will have to look into UPDATETEXT.
Note that you cannot assign variables of the type text at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
Imran
Can you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
sqlsql

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> > Hi,
> > I have two columns name indate and intime. Both of these fields are in
> > varchar type. I need to concat two such a way that it looks like a
> datetime
> > type. Or if there is a way to convert this varchar type into datetime
> > datatype then that would work too.
> >
> > Thanks.
> > Imran
> >
>

Composite key cannot allow bit column

Made an interesting discovery today - a column of data type Bit is not allowed to participate in composite key. Rather surprised.

I have a certain unique code that exhibits 2 unique states, which rendered the use of the boolean column, so the uniqueness goes <code>-1 and <code>-0. Is there any 'hack' so to speak to avoid using a Tinyint for the boolean column instead?Perhaps instead you could use a unique constraint composed of the 2 columns.

Terri|||I'm sure its not a good idea to use a field with so few values as a part of key. I think the tree tranversal will be impacted and your performance will suffer.|||Well, SQL Server won't allow me to use it as a key, so no "worries" on that. I will try Terri's unique constraint suggestion later.|||Unique constraints don't allow bit columns to participate either. Sigh.|||Actually, I'm sorry, what I meant was a unique index! I had tried that out before posting and it did not give me an error.

Terri

Complicated (maybe)

Server Type
-- --
S1 A
S2 A
S3 B
S4 B
S5 C
S6-... D
I need to randomly pick one server that is type A, one that is type B,
one that is type C, and 5 that are type D (Of course there is more than
2 type A's, etc than the example).
Is there a good way to do it thru SQL? Currently I am considering
breaking them out to different views, randomly selecting from those,
then merging the results. But I was wondering if there was an easier
way?
Thanks.d4 (d4mann@.gmail.com) writes:
> Server Type
> -- --
> S1 A
> S2 A
> S3 B
> S4 B
> S5 C
> S6-... D
> I need to randomly pick one server that is type A, one that is type B,
> one that is type C, and 5 that are type D (Of course there is more than
> 2 type A's, etc than the example).
> Is there a good way to do it thru SQL? Currently I am considering
> breaking them out to different views, randomly selecting from those,
> then merging the results. But I was wondering if there was an easier
> way?
Different views seems a bit overkill, but you would need four different
queries:
SELECT TOP 1 Server FROM Servers WHERE Type = 'A' ORDER BY NEWID()
You could of course insert these into a temp table, so that the client
only has to handle one result set.
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|||The "random selection" approaches in SQL are more or less non-intuitive. One
option in t-SQL is to do:
SELECT t1.Type,
( SELECT TOP 1 t2.Server
FROM tbl t2 WHERE t2.Type = t1.Type
ORDER BY NEWID() )
FROM tbl t1
GROUP BY t1.Type ;
If the server value need not be "random", you can use MIN or MAX directly in
your query.
Anith|||Try this:
set nocount on
create table #servers (server char(2), type char(1))
insert #servers select 'S1', 'A'
insert #servers select 'S2', 'A'
insert #servers select 'S3', 'B'
insert #servers select 'S4', 'B'
insert #servers select 'S5', 'C'
insert #servers select 'S6', 'D'
select (select top 1 server from #servers where type = S.type order by
newid()) from #servers S group by S.type
drop table #servers
Erland, do you see a problem with this?
"d4" <d4mann@.gmail.com> wrote in message
news:1132151625.063400.312760@.z14g2000cwz.googlegroups.com...
> Server Type
> -- --
> S1 A
> S2 A
> S3 B
> S4 B
> S5 C
> S6-... D
> I need to randomly pick one server that is type A, one that is type B,
> one that is type C, and 5 that are type D (Of course there is more than
> 2 type A's, etc than the example).
> Is there a good way to do it thru SQL? Currently I am considering
> breaking them out to different views, randomly selecting from those,
> then merging the results. But I was wondering if there was an easier
> way?
> Thanks.
>|||Raymond D'Anjou (rdanjou@.canatradeNOSPAM.com) writes:
> set nocount on
> create table #servers (server char(2), type char(1))
> insert #servers select 'S1', 'A'
> insert #servers select 'S2', 'A'
> insert #servers select 'S3', 'B'
> insert #servers select 'S4', 'B'
> insert #servers select 'S5', 'C'
> insert #servers select 'S6', 'D'
> select (select top 1 server from #servers where type = S.type order by
> newid()) from #servers S group by S.type
> drop table #servers
> Erland, do you see a problem with this?
Yes, it does not meet the requirements:
===
If D is he only he wants more than one of, he could do:
select (select top 1 server from #servers where type = S.type order by
newid()) from #servers S where S.type <> 'D' group by S.type
union all
select top 5 server from #servers where type = 'D' order by newid()
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

complex query / advice needed

Hello!

I'm seeking advice on a rather complex type of query I need to build
in an Access ADP (SQL-Server 7). There are four tables:

tblPeople
ID(PK)PRENAME
-----
1Thomas
2Frank
3Chris

tblInventoryClasses
ID(PK)INVENTORYCLASS
-------
1Car
2Phone

tblInventoryItems
ID(PK)relInvClass(FK)ITEM
-----------
11Dodge Viper
21Chrysler
32Nokia
42Samsung

tblPeopleInventory
ID(PK)relPeople(FK)relInvItem(FK)
------------
112
213
321
423
534

In this example the last table tells me that
Thomas owns a Chrysler (class Car) and a Nokia (class Phone).

Can someone tell me how to write a query or a stored procedure which
produces a resultset like this:

qryOwners
PeopleCarPhone
----------
ThomasChryslerNokia
FrankDodge ViperNokia
Chris[NULL]Samsung

The main idea is that I need to be able to iterate such a collection.
It is guranteed that one "People" only owns one or zero "Car" and one
or zero "Phone".

I guess that it might be impossible to design a stored procedure with
such a variable amount of columns (in this case, each item from
tblInventoryClasses would mean another column).

Ary there any possibilities in accomplishing this without creating
temporary tables?

Any help would be really appreciated ;-)

Greetings,
Christoph BispingChristoph Bisping <bisping.nospamplease@.unikopie.de> wrote in message news:<85d21051enbtmd5g679kpf5bddcml7f3va@.4ax.com>...
> Hello!
> I'm seeking advice on a rather complex type of query I need to build
> in an Access ADP (SQL-Server 7). There are four tables:
> tblPeople
> ID(PK)PRENAME
> -----
> 1Thomas
> 2Frank
> 3Chris
> tblInventoryClasses
> ID(PK)INVENTORYCLASS
> -------
> 1Car
> 2Phone
> tblInventoryItems
> ID(PK)relInvClass(FK)ITEM
> -----------
> 11Dodge Viper
> 21Chrysler
> 32Nokia
> 42Samsung
> tblPeopleInventory
> ID(PK)relPeople(FK)relInvItem(FK)
> ------------
> 112
> 213
> 321
> 423
> 534
> In this example the last table tells me that
> Thomas owns a Chrysler (class Car) and a Nokia (class Phone).
> Can someone tell me how to write a query or a stored procedure which
> produces a resultset like this:
> qryOwners
> PeopleCarPhone
> ----------
> ThomasChryslerNokia
> FrankDodge ViperNokia
> Chris[NULL]Samsung

This particular query looks like:

CREATE VIEW qryOwners AS
SELECT
person.PRENAME AS People,
car.ITEM AS Car,
phone.ITEM AS Phone
FROM
tblPeople person
LEFT JOIN
(
tblInventoryClasses carClass
INNER JOIN tblInventoryItems car
ON carClass.INVENTORYCLASS = 'Car'
AND carClass.ID = car.relInvClass
INNER JOIN tblPeopleInventory carPerson
ON car.ID = carPerson.relInvItem
) ON person.ID = carPerson.relPeople
LEFT JOIN
(
tblInventoryClasses phoneClass
INNER JOIN tblInventoryItems phone
ON phoneClass.INVENTORYCLASS = 'Phone'
AND phoneClass.ID = phone.relInvClass
INNER JOIN tblPeopleInventory phonePerson
ON phone.ID = phonePerson.relInvItem
) ON person.ID = phonePerson.relPeople

> The main idea is that I need to be able to iterate such a collection.
> It is guranteed that one "People" only owns one or zero "Car" and one
> or zero "Phone".
> I guess that it might be impossible to design a stored procedure with
> such a variable amount of columns (in this case, each item from
> tblInventoryClasses would mean another column).

You cannot get each InventoryClass row to magically add a column to
the resultset unless you use a stored procedure that builds dynamic
SQL statements using the EXECUTE(string) command. That would be
qualified as UGLY code, though.

You can see the pattern though.

It's simpler/more efficient on the database side if you just join all
the tables together and use an ORDER BY clause to make your client
code easier to write.

-Russell|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. There is no such thing as a universal "id" -- to be
something is to be something in particular -- a row number or IDENTITY
is a way of destroying a RDBMS and making into a sequential file
system. Why do you have that silly, redundant "tbl-" prefix on data
element name -- tell me what it is LOGICALLY and not how you are
PHYSICALLY representing it. It makes you look like a FORTRAN or BASIC
programmer.

Let's get some DDL and fix the schema. An inventory class is an
attribute of an inventory item.

CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);

CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
item_description CHAR(15) NOT NULL,
..,);

CREATE TABLE Allocations
(person_id INTEGER NOT NULL
REFERENCES People(person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
inventory_nbr INTEGER NOT NULL
REFERENCES Inventory (inventory_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
..,
PRIMARY KEY (person_id, inventory_nbr));

>> Can someone tell me how to write a query or a stored procedure
which produces a result set like this: <<

This is a report and not a query; this ought to be done in the front
end and not in the database at all. Thanks to the lack of specs and
DDL, we have no idea if people can have more than one car or more than
one phone. Here is one possible guess at an answer:

SELECT P1.name,
MAX (CASE WHEN I1.inv_class = 'car'
THEN I1.item_description
ELSE NULL END) AS auto,
MAX (CASE WHEN I1.inv_class = 'phone'
THEN I1.item_description
ELSE NULL END) AS phone
FROM People AS P1, Allocations AS A1, Inventory as I1
WHERE A1.person_id = P1.person_id
AND A1.inventory_nbr = I1.inventory_nbr
GROUP BY P1.name;

if you allowed only one item per class, then use:

CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);

CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
item_description CHAR(15) NOT NULL,
..,);

CREATE TABLE Allocations
(person_id INTEGER NOT NULL
REFERENCES People(person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
inventory_nbr INTEGER NOT NULL
REFERENCES Inventory (inventory_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
UNIQUE (person_id, inv_class),
..,
PRIMARY KEY (person_id, inventory_nbr));|||rustleb@.hotmail.com (Russell Bevers) wrote:

>You cannot get each InventoryClass row to magically add a column to
>the resultset unless you use a stored procedure that builds dynamic
>SQL statements using the EXECUTE(string) command. That would be
>qualified as UGLY code, though.
>You can see the pattern though.
>It's simpler/more efficient on the database side if you just join all
>the tables together and use an ORDER BY clause to make your client
>code easier to write.
>-Russell

That was exactly what I (didn't) want to read...
After digging through hundreds of url's everything looks to me like
that's the only way to accomplish this. Anyway, thanks for your answer
which showed me that I definitely have to handle these things in the
frontend.

In fact, the overall processing performance seems to be good enough if
I just read all of these tables in order to build lookup-tables in my
application. Being familiar with UGLY code, your suggestion using
EXECUTE(strSQL) sounds like an alternative ;-)

Greetings,

Christoph Bisping|||joe.celko@.northface.edu (--CELKO--) wrote:
>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in
>your schema are. There is no such thing as a universal "id" -- to be
>something is to be something in particular -- a row number or IDENTITY
>is a way of destroying a RDBMS and making into a sequential file
>system. Why do you have that silly, redundant "tbl-" prefix on data
>element name -- tell me what it is LOGICALLY and not how you are
>PHYSICALLY representing it.
>It makes you look like a FORTRAN or BASIC programmer.

Well, this wouldn't be a wrong statement. I do agree that I'm using
way to much of these "universal id"-cols in my tables which are surely
wasted.

As you might guess I'm quite inexperienced here and for now pure DDL
isn't one of my favorite languages but I'll see if I'm able to adapt
what you've written. But it seems to be much easier for me to go the
"sequential file system" way and do the complex People<->InventoryItem
mappings entirely at frontend level.

Thanks for your detailed explanation!

Greetings,

Christoph Bisping

Complex Query - Part II

I am passing the following query:
select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
else 0 end) as 'Closed',
sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
from ticket where location in (select location from location where
lstate = 'mp')
and received_date between '5/1/2004' and '5/10/2004' group by type
My requirement is for totals of each column generated, suggest. I am
using an automated asp script written by me for generating dynamic sql
reports, for which i just need to give the sql statement, suggest how to
get totals of each column generated automatically if it is of numeric
value and to ignore totals if it is of non numeric value.
Through sql query only.
Part II because my earlier post on May 10, 2004 was not answered.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
On Wed, 16 Jun 2004 23:03:50 -0700, Preet Kanwaljit Singh Shergill wrote:

>
>I am passing the following query:
>select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
>else 0 end) as 'Closed',
>sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
>sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
>from ticket where location in (select location from location where
>lstate = 'mp')
>and received_date between '5/1/2004' and '5/10/2004' group by type
>My requirement is for totals of each column generated, suggest. I am
>using an automated asp script written by me for generating dynamic sql
>reports, for which i just need to give the sql statement, suggest how to
>get totals of each column generated automatically if it is of numeric
>value and to ignore totals if it is of non numeric value.
>Through sql query only.
>
>Part II because my earlier post on May 10, 2004 was not answered.
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
Hi Preet,
If I understand your question correctly, you can either use this:
SELECT type, COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
GROUP BY type
UNION ALL
SELECT 'Total', COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
(untested)
Or you can look up ROLLUP in Books Online and see if that helps.
Final notes:
1) I changed the ambiguous date format you used to the recommended
YYYYMMDD format. Check if I didn't exchange the DD and MM parts of your
query.
2) I *think* that the subquery for location can be changed to an inner
join, but I'd have to know your table structure to be sure. The inner join
might yield better performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks a ton, it does solve my problem.
With this solution my SQL based reporting will be even faster than
before with minimal network traffic.
I no longer have to bother about html lipstick work.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Comparing varchar value in int type column

Hi,

I have a varchar(255) field on the control_value table which contains 10,159,711. These values are organization_ids (type int) separated by a common.

I am trying to exclude these organization IDs with the following statement:

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

select * from organization org

where org.organization_id not in (@.exclude_clients)

I get the following error:

Server: Msg 245, Level 16, State 1, Line 7

Syntax error converting the varchar value '10,159,711' to a column of data type int.

Is there anyway around this?

You couldn't use variables as part of in clause.

But you could use dynamic SQL for with task:

Code Snippet

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

declare @.query varchar(1000)

set @.query ='

select * from organization org

where org.organization_id not in ('+@.exclude_clients+')'

EXECUTE(@.query)

Another approach - split @.exclude_clients into table, then use join clause. You could use ideas from this link http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||

You can't use 'NOT IN' in this way - i.e. use it to identify multiple values from a comma-separated list provided as a single parameter.

The NOT IN condition as written will cause the entire @.exclude_clients string to be compared with org.organsation_id. However due to datatype precedence, SQL Server is attempting to convert the string to an integer (i.e. the same datatype as org.organsation_id) before the comparison - which is why you're experiencing the error. If the value of @.exclude_clients was '45' then the query wouldn't cause an error.

One way of performing the task would be to build up your SELECT statement dynamically, see below.

Chris

Code Snippet

DECLARE @.exclude_clients VARCHAR(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

/*

SELECT *

FROM organization org

WHERE org.organization_id NOT IN (@.exclude_clients)

*/

DECLARE @.sql VARCHAR(4000)

SET @.sql = 'SELECT * FROM organisation org WHERE org.organisation_id NOT IN ('

+ @.exclude_clients + ')'

EXEC (@.sql)

|||

Code Snippet

DECLARE @.T1 table(exclude_id int)

insert into @.t1
select 10 union
select 159 union
select 711

or if control_value is a table then

insert into @.t1
select value
from control_value
where parameter= 'client_excluded'

Then you can just use @.t1 as a table in your other query(ies)

select * from organization org
where org.organization_id not in (select exclude_id from @.t1)

|||Thanks Konstantin Kosinsky and Chris Howarth. The dynamic query works fine and it really helpful.