Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissqlsql

Concatenate int & var char - SQL

Hi,

I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar.

But i am receiving the error:

'Conversion failed when converting the varchar value ',' to data type int.

The SQL I am trying to use is:

select game_no + ',' + team_name as match
from result

Thanks

As the error message says you can concatenate similar datatype values and use CONVERT or CAST functions otherwise to make them simialr.

selectConvert(Varchar,game_no)+','+ team_nameas match

from result

|||

Yep. Why the parser is so stupid that it assumes the presence of one number means all other exprssions must evaluate to a number, rather than the other way around, is a mystery to me.

CONCAT in Execute SQL Task

I am curious to know if there is a way to suppress or temporarily store event errors until the final one, then take all those errors and write to db field?

I have a package that uses an event error to store msgs into a field. But, I end up getting several errors that will overwrite my db field each time. Only about two out of 10 are relevent to my problem...

I just need to know if there is a way to suppress the number of event errors that come over , or concat them into one event, per event that occurs? Right now when I hit an error, I end up getting 10 error messages that follow, so my OnError event gets triggered 10 times..

Jason

One way to handle this may be to count the errors using a script in your event handler. Update a variable until you reach the last error you wish to handle, then enable the behaviour you wish to execute.

Donald Farmer

Group Program Manager

SQL Server Integration Services

|||Thanks Donald, how do I determine when the final error occurs in a failure?

So, let's say I have an object that fails, I get 5 onerror event called msgs, how do I know that 5 (or whatever number) is my last error msg for the failure?|||

Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:

UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?

where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?

|||

scoobyjw wrote:

Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:

UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?

where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?

Jason,
Why not try building the SQL statement using a property expression on the SQLStatementSource property?

-Jamie|||The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:

if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?

? Sorry about the pseudo code, I am not an expert at SQL.|||

scoobyjw wrote:

The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:

if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?

? Sorry about the pseudo code, I am not an expert at SQL.

Yeah, try this:
[UPDATE ETL_Transactions SET LogDetails = COALESCE(LogDetails, '') + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

I really think you should look at using a property expression tho

-Jamie

Compress text column on SQL2000

I am trying to compress text/image column on a table on MSSQL200 Enterprise Ed.
When will compress when write to the table and uncompress when user retrieve
the data. Anyone can please suggest me how to or any tool to do this.
You need to do the compression and uncompression in the client app or middle
tier and not the database server.
Andrew J. Kelly SQL MVP
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
>I am trying to compress text/image column on a table on MSSQL200 Enterprise
>Ed.
> When will compress when write to the table and uncompress when user
> retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||To add to Andrew's response, I recommend that you test very heavily before
implementing this in a production environment; I've done fairly extensive
testing of various on-the-fly .NET compression libraries for the purpose of
compressing LOB data on the way in and out of the database, and found that
instead of improving performance as I expected (due to lowering disk IOs and
network bandwidth required to retrieve the data), there was instead a
moderate degredation due to the extra processor strain on the middle tier.
If you do find a way to improve performance using compression, I would be
very interested in hearing about your techniques and results -- of course,
if you're doing this only for disk space savings, you can disregard my
rantings...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> I am trying to compress text/image column on a table on MSSQL200
Enterprise Ed.
> When will compress when write to the table and uncompress when user
retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||Andrew J. Kelly wrote:
> You need to do the compression and uncompression in the client app or
> middle tier and not the database server.
>
The OP could place the TEXTIMAGE on a filegroup that is located on a
compressed folder on the server. That's supported by SQL Server if I'm
not mistaken and won't require any additional libraries to manage.
Andrew, any thoughts?
David Gugick
Imceda Software
www.imceda.com
|||Actually I know using compressed volumes is not recommended and I think it
may even be unsupported for Sql Server.
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23S5%23N4uGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
> The OP could place the TEXTIMAGE on a filegroup that is located on a
> compressed folder on the server. That's supported by SQL Server if I'm not
> mistaken and won't require any additional libraries to manage.
> Andrew, any thoughts?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Andrew J. Kelly wrote:
> Actually I know using compressed volumes is not recommended and I
> think it may even be unsupported for Sql Server.
>
You are correct:
http://support.microsoft.com/kb/231347/EN-US/
David Gugick
Imceda Software
www.imceda.com
|||What about upgrade to use Yukon, use CLR written in c# to write unpresss text
and store on sqlserver? Anyone has done this sort of thing?
"Adam Machanic" wrote:

> To add to Andrew's response, I recommend that you test very heavily before
> implementing this in a production environment; I've done fairly extensive
> testing of various on-the-fly .NET compression libraries for the purpose of
> compressing LOB data on the way in and out of the database, and found that
> instead of improving performance as I expected (due to lowering disk IOs and
> network bandwidth required to retrieve the data), there was instead a
> moderate degredation due to the extra processor strain on the middle tier.
> If you do find a way to improve performance using compression, I would be
> very interested in hearing about your techniques and results -- of course,
> if you're doing this only for disk space savings, you can disregard my
> rantings...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
> news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> Enterprise Ed.
> retrieve
>
>
|||"SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> What about upgrade to use Yukon, use CLR written in c# to write unpresss
text
> and store on sqlserver? Anyone has done this sort of thing?
I haven't tried yet -- but I plan to when the next beta is released and
performance is improved a bit. MS hadn't started performance tuning the
last releases much so it wasn't worth testing, IMO.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Please let us know later the.
"Adam Machanic" wrote:

> "SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
> news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> text
> I haven't tried yet -- but I plan to when the next beta is released and
> performance is improved a bit. MS hadn't started performance tuning the
> last releases much so it wasn't worth testing, IMO.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

composite stored procedure

Hi,
I am trying to write a procedure in SQL which will composite values in a table. I have an increment list, which increments by 0.1, I need to average values so I produce composites for 0-1, 1-2 etc grouping by id.
The script below produces a table then a procedure, but I'm stuck on the syntax and get the error

Column 'Increment.increment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

The script will run on the master database.
The end result should look like the following:

thanks for any help

id inc_from inc_to comp_val
-
1a 0 1 4.6

1a 1 2 4

1b 0 1 3.9

1b 1 2 4.5

--

drop table increment

create table increment(id varchar(10),increment float,value float)

insert into increment(id,increment,value) values('1a','0','6')
insert into increment(id,increment,value) values('1a','0.1','7')
insert into increment(id,increment,value) values('1a','0.2','4')
insert into increment(id,increment,value) values('1a','0.3','6')
insert into increment(id,increment,value) values('1a','0.4','2')
insert into increment(id,increment,value) values('1a','0.5','5')
insert into increment(id,increment,value) values('1a','0.6','8')
insert into increment(id,increment,value) values('1a','0.7','5')
insert into increment(id,increment,value) values('1a','0.8','1')
insert into increment(id,increment,value) values('1a','0.9','2')
insert into increment(id,increment,value) values('1a','1','3')
insert into increment(id,increment,value) values('1a','1.1','5')
insert into increment(id,increment,value) values('1a','1.2','4')
insert into increment(id,increment,value) values('1a','1.3','3')
insert into increment(id,increment,value) values('1a','1.4','6')
insert into increment(id,increment,value) values('1a','1.5','2')
insert into increment(id,increment,value) values('1a','1.6','1')
insert into increment(id,increment,value) values('1a','1.7','6')
insert into increment(id,increment,value) values('1a','1.8','6')
insert into increment(id,increment,value) values('1a','1.9','4')
insert into increment(id,increment,value) values('1a','2','2')
insert into increment(id,increment,value) values('1b','0','4')
insert into increment(id,increment,value) values('1b','0.1','7')
insert into increment(id,increment,value) values('1b','0.2','2')
insert into increment(id,increment,value) values('1b','0.3','1')
insert into increment(id,increment,value) values('1b','0.4','3')
insert into increment(id,increment,value) values('1b','0.5','5')
insert into increment(id,increment,value) values('1b','0.6','6')
insert into increment(id,increment,value) values('1b','0.7','4')
insert into increment(id,increment,value) values('1b','0.8','3')
insert into increment(id,increment,value) values('1b','0.9','6')
insert into increment(id,increment,value) values('1b','1','3')
insert into increment(id,increment,value) values('1b','1.1','5')
insert into increment(id,increment,value) values('1b','1.2','4')
insert into increment(id,increment,value) values('1b','1.3','3')
insert into increment(id,increment,value) values('1b','1.4','6')
insert into increment(id,increment,value) values('1b','1.5','8')
insert into increment(id,increment,value) values('1b','1.6','1')
insert into increment(id,increment,value) values('1b','1.7','6')
insert into increment(id,increment,value) values('1b','1.8','6')
insert into increment(id,increment,value) values('1b','1.9','4')
insert into increment(id,increment,value) values('1b','2','2')

go

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'usp_composite' AND type = 'FN')
DROP FUNCTION [dbo].[usp_composite]
GO

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
Select Id,
Ceiling(Increment) - 1 As Inc_From,
Ceiling(Increment) As Inc_To,
Avg(Value * 1.0) as comp_val
From Increment
Group By Id, Ceiling(Increment),Increment.increment
Order By Id, Ceiling(Increment)

You could do something like below:

select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to;

You are getting the error because your GROUP BY clause doesn't include the expressions that are in any of the aggregate functions. So it will work if you include ceiling(Increment) -1 also in the GROUP BY clause.

|||

Thanks for you help.

How do I modify the sql if the last depth value is correct, at the moment the sql returns 1 - 2 and not 1 - 1.95, see the code below with the create table.

drop table increment

create table increment(id varchar(10),increment float,value float)

insert into increment(id,increment,value) values('1a','0','6')
insert into increment(id,increment,value) values('1a','0.1','7')
insert into increment(id,increment,value) values('1a','0.2','4')
insert into increment(id,increment,value) values('1a','0.3','6')
insert into increment(id,increment,value) values('1a','0.4','2')
insert into increment(id,increment,value) values('1a','0.5','5')
insert into increment(id,increment,value) values('1a','0.6','8')
insert into increment(id,increment,value) values('1a','0.7','5')
insert into increment(id,increment,value) values('1a','0.8','1')
insert into increment(id,increment,value) values('1a','0.9','2')
insert into increment(id,increment,value) values('1a','1','3')
insert into increment(id,increment,value) values('1a','1.1','5')
insert into increment(id,increment,value) values('1a','1.2','4')
insert into increment(id,increment,value) values('1a','1.3','3')
insert into increment(id,increment,value) values('1a','1.4','6')
insert into increment(id,increment,value) values('1a','1.5','2')
insert into increment(id,increment,value) values('1a','1.6','1')
insert into increment(id,increment,value) values('1a','1.7','6')
insert into increment(id,increment,value) values('1a','1.8','6')
insert into increment(id,increment,value) values('1a','1.9','4')
insert into increment(id,increment,value) values('1a','2','2')
insert into increment(id,increment,value) values('1b','0','4')
insert into increment(id,increment,value) values('1b','0.1','7')
insert into increment(id,increment,value) values('1b','0.2','2')
insert into increment(id,increment,value) values('1b','0.3','1')
insert into increment(id,increment,value) values('1b','0.4','3')
insert into increment(id,increment,value) values('1b','0.5','5')
insert into increment(id,increment,value) values('1b','0.6','6')
insert into increment(id,increment,value) values('1b','0.7','4')
insert into increment(id,increment,value) values('1b','0.8','3')
insert into increment(id,increment,value) values('1b','0.9','6')
insert into increment(id,increment,value) values('1b','1','3')
insert into increment(id,increment,value) values('1b','1.1','5')
insert into increment(id,increment,value) values('1b','1.2','4')
insert into increment(id,increment,value) values('1b','1.3','3')
insert into increment(id,increment,value) values('1b','1.4','6')
insert into increment(id,increment,value) values('1b','1.5','8')
insert into increment(id,increment,value) values('1b','1.6','1')
insert into increment(id,increment,value) values('1b','1.7','6')
insert into increment(id,increment,value) values('1b','1.8','6')
insert into increment(id,increment,value) values('1b','1.9','4')
insert into increment(id,increment,value) values('1b','1.95','2')

go

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'usp_composite')

DROP PROCEDURE usp_composite

GO

/*******************************************************************************
Usage: exec usp_composite
********************************************************************************/

Create Procedure usp_composite
As
select id, inc_from, inc_to, avg(value)
from (
select id
, case when ceiling(increment) - 1 < 0 then 0 else ceiling(increment) - 1 end as inc_from
, case when ceiling(increment) - 1 < 0 then ceiling(increment) + 1 else ceiling(increment) end as inc_to
, value
from increment
) as t
group by id, inc_from, inc_to
go

|||

CELING will give the next closest integer value. So that value may not be in your table. You could check each max or min value against the values in your table. But it becomes complicated if you want to do it for each boundary value like 0 or 1 in 0-1, 1 or 2 in 1-2 and so on. And depending on how many possible values you have in your table it is going to be difficult. Can you answer some of the questions below?

1. What is the min/max range for the values in your Value column?

2. Do you want each range to be only the values in the column (like 0-1, 1-2, 2-3 and so on)?

If you always want only the actual min/max value within each ID as part of the min/max range buckets (inc_from , inc_to) then you can do something like below. The code will adjust the min/max values of each id based on the values in the table:

select id, inc_from, inc_to, avg(value)
from (
select i.id

/* adjust min if necessary based on the actual min value */
, case when ceiling(i.increment) - 1 < 0
then (case when 0 < i2.min_inc then i2.min_inc else 0 end)
else (case when ceiling(i.increment) - 1 < i2.min_inc then i2.min_inc else ceiling(i.increment) - 1 end)
end as inc_from
/* adjust min if necessary based on the actual max value */

, case when ceiling(i.increment) - 1 < 0
then (case when ceiling(i.increment) + 1 > i2.max_inc then i2.max_inc else ceiling(i.increment) + 1 end)
else (case when ceiling(i.increment) > i2.max_inc then i2.max_inc else ceiling(i.increment) end)
end as inc_to
, i.value
from increment as i
join (
select i1.id, min(i1.increment) as min_inc, max(i1.increment) as max_inc
from increment as i1
group by i1.id
) as i2
on i2.id = i.id
) as t
group by id, inc_from, inc_to;

|||

Many thanks, this works fine. I need to still average the data between 0-1,1-2,2-3 and so on but I need to display the correct increment values i.e. 1-1.95 and also if for example 0.5-1 rather than 0-1.

thanks

|||Put the increment value in your derived table. Now in you outer select, add Min(increment) and Max(increment). You get both the to and from values and the actual increments in your table.

Composite key "NOT IN" query?

I have a table with a composite key formed by the unique combination of columns w, x, y, z

I'm trying to write an INSERT statement along the following lines

INSERT INTO myTable

(SELECT w, x, y, z FROM someTable) t1

WHERE (this is the part I'm stumped on - where the unique combination of w, x, y, z is NOT in myTable already)

Help would be appreciated. Can you use the NOT IN keyword on composite values?

Can you use the NOT IN keyword on composite values?

Yes,but that is expensive.

FromQuery SQL Server Performance Tuning Tips

But If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

Use EXISTS or NOT EXISTS|||

INSERT INTO MyTable

SELECT t1.w,t1.x,t1.y,t1.z

FROM someTable t1

LEFT JOIN MyTable t2 ON t1.w=t2.w and t1.x=t2.x and t1.y=t2.y and t1.z=t2.z

WHERE t2.w IS NULL

This should also work:

INSERT INTO MyTable

SELECT w,x,y,z

FROM someTable

EXCEPT

SELECT w,x,y,z

FROM MyTable

Complicated Update Statement

Hi All,

I need to write one complicated update statement and I'm looking at
maybe finding a simpler way to do it.

I have 2 tables:

1.Photo Table
PhotoID FileName
1 111.jpg
1 111_01.jpg
2 222.jpg
2 222_01.jpg
2 222_02.jpg
3 333.jpg

2.PhotoReport Table
PhotoID FileName1 FileName2 FileName3....FileName12
1 111.jpg 111_01.jpg NULL NULL
2 222.jpg 222_01.jpg 222_02.jpg
..
..
..
I need to update PhotoReport Table to look like an example above. I've
started writing my code and it looks very hedeous with multiple nested
cursors.
So if someone has a sample of a code to accomplish this, please pretty
please send it my way. I appreciate it in advance.

Thank you,
Narine"Narine" <narine.kostandyan@.prurealty.com> wrote in message
news:5628948a.0406111054.2e24cb1b@.posting.google.c om...
> Hi All,
> I need to write one complicated update statement and I'm looking at
> maybe finding a simpler way to do it.
> I have 2 tables:
> 1.Photo Table
> PhotoID FileName
> 1 111.jpg
> 1 111_01.jpg
> 2 222.jpg
> 2 222_01.jpg
> 2 222_02.jpg
> 3 333.jpg
> 2.PhotoReport Table
> PhotoID FileName1 FileName2 FileName3....FileName12
> 1 111.jpg 111_01.jpg NULL NULL
> 2 222.jpg 222_01.jpg 222_02.jpg
> .
> .
> .
> I need to update PhotoReport Table to look like an example above. I've
> started writing my code and it looks very hedeous with multiple nested
> cursors.
> So if someone has a sample of a code to accomplish this, please pretty
> please send it my way. I appreciate it in advance.
> Thank you,
> Narine

You're looking for a crosstab query:

http://www.aspfaq.com/show.asp?id=2462

Simon|||[posted and mailed, please reply in news]

Narine (narine.kostandyan@.prurealty.com) writes:
> I need to write one complicated update statement and I'm looking at
> maybe finding a simpler way to do it.
> I have 2 tables:
> 1.Photo Table
> PhotoID FileName
> 1 111.jpg
> 1 111_01.jpg
> 2 222.jpg
> 2 222_01.jpg
> 2 222_02.jpg
> 3 333.jpg
> 2.PhotoReport Table
> PhotoID FileName1 FileName2 FileName3....FileName12
> 1 111.jpg 111_01.jpg NULL NULL
> 2 222.jpg 222_01.jpg 222_02.jpg
> .
> .
> .
> I need to update PhotoReport Table to look like an example above. I've
> started writing my code and it looks very hedeous with multiple nested
> cursors.
> So if someone has a sample of a code to accomplish this, please pretty
> please send it my way. I appreciate it in advance.

Cursors? You don't need cursors for this! But you here you get a
12-way self-join. Here I use a temp table, to make this a little
less verbose and more efficient:

CREATE TABLE photos (id int NOT NULL,
filename varchar(23) NOT NULL,
CONSTRAINT pk_photos PRIMARY KEY (id, filename))
go
CREATE TABLE photoreport (id int NOT NULL,
filename1 varchar(23) NOT NULL,
filename2 varchar(23) NULL,
filename3 varchar(23) NULL,
filename4 varchar(23) NULL,
filename5 varchar(23) NULL,
filename6 varchar(23) NULL,
filename7 varchar(23) NULL,
filename8 varchar(23) NULL,
filename9 varchar(23) NULL,
filename10 varchar(23) NULL,
filename11 varchar(23) NULL,
filename12 varchar(23) NULL DEFAULT 'test',
CONSTRAINT pk_report PRIMARY KEY(id))
go
INSERT photos (id, filename) values (1, '111.jpg')
INSERT photos (id, filename) values (1, '111_01.jpg')
INSERT photos (id, filename) values (2, '222.jpg')
INSERT photos (id, filename) values (2, '222_01.jpg')
INSERT photos (id, filename) values (2, '222_02.jpg')
INSERT photos (id, filename) values (3, '333.jpg')
INSERT photos (id, filename) values (3, '333_01.jpg')
INSERT photos (id, filename) values (3, '333_03.jpg')
go
INSERT photoreport (id, filename1)
SELECT id, MIN(filename)
FROM photos
GROUP BY id
go
SELECT id, filename,
rowno = (SELECT COUNT(*)
FROM photos p2
WHERE p1.id = p2.id
AND p1.filename >= p2.filename)
INTO #temp
FROM photos p1
ORDER BY id, filename

UPDATE photoreport
SET filename1 = t1.filename,
filename2 = t2.filename,
filename3 = t3.filename,
filename4 = t4.filename,
filename5 = t5.filename,
filename6 = t6.filename,
filename7 = t7.filename,
filename8 = t8.filename,
filename9 = t9.filename,
filename10 = t10.filename,
filename11 = t11.filename,
filename12 = t12.filename
FROM photoreport r
JOIN #temp t1 ON t1.id = r.id
AND t1.rowno = 1
LEFT JOIN #temp t2 ON t2.id = r.id
AND t2.rowno = 2
LEFT JOIN #temp t3 ON t3.id = r.id
AND t3.rowno = 3
LEFT JOIN #temp t4 ON t4.id = r.id
AND t4.rowno = 4
LEFT JOIN #temp t5 ON t5.id = r.id
AND t5.rowno = 5
LEFT JOIN #temp t6 ON t6.id = r.id
AND t6.rowno = 6
LEFT JOIN #temp t7 ON t7.id = r.id
AND t7.rowno = 7
LEFT JOIN #temp t8 ON t8.id = r.id
AND t8.rowno = 8
LEFT JOIN #temp t9 ON t9.id = r.id
AND t9.rowno = 9
LEFT JOIN #temp t10 ON t10.id = r.id
AND t10.rowno = 10
LEFT JOIN #temp t11 ON t11.id = r.id
AND t11.rowno = 11
LEFT JOIN #temp t12 ON t12.id = r.id
AND t12.rowno = 122

SELECT * FROM photoreport
go
DROP TABLE #temp, photoreport, photos
go

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Simon and Erland,

Thank you very much for helping me out here.
Erland's example worked for me because in my case I had to generate a
rownum column to be able to use the Case statement.

Thanks a million,
Narine

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Complicated query with really big tables

Dear Experts,

I want to write a very complex query on very huge tables.

The scenario is the following:

I have one table called #Products, one table called #Shops , one table called #Customers and one table called #CollectedInformation.

#Products (ProductID int, ProductName varchar(10))

Product Information. Around 70,000 unique products

#Shops(ShopID smallint, ShopName varchar(10))

Shop Information. Around 100 unique shops

#Customers(CustomerID smallint, CustomerName varchar(10)

Customer Information. Around 100 unique customers

#CollectedInformation(ProductID int, ShopID smallint, CustomerID int, SalesValue money)

Sales Information collected and inserted in a huge table. We don’t sell all products in all shops and to all customers. Maximum 70,000 products * 100 Shops * 100 customers, but usually it will contain around 650 million records instead of 700 million which is the max.

What I would like to get is for all products, fo all shops, for all customers to create a temporary table that will get SalesValue based on joining #Products, #Customers, #Shops with #CollectedInformation.

In case a product or customer or shop doesn’t have a value for field SalesValue I want it to appeear in the final table with Null as SalesValue.

To achieve this I used the CTE in SQL server 2005 to create a cartesian product between #Products, #Shops and #Customers and then I left outer join it with table #CollectedInformation.

(The cte command only with 70,000 products, 100 customers and only 2 - Out of 100 total shops takes around 1:20 minutes on a 2cpu 3.6GHz xeon 2GB Ram server connected with an HP MSA1000 SAN.)

Can you please help me to determine if there is a better way to do it. I Haven't tried it with the full set, but I believe it will be very slow.

Following is an example of the code I used for testing:

declare @.Customers int, @.Shops int, @.Products int

declare @.counter int

-- select @.Customers = 100, @.Shops = 100, @.Products =70000

--select @.Customers = 100, @.Shops = 2, @.Products =70000

select @.Customers = 100, @.Shops = 2, @.Products =700

create table #Customers(CustomerID smallint, CustomerName varchar(10))

set @.counter=1

while @.counter<=@.Customers

begin

insert into #Customers values(@.counter,'Cn'+str(@.counter,8))

set @.counter = @.counter +1

end

create unique clustered index C1 on #Customers(CustomerID)

create table #Shops(ShopID smallint, ShopName varchar(10))

set @.counter=1

while @.counter<=@.Shops

begin

insert into #Shops values((@.counter*2)+100, 's'+str(@.counter,9))

set @.counter = @.counter +1

end

create unique clustered index S1 on #Shops(ShopID)

create table #Products (ProductID int, ProductName varchar(10))

set @.counter=1

while @.counter<=@.Products

begin

insert into #Products values(@.counter,'p'+str(@.counter,9))

set @.counter = @.counter +1

end

create unique clustered index P1 on #Products(ProductID)

create table #CollectedInformation(ProductID int, ShopID smallint, CustomerID int, SalesValue money)

declare @.TempShopID int

declare Shops_cursor cursor fast_forward for

select ShopID

from #Shops

open Shops_cursor

fetch next from Shops_cursor

into @.TempShopID

while @.@.fetch_status = 0

begin

insert into #CollectedInformation (ShopID, ProductID, CustomerID)

select @.TempShopID, P.ProductID, C.CustomerID

from #Customers C, #Products P

fetch next from Shops_cursor

into @.TempShopID

end

close Shops_cursor

deallocate Shops_cursor

create clustered index ci1 on #CollectedInformation (CustomerID)

create index ci2 on #CollectedInformation (ProductID)

create index ci3 on #CollectedInformation (ShopID)

declare @.random money

set @.random = rand()

update #CollectedInformation

set SalesValue = cast((10000*@.random)+(ProductID*@.random)+(CustomerID*@.random)+(ShopID*@.random) as money)

-- Following is the code that creates the final temporary table:

declare @.PeriodNo int, @.Indicate char(1)

set @.PeriodNo =120

set @.Indicate='A';

with results_cte (ProductID, ProductName, ShopID, ShopName, CustomerID, CustomerName )

as

(

select p.ProductID, p.ProductName, s.ShopID, s.ShopName, c.CustomerID, c.CustomerName

from #Products p, #Shops s, #Customers c

)

select @.PeriodNo as Period , @.Indicate as SpecialSymbol, r.ProductID, r.ProductName, r.CustomerID, r.CustomerName, r.ShopID, r.ShopName, ci.Salesvalue

into #temp

from results_cte r

left outer join #CollectedInformation ci on ci.ProductID=r.ProductID and ci.ShopID=r.ShopID and ci.CustomerID=r.CustomerID

order by r.ProductID, r.CustomerID, r.ShopID

--select top 100 * from #temp

drop table #Products

drop table #Shops

drop table #Customers

drop table #CollectedInformation

drop table #temp

What are you trying to achieve in the end? How is the temporary table going to be used?

If you want to report on this can I suggest you look at analysis services.

In your code, what takes the time? Be aware that you don't have an index on the CollectedInformation table.

In addition when you scale up to 650 million rows. Assuming you have 4 integer fields in this table you will be processing 10Gb of data from 1 table. You are planning on joining two of these together thats 20Gb of data.

Any large processing of this nature is going to take time, reduced by having more processing power and more memory.

|||

Dear Simon,

The reason I need to create the temporary table is because I have to pass it through a CLR function which after a lot of processing, it will export data into a text file. (One line from the text file will consist of many rows from the #temp table - Shops will become columns on the text file - Every Product, every customer, salesvalueshop1, salesvalueshop2 etc. The CTE will be executed and processed by the CLR.)

Thanks for your advice on analysis services. I will take a look at it.

I have three indexes on the CollectedInformation

create clustered index ci1 on #CollectedInformation (CustomerID)

create index ci2 on #CollectedInformation (ProductID)

create index ci3 on #CollectedInformation (ShopID)

About the time and the size the table will contain at least: 1 int, 1 smallint, 3 tinyint, and 2 money fields(perhaps 1 can be reduced to smallmoney). The amount of data is per period. On the text file I may have up to 100 periods as well. I processed data period by period so that to reduce the size of the tables.

Actually I don't know if the cardesian product that the CTE creates which then left outer joins with CollectedInformation table is the best way. What i want from the query is to join collectedinformation with products, customers and shops. Some values from products, customers, shops will not be included on the collectedinformation table, but I want them to appear at the final text file with a * instead of the null value for thr collectedinformation.salesvalue (e.g. for one product, for one customer i have salesvalue for shop1 and shop2 only. text file: Product1, customer1, shop1salesvalue, shop2salesvalue, *, * No sales for shop 3 and 4)

Regards,

Spyros Christodoulou

Complicated join question.

Hi there.

I'm running Microsoft Business Solutions GP 8 backed by SQL Server 2000. I need to write a little C#/.NET 2.0 app to go "behind the scenes" to the SQL server in order to fetch and alter some data.

Unfortunately, the tables that GP 8 has created seem strange to me; I've encountered an issue with a SELECT clause featuring two INNER JOINs.

I need to select columns from three different tables. Unfortunately, the key structure of these tables is strange at best. You may view the structure of the three tables from the following URL.

http://pastebin.com/732226

My SQL query is as follows.

SELECT IV30400.DOCNUMBR, IV30300.DOCDATE, IV30200.GLPOSTDT, IV30400.SERLTNUM, IV30400.ITEMNMBR, IV30400.SERLTQTY

FROM IV30400

INNER JOIN IV30300 ON IV30300.DOCNUMBR = IV30400.DOCNUMBR

INNER JOIN IV30200 ON IV30200.DOCNUMBR = IV30300.DOCNUMBR

The key I am joining on is DOCNUMBR. The end result is that I get multiple copies of the same joint row in my result set. I only want one copy of each joint row.

Curiously, the DOCNUMBR key column does not contain unique values. In several rows (in each of the tables, mind you), the value in the DOCNUMBR column is identical. I don't know of this is the cause of my problem.

If anyone can help me sort out this thorny problem I would greatly appreciate it.

Thank you,

--JT

Curiously, the DOCNUMBR key column does not contain unique values. In several rows (in each of the tables, mind you), the value in the DOCNUMBR column is identical. I don't know of this is the cause of my problem.

I have seen programs like this and it is horrifying at best to work with the data (and this is their goal by making the table names so darn user friendly. I couldn't open your pictures, so you might just want to script them out.

Are there any queries in the program that give you somehting close to what you want? I used canned reports from the program I was working with to figure out the really hairy schema using Profiler (the greatest tool in the SQL Server toolbox.

Also, look to see if there is any table with docnumbr unique, of see if there is some other uniqueness criteria you can use.

|||

GROUP BY?

SELECT IV30400.DOCNUMBR, IV30300.DOCDATE, IV30200.GLPOSTDT, IV30400.SERLTNUM, IV30400.ITEMNMBR, IV30400.SERLTQTY

FROM IV30400

INNER JOIN IV30300 ON IV30300.DOCNUMBR = IV30400.DOCNUMBR

INNER JOIN IV30200 ON IV30200.DOCNUMBR = IV30300.DOCNUMBR

Group By IV30400.DOCNUMBR, IV30300.DOCDATE, IV30200.GLPOSTDT, IV30400.SERLTNUM, IV30400.ITEMNMBR, IV30400.SERLTQTY

Does that help at all?

Adamus

|||

Hi Adamus,

That indeed removed the duplicate result rows. I'm not sure WHY it works: I'm guessing that it's because the GROUP BY clause doesn't contain any sorting criteria, and thus it drops the duplicate rows altogether. Thank you for your suggestion!

Now I wonder why the tables are organized in this fashion. It almost seems counter-productive. Oh well.

Thanks again,

--JT

|||

Sounds like sloppy table design. Must've been crunched for time.

Adamus

complex SQL select query

Hi all

I im trying to write a SELECT query to display a set of my logged in user's 'Friends'. Although the way that i have designed my tables means that its very complex, and im hoping someone out there can tackle it!

To start ill show you how i contruct friends:

Friends

FriendshipID Incrementing PK
InviteeID Unique UserID of person who offered the friendship link
InvitedID Unique UserID of person who was invites
ApprovedBInvitee True/False - sets to 'True' by default (probably isnt needed come to think of it)
ApprovedByInvited True/False/Declined - an nvarchar

Next, I have my UserDetails table:

UserDetails

UserID Unique UserID PK
UserName Unique Username (foreign key from aspnet_Users as created by aspnet_regsql.exe)
Avatar Integer which represents an image name in a photos folder

So, on the myFriends.aspx i firstly set an invisible label's text property to the unique UserID of the logged in user. This gives me a control paremater for the select statement.

The information I want to display is just the UserName and Avatar of all users who are friends with the logged in user.

I know that to get the records where the logged in user is either that Invited or the Invitee, I do this:

WHERE (@.loggedInUser = Friends.IniteeID)OR (@.loggedInUser = Friends.InvitedID)

(that will show the logged in user as his own friend but i dont mind that)

After that I am stuck more or less... it seems to become very complex... maybe i need 2 queries?

If anyone can help i would be very very grateful

This is actually a very simple query... it may seem a bit complex because you join back twice on the user class... actually, you don't *have* to do that... there are many ways to accomplish this.

SELECT
friendUsers.UserName,
friendUsers.Avatar
FROM
dbo.UserDetails u INNER JOIN dbo.Friends f
ON u.UserID = f.InviteeID OR u.UserID = f.InvitedID
INNER JOIN dbo.UserDetails friendUsers
ON friendUser.UserID = f.InviteeID OR friendUser.UserID = f.InvitedID
WHERE
u.UserID = @.loggedInUser

That should work.

|||

Another way to do it would be this:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = InviteeID OR UserID = InvitedID)

Believe it or not, those are the same query.

|||

Nullable:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = InviteeID OR UserID = InvitedID)

I forgot one more piece to filter down by the current user:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

There :)

|||

Hi Nullable

Thanks for the rsponse, you obviously have more skills with sql than me!

I have tried the corrected second query:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

There is only one friends entry at the moment, one where the logged in userid will be the InvitedID (although that will not always be the case of course)

...that query is returning the Avatar and UserName of that user - the logged in one - rather than those of his friend. We need to stick a WHERE ApprovedByInvited = 'True' too, but i think i can manage that.

Do you know why we are getting the wrong user details?

Thanks again

|||I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.|||

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

I'll look into the query again to see where I crossed wires :)

|||

Heh, my "correction" to my earlier query was done in much haste and not thought out :) ... it was close, but not quite right:

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHEREUserID = @.loggedInUser AND (UserID = InviteeID OR UserID = InvitedID))

That is forcing only the current user... which was pretty dumb

SELECT UserName, Avatar FROM dbo.UserDetails
WHERE UserID IN (SELECT UserID FROM dbo.UserDetails, dbo.Friends WHERE UserID != @.loggedInUser
AND (UserID = InviteeID OR UserID = InvitedID) AND (@.loggedInUser= InviteeID OR @.loggedInUser= InvitedID))

To read that in English you would say "Give me the UserName and Avatar FROM the UsersDetails Table WHERE the user that I'm looking at is part of the following list: (Give me all Users who are linked in the friend table WHERE either the user is the Invitee OR the user is the Invited AND the loggedInUser is an Invitee OR the loggedInUser is the Invited)"

Got it? Good :) (Please make sure to mark one of these posts as the answer when you're done so that I know this issue has been resolved.)

Peace,

|||

Nullable:

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

- Sorry to correct you like that, I don't mean to seem rude, so here is a quick explaination into why I corrected you.

SELECT

c.*FROM dbo.SysColumns cINNERJOIN dbo.SysObjects oON c.id= o.id
-- Subtree Cost : 0.0317435

SELECT

*FROM dbo.SysColumns cWHERE idIN(SELECT idFROM dbo.SysObjects)
-- Subtree Cost : 0.0317125These two queries will return the EXACT same result set... but the one with the JOIN is actually slightly more expensive (and takes longer) to run... Do you know why? Well, to put it very simply and I will probably be "corrected" on this explaination... but here goes: The RESULTS of the query were only from the SysColumns table... so joining the two (thereby forcing SQL to have to ORDER the SysObjects table by ID to do it's cross streaming) is more expensive than the second query which only needed to get the list of IDs (in any order) from the SysObjects table.

As a punishment for your crime, you must go tohttp://www.SingingEels.com and spread the word!

|||Thank you nullable and French Duke, I must try to reproduce this myself with varying amounts of test data.|||

Hey Timothy

Thanks bro, thats done the job just nicely. Marked you up

|||

Nullable:

Nullable:

ndinakar:

I would recommend a query with a JOIN than with an IN because JOIN works faster. IN is like looking for each value in the IN clause separately. JOIN is like a batch.

- Good book knowledge, but unless you run the execution plan on the two and look at the subtree cost, you wouldn't want to make this statement.

- Sorry to correct you like that, I don't mean to seem rude, so here is a quick explaination into why I corrected you.

SELECT

c.*FROM dbo.SysColumns cINNERJOIN dbo.SysObjects oON c.id= o.id
-- Subtree Cost : 0.0317435

SELECT

*FROM dbo.SysColumns cWHERE idIN(SELECT idFROM dbo.SysObjects)
-- Subtree Cost : 0.0317125These two queries will return the EXACT same result set... but the one with the JOIN is actually slightly more expensive (and takes longer) to run... Do you know why? Well, to put it very simply and I will probably be "corrected" on this explaination... but here goes: The RESULTS of the query were only from the SysColumns table... so joining the two (thereby forcing SQL to have to ORDER the SysObjects table by ID to do it's cross streaming) is more expensive than the second query which only needed to get the list of IDs (in any order) from the SysObjects table.

As a punishment for your crime, you must go tohttp://www.SingingEels.com and spread the word!

Here's one article that I could find with peformance issues with IN:http://support.microsoft.com/kb/829205|||

Just recollected that the queries work differently if you have duplicate records in the subquery table. If your subquery has more records (like a 1-many relationship) doing a JOIN will return multiple records where as an IN might return only one record.

Complex Query - Need help

Hi,

I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ........., Sum of Amount for December, Total for All months, Average for all months.

I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?

Thanks!Are you just looking for something like this?

select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate|||No, I need total for each month for each employee, here is a sample of what I am looking for:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ...... $341

Thanks a lot for your help!|||Is an employee's rate going to change? If so what rate should be displayed?|||It will use group by "corpcode, EmpName, rate". So if rate changes for an employee, there should be a new line in the query output.

Thanks again!|||select corpcode
, EmpName
, rate
, sum(Amount) as test
, Sum (CASE DATEPART(yy,reportdate)
WHEN 1 THEN Amount Else 0) As January
End,
, Sum (CASE DATEPART(yy,reportdate)
WHEN 2 THEN Amount Else 0) As February
End,
...List rest oh the months here
...

FROM <table>
Group By corpcode, EmpName, rate|||I am getting this error message:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

also instead of DATEPART(yy,reportdate), don't we need DATEPART(mm,reportdate)?

Thanks again!|||I believe jaraba's response will still have all the months in seperate rows. You may have to do something like this. I am not sure if there is a better way it is just the first thing that came to mind.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

select corpcode, EmpName, rate,
(SELECT top 1 Amount From #tmp a where mymonth = 1
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as January,
(SELECT top 1 Amount From #tmp a where mymonth = 2
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as February
...
from #tmp

drop table #tmp

This will only display the most recent months, if you want years too you could have a whole mess of columns.

Hope this helps|||If you don't mind, send me some data in an excel spreadsheet. i will work a solution for you.|||Thanks a lot, you guys are big help.

I got jaraba's query to work, but I want to ask one more question,

Whenever CorpCode changes, I need a line for sub totals and grand total as follows:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ......$341
----------------------
Sub-Total--2 employees--$134--$903--$690--$458.........$1019
----------------------
B1114444--ABC Zeb--$15--$13--$46--$0.00 ...... $68.0
B1114444--TTT Bob--$11--$0--$23--$48.0 .......$31
B1114444--GTH Bob--$19--$70--$3--$8.0 .........$15
----------------------
Sub-Total--3 employees--$45--$83--$73--$56..........$114
----------------------
----------------------
Grand Total--5 employees--$189--$986--$763--$514.........$1133
----------------------|||Actually, my response was no better. Sorry, I think something like this should get you what you are looking for.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

CREATE TABLE #emp (corpcode int, EmpName varchar(50), rate int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

insert into #emp
SELECT DISTINCT corpcode, EmpName, rate
FROM #tmp

select #emp.corpcode, #emp.EmpName, #emp.rate, j.Amount as January, f.Amount as February ...
from #emp
LEFT OUTER join #tmp j on #emp.corpcode = j.corpcode
and #emp.EmpName = j.EmpName
and #emp.rate = j.rate and j.mymonth = 1
LEFT OUTER join #tmp f on #emp.corpcode = f.corpcode
and #emp.EmpName = f.EmpName
and #emp.rate = f.rate and f.mymonth = 2
...

drop table #tmp
drop table #emp

Sorry for the confusion.|||Sorry again, you can ignore my last post if jaraba's query got you what you are looking for. If you want subtotals for each corpcode you will have to write a seperate query or use the functionality of your report writer.|||Look up COMPUTE BY in BOL

Complex query

I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and
another column as Virtual_Pack. I've to write a query to update
Virtual_Pack column. The logic is as follows

1 virtual pack consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6

Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virtual_Pack will
be = 1 because Q2 = 2

Example Q1 = 30, Q2 = 5, Q3 = 8, Q4 = 15 then the Virtual_Pack will be
= 2

If any of the Q1, Q2, Q3 or Q4 = 0 then Virtual_Pack will be = 0

I don't want to write a cursor or do it in a loop. Any thoughts how
virtual_pack column can be updated in a single query.

Thanks in advance.

SubodhI don't understand the logic of how you calculate Virtual_Pack from
Q1,Q2,Q3,Q4. If you want a complete solution maybe you could explain by
showing us a formula or a pseudo-code algorithm. "Virtual_Pack will be = 1
because Q2 = 2" doesn't explain much to me but maybe I'm just particularly
dense today!

Lookup the CASE statement in Books Online. I think that may help you. For
example:

Virtual_Pack =
CASE
WHEN Q2 = 2 THEN 1
WHEN Q2 = x THEN y
END

Hope this helps.

--
David Portas
SQL Server MVP
--|||Okay, it SOUNDS LIKE what you want is the largest number (Virtual_Pack)
such that no element of (10, 2, 3.5, 6)*(Virtual_Pack)
is greater than (Q1, Q2, Q3, Q4). Am I right?

In that case, what you're asking for is the smallest (integer)
from among Q1/10, Q2/2, Q3/3.5, Q4/6

Unfortunately, MSSQL has no built-in function such as SmallestOf(A,B,...)
You can do it with a gawdawful CASE statement.

Old SQL coders kludge SmallerOf(A,B) as: (A+B-abs(A-B))/2
This does NOT extend to 3,4,... arguments gracefully :-)
But assuming your Qty table has columns (Q1, Q2, Q3, Q4, F1,...)
you can write a query (preferrably for burial in a view):

select (Q1+VP-abs(Q1-VP))/2 as Virtual_Pack, *
from(
select (Q2+VP-abs(Q2-VP))/2 as VP, *
from(
select (Q3+Q4-abs(Q3-Q4))/2 as VP, *
from Qty
) X
) X

The 'X' tags are just a syntactic requirement for nested queries.

"Subodh" <sgoyal@.agline.on.ca> wrote in message
news:90104bf0.0407131244.28097221@.posting.google.c om...
> I've a table where there are 4 columns for Qty(Q1, Q2, Q3, Q4) and
> another column as Virtual_Pack. I've to write a query to update
> Virtual_Pack column. The logic is as follows
> 1 virtual pack consists of Q1 = 10, Q2 = 2, Q3 = 3.5, Q4 = 6
> Lets say Q1 = 20, Q2 = 2, Q3 = 20, Q4 = 6 then the Virtual_Pack will
> be = 1 because Q2 = 2
> Example Q1 = 30, Q2 = 5, Q3 = 8, Q4 = 15 then the Virtual_Pack will be
> = 2
> If any of the Q1, Q2, Q3 or Q4 = 0 then Virtual_Pack will be = 0
> I don't want to write a cursor or do it in a loop. Any thoughts how
> virtual_pack column can be updated in a single query.
> Thanks in advance.
> Subodh|||> In that case, what you're asking for is the smallest (integer)
> from among Q1/10, Q2/2, Q3/3.5, Q4/6

If that's correct then here's one method that gives the desired result:

CREATE TABLE Qty (Q1 INTEGER NOT NULL, Q2 INTEGER NOT NULL, Q3 INTEGER NOT
NULL, Q4 INTEGER NOT NULL /* , PRIMARY KEY ? */)

INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (20, 2, 20, 6)
INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (30, 5, 8, 15)

SELECT Q1,Q2,Q3,Q4,
(SELECT CAST(MIN(x) AS INTEGER)
FROM
(SELECT Q1/10.0 AS x UNION ALL
SELECT Q2/2.0 UNION ALL
SELECT Q3/3.5 UNION ALL
SELECT Q4/6.0) AS X)
AS virtual_pack
FROM Qty

Result:

Q1 Q2 Q3 Q4 virtual_pack
---- ---- ---- ---- ----
20 2 20 6 1
30 5 8 15 2

Mischa, I couldn't get your query to produce the answer that the OP wanted.
Maybe you could test it again against my sample data. I'm interested to see
if it can be extended in the way you suggest.

Hope this helps.

--
David Portas
SQL Server MVP
--|||Ummm ... what was I smoking. This one passes the smoke test:

select (Q1/10+V234-abs(Q1/10-V234))/2 as Virtual_Pack, *
from(
select (Q2/2+V34-abs(Q2/2-V34))/2 as V234, *
from(
select (Q3*2/7+Q4/6-abs(Q3*2/7-Q4/6))/2 as V34, *
from Qty
) X
) X

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:CuydncOuNJqcQmnd4p2dnA@.giganews.com...
> > In that case, what you're asking for is the smallest (integer)
> > from among Q1/10, Q2/2, Q3/3.5, Q4/6
> If that's correct then here's one method that gives the desired result:
> CREATE TABLE Qty (Q1 INTEGER NOT NULL, Q2 INTEGER NOT NULL, Q3 INTEGER NOT
> NULL, Q4 INTEGER NOT NULL /* , PRIMARY KEY ? */)
> INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (20, 2, 20, 6)
> INSERT INTO Qty (Q1,Q2,Q3,Q4) VALUES (30, 5, 8, 15)
> SELECT Q1,Q2,Q3,Q4,
> (SELECT CAST(MIN(x) AS INTEGER)
> FROM
> (SELECT Q1/10.0 AS x UNION ALL
> SELECT Q2/2.0 UNION ALL
> SELECT Q3/3.5 UNION ALL
> SELECT Q4/6.0) AS X)
> AS virtual_pack
> FROM Qty
> Result:
> Q1 Q2 Q3 Q4 virtual_pack
> ---- ---- ---- ---- ----
> 20 2 20 6 1
> 30 5 8 15 2
> Mischa, I couldn't get your query to produce the answer that the OP
wanted.
> Maybe you could test it again against my sample data. I'm interested to
see
> if it can be extended in the way you suggest.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --

Complex Join, I think.

I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.

customer
id | name
1 | fred
2 | tom
3 | eric
4 | fred

custrate
cust_id | rate_id
1 | 120
1 | 200
2 | 130
2 | 300
3 | 120
4 | 130

I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
inner
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
case when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null|||select * from customer c
where not exists
(select 1 from custrate r
where c.id=r.cust_id
group by r.cust_id
having count(*)%2=0 -- Must be paired
)
PS. There is something amiss with Rudy's query|||PS. There is something amiss with Rudy's queryindeed there was, 2 things amiss

try it now --select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
left outer
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null:)|||4:30?

As in AM?

you just getting in?|||Rudy has no need for sleep.
Sleep is inefficient.

:p|||sorry for the late reply, was out all day

yeah, i normally get up around 4:00 or 4:30 am

go to bed when it gets dark

:)|||sorry for the late reply, was out all day

yeah, i normally get up around 4:00 or 4:30 am

go to bed when it gets dark

:)

You must have a heck of a time during the summer when it doesn't get dark until after 10:00 PM ... or is it about 10:30 in your area?|||indeed, sometimes i get sleepy well before it gets dark!

Complex insert?

I am at a loss for how to write an insert for my situation. There are two tables involved:

SubstanceAbuse (OrderId, AssessmentID)
TempAsst(AssessmentID)

The insert has to do this:

For each TempAsst.AssessmentID, insert a record into SubstanceAbuse (3, AssessmentID) if there is not already a record in SubstanceAbuse with that AssessmentID and a value of 3 in the OrderID column.

Thanks!

Maybe something like:

insert into substanceAbuse (orderId, assessmentId)
select distinct
3,
assessmentId
from tempAsst a
where not exists
( select 0 from substanceAbuse b
where a.assessmentId = b.assessmentId
)

|||Perfect! Thank you!!

Compiling ideas about security

Dear gurus,
We have got front-end and back-end app write in VB6 which at the beginning
retrieve information of paramount importance through XP registry, info such
as: login, password, strategic folders and so on. Well, I have been thinking
in change this and maybe storing that information in Sql tables help us to
display better our hindrances as well as holes security.
-Storing these data in Sql tables and encrypting the data there (how?)
-Storing these data in XML ??
Any help will be greatly welcomed.
Thanks in advance,
EnricHi Enric,
I see a small problem with storing your login info in SQL tables.
What would happen if your SQL password were to change? How would the app
retrieve the changed password if it can't login to the DB to begin with? :)
In terms of storing it in XML files or any config files for that matter,
what we did was we wrote a custom encryption/decryption function to handle
the read and write. To be more specific, we implemented the triple-DES
algorithm.
Hope this helps.
EK
"Enric" wrote:

> Dear gurus,
> We have got front-end and back-end app write in VB6 which at the beginning
> retrieve information of paramount importance through XP registry, info suc
h
> as: login, password, strategic folders and so on. Well, I have been thinki
ng
> in change this and maybe storing that information in Sql tables help us to
> display better our hindrances as well as holes security.
> -Storing these data in Sql tables and encrypting the data there (how?)
> -Storing these data in XML ??
> Any help will be greatly welcomed.
> Thanks in advance,
> Enric
>|||Why not use Windows Domain level security rather than create your own
security layer?
Password recovery mechanisms are inherent security weaknesses, so don't
store the password at all. Instead, store a secure hash of the password with
salt. The MS Crypto API provides the tools to do this.
David Portas
SQL Server MVP
--|||It sounds like you are basically wanting to query sensitive information from
it's designated location (Active Directory) and save it off where it is more
accessable. But accessable to whom? There are probably developers in your IT
department with admin logins to SQL Server. Unless this is part of some
disaster recovery plan, and the data is placed offsite in a safe deposit
box, I do not see the need for it.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:B8CB31DE-F58E-4DB8-BE68-586E387DF217@.microsoft.com...
> Dear gurus,
> We have got front-end and back-end app write in VB6 which at the beginning
> retrieve information of paramount importance through XP registry, info
such
> as: login, password, strategic folders and so on. Well, I have been
thinking
> in change this and maybe storing that information in Sql tables help us to
> display better our hindrances as well as holes security.
> -Storing these data in Sql tables and encrypting the data there (how?)
> -Storing these data in XML ?
> Any help will be greatly welcomed.
> Thanks in advance,
> Enric
>

compiler is not recognizing my using statement for SglConnection statement

I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code.

Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message.

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:

Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");

1 <asp:sqldatasource runat="server"></asp:sqldatasource>

2 <%@. Page Language="C#"%>

3 using System;

4 using System.Data;

5 using System.Collections;

6 using System.Data.SqlClient;

7

8 <script runat=server>

9

10 protected void Page_Load(object o, EventArgs e)

11 {

12 ObjectDataSource dsa; // This works no problems from the compiler here

13 SqlDataSource ds; // This works no problems from the compiler

14

15 if (IsPostBack)

16 {

17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text))

18 {

19 instructions.Text = "Congratulations, your authenticated!";

20 instructions.ForeColor = System.Drawing.Color.Red;

21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");

22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'";

23 }

24 else

25 {

26 instructions.Text = "Please try again!";

27 instructions.ForeColor = System.Drawing.Color.Red;

28 }

29 }

30

31 }

32

33 bool AuthenticateUser(string username, string password)

34 {

35 // Authentication code goes here

36

37 }

When using the in-line server code instead of code-behind, you have to use the following syntax instead:

<%@. Import Namespace="System.Data.SqlClient" %>