Choosing field from same row based on an aggregate.

I have a table with two columns. Let's call them Value and Hour. Looks like this:

Value Hour

4 9:00

3 11:00

6 2:00

2 12:00

I want to be able to make a total line and give the Max(Value) and the time it happened. What would be the function to get the Hour value based on the Max(Value). Just for example, for this one it would be Max(Value) = 6 and it's Hour would be 2:00.

Hi,

what about getting this right back from the database system with a query ?

SELECT Hour,MAX(Value)
From SomeTable
Group by Hour

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I'm actually getting my data from a Custom Data Processing extension. I would need a way to get it with a function straight out of the resultant dataset.|||

Can't you make a calculated field with the expression = Max(Value)?

That can be put in the footer of the table (which will display '6' in this example). To get the hour belonging to the value with an expression ...I'm not so sure how to do that ... maybe with a switch expression, based on the textbox where you show the MAX(Value)?

Choosing explicit cell in Excel to SQL Database

Hi, is there a way to choose a specific Excel cell and put the value into a specifc column in a SQL table using the wizard? For example, when the package is run, to have cell B3's value placed into a newly created row in column 0 and have F6's value placed into the same row in column 1?

Thanks for your time,

James

EDIT: What happens currently is I'll map a particular Excel column and it will create almost 200 rows in my table with mostly null values, as that is the format of the Excel sheet. I would like to create 1 row per Excel sheet, with selected information input. Thanks!

It is unclear.

With the explanation you gave, I would recommend to look into ways to reformat your Excel input so you can use either PIVOT or UNPIVOT to load this into your database.

You will still have to deal with the potential lack of heterogeneity of Excel.

What if the user give you smashed potatoes?

Regards,

Philippe

|||

Hi, thanks for the response. The Excel sheet is template based - every Excel sheet put into the database has the exact same format.

What I'm looking to do is this:

I would like to take the value from one particular cell from the Excel sheet (say, B5 for example) and insert it into a particular spot into a new row of a SQL database table. So, in all, I want to create a new row in the table, put B5 into the first column of this row, E9 into the second column of this row, and F13 into the third column of this row. Does this make sense?

Thanks,

James

|||

It makes sense. A way would be to use Automation to trigger a macro in the workbook from the ssis package, this macro would put B5, F9 and F13 in one row on another spreadsheet and the package would load this spreadsheet rather than the original.

You could also create another spreadsheet with indirect references to the master sheet. That would work only if the cells to "normalize" have a constant position.

The question I am asking myself is "How do you know that B5, F9 and F13 have to be in Col1 Col2 Col3 Is it set in stone? "

May be you could Name these ranges, then load several times by refering these names, the first pass would create the row and insert Name1, the second and third pass would only update the row with Name2 and Name3.

I guess I prefer the last approach.

Philippe

|||

James, you can use DataDefractor SSIS source data flow component to extract data from Excel workbooks. It is template driven and it supports data extraction from specific cells, series of cells, columns, rows and any combination of the above. You can download a free beta of the component at http://www.datadefractor.com.

Choosing explicit cell in Excel to SQL Database

Hi, is there a way to choose a specific Excel cell and put the value into a specifc column in a SQL table using the wizard? For example, when the package is run, to have cell B3's value placed into a newly created row in column 0 and have F6's value placed into the same row in column 1?

Thanks for your time,

James

EDIT: What happens currently is I'll map a particular Excel column and it will create almost 200 rows in my table with mostly null values, as that is the format of the Excel sheet. I would like to create 1 row per Excel sheet, with selected information input. Thanks!

It is unclear.

With the explanation you gave, I would recommend to look into ways to reformat your Excel input so you can use either PIVOT or UNPIVOT to load this into your database.

You will still have to deal with the potential lack of heterogeneity of Excel.

What if the user give you smashed potatoes?

Regards,

Philippe

|||

Hi, thanks for the response. The Excel sheet is template based - every Excel sheet put into the database has the exact same format.

What I'm looking to do is this:

I would like to take the value from one particular cell from the Excel sheet (say, B5 for example) and insert it into a particular spot into a new row of a SQL database table. So, in all, I want to create a new row in the table, put B5 into the first column of this row, E9 into the second column of this row, and F13 into the third column of this row. Does this make sense?

Thanks,

James

|||

It makes sense. A way would be to use Automation to trigger a macro in the workbook from the ssis package, this macro would put B5, F9 and F13 in one row on another spreadsheet and the package would load this spreadsheet rather than the original.

You could also create another spreadsheet with indirect references to the master sheet. That would work only if the cells to "normalize" have a constant position.

The question I am asking myself is "How do you know that B5, F9 and F13 have to be in Col1 Col2 Col3 Is it set in stone? "

May be you could Name these ranges, then load several times by refering these names, the first pass would create the row and insert Name1, the second and third pass would only update the row with Name2 and Name3.

I guess I prefer the last approach.

Philippe

|||

James, you can use DataDefractor SSIS source data flow component to extract data from Excel workbooks. It is template driven and it supports data extraction from specific cells, series of cells, columns, rows and any combination of the above. You can download a free beta of the component at http://www.datadefractor.com.

choosing a the column that is not null

I have two columns in a table. For every record, one column contains a value and the other one is null. Which one is filled and which one is empty differs per record. In my query I only want to retrieve the column that contains the value, but not the other one. Is it possible to do this with a standard tsql function? Other solutions are also welcome.REfer IS [NOT] NULL from BOL.|||I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.|||Originally posted by jora
I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.

You might want to take a look at the function COALESCE() which is designed to solve the problem you have.

The function is also part of the ANSI standard and is supported by many RDBMS.

Choose path to travel on!!

Hi there

I am up against a condition where my control flow should be divided into threads and depending on value of a variable i have to choose 1 of the threads.

Its something like

if a =1 then execute this

else execute this one.

Thanks and Regards

Rahul Kumar, Software Enggineer

You need conditional precedence constraints. This article explains them:

Introduction to Expressions on Workflow
(http://www.sqlis.com/default.aspx?306)

-Jamie

|||

Thanks a lot,

Regards

Rahul Kumar, Software Engineer

choose max value of multiple calculations

Hi,

I need to be able to get the maximum value of different calculations.

Example :

i have 5 calculations like this :

(sum(Fields!CountTest1.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest2.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest3.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest4.Value)/sum(Fields!TestCount.Value))*100

(sum(Fields!CountTest5.Value)/sum(Fields!TestCount.Value))*100

these calculations give me a percentage from a test value against the total test values.

what function or expression can i use to get the max value of all the calculations ?

I was looking at the "choose" function but i'm not quite sure how...

anybody ? help ....

Vinnie

Hi again ...

I found a solution to my problem.

the way to do this is by using the following expression:

=math.max(value1, math.max(value2, math.max(value3, math.max(value4, value5))))

in fact it is the max function that is standard in the math expressions from rs.

greetings.

Vinnie

choose default value from DropDownList and SqlDataSource

Hello everyone

I'm really new to Data Presentation Controls and I already hate them couse I think they are way too complicated. Becouse of that i have already met wall many times. My last one sounds like this.

I'm triing to put a default value from, lets say SqlDataSource1 in a DropDownList that is created by SqlDataSource2. I know how to get value from SqlDataSource1 but I don't know how to use exactly that value as my default value in DropDownList.

Two more question.

1. Is it possible to have two different select statements in a SqlDataSource?

2. A SqlDataSource may contain an insert statement, select statement, update and delete. If I bind this SqlDataSource to a control how can I specify which of those statements will be used?

Thanks in advance

There are tutorials that cover all these topics in detail.

You bind the value to the dropdownlist's selectedvalue property.

1) Yes, but I don't recommend it.

2) By the command that is clicked within the presentation data control (Formview, detailview, gridview, etc).

I highly suggest you start with the tutorials. From those you learn the basics of how the data controls work. After you've mastered the data controls (Which really only takes an hour to learn the basics, and after a few days you've master them), then you can use them, or not. You always have the option of handling everything yourself. Do your own data retrieval, populate your controls, etc.

Chicken and egg

Hi there,

This strikes me as odd but I was trying to create a NULL datetime value for storage in a SQL server table and found that a .NET datetime object defaults to its min value of Jan 1, 1 A.D. OK, now this in itself is not a problem, but the min value for a date in SQL server is Jan 1 1753, so if I just try to save the default value .NET datetime, I get a value out of range exception. Annoying but reasonably easy to remedy.

I was just wondering if anyone knew which min value came first, why, and why the second min value, or at least its default value, wasn't made equal to the other.

Cheers, DanYou could just give your input parameter in SQL a default of NULL and if your date in your .NET code is 1/1/1 then do NOT add a parameter for it (so your NULL default will be used).

Checking values with range parameters

How can i match a value in my query with a parameter that has range e.g:
i want to check where order_date between {?Datapara}
where date parameter is a single field that has range.I have CR XI, can not swear this is supported in earlier releases.
Go to the sample reports and open the Record selection on date range.
look at the select statement and check out the report custom formulas.
You may be able to do this same thing in earlier versions, but I do not know that for certain. Only have CR XI

checking value in 2 tables.

i want to check a value in 2 tables. the 1st table i want to check if the value exists and from the 2ed table i samething.

i came up with this but when it's doing the second if i get error on the page.

Dim ReturnValAsInteger

Dim ReturnVal2AsInteger

Dim connAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString)

Dim cmdAs SqlCommand =New SqlCommand("SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber)", conn)

'To check if the # is registered.

Dim cmd2As SqlCommand =New SqlCommand("SELECT [ReportNumber] FROM [t_RegisterInfoTemp] WHERE ([ReportNumber] = @.ReportNumber)", conn)

cmd.Parameters.AddWithValue("@.ReportNumber", txtReport.Text)

cmd2.Parameters.AddWithValue("@.ReportNumber", txtReport.Text)

conn.Open()

ReturnVal = Convert.ToInt32(cmd.ExecuteScalar())

conn.Close()

'This checks if the # is correct.

If ReturnVal > 0Then

'This checks if its registered with someone else.

conn.Open()

ReturnVal2 = Convert.ToInt32(cmd2.ExecuteScalar())

conn.Close()

If ReturnVal2 <> 0Then

Server.Transfer("regccinfo.aspx")

Else

lblError.Text ="the # is registered with someone else."

EndIf

Else

lblError.Text ="the # does not exists."

txtReport.Focus()

EndIf

Sure would help if you would post the error that you're getting

|||

this is the error i get

Value was either too large or too small for an Int32.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.OverflowException: Value was either too large or too small for an Int32.

|||

Well, looks like you're sending in too big a value (absolute value speaking).

|||

you mean the value in the textbox to the query ?

the value would be numbers 10 digits.

If ReturnVal > 0Then

'This checks if the certificate is registered with someone else.

Server.Transfer("regccinfo.aspx")

Else

lblError.Text ="The # is incorrect."

txtReport.Focus()

EndIf

if i have my if else statment like this then it works fine.( Now this doesnt work either.)

|||

"TheInt32 value type represents signed integers with values ranging from negative 2,147,483,648 through positive 2,147,483,647."

fromhttp://msdn2.microsoft.com/en-us/library/system.int32(vs.80).aspx

|||

i got it to work. i chanded my ReturnVal as String running it like that. So far looking good.

Thanks for all your help guys

checking to see if field value is unique

When entering a value into a SQL database is there a way to find out if that value has already been used in that field?

eg. If I were entering a last name into a field called "user_name" could I add some validation whereby the record couldn't be entered if someone else had already inserted that last name?You could:

1) use unique index in the user_name column to prevent duplicate values (i.e first name and last name into separate columns as well and target the index to the last name column). In this case DB throws an error if duplicate insert is tried.

2) you could also check the existence before inserting into table, something like:

IF (SELECT count(*) FROM MyTable WHERE Last_name='thename') > 0
BEGIN /*There is such last name already*/

END

ELSE /*There isn't such in the table*/

BEGIN

END|||Definitely create the unique contraint on the columns you don't want duplicated, as suggested. This will ensure that duplicate values cannot physically be entered.

You should also check for the existence before inserting into the table, so that you can gracefully capture the error and return user-friendly information back to the user. But, instead of the method suggested, you should try using EXISTS, which will stop processing as soon as the condition is met

IF EXISTS(SELECT NULL FROM MyTable WHERE Last_name='thename')
BEGIN /*There is such last name already*/
END
ELSE /*There isn't such in the table*/
BEGIN
END

Terri

Checking the state of a variable as first step of package?

Am I correct in thinking that I need to place a dummy script component which does nothing in order to route logic depending on the value of a variable?

ie. I want to output to a file if a Parent variable is a certain value, or i want to load a table if its a different value.

Unfortunately, yes.

checking null value

Hi all,
I know someone answered this in the past but couldn't find the post.
How do I check for nulls if they might be in the column? colb and colc might
have null value?
the below query returns some rows that don't contain value "blue" which is
wrong.
select cola,colb,colc from tableA where
cola = 'blue' or
colb = 'blue' or
colc = 'blue'
thanks
gv>> How do I check for nulls if they might be in the column? colb and colc
Nulls can be checked using IS [NOT] NULL clause in SQL.
Since you are using OR operator, the query might return every row where any
one of cola, colb, col3 or any combination of the three has a value 'blue'.
Based on the query you posted, the result you see is valid.
Anith

checking length of textbox value!

Hi

I was looking to check the length of a textbox then truncate it if it is graeter than some value and assign the that value for a textbox control in reporting service.How can i acheve this?

Thanks

This expression truncates any string in the textbox longer than 2 characters.

=Iif(Fields!FieldName.Value.ToString().Length() > 2, Fields!FieldName.Value.ToString().Substring(0,2), Fields!FieldName.Value)

You will need to change the field name to the actual field going into the textbox.

|||

I do not know who you are but I really would like to appreciate you !

Thanks a lot!

Ephi.

|||

I want to do the same thing as well as if the length of the field is shorted than the required length, I need to pad it with spaces. Can someone help with how to do that as well ?

Thanks

AY

checking length of textbox value!

Hi

I was looking to check the length of a textbox then truncate it if it is graeter than some value and assign the that value for a textbox control in reporting service.How can i acheve this?

Thanks

This expression truncates any string in the textbox longer than 2 characters.

=Iif(Fields!FieldName.Value.ToString().Length() > 2, Fields!FieldName.Value.ToString().Substring(0,2), Fields!FieldName.Value)

You will need to change the field name to the actual field going into the textbox.

|||

I do not know who you are but I really would like to appreciate you !

Thanks a lot!

Ephi.

|||

I want to do the same thing as well as if the length of the field is shorted than the required length, I need to pad it with spaces. Can someone help with how to do that as well ?

Thanks

AY

checking length and triming the value at ','!

Hi all!

I have a record coming from the database having of F_name,L_name, and M_name being concatenated and separated with comma. In an expression of a textbox control value I would like to check the length of this record and if it is more than 12 only to take the first values till the comma’,’ which appears prior to the 12^th character and truncate the rest. For example:

Record from the database= FirstName,LastName,MiddleName

1) GAUTHIER, JEFFREY, Jane Desired result GAUTHIER, b/c if Last name is added it will be more than 12.

2) Jane, JEFFREY, GAUTHIER Desired result Jane, b/c if Last name and first name are added it will be more than 12.

3) JEFFREY, Job, GAUTHIER Desired result JEFFREY, Job, b/c if First name is added it will be more than 12.

4) JEFFRE, Job, G Desired result JEFFREY, Job, G etc b/c all the values are not more than 12

So would you mind helping me on how the expression looks like?

Thank you in advance!

Try this expression:

=Iif(Fields!name.Value.ToString().Length() > 12, Iif(Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",") <> -1, Fields!name.Value.ToString().Substring(0, Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",")), Fields!name.Value.ToString().Substring(0, 12)), Fields!name.Value)

hehe, it's big and ugly but it does what you want. You could use embedded code to make it prettier.

|||

Hi Ryan,

That doesn't work if the three names' total length is less than 12. I just ran it on my name database and some come up as #error#.. Like "Hat, Jo, Ty" ...

pl

|||

Ugh...You're right. Ok, try this instead, add the following embedded code to your report:

Function TrimWithCommas(ByVal name As Object) As String

if (name is Nothing) then
TrimWithCommas = Nothing
end if

Dim nameString as String = name.ToString()
if (nameString.Length() > 12) then
Dim index as Integer = nameString.SubString(0, 12).LastIndexOf(",")
if (index <> -1) then
nameString = nameString.SubString(0, index)
else
nameString = nameString.SubString(0,12)
end if
end if
TrimWithCommas = nameString
End Function

Then use the following expression in the textbox you want the name to appear in:

=Code.TrimWithCommas(Fields!name.Value)

|||

Hi Ryan,

Excellent! That worked on 17000 employees! No errors!

Thanks for the lesson.

pl

|||

Hi,

I've been trying to write this up in the Expression editor, and, if the first 12 bytes of the name does not contain a comma, it gives an error. Here is the code:

=Iif( len(Fields!EMPLOYEE_NAME.Value) > 12,
Iif( instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12 ), ",",12) > 0,
left(Fields!EMPLOYEE_NAME.Value, instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12), ",",12 ) - 1 ) ,
left(Fields!EMPLOYEE_NAME.Value, 12)),
left(Fields!EMPLOYEE_NAME.Value,12))

It seems that the true and false clauses are evaluated every time, and the case where the "instrrev" fails (evaluates to zero), causes the whole condition to die...

I'm just starting to learn. Did I miss something?

Thanks.

Checking last user Identity on remote server

Is it possible to get the latest inserted Identity value on a remote server?
(The following gives a NULL result:
select ident_current('[my_linked_server].thedatabase.dbo.thetable')
)@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).

HTH|||Beg your pardon - @.@.Identity is specific to session - sorry - didn't read every last line of BoL before hitting Submit.|||@.@.IDENTITY (assuming you mean server wide irrespective of the user\ connections etc).
HTH

Are you sure? I think that's what I tried first. Doesn't
it give the latest Id value used in the server where
you are (so to speak), not the remote server?|||Finally, I understood this is the way to do it. Just as feedback.

DECLARE @.return_status int
EXEC @.return_status = MyLinkedServer.the_database.dbo.a_stored_procedure
SELECT 'Return Status' = @.return_stat

The SP "a_stored_procedure" is stored on the linked server. It returns
Ident_Current for the appropriate table, using RETURN, simply.

Checking KPI value for divide-by-zero

We've got some potentially complex KPI value definitions, which can involve division of two cell values. I want to put in a check to make sure we don't do a divide-by-0. If I do, is there any performance implication due to the calculation of the denomerator happening twice (once in the first IIf clause, again in the actual value). For example, if I have a value that's essentially:

[Measure1] / (Aggregate([A Set], [Measure 2])

and I want to make it

IIf( (Aggregate([A Set], [Measure 2]) > 0, [Measure1] / (Aggregate([A Set], [Measure 2]), Null)

Is there a performance hit for doing this? Does the aggregate happen twice? Is there a smarter way of doing this check?

Yes, Aggregate will happen twice. The best way to handle it is

CREATE HIDDEN AggASet = Aggregate([A Set], [Measure 2])

and then you can say

IIf( AggASet > 0, [Measure1] / AggASet, Null)

|||Thanks Mosha. But can I specify a composite expression like that in a KPI value expression?|||No - the CREATE HIDDEN statement should be somewhere inside the MDX Script.|||Shoot, that's what I was afraid you were going to say. The problem is that these KPIs are being generated by a GUI tool, and so far we've done everything inside the KPI definition itself, without having to muck with the MDX script. Changing that is going to involve some major code changes, sadly.

Is there any other way to handle this condition gracefully?|||

Your choices as I see them are:

1. Do nothing - take perf hit, and hope that in the next version query optimizer will automatically detect such expressions and will do the "right thing"

2. Change your logic to add hidden calculated measures to MDX Script

3. Don't check for 0 in denominator and let users see 1.#INF when such division occurs

4. Create another KPI which will hold the Aggregate expression and reference its through KPIValue function from your KPI expression. This way you won't need to change MDX Script. I am not sure, however, if it is possible to create hidden KPIs. So the drawback is that you will get an extra 'junk' KPI.

5. Something else ?

HTH,

Mosha (http://www.mosha.com/msolap)

Checking if Value from a column is blank in SQL Server 2000

Hi

I want to check if the values of a column from a table is blank (i.e. empty but not null) . If it is blank, then I want to replace it with some other text. Is there some function like, Isnull or coalesce in SQL Server 2000, with which I can check if the value is blank and replace it. I want to use it as part of query so, I do not wish to use if trim(...) etc.

If any one has an solution, please do tell me.

Regards

Vineed

Here it is,

Code Snippet

Create table #Data(

ValueColumn varchar(100)

)

Insert Into #Data Values(' ');

Insert Into #Data Values('');

Insert Into #Data Values(' ');

Insert Into #Data Values(' ');

Insert Into #Data Values(NULL);

Insert Into #Data Values('Some value');

Select Case When ValueColumn='' Then '(Empty)' Else ValueColumn End From #Data

Checking if String is NULL or EMPTY in SQL

I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.

IFISNULL(@.param)OR @.param =''THEN doSomething...

(Note the two apostrophes ' and ', not a quote mark!)

|||

Books online (help files that comes with SQL) is an amzing little app. Below is an example and the Syntax.

USE pubsGOSELECTAVG(ISNULL(price, $10.00))FROM titlesGOISNULL ( check_expression , replacement_value )

|||

Create Procedure mySpNameHere

@.InputValueHere VARCHAR(50) = NULL

AS

IF @.InputValue IS NULL OR @.InputValue = ''

/*Keep the old value*/

ELSE

/*Run the update statement here with your new value*/

|||

cheetahtech:

ISNULL ( check_expression , replacement_value )

Sorry. I got a blackout. Of course, ISNULL syntax is to be used in a query where you want to specify an alternative value, if the expression is NULL.

The correct way to check for NULL in a condition is IF @.Param IS NULL as rich freeman points out.