Last time table was updated?

Not sure if this is right place to post this but hopefully someone can help me anyways. The problem I have is the database I am using is populated by dozens of tables, my program doesn't create these tables but I need to find an easy way to determine when the last INSERT was done on these tables to see which one is the currently active table. I can't seem to find in MSSQL a last updated datetimestap on the tables, is there a way to easily do this, I don't think open each and every table and than finding the timestamp on the last entry is going to work these tables can get rather large and there could be many of them I think it will take too long to accomplish that way.

Hi Mitch,

Without there being a timestamp or datetime column on the actual table, there's no way for you to dynamically determine the date/time a row was inserted.

You can, however query the system catalog to determine the date the table was created.

As you mention that the tables themselves are created by an application, obviously you'd be hesitant to make any changes to the existing schema; you could, however create generic triggers on each of the tables in question that would in turn insert a flag into a control table to indicate the current "active" table"

CREATE TRIGGER tr_TableHere_ins
ON TableSourceA
FOR INSERT
AS
INSERT INTO ControlTable (InsertDate, TableSource)
VALUES(GetDate(), 'TableSourceA' )

Or Similar...

Cheers,
Rob

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

sql

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help

Last Populated Quarter''s Data

Currently I have the following calculation to get the closing Inventory balance for a year

Code Snippet

([Measures].[Total Square Area], ClosingPeriod([Time].[Quarter].[Quarter]))

But for 2007 for example this is Empty as Q4 has no data.

I know there must be a simple way to return the Last NON EMPTY Quarter's balance but I am having a fog on it....

Any help or link to an example would be great!

Will

Maybe this will work:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmpty([Time].[Quarter].[Quarter],

[Measures].[Total Square Area])).Item(0))

|||

Hi Deepak,

Unfortunately that returns an error

"Token Error, Syntax is not valid"

I figured it must be close though

|||

Well, here's a sample Adventure Works query, based on the above code snippet, which returns the last populated Quarter Exchange Rate for DeutscheMarks in FY2002 - a value of .49 in FY2002Q2 (after which the Euro replaced the DM):

Code Snippet

With

Member [Measures].[QuarterExchange] as

([Measures].[Average Rate],

Tail(NonEmpty(

[Date].[Fiscal Quarter of Year].[Fiscal Quarter of Year],

[Measures].[Average Rate])).Item(0)),

FORMAT_STRING = '#.00'

select

{[Measures].[QuarterExchange]} on 0

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002],

[Destination Currency].[Destination Currency].&[Deutsche Mark])

-

QuarterExchange
.49

|||I can run that in 2005 against AdventureWorks, however I need this to work in SSAS 2000. It does not seem to like NONEMPTY in 2000 - is this new & was there a workaround to get the same result prior to 2005 SSAS?|||

Hi Will,

Yes, this is new in AS 2005 - in AS 2000, you could try NonEmptyCrossJoin(), like:

Code Snippet

([Measures].[Total Square Area],

Tail(NonEmptyCrossJoin([Time].[Quarter].Members,

{[Measures].[Total Square Area]}, 1)).Item(0).Item(0))

For future reference, you might want to mention explicitly that you're asking a question specific to AS 2000, given that SQL Server 2005 was launched nearly 2 years ago, and we're on the threshold of SQL Server 2008

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

|||

Hi,

Thanks - sorry about the confusion with versions...

I was using 2005 at my old place. No I am 2005 for SSIS & SSRS but they won't budge from 2000 for AS just yet so it's a real pain & I tend to forget to mention it.

Sorry, & thanks for the help