how to get Time time difference in sql?

Could any one please tell me how to find time difference between two dates?

i have two fields in database as datetime data type.i need to get time difference between this two fields.how to do that?

i use this one

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

but it giving me results as

1.00000000

1.250000

3.00000

i jus want it to be

1

1.25

3

How to do this?

Thanks for any help.

To find the time difference in days, hours, minutes. Try it, you can replace the dates with you column datetime.

selectDATEDIFF(day,2007-11-30,2007-11-20)AS NumberOfDays,DATEDIFF(hour,2007-11-30,2007-11-20)AS NumberOfHours,DATEDIFF(minute,2007-11-30,2007-11-20)AS NumberOfMinutesFROM test_table

|||

hi, thanks for the reply i dint useDATEDIFF(hour,2007-11-30,2007-11-20) this option jus because

i have one data as 10:45 and another as 12:00 it gives me time diff as 2 in hrs but actual diff is only 1:15 min thats y im convertin to min first and dividing it by 60.

but my outputs are like 1.02345,2.4567

i jus need to be like 1, 2.45

|||

Tweety@.net:

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

simple...

just write this query as...

SELECT outdate, ROUND(cast((datediff(mi, outtime, intime) / 60.0) as FLOAT),2) AS TimeUtilized FROM breaktime

and it should work.

hope this helps./.

|||

Try casting the result to decimal data type. The syntax is cast ( <number> as decimal ( 5 , 2 ) ).

sql

How to get Time in SmallDateTime?

Hi,
I just want to get the value from a smalldatetime into time only in a SQL Statement
10/10/2005 10:00:00 AM -- > 10:00:00 AM
What would be the best approached?
Thanks,
VinceThis will truncate all datetime values to Jan 1, 1900, leaving only the time portion. To format the resulting value, look up the CAST and CONVERT functions.
declare @.MyTime smalldatetime
set @.MyTime = getdate()
select dateadd(d, -datediff(d, 0, @.MyTime), @.MyTime)

How to get the total time of the records in a table

Hello,

I have one table that has a column called CallDuration. This columns has always the format "1/01/2000 12:01:38 AM". The date part "1/01/2000" I want to discard, and sum the time part to get a total time in my query. How can I do that?

Thxselect datediff(hour,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') Hours,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') % 60 Minutes,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') TotalMinutes|||select datediff(hour,min(callduration), max(callduration)) Hours,
datediff(minute,min(callduration), max(callduration)) % 60 Minutes,
datediff(minute,min(callduration), max(callduration)) TotalMinutes

how to get the top value?

hi,

this my prediction query.

select Flattened (select $time as Date, [Perf] as Perf from PredictTimeSeries([Perf],100) where $Time <'1/1/2007' ) as A from [Stud_Model] WHERE [StudID]=8

Its giving me result where the date is less than 1/1/2007.i need only one result, where the date should be nearer to 1/1/2007. I am not able to use Top 1 inside like

select Flattened (select top 1 $time as Date, [Perf] as Perf from PredictTimeSeries([Perf],100) where $Time <'1/1/2007' ) as A from [Stud_Model] WHERE [StudID]=8

can anyone help.

Thanks,

Karthik

I haven't tried a query like that, but you can't use Top in that context. You may be able to use the TopCount function, I believe the syntax is

TopCount(<table expression>, <count>, <column-ref>) // the last two may be reversed.

TopCount is itself a table expression.

|||

hi jamie,

Its working cool.

Thanks a lot.

How to get the time since a sql server is running

Do you know any easy way for getting the time that the SQL Server was
running?
Thanks
JavierHi Javier
Look at the creation date for the tempdb database.
In SQL 2005:
select create_date from sys.databases
where name = 'tempdb'
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Javier" <ji_villegas@.hotmail.com> wrote in message
news:ACBD90A1-D7B3-4D81-868F-EB2528A10741@.microsoft.com...
> Do you know any easy way for getting the time that the SQL Server was
> running?
> Thanks
> Javier|||You can look at your SQL Server logs as they are created when SQL Server
restarts (although it may be a spill-over of the previous log which may be
too big to fit into one file). Just check the very first entry in the
latest log
"Javier" <ji_villegas@.hotmail.com> wrote in message
news:ACBD90A1-D7B3-4D81-868F-EB2528A10741@.microsoft.com...
> Do you know any easy way for getting the time that the SQL Server was
> running?
> Thanks
> Javiersql

How to get the system date and time into database

Hi,

I m using ASP.NET with C#.
I m having one field which should store the datetime of the system.

The datetime should be automatically stored for that entry when the user submits that record on the click event.
Then it should display the date time on the gridview from database.

I m using sqlserver 2005 and i have created the stored procedure for the insert command.

Thisis the sample sp what should be written here to insert system date timeautomatically when the user submits the asp.net form ?

Is there any code for writing directly in stored procedure or asp.net coding page...

ALTER PROCEDURE [dbo].[StoredProcedure1]
@.salesid INT OUTPUT,
@.salesdate datetime,
@.customername varchar(20)
AS
BEGIN
SET NOCOUNT ON
BEGIN
 INSERT INTO sales (customername)
 VALUES (@.customername)
 SELECT @.companyid = SCOPE_IDENTITY()
END
SET NOCOUNT OFF
END

Thanxs in advance...

Hi,

use getdate() function of sql server.

for more info look here

http://msdn2.microsoft.com/en-us/library/ms188383.aspx

|||

You have a choice as to where to use the getdate() function (which returns the current system date and time).

You can actually define it on a column in the table itself so that you don't have to set it programmatically. Here is sample syntax:

create table sales (sales_id int identity, customer_name varchar(30) not null,sales_date datetime default getdate() not null)

Alternatively, within the stored procedure assign your variable with the function call. Here is sample syntax:

select @.SystemDate = getdate()

How to get the first date

I have a time dimention of Year, Month and Date
I want to know the unit sales of the first date of every month of every
store.
How can I implement the MDX?
look at this:
http://msdn.microsoft.com/library/de...l/semiadd2.asp
(Semi additive measures)
You are in the same case, except you want the start date instead-of the end
date in your calculations.
"ad" <ad@.wfes.tcc.edu.tw> a crit dans le message de news:
%23%23A33vNsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>I have a time dimention of Year, Month and Date
> I want to know the unit sales of the first date of every month of every
> store.
> How can I implement the MDX?
>

How to get the first date

I have a time dimention of Year, Month and Date
I want to know the unit sales of the first date of every month of every
store.
How can I implement the MDX?look at this:
http://msdn.microsoft.com/library/d...br />
add2.asp
(Semi additive measures)
You are in the same case, except you want the start date instead-of the end
date in your calculations.
"ad" <ad@.wfes.tcc.edu.tw> a crit dans le message de news:
%23%23A33vNsEHA.3556@.TK2MSFTNGP10.phx.gbl...
>I have a time dimention of Year, Month and Date
> I want to know the unit sales of the first date of every month of every
> store.
> How can I implement the MDX?
>sql

How to get the date part of Datetime

Is there any other way which is more simpler(shorter) to get the date
part (I don't want the time part ) of Datetime than this ?

Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,
DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As
[Date]> Is there any other way which is more simpler(shorter) to get the date
> part (I don't want the time part ) of Datetime than this ?
> Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,
> DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As
> [Date]

convert(char(10),getdate(),120)
http://www.karaszi.com/SQLServer/info_datetime.asp

how to get System date in SQL

how to get the system date or time in any format through SQL?Hi there,

think it should be

select getdate()

Carsten|||To get it to a different format, use the CAST or CONVERT functions.

blindman|||Originally posted by blindman
To get it to a different format, use the CAST or CONVERT functions.

blindman

Or

DATENAME ( datepart , date ), DATEPART ( datepart , date ) ;)|||Originally posted by snail
Or

DATENAME ( datepart , date ), DATEPART ( datepart , date ) ;)

Or select GETUTCDATE() - Greenwich Mean Time.

how to get sqlcmd return values

I'm having a hard time getting a non-zero return value from sqlcmd when a
error in the sql script it is executing occurs.
I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
is relevant) but regardless ERRORLEVEL is always 0
Here is a sample of the sqlcmd and the os syntax I'm running from within a
sql agent job step:
sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
input file.sql"
echo %ERRORLEVEL%
or
set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
also, I've tried different iterations with and without spaces between -V and
10
any help or suggestions is greatly appreciated.What error occurs on the script you run? I just tried with RAISERROR and it
work find. Here's the
bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And here's the contents of a.sql:
RAISERROR('Ouch', 15, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V a
nd
> 10
>
> any help or suggestions is greatly appreciated.
>|||Here is the error message:
Msg 945, Level 14, State 2, Server servername, Line 1
Database 'databasename' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Basically I iterate through different databases and select from some sys
tables. The problem here is one particular db is either in a loading state
or in a suspect state and therefore causes the error. Nonetheless, resolvin
g
that issue is not hard and not as important as just catching the error itsel
f.
Thanks
"Tibor Karaszi" wrote:

> What error occurs on the script you run? I just tried with RAISERROR and i
t work find. Here's the
> bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And here's the contents of a.sql:
> RAISERROR('Ouch', 15, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>|||Try the "-b" command line option.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
> I'm having a hard time getting a non-zero return value from sqlcmd when a
> error in the sql script it is executing occurs.
> I've tried setting the -V parm to 10 and -m to 10 (although don't thing -m
> is relevant) but regardless ERRORLEVEL is always 0
> Here is a sample of the sqlcmd and the os syntax I'm running from within a
> sql agent job step:
> sqlcmd -U user -P pass -S server -V 10 -h-1 -i "C:\Admin\bcp table space
> input file.sql"
> echo %ERRORLEVEL%
> or
> set %ERRORLEVEL% = sqlcmd -U user -P pass -S server -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> also, I've tried different iterations with and without spaces between -V
> and
> 10
>
> any help or suggestions is greatly appreciated.
>|||Tried this but didn't work. Is my dos look right?
!!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
"C:\Admin\bcp table space input file.sql"
!!echo %ERRORLEVEL%
with or without -V doesn't work?
"Mike C#" wrote:

> Try the "-b" command line option.
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:5CD9F397-04BD-465F-A818-2E77C30D43BC@.microsoft.com...
>
>|||It work just fine for me with such an error as well. Here's what I did:
CREATE DATABASE x
ALTER DATABASE x SET OFFLINE
And here's what I have in the bat file:
sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
ECHO %ERRORLEVEL%
pause
And what is in a.sql:
SELECT 'Hello'
SELECT * FROM x..sysobjects
SELECT 'Hello again'
And below is output from executing the bat file:
C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
Database 'x' cannot be opened because it is offline.
C:\>ECHO 14
14
C:\>pause
Press any key to continue . . .
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
> Here is the error message:
> Msg 945, Level 14, State 2, Server servername, Line 1
> Database 'databasename' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for detail
s.
>
> Basically I iterate through different databases and select from some sys
> tables. The problem here is one particular db is either in a loading stat
e
> or in a suspect state and therefore causes the error. Nonetheless, resolv
ing
> that issue is not hard and not as important as just catching the error its
elf.
> Thanks
>
> "Tibor Karaszi" wrote:
>|||Tibor,
Thanks for following through. Can you try one more thing that I'm wondering
may be having an effect.
Change you select sql to be a dynamically executed string.
Like this:
declare @.sql varchar(255)
set @.sql = 'select * from x..sysobjects'
select 'Hello'
exec(@.sql)
select 'Hello again'
If that works on your machine, I would say it is something local to mine,
else I'm wondering if exec() behaves the way I'm expecting it to.
Thanks again.
"Tibor Karaszi" wrote:

> It work just fine for me with such an error as well. Here's what I did:
> CREATE DATABASE x
> ALTER DATABASE x SET OFFLINE
> And here's what I have in the bat file:
> sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> ECHO %ERRORLEVEL%
> pause
> And what is in a.sql:
> SELECT 'Hello'
> SELECT * FROM x..sysobjects
> SELECT 'Hello again'
>
> And below is output from executing the bat file:
> C:\>sqlcmd -STIBWORK\RTM -V10 -h-1 -ia.sql
> Msg 942, Level 14, State 4, Server TIBWORK\RTM, Line 2
> Database 'x' cannot be opened because it is offline.
> C:\>ECHO 14
> 14
> C:\>pause
> Press any key to continue . . .
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:7B3754E7-C857-4BE3-9DFD-14BED25FD33B@.microsoft.com...
>|||Same result. I still managed to catch the error with errorlevel.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paul" <paul@.discussions.microsoft.com> wrote in message
news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
> Tibor,
> Thanks for following through. Can you try one more thing that I'm wonderi
ng
> may be having an effect.
> Change you select sql to be a dynamically executed string.
> Like this:
> declare @.sql varchar(255)
> set @.sql = 'select * from x..sysobjects'
> select 'Hello'
> exec(@.sql)
> select 'Hello again'
>
> If that works on your machine, I would say it is something local to mine,
> else I'm wondering if exec() behaves the way I'm expecting it to.
> Thanks again.
>
> "Tibor Karaszi" wrote:
>|||I've been able to run it ok and get the return error value as well... I'm
running SQL 2K5 SP 1 if that makes a difference.
"paul" <paul@.discussions.microsoft.com> wrote in message
news:6897329C-EEFC-4756-A1DA-46E366EAF3EA@.microsoft.com...
> Tried this but didn't work. Is my dos look right?
> !!sqlcmd -U username -P password -S servername -b -V 10 -h-1 -i
> "C:\Admin\bcp table space input file.sql"
> !!echo %ERRORLEVEL%
> with or without -V doesn't work?
>
>
> "Mike C#" wrote:
>|||Tibor / Mike,
Thanks for all your help, I believe I have solved the riddle. As contorted
as my logic might seem, I am actually calling numerous sqlcmd from inside th
e
same transact sql bactch.
To do this I am prefixing it of course with '!!'. So there in lies the
problem, each !!sqlcmd must be spawning its own dos session so if I do the
following:
!!sqlcmd ....
!!echo %errorlevel%
error level will always be 0 in the second dos session.
thanks for helping with that guys
"Tibor Karaszi" wrote:

> Same result. I still managed to catch the error with errorlevel.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:1AB3EA52-EAB2-4C11-82A2-E0A9A97A0B19@.microsoft.com...
>

how to get sql procedure parameters count and their type?

Hi All,
i am using DBLib and VC++. i want to know the number of parameters
of a procedure at run time. is it posible if yes then how can i do this.
thanks.Use ADO's OpenSchema method with the schema ID: adProcedureParameters|||is there another way of getting parameter count other than ADO.
actually we dont use ADO. some winodws api etc.

thanks for reply.

omar|||Check out INFORMATION_SCHEMA.ROUTINE_COLUMNS

How to get rid of time in a date/time field?

I am using vb.net 2005 with crystal and I am wanting to only show the date on the record being grabbed from SQL. currently it is showing the date along with the time stamp. Is there an easy way to do this in Crystal?I figured it out...format object :-) Easy enough!

How to get remote server datetime

Hi All ,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RP
Haven't tested but it should work using Openquery instead of
the 4 part name. The statement passed in the openquery is
executed on the remote server.
-Sue
On Fri, 27 Apr 2007 12:24:01 -0700, Ram
<Ram@.discussions.microsoft.com> wrote:

>Hi All ,
>I have 3 sql servers located at different time zones. Say, CST,PST,EST.
>Now how can I get current time at EST,PST from the SQL server located
>at CST? Is there any query to do that?
>I have a stored proc located in SQL server at CST zone where I need to
>query for the current date/time of the other zone sql servers.
>I tried below query at CST SQL server
>SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
>But it always gives CST datetime.
>Please reply...
>Thanks
>RP
|||Hi Sue,
It works gr8...
Thanks for the help...
"Sue Hoegemeier" wrote:

> Haven't tested but it should work using Openquery instead of
> the 4 part name. The statement passed in the openquery is
> executed on the remote server.
> -Sue
> On Fri, 27 Apr 2007 12:24:01 -0700, Ram
> <Ram@.discussions.microsoft.com> wrote:
>
>
|||Hi,
Can you please write the exact syntax you used for Openquery? I also have
requirement similar to this.
Thanks for your help.
Namwar
"Ram" wrote:
[vbcol=seagreen]
> Hi Sue,
> It works gr8...
> Thanks for the help...
>
> "Sue Hoegemeier" wrote:

How to get query execution time of an MS SQL Query in Visual basic

please let me know How to get query execution time of an MS SQL Query
in Visual basic
If you are executing the query from vba, create a timer (using
GetTickcount from the Windows API or something similar). Record the
start time at the beginning of the query, and the stop time after it
completes. Compute the total time elapsed.
--Mary
On 14 Sep 2004 23:46:21 -0700, rajeevmc@.yahoo.com (Rajeev M C) wrote:

>please let me know How to get query execution time of an MS SQL Query
>in Visual basic

How to get query execution time of an MS SQL Query in Visual basic

please let me know How to get query execution time of an MS SQL Query
in Visual basicIf you are executing the query from vba, create a timer (using
GetTickcount from the Windows API or something similar). Record the
start time at the beginning of the query, and the stop time after it
completes. Compute the total time elapsed.
--Mary
On 14 Sep 2004 23:46:21 -0700, rajeevmc@.yahoo.com (Rajeev M C) wrote:
>please let me know How to get query execution time of an MS SQL Query
>in Visual basic

How to get Procedure call text from within SQL Stored Procedure

Hi,
I'm using SQL Server 2005.
I have implemented some SPs with the TRY..CATCH structure and each time
that I have an error, I just log it into a dedicated LogError table.
I would like to add in LogError a column containing the command that
was used by the user (containing the Procedure Name and the calling
parameters)
Is there any way to perform it ?
Thanks
PFIhave a look at
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
The bits with
dbcc inputbuffer
and
fn_get_sql
Get the sql executed if it is available.
dbcc inputbuffer is more likely to have the user command.
Neither is guaranteed.
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***|||Thanks a lot.
dbcc inputbuffer was exactly what I was looking for.
PFI
nigelrivett a =E9crit :
> have a look at
> http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html
> The bits with
> dbcc inputbuffer
> and
> fn_get_sql
> Get the sql executed if it is available.
> dbcc inputbuffer is more likely to have the user command.
> Neither is guaranteed.
> www.nigelrivett.net
> > > *** Sent via Developersdex http://www.developersdex.com ***

How to get only date component from a datetime column

Hi all,

I have a table containing a datetime column and it stores date with time ( I use GETDATE() to store the value ). For reporting purpose I need only the date component from this column without the time component. I do not want to use the datepart(yyyy, orderdt), datepart(mm, orderdt) and datepart(dd, orderdt).

Will following help

select CONVERT ( VARCHAR (10) , GetDATE(), 121 )

How to get modified time of a table

Hello All,

I would like to get last modified timestamp for a table. Is there any way to
get that information using any commands?

Thanks in Advance,
Muthu.Muthu (muthuviji.k@.gmail.com) writes:

Quote:

Originally Posted by

I would like to get last modified timestamp for a table. Is there any
way to get that information using any commands?

No, there is no built-in support for this. You could have to add
a modified_date column to the table, and then use a trigger to ensure
that it's updated accordingly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I would like to get last modified timestamp for a table. Is there any way to get that information using any commands? <<

Before you send your boss to jail, you might want to look at SOX rules
for audits. Then look at third party tools for such things.

How to get MDX query that BI Studio generates in the Cube''s Browser

Hi,

I have been trying to write an MDX query which restricts the data based on a date range on the time dimension.

I am able to frame this query on the GUI interface of the Business Intelligence Studio (Using the cube browser) But I want the actual MDX for it.

To be specific, I am trying to view how the "Range (Inclusive") operator is implemented in the "Browser" Tab of the Cube Editor in the Business Intelligence Studio.

I have a Time dimension which has Year, Quarter, Month, Day Hierachy and I need to form the query such that I have a couple of pre-defined CALCULATED measures on the Column axis and Another dimension on the Row axis. I want to limit the records to a Range (Say Jan 1, 2007 to Aug 5, 2007).

My MDX works when it is for a single value of the Time Dimension.But for a range, I get nulls in the calculated measures.

My MDX is :

WITH

MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '

AGGREGATE({{[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.lastChild.lastChild}

+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1].parent.parent.nextMember:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.prevMember}

+ {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5].parent.parent.firstChild.firstChild:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}})

', SOLVE_ORDER = 5001, SCOPE_ISOLATION=CUBE

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]

=====================================

Where the calculated MEASURE.Agency is pre-defined as:

CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS

'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),

IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,

"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;

Any help is greatly appreciated.

Regards,

Mehernosh

I'm not entirely sure, but if I read your statement correctly all you are trying to achieve is to get the aggregate of the dates from Jan 1 to Aug 5. In which case you should be able to do the following.

SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,

{[Resource-By Pool].[Resource Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}

And if you are after a YTD amount it could be expressed even more concisely as:

SELECT {[Measures].[Available Hours], [Measures].[Agency] } ON AXIS(0) ,

{[Resource-By Pool].[Resource Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {YTD([Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5])}

I'm not sure why you are using a measure for the agency name and not just crossjoining in the Agency dimension:

SELECT {[Measures].[Available Hours] } ON AXIS(0) ,

NON EMPTY {[Resource-By Pool].[Resource Name].members} *

{Agency-By Pool].[Agency Name].members} on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE {[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5]}

There are a couple of issues with your calculated measure. This statement "NOT ([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL)" will always return true as the IS operator does a member comparison not a value comparison and CurrentMember will always return a valid member. I think what you probably meant was "NOT IsEmpty(Resource-By Pool].[Resource-By Pool].CurrentMember)". But I would have thought including the Agency dimension in the row axis would have done a similar thing (unless agency &[0] has some other label)

|||

Hello Darren,

Thank you for your response. You are correct that I want the aggregate of the Hours for a Resource, but also want the value of the Agency dimension to be displayed along, BUT as a measure.

The MDX you provided, (with the cross join on Row Axis), certainly works. But alas I need the AGENCY to be a measure on the Column axis.

The reason is that our enterprise application has a customizable Portfolio management component (that uses Analysis Server) wherein Users can add/remove a set of predefined measures to their view. Very much like how the Cube browser does, but with limited functionality such that only predefined measures can be dropped on to the Column axis. And the ROWS are limited by Time dimension. The application generates the MDX based on the set of measures provided. Hence we need each measure to be declared separately as 'CREATE MEMBER CURRENTCUBE.Measures'.

Also the switch to using "NOT IsEmpty" instead of "NOT null" check in the definition of the Agency measure did not make any difference. The measure value still returns as null for each row.

The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue.

But I see that when I do a similar thing with the "Cube Browser", we get the correct result. (Agency is declared as a calculated measure on the cube)

Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded

Any help will be highly appreciated as the porting of the application to AS2005 has stalled because of this issue

Regards,

Mehernosh

|||

Mehernosh Vadiwala wrote:

The funny thing is that this query WORKs in AS2000. But as we were porting our application to use AS2005 we ran into this issue.

You did not mention that this used to run under AS2000, on double checking the calc I noticed that you are referencing the [Resource-By Pool].[Resource-By Pool] attribute hierarchy. At the attribute hierarchy level there is usually a default "All" member so the count will always be 1. If you add an extra [Resouce-By Pool] reference to specify just the level in the attribute hierarchy with the actual members.

CREATE MEMBER CURRENTCUBE.Measures.[Agency] AS

'IIF(NOT([Resource-By Pool].[Resource-By Pool].CurrentMember IS NULL) AND IsLeaf([Resource-By Pool].[Resource-By Pool].CurrentMember),

IIF(NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Pool].&[].&[0]},{[Resource-By Pool].[Resource-By Pool].[Resource-By Pool].CurrentMember}).count > 0,

"",NONEMPTYCROSSJOIN({[Agency-By Pool].[Agency Name].members},{[Resource-By Pool].[Resource-By Pool].CurrentMember}).item(0).item(0).name),"")', SOLVE_ORDER = 5000;

Mehernosh Vadiwala wrote:

Hence I wanted to see the MDX query that is generated by the Cube browser. Should I listen on any particular port to get the MDX that is sent to the Analysis Server. Is the traffic encoded

Yes, the traffic is encoded, but you can use SQL Profiler to connect to SSAS in 2005 and see the begin and end query events which have most of the MDX, some of the cube browser controls create named sets within the session, so you might need to assemble the MDX from a couple of trace statements.

|||

Darren,

This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember )

But that was the only change I made to the Query.

Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member.

======= Not Working =================================

WITH

MEMBER [Time-By Calendar].[Time-By Calendar].[Selected Time] AS '

AGGREGATE({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})

', SOLVE_ORDER = 5000, SCOPE_ISOLATION=CUBE

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE [Time-By Calendar].[Time-By Calendar].[Selected Time]

======= WORKING =================================

SELECT DISTINCT( {[Measures].[Available Hours], [Measures].[Agency] ) ON AXIS(0) ,

DISTINCT( {[Resource-By Pool].[Resource Name].members} ) on AXIS(1)

FROM [RESOURCE SUMMARY]

WHERE ({[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 1].[January].[1]:[Time-By Calendar].[Time-By Calendar].[Year].&[2007].[Quarter 3].[August].[5})

=================================================

Any insight on why this works and not the other ?

Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information.

Could this be the reason? Is there some documentation on this ?

Mehernosh

|||

Mehernosh Vadiwala wrote:

This query used to work in AS2000, but I had already ported it for AS2005. i.e. I had already added the additional hierarchy attribute. (If you notice, I already have [Resource-By Pool].[Resource-By Pool].currentMember in the query I gave above. The SQL2000 version had only [Resource-By Pool].currentMember )

Sorry, you're right the formula has already been adjusted correctly.

Mehernosh Vadiwala wrote:

Anyway I found the solution. All I had to do was not declare the slicer axis (WHERE clause) in place, instead of with a separate Member.

Yeah, that was what I suggested in my first response.

Mehernosh Vadiwala wrote:

Any insight on why this works and not the other ?

The only reason I could think that this might have issues would be if you [available hours] measure is calculated and relies on having a [Time-by Calendar] context. A cacluated aggregate in the query will not supply such a context and the formula would calculate as if it were at the all level. The Aggregate function is meant to calculate early in the solve order, but your setting of the solve order may be overriding this.

Mehernosh Vadiwala wrote:

Also I have a bigger issue coming up with "LookupCube" function. None of our calculated Measures that have the LookupCube function in them return any results now. In the documentation, there is just one line, stating that the implementation of LookupCube has changed. There is no additional information.

I don't use LookupCube any more I use multiple measure groups, possibly linking them in if they are shared across more than one cube.