Computed column

Hi,
I am trying yo return a set or records by using a SELECT statement, the
table contains a column called CreationDate.
I want to add one more column to the resulting dataset (not to the table),
that value of the column will be 1 if the CreationDate is 1 day before the
current time, 0 otherwise.
Any idea how to do it?
thanks first,
Gnicuse a case statement.
SELECT 'return' = case
WHEN datediff > 0 THEN 1
ELSE 0
FROM table|||I tried the statement and it returns
"Incorrect syntax near the keyword 'FROM'."
So it doesn't work, it that a Yukon feature?
Because I am using SQL Server 2000
thanks
"Red2" <sdibello@.gmail.com> wrote in message
news:1142952098.221443.242870@.i39g2000cwa.googlegroups.com...
> use a case statement.
> SELECT 'return' = case
> WHEN datediff > 0 THEN 1
> ELSE 0
> FROM table
>|||Oh I found that I need to add an END keyword at the end the case statement.
It works now.
thanks for your help
Gnic
"Gnic" <gasnic@.gmail.com> wrote in message
news:e4rrd8QTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I tried the statement and it returns
> "Incorrect syntax near the keyword 'FROM'."
> So it doesn't work, it that a Yukon feature?
> Because I am using SQL Server 2000
> thanks
>
> "Red2" <sdibello@.gmail.com> wrote in message
> news:1142952098.221443.242870@.i39g2000cwa.googlegroups.com...
>sqlsql

Computed column

Hi,
I am trying yo return a set or records by using a SELECT statement, the
table contains a column called CreationDate.
I want to add one more column to the resulting dataset (not to the table),
that value of the column will be 1 if the CreationDate is 1 day before the
current time, 0 otherwise.
Any idea how to do it?
thanks first,
Gnic
use a case statement.
SELECT 'return' = case
WHEN datediff > 0 THEN 1
ELSE 0
FROM table
|||I tried the statement and it returns
"Incorrect syntax near the keyword 'FROM'."
So it doesn't work, it that a Yukon feature?
Because I am using SQL Server 2000
thanks
"Red2" <sdibello@.gmail.com> wrote in message
news:1142952098.221443.242870@.i39g2000cwa.googlegr oups.com...
> use a case statement.
> SELECT 'return' = case
> WHEN datediff > 0 THEN 1
> ELSE 0
> FROM table
>
|||Oh I found that I need to add an END keyword at the end the case statement.
It works now.
thanks for your help
Gnic
"Gnic" <gasnic@.gmail.com> wrote in message
news:e4rrd8QTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I tried the statement and it returns
> "Incorrect syntax near the keyword 'FROM'."
> So it doesn't work, it that a Yukon feature?
> Because I am using SQL Server 2000
> thanks
>
> "Red2" <sdibello@.gmail.com> wrote in message
> news:1142952098.221443.242870@.i39g2000cwa.googlegr oups.com...
>
sqlsql

Composite primary key

Hello,

Does composite primary key affect performance on the table that contains the composite primary key or tables that references this table?

When composite primary key should be used?

In my experience composite keys are a through back to old systems that where converted to databases such as SQL Server / Oracle.

As a developer, rather than a DBA, composite keys are a real pain to program against especially in the modern world of object programming, data objects etc. I would always use a single unique value to identify a row rather than using composite keys. If you regularly query dataing using mulitple values from columns in the where statement then by all means create a normal index based on these columns (in the order that you use them in the where statement).

|||

so considering performance issue!! does databases with such structure perform worse than ones with single keys!! i mean directly or indirectly.

*Directly: if it has a bad performance in join clauses or in search criteria.

*Indirectly: in programming specially in .Net when using datagrids and such controls that generate relations and act with whole tables in runtime.

Thnx

Complicated SQL Select help needed

Hi,

My users table contains a field called researchInterestId which looks like this: 1, 5, 10

This is because users where allows to select multiple options when choosing their research interests.

I have another table which contains the names of those research interests, which looks like this:

researchInterestId researchInterestName

1 Biology

2 Cancer

My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10)

Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called?

Thanks

Sam

You can create a function as shown here:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx in the comments and use it to join with the researchinterests table to get the description.

|||

It worked perfectly, thanks so much!

complicated sql condition query

Hi,

hope someone can help or suggest something to help me with the issue.

I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up

parent_ID child_id Type projname

--

100 100 P parent_X_proj

100 25 C child_X_proj

100 29 C child_X_proj2

200 200 P parent_Z

300 300 P parent_Y

etc................

this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project.

so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29.

I know it could be done in stored procedure buy my application cannon executre SP but only sql statements.

Thank you for any help

which version of SS are you using?

declare @.child_id int

set @.child_id = 100

;with proj

as

(

select

child_id, projname, type, 1 as lvl

from

dbo.t1

where

child_id = @.child_id

union all

select

c.child_id, c.projname, c.type, p.lvl + 1 as lvl

from

dbo.t1 as c

inner join

proj as p

on p.child_id = c.parent_id

and ((p.lvl = 1 and p.type = 'P') or (p.lvl > 1 and p.type = 'C'))

)

select

*

from

proj;

AMB

P.S. not tested

|||

thank you. I'm using SS 2005. as I mentioned in my post that I can't use Stored Procedure since my application can only execute plain sql statement.

thanks

|||

AMB has given you the answer. Your application just needs to form the query and execute it.

Code Snippet

;with proj

as

(

select

child_id, projname, type, 1 as lvl

from

dbo.t1

where

child_id = <selectedValue_goes_here>

union all

select

c.child_id, c.projname, c.type, p.lvl + 1 as lvl

from

dbo.t1 as c

inner join

proj as p

on p.child_id = c.parent_id

and ((p.lvl = 1 and p.type = 'P') or (p.lvl > 1 and p.type = 'C'))

)

select * from proj;

complicated query

Hi Guys

I Have not been able to solve this problem from quiete a while now.

I am using sql server 2005.

I have got a table which contains these columns - start date, end date and volumes
if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month

I have to perform a query on this table so that I can group the volumes for different months and different years.

Here is the sample data...

Service Start

Service End

FMIS Code

No of Units

Year

Month

Volumes

01-Oct-00

15-Aug-01

6440

32

?

?

?

01-Oct-00

30-Sep-02

6441

40

?

?

?

01-Oct-02

22-May-03

6440

78

?

?

?

01-Oct-02

23-May-03

6990

87

?

?

?

06-Mar-03

31-Jul-03

6997

102

?

?

?

07-Mar-03

31-Jul-03

6744

3

?

?

?

01-May-03

31-May-03

6440

789

?

?

?

23-Jun-03

31-Aug-03

6447

1000

?

?

?

29-Jun-03

30-Jun-03

6440

981

?

?

?

30-Jun-03

31-Jul-03

6000

50

?

?

?

01-Jul-03

08-Jul-03

6002

54

?

?

?

01-Jul-03

13-Jul-03

6000

562

?

?

?

I have to calculate the columns coloured in blue..

Please help guys!

Thanks

Mita

I don't fully understand the purpose of the year and month columns. Should they relate to the Service Start or Service End or are they a count of the years and months over which the volume is spread?

Presumably you then want to spread the volume over however many months. Should these volumes remain an integer value?

Months and years are not the best units of measure for the reasons you state in your post. I believe weeks is a much better measure (although we all know our managers know better ;) ). Obviously a Quantity spread over three months from Jan to Mar is not the same as the same quantity spread over say Oct to Dec. I'm sure you know this, and a query will not be impossible, but there is going to be either

a) an error in the results or

b) an inconsistency in results over different portions of the year.

Which would you prefer?

|||

Hi Thanks for replying

Well I have been doing this query in access from quiete a long time and am doing it for each individual month.

Therefore I need to worko out a way to make this less complicated. The reason why I included year and month was because when the month and year in start date and end date are not same,then we have to distribute the number of units in such a way for ex if start date is 20th oct and end date is 10th nov then we have to calculate the result by finding out volumes for 20 days in october and (october month) and 10 days for november(november month).

I have pasted the exact coding which I do in Access query for individual month(its a bit scary!)

This one is for Year 2004

Jan 2004

IIf([Service End]<#1/01/2004#,0,IIf([Service Start]>#31/12/2003# And [Service end]<#1/02/2004#,[MyTable]![NoOfUnits],IIf([Service Start]<#1/01/2004# And [Service end]<#1/02/2004#,([Service End]-#31/12/2003#)/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits],IIf([Service Start]<#1/01/2004# And [Service End]>#31/01/2004#,31/(1+[Service End]-[Service Start])*[MyTable]![NoofUnits,IIf([Service Start]>#31/01/2004#,0,(#1/02/2004#-[Service Start])/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits])))))

Feb 2004

IIf([Service End]<#1/02/2004#,0,IIf([Service Start]>#31/01/2004# And [Service end]<#1/03/2004#,[MyTable]![NoofUnits],IIf([Service Start]<#1/02/2004# And [Service end]<#1/03/2004#,([Service End]-#31/01/2004#)/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits],IIf([Service Start]<#1/02/2004# And [Service End]>#28/02/2004#,29/(1+[Service End]-[Service Start])*[MyTable]![NoofUnits],IIf([Service Start]>#28/02/2004#,0,(#1/03/2004#-[Service Start])/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits])))))

and so on for the rest of the months.....

complexity of nested self left joins?

Hi,

I'm curious about the computational complexity of a query I have. The
query contains multiple nested self left joins, starting with a simple
select, then doing a self left join with the results, then doing a self
left join with those results, etc. What puzzles me is that the time
required for the query seems to grow exponentially as I add additional
left joins, which I didn't expect. I expected the inner select to
return about 25 rows (it does), then I expected the self join to result
in about 25 rows (it does), etc. Each join just adds another column; it
doesn't add more rows. So the left part of the join is staying the same
size, and so is the right part of the join, since I'm always joining
with the same table.

So I would think the time for this query should be (time to join 25
rows against the source table) * (num joins), but it seems to be
something like (num rows) ^ (num joins). Any ideas? I'm just trying to
understand the system a little better. (But if you have any ideas about
improving the query, I'm always open to those, too.)

The execution plan is what you'd expect: an index seek loop-joined with
another index seek, the results of which are merge-joined with another
index seek, the results of which are merge-joined with another index
seek, ad nauseum, until a final "compute scalar cost (39%)" and "select
(0%)"

For the brave and curious, I've pasted the query below.

Thanks

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as
[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as
[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as
[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as
[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as
[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((select
distinct cp_yyyymm from cp_deal_history where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymm
between 200304 and 200504) as x left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') as
table0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1
on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2
on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3
on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4
on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5
on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6
on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7
on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8
on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9
on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') as
table10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymmFor those of you who's cranial parser requires liberal use of indents
and line breaks, I spent about an hour cleaning this up.

BTW, did you write this query to experiment with running times or are
you actually trying to accomplish something useful?

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
table0.cp_num_loans/1 as [AFCM9704],
table1.cp_num_loans/1 as [AFC9104],
table2.cp_num_loans/1 as [BFAT01C],
table3.cp_num_loans/1 as [BFAT02B],
table4.cp_num_loans/1 as [BFAT03D],
table5.cp_num_loans/1 as [BFAT03E],
table6.cp_num_loans/1 as [BFAT03F],
table7.cp_num_loans/1 as [BFAT04A],
table8.cp_num_loans/1 as [BFAT04C],
table9.cp_num_loans/1 as [BFAT04D],
table10.cp_num_loans/1 as [BFAT99C]
from (select distinct cp_yyyymm
from cp_deal_history
where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C',
'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C',
'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as x

left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFCM9704') as table0
on x.cp_yyyymm=table0.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFC9104') as table1
on x.cp_yyyymm=table1.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT01C') as table2
on x.cp_yyyymm=table2.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT02B') as table3
on x.cp_yyyymm=table3.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03D') as table4
on x.cp_yyyymm=table4.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03E') as table5
on x.cp_yyyymm=table5.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03F') as table6
on x.cp_yyyymm=table6.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04A') as table7
on x.cp_yyyymm=table7.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04C') as table8
on x.cp_yyyymm=table8.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04D') as table9
on x.cp_yyyymm=table9.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT99C') as table10
on x.cp_yyyymm=table10.cp_yyyymm
order by x.cp_yyyymm|||Still not entirely sure what you're trying to accomplish, but -- if
you're doing what I think you are -- why not try something like this:

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C],
from (select distinct cp_yyyymm
from cp_deal_history
where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C',
'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C',
'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as x
left outer join cp_deal_history y
on x.cp_yyyymm = y.cp_yyyymm
order by x.cp_yyyymm|||That is not great surprise. LEFT OUTER JOINs are not well optimized
and thigns get flaky about five self joins in most queries. The real
proglem is that your code is awful.

Why would anyone, except an 1950's COBOL programmer, store temporal
data in strings! Why did you name a column for its format and not its
contents? Time is always stored as durations in a temporal data type.

Why would you divide by integer 1 when the column looks like a count of
loans? Tell me that you did not store that data as strings too and
have to force type conversions!! Try this:

SELECT deal_date,
MAX(CASE WHEN deal_id = 'AFCM9704'
THEN loan_cnt ELSE NULL END) AS AFCM9704,
..
MAX(CASE WHEN deal_id = 'BFAT99C'
THEN loans_cnt ELSE NULL END) AS BFAT99C
FROM DealHistory
WHERE deal_date BETWEEN '2003-04-01' AND '2005-04-30'
GROUP BY deal_date;|||Sweet Jesus, ZeldorBlat, if I'd thought anyone was actually going to
look at that I'd have pretty-printed it myself. So I apologize, and
thank you for your effort. The query constructs the data for a chart,
with cp_yyyymm being the independent variable, and each row needing the
value of cp_num_loans for each month and each particular deal id. So
the result set needs exactly one row per month. Your suggested query is
definitely moving in the right direction, but it just pulls all the
data - it doesn't "filter" it and arrange it as required. After playing
around a little bit, the query I eventually got working was basically
what CELKO suggested:

select right(z.cp_yyyymm, 2)+'-'+left(z.cp_yyyymm, 4) as [Month],
max(AFCM9704_unfiltered) as [AFCM9704],
max(AFC9104_unfiltered) as [AFC9104],
max(BFAT01C_unfiltered) as [BFAT01C],
max(BFAT02B_unfiltered) as [BFAT02B],
max(BFAT03D_unfiltered) as [BFAT03D],
max(BFAT03E_unfiltered) as [BFAT03E],
max(BFAT03F_unfiltered) as [BFAT03F],
max(BFAT04A_unfiltered) as [BFAT04A],
max(BFAT04C_unfiltered) as [BFAT04C],
max(BFAT04D_unfiltered) as [BFAT04D],
max(BFAT99C_unfiltered) as [BFAT99C]
from
(select cp_yyyymm, cp_deal_id,
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704_unfiltered],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104_unfiltered],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C_unfiltered],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B_unfiltered],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D_unfiltered],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E_unfiltered],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F_unfiltered],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A_unfiltered],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C_unfiltered],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D_unfiltered],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C_unfiltered]
from cp_deal_history as y
where cp_deal_id in ('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B',
'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as z
group by z.cp_yyyymm
order by z.cp_yyyymm

Note the lack of a single join. Sigh - I don't know what I was
thinking. Thanks very much to both of you for your help. (I'm still
curious as to why the joins weren't working the way I expected, though.)

Complex T-SQL

Guys
I have a data table that contains emails that could come from one or
more source. I want to add a column to the table to reflect the
combination of sources that the record came from.
Basically I am trying to create the hybird_src and the hybrid tables
from the data_table. Then I want to update a new column on data table
with hybrid_id.
I know I can do this with a cursor, but it seems like there should be a
better (set based) way to accomplish this. Does anyone have any
suggestions?
create table #data_table(email_id int,src_id int)
--raw data
insert into #data_table (1,5)
insert into #data_table (1,6)
insert into #data_table (1,7)
insert into #data_table (2,5)
insert into #data_table (2,6)
insert into #data_table (2,7)
insert into #data_table (3,5)
insert into #data_table (3,6)
insert into #data_table (3,7)
insert into #data_table (4,5)
insert into #data_table (4,6)
insert into #data_table (5,5)
insert into #data_table (5,9)
insert into #data_table (5,4)
insert into #data_table (5,20)
insert into #data_table (6,20)
insert into #data_table (6,5)
insert into #data_table (6,9)
insert into #data_table (6,4)
create table #hybrid_src (hybrid_id int,src_id int)
--results I am looking for
insert into #hybrid_src (1,5)
insert into #hybrid_src (1,6)
insert into #hybrid_src (1,7)
insert into #hybrid_src (2,5)
insert into #hybrid_src (2,6)
insert into #hybrid_src (3,5)
insert into #hybrid_src (3,9)
insert into #hybrid_src (3,4)
insert into #hybrid_src (3,20)
create table #hybrid(hybrid_id int,hybrid_name varchar(200))
--results I am looking for
INSERT INTO #hybrid(1,'5,6,7')
INSERT INTO #hybrid(2,'5,6')
INSERT INTO #hybrid(3,'4,5,9,20')Dave wrote:
> Guys
> I have a data table that contains emails that could come from one or
> more source. I want to add a column to the table to reflect the
> combination of sources that the record came from.
> Basically I am trying to create the hybird_src and the hybrid tables
> from the data_table. Then I want to update a new column on data table
> with hybrid_id.
> I know I can do this with a cursor, but it seems like there should be a
> better (set based) way to accomplish this. Does anyone have any
> suggestions?
>
Why would you want to destroy the apparently sensible and practical
design you already have by kludging it into the "hybrid" tables that
you say you want? Concatenating lots of values together in a column
just results in redundancy and denormalization. If you need to display
it that way in a report then do it in your presentation tier, not in
the database.
David Portas
SQL Server MVP
--|||I am trying to accurately reflect which source an email came from.
Since an email can belong to more than one source it makes since (at
least to me so far) to report on a hybrid of all the valid sources.
If I model email transactions in a Datamart the fact grain would be the
email. I need a source dimension.
Any feedback on this would be very helpful. Including a better way to
model this.

Complex sum

I have a report created using SQL Server Reporting tools.

The report contains a field 'FIELD A' which displays the difference of hours in that record.
At the end of the report, I need to sum all FIELD A and display in FIELD B.

How do I do it?One way to do this, if all the 'FIELD A' textboxes exist on one page, is to add the 'Field B' textbox to the Page Footer, and use the SUM aggregate over the textbox. For example,

=(Sum(ReportItems!FieldATextbox.Value))

This will only work if the expression is in the Page Header or Footer, and if all instances of the 'Field A' textbox is on the same page.

If not all instances will exist on one page, then you could use a simple custom function that is called from the 'Field A' textbox. This custom function adds the value passed to a member field, and then returns the value passed. In the 'Field B' textbox call a separate custom function that returns the value stored in the member field.

Ian

Complex query. Can anyone solve this one

Hi
A little complex one
What i really want is that my table contains duplicate
firstname,lastname and dob.
what I need to do is check the two fields datecreated and
datelastupdated and retain the latest record based on
date of these two columns and update the other records
with OLD.
so the result I want is retain row 1 as datelastupdated
is latest for John Li as it is and update the 2 John Li
as John (OLD) Li
and also retain record 5 as datecreated is the latest so
retain this one and update the other one as OLD.
I have about 700 records to do and some are 2 duplicates
and some 3.
How can i write a query/cursor to solve this one.
DDL and DML are posted
CREATE TABLE Employees (
[EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (45) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dob] [datetime] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateLastUpdated] [datetime] NULL ,
[EmployerId] [int] NOT NULL
) ON [PRIMARY]
GO
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-12-06
12:20:48.577','2005-02-01 17:21:33.047',111)
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-03-26
12:20:48.577','2004-02-01 17:21:33.047',111)
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-12-10
12:20:48.577','2003-02-01 17:21:33.047',111)
insert into employees
values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
14:00:15.207','2004-04-21 13:20:20.273',111)
insert into employees
values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
14:00:15.207','2004-09-25 13:20:20.273',111)
--list the duplicates
select firstname,lastname,dob,count(*) as count
from employees e
group by firstname,lastname,dob
having count(*)>1
order by count(*) desc
--list the table
select * from employeesHi
Adding a status column...
CREATE TABLE Employees (
[EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (45) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dob] [datetime] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateLastUpdated] [datetime] NULL ,
[EmployerId] [int] NOT NULL,
[Status] CHAR(3) NULL
) ON [PRIMARY]
GO
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20041206 12:20:48.577','20050201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20040326 12:20:48.577','20040201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20041210 12:20:48.577','20030201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('Tom','Lan','19790507 00:00:00','20050322 14:00:15.207','20040421
13:20:20.273',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('Tom','Lan','19790507 00:00:00','20041022 14:00:15.207','20040925
13:20:20.273',111)
--list the duplicates
select firstname,lastname,dob,count(*) as count
from employees e
group by firstname,lastname,dob
having count(*)>1
order by count(*) desc
--list the table
UPDATE e
SET status = 'Old'
FROM employees e
WHERE EXISTS ( SELECT 1 FROM Employees p where p.firstname = e.firstname and
p.lastname = e.lastname and p.dob = e.dob and
e.[DateLastUpdated] < p.[DateLastUpdated] )
select * from employees
This is ignoring datecreated as it seems to be obsolete once it has been on
older record has been updated!
John
"Apok" wrote:

> Hi
> A little complex one
> What i really want is that my table contains duplicate
> firstname,lastname and dob.
> what I need to do is check the two fields datecreated and
> datelastupdated and retain the latest record based on
> date of these two columns and update the other records
> with OLD.
> so the result I want is retain row 1 as datelastupdated
> is latest for John Li as it is and update the 2 John Li
> as John (OLD) Li
> and also retain record 5 as datecreated is the latest so
> retain this one and update the other one as OLD.
> I have about 700 records to do and some are 2 duplicates
> and some 3.
> How can i write a query/cursor to solve this one.
> DDL and DML are posted
> CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-12-06
> 12:20:48.577','2005-02-01 17:21:33.047',111)
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-03-26
> 12:20:48.577','2004-02-01 17:21:33.047',111)
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-12-10
> 12:20:48.577','2003-02-01 17:21:33.047',111)
>
> insert into employees
> values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
> 14:00:15.207','2004-04-21 13:20:20.273',111)
>
> insert into employees
> values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
> 14:00:15.207','2004-09-25 13:20:20.273',111)
> --list the duplicates
> select firstname,lastname,dob,count(*) as count
> from employees e
> group by firstname,lastname,dob
> having count(*)>1
> order by count(*) desc
> --list the table
> select * from employees
>|||No But there is record where datecreated is the latest
one
your query does not cover that
I need to compare both the dates and find the latest one
leave that one and update the rest

>--Original Message--
>Hi
>Adding a status column...
>CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL,
> [Status] CHAR(3) NULL
> ) ON [PRIMARY]
>GO
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20041206
12:20:48.577','20050201
>17:21:33.047',111)
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20040326
12:20:48.577','20040201
>17:21:33.047',111)
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20041210
12:20:48.577','20030201
>17:21:33.047',111)
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('Tom','Lan','19790507 00:00:00','20050322
14:00:15.207','20040421
>13:20:20.273',111)
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('Tom','Lan','19790507 00:00:00','20041022
14:00:15.207','20040925
>13:20:20.273',111)
>--list the duplicates
>select firstname,lastname,dob,count(*) as count
>from employees e
>group by firstname,lastname,dob
>having count(*)>1
>order by count(*) desc
>--list the table
>UPDATE e
>SET status = 'Old'
>FROM employees e
>WHERE EXISTS ( SELECT 1 FROM Employees p where
p.firstname = e.firstname and
>p.lastname = e.lastname and p.dob = e.dob and
>e.[DateLastUpdated] < p.[DateLastUpdated] )
>select * from employees
>This is ignoring datecreated as it seems to be obsolete
once it has been on
>older record has been updated!
>John
>"Apok" wrote:
>
and
datelastupdated
Li
so
duplicates
>.
>|||Apok,
It's not clear what you mean by "latest record based on
date of these two columns." But here's a try:
update employees set
Status = 'Old'
where exists (
select * from employees as Ecopy
where Ecopy.FirstName = employees.FirstName
and Ecopy.LastName = employees.LastName
and (
(Ecopy.DateCreated > employees.DateCreated
and Ecopy.DateCreated > employees.DateLastUpdated)
or
(Ecopy.DateLastUpdated > employees.DateCreated
and Ecopy.DateLastUpdated > employees.DateLastUpdated)
)
)
Steve Kass
Drew University
Apok wrote:

>Hi
>A little complex one
>What i really want is that my table contains duplicate
>firstname,lastname and dob.
>what I need to do is check the two fields datecreated and
>datelastupdated and retain the latest record based on
>date of these two columns and update the other records
>with OLD.
>so the result I want is retain row 1 as datelastupdated
>is latest for John Li as it is and update the 2 John Li
>as John (OLD) Li
>and also retain record 5 as datecreated is the latest so
>retain this one and update the other one as OLD.
>I have about 700 records to do and some are 2 duplicates
>and some 3.
>How can i write a query/cursor to solve this one.
>DDL and DML are posted
>CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-12-06
>12:20:48.577','2005-02-01 17:21:33.047',111)
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-03-26
>12:20:48.577','2004-02-01 17:21:33.047',111)
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-12-10
>12:20:48.577','2003-02-01 17:21:33.047',111)
>
>insert into employees
>values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
>14:00:15.207','2004-04-21 13:20:20.273',111)
>
>insert into employees
>values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
>14:00:15.207','2004-09-25 13:20:20.273',111)
>--list the duplicates
>select firstname,lastname,dob,count(*) as count
>from employees e
>group by firstname,lastname,dob
>having count(*)>1
>order by count(*) desc
>--list the table
>select * from employees
>
>

complex 'query' using full text indexing

Hi people

i am having some difficulty with a quite advanced query (at least from my level).

select * from cities join areacodes on contains (city,'select area from areacodes right join cities on area not in (select city from cities)' )

Server: Msg 7631, Level 15, State 1, Line 1
Syntax error occurred near 'area'. Expected ''''' in search condition 'select area from areacodes right join cities on area not in (select city from cities)'.

what i am trying to do is find the areas in the postcodes table that contain words that are in the cities table. I need to match them up and modify the data in the postcodes table. the data is inconsistent as it comes from two unrelated sources. i have to be aware of spelling mistakes etc and have tried usind the soundex function to match them but it produces too many results. as you can see from the data in the postcodes table some areas (the second column) are not entirely upper case. these are the areas that have matched records in the cities table and the data is valid.

please can someone suggest the correct and easy way of doing this?

Thanks

Chris Morton

POSTCODES TABLE FIRST 30 ROWS

1

Aberdeen

49

3450

2

ABERDEEN FARM LINES

49212

3450

3

ABERFELDY

58652

3451

4

ACORNHOEK

13

3454

5

ACORNHOEK FARM LINES

137952

3454

6

Addo

42

3450

7

Adelaide

46

3450

8

Aggeneys

54

3456

9

AKASIA

12

3452

10

Albertinia

28

3458

11

Alberton

11

3452

12

ALETTASRUS

53922

3451

13

Alexander Bay

27

3456

14

Alexandria

46

3450

15

Alice

40

3450

16

ALICE FARM LINES

4049

3450

17

ALICEDALE

42

3450

18

Aliwal North

51

3450

19

Allanridge

57

3451

20

Alldays

15

3457

21

ALMA

14

3455

22

Amalia

53

3455

23

AMANDEBULT

14

3455

24

Amanzimtoti

31

3453

25

AMATIKULU

35

3453

26

Amersfoort

17

3454

27

Amsterdam

17

3454

28

ANERLEY

39

3453

29

APEL

15

3457

30

APEL FARM LINES

15482

3457

CITIES TABLE FIRST 30 ROWS

1

Aberdeen

3450

2

Addo

3450

3

Adelaide

3450

4

Alexandria

3450

5

Alice

3450

6

Aliwal North

3450

7

Balfour

3450

8

Barkly East

3450

9

Bathurst

3450

10

Bedford

3450

11

Bisho

3450

12

Burgersdorp

3450

13

Butterworth

3450

14

Cathcart

3450

15

Cintsa

3450

16

Coffee Bay

3450

17

Cookhouse

3450

18

Cradock

3450

19

Dordrecht

3450

20

East London

3450

21

Elliot

3450

22

Flagstaff

3450

23

Fort Beaufort

3450

24

Gonubie

3450

25

Graaff Reinet

3450

26

Grahamstown

3450

27

Haga-Haga

3450

28

Hamburg

3450

29

Hankey

3450

30

Herschel

3450

Hi Chris,

The only way I can think of that you can leverage CONTAINS/fulltext functionality in this case is unfortunately through a cursor - you'll need to fetch a row from the Cities table and run a CONTAINS query on your Postcodes table per every city you fetch.

If you have a requiement for matching large amounts of "fuzzy" data, you may want to check if Fuzzy Lookup Transform in SSIS could be useful.

Hope this helps.

Best regards,

complex query - need help

I have three tables-

MemberTable (primary key: memberID),
MemberDataTable (PK: DataID),
CompanyTable(PK: CompID)

The MemberTable contains name,address of members along with companyID (CompID)
The CompanyTable contains list of all comapny name along with CompID
The memberData table contains multiple rows for each memberID in memberTable.

I want to write a query which will give me the latest entry in memberDataTable for every member of a particular company. How do I write the query?

For eg, if I want compID=1, i need to get:
Select max(DataID) from memberdatatable where memberID=__
I would have to repeat the above for every member in the company with companyID=1

Any help in forming a proper query would be greatly appreciated

One way to do this is:

Code Snippet

declare @.MemberTable Table(memberID int,name varchar(100),address varchar(500),companyID int)
declare @.MemberDataTable table(DataID int,memberID int)
insert into @.MemberTable select 1,'name1','-',1
insert into @.MemberTable select 2,'name2','-',1
insert into @.MemberDataTable select 1,1
insert into @.MemberDataTable select 2,1
insert into @.MemberDataTable select 3,2
insert into @.MemberDataTable select 4,1
insert into @.MemberDataTable select 5,2

declare @.companyID int
set @.companyID=1

select max(DataID) DataID,memberID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID

|||What about:

SELECT MAX(DataId), CompId
From MemberDataTable
Group by CompId

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for the reply Mobin

but I still have a problem. I have more data in the memberdatatable which i need to retrieve along with the last row. When i add these data fileds: data1, data2, it gives me duplicate values of memberID.

declare @.MemberTable Table(memberID int,name varchar(100),address varchar(500),companyID int)
declare @.MemberDataTable table(DataID int,memberID int,data1 int, data2 int)
insert into @.MemberTable select 1,'name1','-',1
insert into @.MemberTable select 2,'name2','-',1
insert into @.MemberDataTable select 1,1,9,9
insert into @.MemberDataTable select 2,1,9,8
insert into @.MemberDataTable select 3,2,8,9
insert into @.MemberDataTable select 4,1,8,8
insert into @.MemberDataTable select 5,2,9,8

declare @.companyID int
set @.companyID=1

select max(DataID) DataID,memberID,data1,data2 from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID,data1,data2

Another thing i want to know is why does the query show an error when data1, data2 are fetched without being written in group by clause
|||Thanks Jens, but :
MemberDataTable doesn't have CompId.
|||I just modified what Mobin sent and this worked:

select * from @.memberdatatable where dataid in (select max(DataID) DataID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID)

Mobin, I'd really appreciate it if u could explain why the query showed an error when i tried to fetch data1, data2 without listing it in the groupby clause as in:
select max(DataID) DataID,memberID,data1,data2 from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID
|||

The following query may help you..

Code Snippet

Create Table #companytable (

[CompID] Varchar(100) ,

[name] Varchar(100)

);

Insert Into #companytable Values('1','Comp1');

Insert Into #companytable Values('2','Comp2');

Insert Into #companytable Values('3','Comp3');

Insert Into #companytable Values('4','Comp4');

Insert Into #companytable Values('5','Comp5');

Create Table #membertable (

[memberID] Varchar(100) ,

[name] Varchar(100) ,

[address] Varchar(100) ,

[companyID] Varchar(100)

);

Insert Into #membertable Values('1','Mem1','Address1','1');

Insert Into #membertable Values('2','Mem2','Address2','3');

Insert Into #membertable Values('3','Mem3','Address3','2');

Insert Into #membertable Values('4','Mem4','Address4','2');

Insert Into #membertable Values('5','Mem5','Address5','1');

Insert Into #membertable Values('6','Mem6','Address6','2');

Insert Into #membertable Values('7','Mem7','Address7','2');

Insert Into #membertable Values('8','Mem8','Address8','3');

Create Table #memberdatatable (

[memberID] Varchar(100)

);

Insert Into #memberdatatable Values('1');

Insert Into #memberdatatable Values('4');

Insert Into #memberdatatable Values('5');

Insert Into #memberdatatable Values('8');

Select * From #membertable Main

Join #companytable Comp On Comp.CompID= Main.companyID

Join (Select Max(MD.memberID) memberID From #memberdatatable MD

Join #membertable MT On MD.memberID = MT.memberID Group BY companyID) as Data

On data.memberID=Main.memberID

|||

hi

error is caused since the fields data1 and data2 are not mentioned in the group by clause. the following query will fix the problem:

Code Snippet

select * from @.MemberDataTable where DataID in(
select max(DataID) DataID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyIDmailto:companyID=@.companyID">companyID=@.companyID</A< A>> ) group by memberID)

regards

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.

Comparing values between 2 matrices (matrix)

Hello
I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets

I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.

Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".

Any suggestions are appreciated.
Thanks

I would like to know the official answer to this too...

see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673719&SiteID=1 for simular question...