Question -sub query

I’m needing help with a query here. I have a table with an ID and Date which are my PK’s.

I believe this will take a sub query. I want several fields pulled (one per ID). It would be the one with the max(date)

Here is what I have so far (yes I know it doesn’t work but you should get the idea).

select myID, myDate, field1, field2, field3, field4

FROM

(select myID,max(myDate)

from myTable1

WHERE

DATALENGTH(field1)> 0OR

DATALENGTH(field2)> 0OR

DATALENGTH(field3)> 0OR

DATALENGTH(field4)> 0

groupby myID)

By the way the sub query here works on it’s own and gives me the records I want, I just need the other fields pulled in there

Here you are:

SELECT t1.myID, t1.myDateMax, t2.field1, t2.field2, t2.field3, t2.field4FROM

(SELECT myID,max(myDate)AS myDateMax

FROM myTable1WHERE field1ISNOTNULLOR field2ISNOTNULLOR field3ISNOTNULLOR field4ISNOTNULL

GROUPBY myID)AS t1INNERJOIN myTable1AS t2ON t1.myID=t2.myIDAND t1.myDateMax=t2.myDate

|||

Beautiful!!

Thank you Limno

sql

Question on returning all records when using parameters...

I'm trying to fully utilize parameters on a report.

I have 3 parameters, a start date, end date, and a cause.

The start and the end date are working as expected. What I would like is if a user fails to select a cause (queried from a lookup dataset) I would like to return all records, not filtering by cause. I'm using parameters handed to the dataset to do this and the report will not allow me not to select a value.

Should I be using Filter, Report Parameters, or some other means to solve this?

Any advice would be appreciated as I don't want to "KLUDGE" up the system with a bunch of reports each with varying levels of criteria.

Thanx!

TR

I handled a similar situation with creating an "All" choice in my optional parameter and setting this as the default value. If I understand what you are looking to accomplish, I believe this may work for you as well.|||Simone's suggestion will work, you may also use the 'multi-value' drop down which will automatically create the 'All' option. Assuming you are using SSRS 2005...|||

Say that I create an ALL choice on my drop down... What parameter do I put behind it such that my query will know to return all records as opposed to 'all' the string...

IE: Select * from myTable where myField = 'All'

would obviously try looking for the string value 'All' am I missing something really obvious? I have seen others suggest the all method so I have no doubt it works and I think I'm just missing part of the equation.

Thanks for the help.

TR

|||If you use a multivalued parameter, it will send in the values for the 'cause' parameter, so you can use an IN statement.

For example:

SELECT * FROM tblMyTable Where Cause in ('value1, value2, value3')|||

Hi Andy,

The problem with a multival box is that I will have between 24 and 60 values. Obviously too many to have a user selecting.

Thanks for the reply though.

TR

|||You can always go down the path of using dynamic SQL and generating your statment on the fly. You'll want to consider the performance impact, but on relatively basic queries, it should be negligble.

In that situation, set the 'All' label of the Cause parameter to a value of NULL (or -1, or 'All', whatever float your boat), check for the Cause query parameter value corresponding to All and if so, omit it from the WHERE clause.

Something like this:

If @.Cause IS Null then

Set @.SQL = 'SELECT * FROM Table'

Else

Set @.SQL = 'SELECT * FROM Table WHERE Cause = ' + @.Cause

Exec (@.SQL)|||We use dynamic SQL all the time....for further information see: http://sommarskog.se/|||

The SQL that I use when I want the 'all' option is as follows:

Code Snippet

Select * from table where id = case when @.id = -1(all) then id else @.id end

Hope this helps.

Simone

Question on refining query

I tried to search for google but i wasn't exactly how to phase it. My tables has a column "DATE", i am populating a listbox using a record set and the generic query "Select DATE From Table1". I want to display only the Uniqu dates, but that i mean if 08/31/04 shows up a 100 times I only want to show it once in that listbox. I have tried altering the values from the recordset and storing them in a collection and looking to see if they reappear but i keep getting errors. So I figured that there had to be an easier way of just redefining the orinal query just to get those dates and then populating the listbox because much more trivial.

any help would be appreciatedFigured it out, just had to add distinct into the query

thanks

Question on KPI

When I view the KPI in browser view, what is the time for which the KPI is applied. Is it todays date for which the KPI displays value and goals or some time in the past. How can I see the value of KPI for time in the past, for eg, like June 2003.

Thanks,

Vijay

If you have not pointed to a time member in a calculated member(that you use for your KPI) or in the definition of the KPI, it is always time current member.

More clearly it then depends on the time members on of the axis or if you have filtered or sliced by the time dimension.

HTH

Thomas Ivarsson

|||

In the browser view of KPI tab, I am not slicing by any time dimension. I am viewing what is shown as default after creating the KPI. The KPI value seems to be a cumulative value over all the time. For eg, if the sales amount is the value, and the sales quota is the goal, then the values displayed are the sum over all the years. This does not make sense to me.

If I filter by time dimension like Calendar Year, then I would expect the sales amount and sales quota for that calendar year.

|||

The current member for your time dimension is "All". I agree that this does not make sence.

You should be aware of that the browser in the KPI-tab is not always correct.

My advice is to look at the KPI with Excel 2007 or ProClarity Professional 6.3

In the adventure works cube project, part of the installation of samples, you have examples of KPI that you can have a look at.

Here is a link to a good article about KPI

http://www.databasejournal.com/features/mssql/article.php/3604206

HTH

Thomas Ivarsson

Question on getting the date value into a filename

I am running the following sqllitespeed backup script on Windows 2003 Server
against SQL Server 2000

sqllitespeed -Bdatabase -T -DNorthwind -FH:\MicrosoftSQLServer\BACKUPS\DATABACKUPS\NorthWi nd\northwind.bak

How could I place the date variable into the output filename?

I've tried variations of %date% etc but nada.

Any thoughts?

Thanks in advance.

Gerryhow do you executye this? Command line?, xp_cmdshell?|||It would be in a script run by an external controller.
A batch file.|||OK, batch file

I would use T-SQL to create said batch fiile, and as a matter of fact I would have a sql server job lauch said batch file

question on date (or string after using convert function) comparis

select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
the result return is 'T' why? (2005 should be < 2006)
On Mon, 13 Feb 2006 19:05:26 -0800, kei wrote:

>select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
>the result return is 'T' why? (2005 should be < 2006)
Hi kei,
You are comparing two string constants. They may look like dates to you
and me (though probably not the same dates - I'm from the part of the
world that uses dd/mm/yyyy), but SQL Server doesn't try to interpret
what you write - it takes you literally.
You could try
SELECT CASE WHEN CAST('02/12/2005' AS datetime) >=
CAST('01/02/2006' AS datetime) THEN 'T' ELSE 'F' END
and pray that SQL Server interprets the ambiguous date format the same
way you do.
Or you could switch to a non-ambiguous date format:
SELECT CASE WHEN CAST('20051202' AS datetime) >=
CAST('20060201' AS datetime) THEN 'T' ELSE 'F' END
For more information, check Tibor Karaszi's article on SQL Server date
and time handling: http://www.karaszi.com/SQLServer/info_datetime.asp
Hugo Kornelis, SQL Server MVP