latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

latest entry per distinct ID

This summary is not available. Please click here to view the post.

LastPeriods

Hello,

I am trying to retrieve the last 3 periods from the current month (ie Sept, Oct, Nov). I am thinking that LastPeriods function might work, but I am getting the following Error "The LASTPERIODS function expects a member expression for the 0 argument. A tuple set expression was used." Here's the code.

SELECT { LASTPERIODS (3, TAIL(nonempty([Week].[Week].[Week Name].Members)) ) } ON COLUMNS FROM [GMDSummary]

Maybe something like that ?

SELECT { LASTPERIODS (3, TAIL(nonempty([Week].[Week].[Week Name].Members)).Item(0).Item(0) ) } ON COLUMNS FROM [GMDSummary]

|||

Hello,

I ran into another issue as to I would like to order the [Week Name] by the Member Key. Currently, the LastPeriods() is returning "Week of 12/25/2006", "Week of 12/17/2006" instead of "Week of 1/22/2007" because it is order by Member Name instead I would like to order by Member Key. Can anyone help me with the code below so the LastPeriods() is indeed the last 3 week (order by Member Key) instead of by Member Name?

SELECT { LASTPERIODS (3, TAIL(nonempty([Week].[Week].[Week Name].Members)).Item(0).Item(0) ) } ON COLUMNS FROM [GMDSummary]

Much Appreciated!

-Lawrence

|||y cant we use LAG function. It is more easier right?

LastPeriods

Hello,

I am trying to retrieve the last 3 periods from the current month (ie Sept, Oct, Nov). I am thinking that LastPeriods function might work, but I am getting the following Error "The LASTPERIODS function expects a member expression for the 0 argument. A tuple set expression was used." Here's the code.

SELECT { LASTPERIODS (3, TAIL(nonempty([Week].[Week].[Week Name].Members)) ) } ON COLUMNS FROM [GMDSummary]

Maybe something like that ?

SELECT { LASTPERIODS (3, TAIL(nonempty([Week].[Week].[Week Name].Members)).Item(0).Item(0) ) } ON COLUMNS FROM [GMDSummary]

|||

Hello,

I ran into another issue as to I would like to order the [Week Name] by the Member Key. Currently, the LastPeriods() is returning "Week of 12/25/2006", "Week of 12/17/2006" instead of "Week of 1/22/2007" because it is order by Member Name instead I would like to order by Member Key. Can anyone help me with the code below so the LastPeriods() is indeed the last 3 week (order by Member Key) instead of by Member Name?

SELECT { LASTPERIODS (3, TAIL(nonempty([Week].[Week].[Week Name].Members)).Item(0).Item(0) ) } ON COLUMNS FROM [GMDSummary]

Much Appreciated!

-Lawrence

|||y cant we use LAG function. It is more easier right?

Last working day from last year!

Dear Friends,

How can I retrieve the last working day from the currentyear-1?

Thanks

In SQL, or an expression inside SSIS?|||

Not sure what this has got to do with SSIS.

Which country are you in? Because different countries have different public holidays around the end of the year and this obviously makes a difference.

-Jamie

|||

I need to create a new column with the last working day for each row in the datasource... as for example inside a derived column...

How can I do it?

And I have the problem about holidays... but could be resolved if I have a table wich indicate the holidays for this case in my country Portugal... or have to apply for all the cases using a key to each country...

could help me?

Thanks!!

|||How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column?
Help me!!|||

PedroCGD wrote:

How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column?
Help me!!

What is considered the last working day of the year? The last Friday?|||YES|||

This is usually a table-driven information item such as a time dimension table. The DIMTIME records would be flagged for weekdays.

As Jamie mentioned, holidays are a regional thing so you would join entries in a DIMTIME table against a local holiday table to derive a list of working days. Then you could query the join to find the last working day of a given year.

This is not a SSIS issue, but a TSQL or engine question.

|||

No Benda, forget the holidays....

I need to transform each row, to add a new column that for each date I need to get the Reference Rate that is the last day of the last year for the each row of the data source....

thanks

|||This assumes you have a VARIABLE containing the year. If you don't have that, and need to dynamically read the year, replace the variable here with DATEPART("yyyy",[your_date_field])

So you'd replace "(DT_WSTR,4)@.[User::Year]" with "DATEPART("yyyy",[your_date_field])" if that's the case.

DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) < 6 ? DATEADD("d",-1 * (DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) + 1),(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) : DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) > 6 ? DATEADD("d",-1,(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) : (DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])|||

Phil,

i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday...

My last working day of a week is friday not saturday! :-)

Where you define it in your code?

Thanks!

|||

PedroCGD wrote:

Phil,

i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday...

My last working day of a week is friday not saturday! :-)

Where you define it in your code?

Thanks!

That's a locale issue then... DATEPART("dw",[datefield]) == 6 for Fridays here in the US.

Never the less, you should be able to adjust the logic by playing around with it a bit. Try to read the expression I've built for you and work on it your own. It's pretty simple logic, really, despite the fact the expression looks complicated.|||your code in US give you the friday?|||

PedroCGD wrote:

your code in US give you the friday?

Yes, absolutely. I tested it for several years. 2006 = 12/29/2006 = Friday|||

your code for me gives me 2006 = 12/30/2006 = Saturday!

:-(

bbrrr.... :-(

last updated date

Hi,
I'm trying to retrieve data of previous day from my 'sales' table. Sales
table has:
order#, order date, cust#, sales, qty
I have a metadates table: one colummn
date
my script is
select sum(sales), sum(qty) from sales where order_date=(select top 1 date
from metadates order by date desc)
But I got time out all the time.
I'm considering the Christmas holiday, when I come back on Dec 28, the job
runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
Please help,
Thanks so much,
SarahPerhaps this will run faster:
declare @.prev_workday as datetime
select @.prev_workday = (select top 1 date from metadates order by date desc)
select sum(sales), sum(qty) from sales where order_date = @.prev_workday
So, if Dec 24 - 27 are not business days, then do not include them in the
metadates table.
"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
>|||"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
This may give you a better idea of what the date table should look like.
http://www.aspfaq.com/show.asp?id=2519sql

Last Transact-SQL Statement Executed

Hello All,
Please how can I retrieve/save the last Transact-SQL
statements executed against a database? Can I use a
trigger for this?
Thanks.Triggers work on tables and will only fire for data
updates not selects.
If all your access is via SPs then you could log in the SP.
You could log in the client if you are in control of that.
Otherwise the easiest way is to use the profiler to log
statements to a table or text file.
>--Original Message--
>Hello All,
>Please how can I retrieve/save the last Transact-SQL
>statements executed against a database? Can I use a
>trigger for this?
>Thanks.
>
>.
>

Last modified date of a stored procedure

Is it possible to retrieve the last modified date for Stored procedures without programming?
I only can find the the creation date in the sysobjects table.To my knowledge SQL Server does not maintain changes to stored procedures, only the last version and compilation date.

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}