Getting a parameter to depend on another parameter.

I'm putting together a crosstab showing some facts for a year, compared to the year before (so we're showing 2 years). Years in the columns and a dimension in the rows. Nothing fancy, but we do want to put in a parameter so the users can choose which year they want to see. So I put a range and get two parameters. We now want the first parameter, the "from", to take the value of the "to" - 1. I can get the same value in there, but appearantly getting the previous year isn't as simple as just taking the parameter - 1.
A helping hand would be great. This all seems a bit overkill for what to me feels like an easy report, so I feel like I'm missing something.

We're building this report on a cube.

Hi,

In the Report Parameters dialog, set the Default value of your second parameter to Non Queried and enter the following in the Expression Editor

Code Snippet

=CINT(Parameters!<Param1>.Value) - 1

Replace with the name of your parameter and this should achieve what you want to do.

HTH.

Cheers,

Leigh

|||That gives me an error while previewing. More precisely, when I select the "to" in preview, I get:

Code Snippet

An error occured during local report processing.
Error during processing of of "FromYear" report parameter.

Can it be because the first (to) depends on a time dimension?

Getting 0 padded values in the columns.

Getting 0 padded values in the columns.

Hi All,

I have a requirement to convert a integer to string and display it in
Sql server with fixed length say 3 chars. (in c, we wud use %03d in
printf)

If the number is small say, 9 then it has to be displayed as 009,
56 -> 056, 897-> 897, 6786 -> xxx

Checked through STR and CAST functions, couldn't find any relevant
paramters.

if you have any ideas, please mail me.

Thanks & Regards,
Chandra MohanDo:

SELECT CASE WHEN LEN(@.n) <= 3
THEN RIGHT('000' + CAST(@.n AS VARCHAR), 3)
ELSE 'xxx'
END ;

--
- Anith
( Please reply to newsgroups only )|||bschandramohan@.yahoo.com (Chandra Mohan) wrote in message news:<bb0ef6.0308200036.236c3321@.posting.google.com>...
> Getting 0 padded values in the columns.
> Hi All,
> I have a requirement to convert a integer to string and display it in
> Sql server with fixed length say 3 chars. (in c, we wud use %03d in
> printf)
> If the number is small say, 9 then it has to be displayed as 009,
> 56 -> 056, 897-> 897, 6786 -> xxx
> Checked through STR and CAST functions, couldn't find any relevant
> paramters.
> if you have any ideas, please mail me.
> Thanks & Regards,
> Chandra Mohan

Hi ,

You could use this :

select replicate('0', 3-datalength(cast(column as varchar(10)))) +
cast (column as varchar(10)) from table

Replace the column and table with the right values and here the
assumption is the column is of int datatype.

Regards,
-Manoj Rajshekar

Getting "infinity" when running this expression

When I run this expression, percentage, I'm still getting infinity as my
return when I have a zero in one of the columns
=IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW())-1,
Fields!APR_CNT.Value,0)) = 0, 1, SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
= DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
Any ideas where I'm screwing up... Thanks in advanceThe IIF is a VB function. All function arguments are evaluated immediately
before the function is called. So this will fail: IIF(1 = 1, 1/1, 1/0) even
though it seems like 1/0 should not be evaluated because the condition (1=1)
is true.
This is what most likely happening in your expression.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:C3944D44-31B9-449C-ABC3-DFF6036BC46D@.microsoft.com...
> When I run this expression, percentage, I'm still getting infinity as my
> return when I have a zero in one of the columns
> =IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))=0, 0,
> SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) = DATEPART("yyyy",NOW()),
> Fields!APR_CNT.Value,0))-SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) => DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0))) /
> IIF(SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value) =DATEPART("yyyy",NOW())-1,
> Fields!APR_CNT.Value,0)) = 0, 1,
SUM(IIF(DATEPART("yyyy",Fields!APR_DT.Value)
> = DATEPART("yyyy",NOW())-1, Fields!APR_CNT.Value,0)))
>
> Any ideas where I'm screwing up... Thanks in advance

getting left _

how can I can update a table ::
I must get all the left part of a columns part1_part2
I dont know how many characters

something like :

UPDATE Users SET Users.Info = left(Users.name, _ ? or split('_'))

in that case Users.name = part1

i must update only the one with a '_' and do nothing for the others

thank youTry variations of these to get what you want:
declare @.Name varchar(50)
set @.Name = 'Part1Part2'
select left(@.Name, charindex('_', @.Name + '_')-1)
select substring(@.Name, charindex('_', @.Name + '_')+1, 50)
...or if the names are more complex (such as including middle names, initials, or titles), I have a name splitting function I can give you.|||thank you BlindMan

but the name of the column is Users.Name and part1_part2 was an exemple of value
in that exemple i must get part1

it could be werfewgf fff jjj_rgfregreg
and in that case i must get werfewgf fff jjj

allways the left part of '_'|||with select substring(Users.Name, charindex('_', Users.Name + '_')+1, 50) FROM Users

I get exactly the right part

the left will be great :-)

but I get all even if there is no '_'|||Can you supply some sample data and what the expect result is suppose to be?

My esp usb port is clogged|||sample data >> expect result

wefwefqrwf_hhhh >> wefwefqrwf
fff jj ff_rgdefrhbg >> fff jj ff
acacac145 i4_kk >> acacac145 i4

dddd >> no result, I dont take it there is no _ in the sample data

thanks Brett

getting in textbox while retrieving

hi,
I am using sql server database. In my table i am having three columns namely Name, age and salary. I am having three textboxes in my webform to retrieve data from the database.The column salary has no value in the database. When i retrieve the values in to textbox controls, i am getting in my textbox for the salary field.

How to overcome this.?

pls help

Are you HTML Encoding the values that appear in the TextBox?|||

(1) It is a good idea to default 0 in the column.(2) You could do a convert to double in the front end.

If you still have problems please post some code as to how you are populating the textbox.

Getstring question

how can i add two columns in each row (both strings) in a table only using getstring?

i'm getting an error from this statement... thanks

dim row as string

while dr.read()

problem with this statment

--->> row = dr.getstring(1) + dr.getstring(2)

end whileExactly what is the error message?|||the error message is

Index was outside the bounds of the array.|||Index was outside the bounds of the array.|||Can you verify that your query is in fact returning 2 fields?|||select lastname,firstname from employees

i'm using the northwind db in sql server 2000|||I am not familiar with VB.NET syntax, but in C#, GetString() is zero based, so you'd have:

row = dr.getstring(0) + dr.getstring(1)

Not sure if its the same in VB.NET, but you might want to give that a try.

get the ID value from MAX value of a set of columns?

Hi to everybody
I'm having some trouble while trying to solve this issue.
I have this table:
DAY HOUR MINUTE
ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
103 897544 750916 26 17 04
165 897544 750916 25 17 13
203 897544 750916 25 17 02
514 334692 264553 15 03 46
517 334692 264553 16 03 43
1060 334692 264553 16 10 01
I need to group the rows with same FIX_VALUES and, from each group, get
the ID of the row with maximum set of VARIABLE_VALUES
Actually,
VARIABLE_VALUE_C = DAY
VARIABLE_VALUE_D = HOUR
VARIABLE_VALUE_E = MINUTE
and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
I need to get the ID of the row with the highest set of
(DAY,HOUR,MINUTE)

>From the previous example, the expected results should be ID=103 and
ID=1060
Could anybody help me?
TIA,
David Grantselect ID, FIX_VALUE_A, FIX_VALUE_B
from T as T1
where not exists (
select * from T as T2
where T2.TIMESTAMP_FIELD > T1.TIMESTAMP_FIELD
)
If the TIMESTAMP_FIELD values aren't ordered correctly, you
can try
...
where not exists (
select * from T as T2
where T2.VARIABLE_C + T2.VARIABLE_D + T2.VARIABLE_E
> T1.VARIABLE_C + T1.VARIABLE_D + T1.VARIABLE_E
)
Without seeing the details, I'm doing some guessing about types, but
hopefully this will be helpful.
Steve Kass
Drew University
icebold54@.hotmail.com wrote:

>Hi to everybody
>I'm having some trouble while trying to solve this issue.
>I have this table:
> DAY HOUR MINUTE
>ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
>103 897544 750916 26 17 04
>165 897544 750916 25 17 13
>203 897544 750916 25 17 02
>514 334692 264553 15 03 46
>517 334692 264553 16 03 43
>1060 334692 264553 16 10 01
>
>I need to group the rows with same FIX_VALUES and, from each group, get
>the ID of the row with maximum set of VARIABLE_VALUES
>Actually,
>VARIABLE_VALUE_C = DAY
>VARIABLE_VALUE_D = HOUR
>VARIABLE_VALUE_E = MINUTE
>and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
>I need to get the ID of the row with the highest set of
>(DAY,HOUR,MINUTE)
>
>ID=1060
>
>Could anybody help me?
>TIA,
>David Grant
>
>|||>> I need to get the ID of the row with the highest set of (DAY,HOUR,MINUTE)
One simple approach is:
SELECT *
FROM tbl t1
WHERE CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) =
( SELECT MAX( CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) )
FROM tbl t2
WHERE t2.fix_col1 = t1.fix_col2
AND t2.fix_col2 = t2.fix_col2 ) ;
Have you considered representing the value in a single DATETIME column?
Anith|||Try,
select
FIX_VALUE_A,
FIX_VALUE_B,
[id]
from
t1 as a
where
((((VARIABLE_C * 100) + VARIABLE_D) * 100) + VARIABLE_E) = (select
max((((b.VARIABLE_C * 100) + b.VARIABLE_D) * 100) + b.VARIABLE_E) from t1 as
b where b.FIX_VALUE_A = a.FIX_VALUE_A and b.FIX_VALUE_B = a.FIX_VALUE_B)
go
AMB
"icebold54@.hotmail.com" wrote:

> Hi to everybody
> I'm having some trouble while trying to solve this issue.
> I have this table:
> DAY HOUR MINUTE
> ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
> 103 897544 750916 26 17 04
> 165 897544 750916 25 17 13
> 203 897544 750916 25 17 02
> 514 334692 264553 15 03 46
> 517 334692 264553 16 03 43
> 1060 334692 264553 16 10 01
>
> I need to group the rows with same FIX_VALUES and, from each group, get
> the ID of the row with maximum set of VARIABLE_VALUES
> Actually,
> VARIABLE_VALUE_C = DAY
> VARIABLE_VALUE_D = HOUR
> VARIABLE_VALUE_E = MINUTE
> and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
> I need to get the ID of the row with the highest set of
> (DAY,HOUR,MINUTE)
>
> ID=1060
>
> Could anybody help me?
> TIA,
> David Grant
>|||Thank your for your interest, Steve.
The table has this aspect:
ID FIXA FIXB FIXC FIXD VARE VARF VARG VARH VARI VAR J
107 36902 7187815 2004 8 2004 09 23 14 06 50
104 36902 7187815 2004 8 2004 09 15 17 06 50
101 36902 7187815 2004 8 2004 09 15 14 07 50
14 36902 7187815 2004 8 2004 09 15 14 06 50
98 75184 4066402 2004 8 2004 11 05 17 17 43
105 75184 4066402 2004 8 2004 10 05 17 17 43
102 75184 4066402 2004 8 2004 10 04 18 17 43
97 75184 4066402 2004 8 2004 10 04 17 19 50
15 75184 4066402 2004 8 2004 10 04 17 17 43
99 91006 9131800 2004 8 2004 10 24 11 40 04
106 91006 9131800 2004 8 2004 09 24 11 40 04
103 91006 9131800 2004 8 2004 09 23 12 40 04
100 91006 9131800 2004 8 2004 09 23 11 43 14
16 91006 9131800 2004 8 2004 09 23 11 40 04
where the FIX* are the FIXED_VALUES and thee VAR* are the
VARIABLE_VALUES that come from a previous
SUBSTRING(TIMESTAMP_FIELD,X,Y). In this table there's no timestamp
anymore.
The VARIABLE_VALUES stand for
VARE=YEAR
VARF=MONTH
VARG=DAY
VARH=HOUR
VARI=MINUTE
VARJ=SECOND
The self-join you proposed only got one value and unfortunately it
wasn't none from the expected three.
Do you have any idea about what could I do now?
TIA,
David Grant|||Should be :
...
WHERE CAST( t1.day AS CHAR( 10 ) +
CAST( t1.hour AS CHAR( 10 ) +
CAST( t1.minute AS CHAR( 10 ) =
( SELECT MAX( CAST( t1.day AS CHAR( 10 ) +
CAST( t1.hour AS CHAR( 10 ) +
CAST( t1.minute AS CHAR( 10 ) )
...
Anith|||Sorry Anith, but I must be doing something wrong with your solution
because the Query Analyzer is complaining about it. (Something like:
'Multiple columns have been specified in an aggregation expression that
contains an extern reference. If an aggregated expression contains an
extern reference, this must be the only column to be referred in the
expression')
Se han especificado m=FAltiples columnas en una expresi=F3n de agregado
que contiene una referencia externa. Si una expresi=F3n agregada
contiene una referencia externa, =E9sta deber=E1 ser la =FAnica columna a
la que se haga referencia en la expresi=F3n.
SELECT *
FROM REPEATDETECT t1
WHERE CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)) =3D
(SELECT MAX(CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)))
FROM REPEATDETECT t2
WHERE t2.FIX_VALUE_A =3D t1.FIX_VALUE_A
AND t2.FIX_VALUE_B =3D t2.FIX_VALUE_B
AND t2.FIX_VALUE_C =3D t2.FIX_VALUE_C
AND t2.FIX_VALUE_D =3D t2.FIX_VALUE_D)
Do you know what am I doing wrong?
TIA,
David Grant|||Alejandro,
Your solution is working good. Could you please be so kind to explain
me why does it work? I know you are doing a self-join and using the 2nd
'WHERE' clause to stablish a relationship between the two copies of the
table. However, at this moment I can't guess which is the objective
(apart from solving my problem, thankyou again) of all these
multiplications by 100.
TIA,
David Grant|||> SELECT *
> FROM REPEATDETECT t1
> WHERE CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)) =
> (SELECT MAX(CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)))
> FROM REPEATDETECT t2
> WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
> AND t2.FIX_VALUE_B = t2.FIX_VALUE_B
> AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
> AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
Use t2 instead t1 in the aggregation function.
SELECT *
FROM REPEATDETECT t1
WHERE CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)) =
(SELECT MAX(CAST(t2.day AS CHAR(10)) +
CAST(t2.hour AS CHAR(10)) +
CAST(t2.minute AS CHAR(10)))
FROM REPEATDETECT t2
WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
AND t2.FIX_VALUE_B = t1.FIX_VALUE_B
AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
AMB
"icebold54@.hotmail.com" wrote:

> Sorry Anith, but I must be doing something wrong with your solution
> because the Query Analyzer is complaining about it. (Something like:
> 'Multiple columns have been specified in an aggregation expression that
> contains an extern reference. If an aggregated expression contains an
> extern reference, this must be the only column to be referred in the
> expression')
> Se han especificado múltiples columnas en una expresión de agregado
> que contiene una referencia externa. Si una expresión agregada
> contiene una referencia externa, ésta deberá ser la única columna a
> la que se haga referencia en la expresión.
>
> SELECT *
> FROM REPEATDETECT t1
> WHERE CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)) =
> (SELECT MAX(CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)))
> FROM REPEATDETECT t2
> WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
> AND t2.FIX_VALUE_B = t2.FIX_VALUE_B
> AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
> AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
>
> Do you know what am I doing wrong?
> TIA,
> David Grant
>|||Create an int number with the three values that we can use to compare,
instead comparing them individualy. Let see an example.
var_c = 26
var_d = 17
var_e = 04
((((var_c * 100) + var_d) * 100) + var_e) = 261704
26 * 100 = 2600
2600 + 17 = 2617
2617 * 100 = 261700
261700 + 04 = 261704
var_c = 25
var_d = 17
var_e = 13
((((var_c * 100) + var_d) * 100) + var_e) = 251713
now we can compare 261704 to 251713.
After reading your post to Steve, I would recommend to create a view that
recreate the datetime value, or a calculated column in the same table.
create view dbo.v1
as
select
[ID],
FIXA,
FIXB,
FIXC,
FIXD,
cast(VARE + '-' + VARF + '-' + VARG + 'T' + VARH + ':' + VARI + ':' + VARJ
+ '.000' as datetime) as dt_col
from
dbo.t1
go
Now you can use dt_col to do the comparison.
create view dbo.v1
as
select
[ID],
FIXA,
FIXB,
FIXC,
FIXD,
cast(VARE + '-' + VARF + '-' + VARG + 'T' + VARH + ':' + VARI + ':' + VARJ
+ '.000' as datetime) as dt_col
from
dbo.t1
go
select
FIXA,
FIXB,
FIXC,
FIXD,
[ID]
from
dbo.v1 as a
where
a.dt_col = (select max(b.dt_col) from dbo.v1 where b.FIXA = a.FIXA and
b.FIXB = a.FIXB and b.FIXC = a.FIXC and b.FIXD = a.FIXD)
-- or
select
a.FIXA,
a.FIXB,
a.FIXC,
a.FIXD,
a.[ID]
from
dbo.v1 as a
inner join
(
select
FIXA,
FIXB,
FIXC,
FIXD,
max(dt_col) as max_dt_col
from
dbo.v1
group by
FIXA,
FIXB,
FIXC,
FIXD
) as b
on b.FIXA = a.FIXA and b.FIXB = a.FIXB and b.FIXC = a.FIXC and b.FIXD =
a.FIXD and b.max_dt_col = a.dt_col
go
AMB
"icebold54@.hotmail.com" wrote:

> Alejandro,
> Your solution is working good. Could you please be so kind to explain
> me why does it work? I know you are doing a self-join and using the 2nd
> 'WHERE' clause to stablish a relationship between the two copies of the
> table. However, at this moment I can't guess which is the objective
> (apart from solving my problem, thankyou again) of all these
> multiplications by 100.
>
> TIA,
> David Grant
>

Get rows with duplicate values in certain columns

Hi there,

I would like to know how to get rows with duplicate values in certain
columns. Let's say I have a table called "Songs" with the following
columns:

artist
album
title
genre
track

Now I would like to show the duplicate songs to the user. I consider
songs that have the same artist and the same title to be the same song.
Note: All columns do not have to be the same.

How would I accomplish that with SQL in SQL Server?

Thanks to everyone reading this. I hope somebody has an answer. I've
already searched the whole newsgroups, but couldn't find the solution.<agekay@.gmx.de> wrote in message
news:1120392807.001826.295060@.g49g2000cwa.googlegr oups.com...
> Hi there,
> I would like to know how to get rows with duplicate values in certain
> columns. Let's say I have a table called "Songs" with the following
> columns:
> artist
> album
> title
> genre
> track
> Now I would like to show the duplicate songs to the user. I consider
> songs that have the same artist and the same title to be the same song.
> Note: All columns do not have to be the same.
> How would I accomplish that with SQL in SQL Server?
> Thanks to everyone reading this. I hope somebody has an answer. I've
> already searched the whole newsgroups, but couldn't find the solution.

You probably need something like this:

select
s.artist,
s.album,
s.title,
s.genre,
s.track
from
dbo.Songs s
join
(
select
artist,
title
from
dbo.Songs
group by
artist,
title
having count(*) > 1
) dt
on s.artist = dt.artist and
s.title = dt.title

If this doesn't give the results you expect, then you should post some DDL
and sample data to clarify exactly what you need:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Thank you so much! That's exactly what I was looking for. Works like a
charm!

Get rows for latest date

Hello!

I have a table something like this:

ID INTEGER

Info VARCHAR (actually several columns but that is not important here)

DAT DateTime

For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:

ID - DAT - Info

1 - 2007-02-01 - Info1

1 - 2007-02-01 - Info2

1 - 2006-02-01 - Info3

2 - 2007-05-05 - Info4

2 - 2007-02-01 - Info5

2 - 2006-02-01 - Info6

I would like to get:

Info1

Info2

Info4

This has to be done in one Query. Can anybody help me?

Here it is,

Code Block

Create Table #data (

[ID] int ,

[DAT] datetime ,

[Info] Varchar(100)

);

Insert Into #data Values('1','2007-02-01','Info1');

Insert Into #data Values('1','2007-02-01','Info2');

Insert Into #data Values('1','2006-02-01','Info3');

Insert Into #data Values('2','2007-05-05','Info4');

Insert Into #data Values('2','2007-02-01','Info5');

Insert Into #data Values('2','2006-02-01','Info6');

select main.info from #data main

join (select ID,max(dat) dat from #data group by ID) as latest

on latest.ID=main.ID and latest.dat=main.dat

|||

A couple of options:

Code Block

create table testdata

(ID int, Dat DATETIME, Nm CHAR(5))

INSERT INTO testdata

SELECT 1, '1 feb 2007', 'Info1'

UNION ALL

SELECT 1, '1 feb 2007', 'Info2'

UNION ALL

SELECT 1, '1 feb 2006', 'Info3'

UNION ALL

SELECT 2, '5 may 2007', 'Info4'

UNION ALL

SELECT 2, '1 feb 2007', 'Info5'

UNION ALL

SELECT 2, '1 feb 2006', 'Info6'

--SQL2005

WITH cte

AS

(SELECT ID, Nm, RANK() OVER (PARTITION BY ID ORDER BY Dat DESC) AS D

FROM testData)

SELECT Nm

FROM cte

WHERE D = 1

--SQL2000

SELECT Nm

FROM

(SELECT ID, MAX(Dat) AS Dt

FROM testData

GROUP BY ID) AS Bob

INNER JOIN testData t

ON Bob.Dt = t.Dat AND Bob.ID = t.ID

HTH!|||

One more trick..

Code Block

--SQL2005

;with cte

as

(select id, nm,dat,max(dat) over (partition by id) as latestdat from testdata)

select nm

from cte

where dat = latestdat

Code Block

--SQL Server 2000

select

main.info

from

#data main

where

exists

(

select * from

(

select

d

,max(dat) dat

from

#data

group by D

) data

where

data.d = main.d

and data.dat=main.dat

)

|||

Thank You guys!!

...for the fast and helpful response. I would never figure that out.

Get Primary Key Column

Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards

Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||

Well no, I am running this script against the IssueTrackerStarterKit database. I am getting that table for sure, listing al columns, but I would like to know if I can know the primary key programmatically !!

regards

|||Run a search for sp_helpindex in SQL Server BOL(books online). And your select statement is using SQL- DMO(data management object) which is Microsoft property and all service packs makes changes which will make you code out dated because the tables have moved. Go to Query Analyzer open the object browser and right click on your table and you will have options of select statements without Syscolumns and Sysobjects. Hope this helps.|||Hi,
You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below

declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid

I hope this helps
Eralper
http://www.kodyaz.com

|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||

The Systems tables have a poster but since SQL Server 2005 is almost here Microsoft have removed it and have made others to remove it. But they are all in the SQL Server Master database with the System title next to them. A good book for starting SQL Server for a developer is SQL Server a beginner's guide by Dusan Petkovic but don't let the title fool you it is not really a beginner's book it was given that title because English is not the writer's first language, he is German. The book covers everything usefull to a developer including complex configurations and the language, he also covered XML and Full text that most other books did not cover. I have a lot of them I call crappy but his book is worth the money for a C# developer, while SQL Server Developer's guide is good for VB developer. Try this link for T-SQL tutorial but I have sent you an ANSI SQL tutorial in the mail. Dowload the file because the site is now part of a consolidator so that file may be removed soon. Hope this helps.

http://www.mssqlserver.com/tsql/

|||

The Systems tables have a poster but since SQL Server 2005 is almosthere Microsoft have removed it and have made others to remove it. Butthey are all in the SQL Server Master database with the System titlenext to them.

Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.

Get Nth column in a table

HI,

Can any one please help me to solve this....

if we have N number of columns in a table.I need to get 4th column of that table directly.

Thanks
SRI

Use the following query...

Code Snippet

--If you want to Fetch the nth column name (only name)

Declare @.TableName as nVarchar(100);

Declare @.NthCol as Int

Select

@.TableName =N'Sysobjects',

@.NthCol=2

select Col_name(object_id(@.TableName),@.NthCol) ColumnName

Code Snippet

--If you want to select Record for given Column (with data)

Declare @.TableName as nVarchar(100);

Declare @.NthCol as Int

Select

@.TableName =N'Sysobjects',

@.NthCol=2

Declare @.ColName as varchar(100);

select @.ColName = Col_name(object_id(@.TableName),@.NthCol)

Exec ('Select ' + @.ColName + ' From ' + @.TableName)

|||Thanks..a lot.....i execute query and it works

Get Next Number

I have a table called e_next_number that holds a variety of different IDs
currently in use. The columns are NumberType (nchar) and ID (int). My
question is, what is the best way to insure a inquire number is returned,
currently my stored procedure looks like this:
CREATE PROCEDURE _GetNextNumber
@.NumberType nchar(10),
@.NextNumber int OUTPUT
AS
SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype = @.NumberType
UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
return
TIADisregard, I posted in the wrong group. Sorry.
"MikeB" <m@.nospam.com> wrote in message
news:uj6gBrHrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I have a table called e_next_number that holds a variety of different IDs
>currently in use. The columns are NumberType (nchar) and ID (int). My
>question is, what is the best way to insure a inquire number is returned,
>currently my stored procedure looks like this:
> CREATE PROCEDURE _GetNextNumber
> @.NumberType nchar(10),
> @.NextNumber int OUTPUT
> AS
> SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype => @.NumberType
> UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
> return
> TIA
>
>

Get Next Number

I have a table called e_next_number that holds a variety of different IDs
currently in use. The columns are NumberType (nchar) and ID (int). My
question is, what is the best way to insure a inquire number is returned,
currently my stored procedure looks like this:
CREATE PROCEDURE _GetNextNumber
@.NumberType nchar(10),
@.NextNumber int OUTPUT
AS
SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
@.NumberType
UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
return
TIA
Disregard, I posted in the wrong group. Sorry.
"MikeB" <m@.nospam.com> wrote in message
news:uj6gBrHrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I have a table called e_next_number that holds a variety of different IDs
>currently in use. The columns are NumberType (nchar) and ID (int). My
>question is, what is the best way to insure a inquire number is returned,
>currently my stored procedure looks like this:
> CREATE PROCEDURE _GetNextNumber
> @.NumberType nchar(10),
> @.NextNumber int OUTPUT
> AS
> SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
> @.NumberType
> UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
> return
> TIA
>
>

Get Next Number

I have a table called e_next_number that holds a variety of different IDs
currently in use. The columns are NumberType (nchar) and ID (int). My
question is, what is the best way to insure a inquire number is returned,
currently my stored procedure looks like this:
CREATE PROCEDURE _GetNextNumber
@.NumberType nchar(10),
@.NextNumber int OUTPUT
AS
SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
@.NumberType
UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
return
TIADisregard, I posted in the wrong group. Sorry.
"MikeB" <m@.nospam.com> wrote in message
news:uj6gBrHrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I have a table called e_next_number that holds a variety of different IDs
>currently in use. The columns are NumberType (nchar) and ID (int). My
>question is, what is the best way to insure a inquire number is returned,
>currently my stored procedure looks like this:
> CREATE PROCEDURE _GetNextNumber
> @.NumberType nchar(10),
> @.NextNumber int OUTPUT
> AS
> SELECT @.NextNumber = ID + 1 FROM e_next_number WHERE numbertype =
> @.NumberType
> UPDATE e_next_number SET ID = @.NextNumber WHERE numbertype = @.NumberType
> return
> TIA
>
>

Get max-value of columns

Hi
Is there an easy way to get the MaxVal of some Columns
SELECT ID, MaxVal( Value1, Value2, Value3, ... , Value13) AS BestBet
FROM ...
With only 2 columns I could just compare the values an return the max
but with an increasing number of colums ...
any ideas
thx PeterYour requirement indicates to me that this table isn't in correctly
normalized. It rarely makes sense to take a maximum accross *different*
attributes in a table (dates are sometimes an exception). The fact that you
want to do so suggests that the columns Value1 .. Value13 may be a repeating
group (the *same* attribute in different columns). That's always a weak
design for a table so a better solution may be to redesign it. If that isn't
the case then here's one query to get the maximum across columns:
SELECT id,
(SELECT MAX(val)
FROM
(SELECT value1 AS val UNION ALL
SELECT value2 UNION ALL
SELECT value3
...
) AS X) bestbet
FROM YourTable
or, if you need to handle NULLs by returning BestBet as NULL when any value
is NULL:
SELECT id,
(SELECT MAX(val)
FROM
(SELECT value1 AS val UNION ALL
SELECT value2 UNION ALL
SELECT value3
) AS X
HAVING COUNT(*)=COUNT(val)) bestbet
FROM YourTable
--
David Portas
SQL Server MVP
--|||One approach would be to create a scalar function. You need to define the
maximum number of values supported for comparison as input paramters, but it
would be flexible. An issue is that you always have to pass in all
parameters to the function, so a null placeholder is needed.
CREATE FUNCTION Greatest (
@.p1 int=0
, @.p2 int=0
, @.p3 int=0
, @.p4 int=0)
RETURNS int
AS
BEGIN
declare @.t1 table (c1 int), @.r1 int
insert @.t1
select @.p1
union all
select @.p2
union all
select @.p3
union all
select @.p4
select @.r1 = max(c1) from @.t1
RETURN @.r1
END
GO
SELECT dbo.Greatest
(1,
2,
null,
null)
Regards,
Carl
"Peter Plumber" <Klempner@.gmxdot.net> wrote in message
news:%23RSe6MUqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there an easy way to get the MaxVal of some Columns
> SELECT ID, MaxVal( Value1, Value2, Value3, ... , Value13) AS BestBet
> FROM ...
> With only 2 columns I could just compare the values an return the max
> but with an increasing number of colums ...
> any ideas
> thx Peter
>

Get max-value of columns

Hi
Is there an easy way to get the MaxVal of some Columns
SELECT ID, MaxVal( Value1, Value2, Value3, ... , Value13) AS BestBet
FROM ...
With only 2 columns I could just compare the values an return the max
but with an increasing number of colums ...
any ideas
thx Peter
Your requirement indicates to me that this table isn't in correctly
normalized. It rarely makes sense to take a maximum accross *different*
attributes in a table (dates are sometimes an exception). The fact that you
want to do so suggests that the columns Value1 .. Value13 may be a repeating
group (the *same* attribute in different columns). That's always a weak
design for a table so a better solution may be to redesign it. If that isn't
the case then here's one query to get the maximum across columns:
SELECT id,
(SELECT MAX(val)
FROM
(SELECT value1 AS val UNION ALL
SELECT value2 UNION ALL
SELECT value3
...
) AS X) bestbet
FROM YourTable
or, if you need to handle NULLs by returning BestBet as NULL when any value
is NULL:
SELECT id,
(SELECT MAX(val)
FROM
(SELECT value1 AS val UNION ALL
SELECT value2 UNION ALL
SELECT value3
) AS X
HAVING COUNT(*)=COUNT(val)) bestbet
FROM YourTable
David Portas
SQL Server MVP
|||One approach would be to create a scalar function. You need to define the
maximum number of values supported for comparison as input paramters, but it
would be flexible. An issue is that you always have to pass in all
parameters to the function, so a null placeholder is needed.
CREATE FUNCTION Greatest (
@.p1 int=0
, @.p2 int=0
, @.p3 int=0
, @.p4 int=0)
RETURNS int
AS
BEGIN
declare @.t1 table (c1 int), @.r1 int
insert @.t1
select @.p1
union all
select @.p2
union all
select @.p3
union all
select @.p4
select @.r1 = max(c1) from @.t1
RETURN @.r1
END
GO
SELECT dbo.Greatest
(1,
2,
null,
null)
Regards,
Carl
"Peter Plumber" <Klempner@.gmxdot.net> wrote in message
news:%23RSe6MUqEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there an easy way to get the MaxVal of some Columns
> SELECT ID, MaxVal( Value1, Value2, Value3, ... , Value13) AS BestBet
> FROM ...
> With only 2 columns I could just compare the values an return the max
> but with an increasing number of colums ...
> any ideas
> thx Peter
>