Latest information acquisition place

Where can I obtain the latest information on a Data Mining function?Books on line has a lot of information. You can also find a lot of information at www.sqlserverdatamining.com. Later this month (9/26) the book "Data Mining with SQL Server 2005" by Zhaohui Tang and me will be released as well.

Thanks
-Jamie MacLennan
SQL Server Data Mining
SQL Server Analysis Servicessql

LastPeriods() Function producing incorrect results

I am using the LastPeriods Function to SUM the Last 12 periods at a month level:

MEMBER [Measures].[Last 12 Months] As
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].[2006].[Q4-06].&[December]), [Measures].[Total TEUS]) '

This should equal out to be [Time].[Calendar Year].[Year].[2006] but for some reason, the totals are different.

I thought it might be an issue with solve order so I took all other calculations out and it is still incorrect.
I am not sure what to look for now in fixing this problem. Is my syntax structure wrong here? Hopefully it is something small I am missing here.

Regards,

-Troy

What is the aggregation function for [Measures].[Total TEUS] - is it a "sum" measure?|||

Deepak,

Actually, this issue was a user error on my part. Turns out I was comparing it with a different Time Dimension.

The numbers are actually coming out correctly. Sorry for that. The problem I am seeing now is when using this member and a SET for the TopCount() function.

For Example:

WITH

MEMBER [Measures].[LastPeriod] AS
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].&[2006].&[Q4-06].&[December]), [Measures].[Total TEUS]) '

SET [TopShipline] AS
'NONEMPTY({TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].CurrentMember)})'

SELECT {[Measures].[LastPeriod]} ON COLUMNS,
[TopShipline] ON ROWS
From [Account Level Aggregates]
WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])

The results are as follows:

LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9

ShiplineA should not be in the results here. If I change to TopCount count to 6. The result is correct, minus ShiplineA.

LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9
ShiplineF 1,660.0

TopCount() for other dimensions that use this time dimension have similar results. This is only when using LastPeriods().

If I use say:

SET [TopPorts] AS
'NONEMPTY({TOPCOUNT([US Port].[US Port Name].Children, 5, [Measures].CurrentMember)})'

LastPeriod
PortA 2,690.7
PortB 5,036.4
PortC 6,124.4
PortD 5,542.2
PortE 513.7

In this case, PortE does not belong and the sort order of my measure is not as it should be. Again, changing the count to 6 from five brings the correct fifth Port, PortF

|||

Let me add one last piece to this and this is this apears to only be an issue when my [Domestic Company] dimension is used in the where clause:

WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])

If I use a different dimension in the WHERE clause, the results are correct.

This is a somewhat large, flat dimension with about 600,000 members.

|||

Keeping in mind that the named set will be computed only in the context of the "where" slicer axis, which doesn't include a measure, you should explicitly specify [Total TEUS], like:

SET [TopShipline] AS
'NONEMPTY(TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].[Total TEUS]))'

|||

Thanks for reply. I have resolved this. What I needed to do was use the Measure for my TopCount() function that I created as my calculated member.

MEMBER [Measures].[Last 12 Months] AS

'..........'

So, instead of:

SET [TopShipline] AS

'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Total TEUS])})'

I needed to use:

SET [TopShipline] AS

'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Last 12 Months])})'

LastPeriods Function

Does the LastPeriods function have to be used with the Aggregate function when creating a KPI expression?

Aggregate({[Incident].[Incident].&[1]} *

{LastPeriods(4, [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00])},

[Measures].[TOTAL])

If so, is it better to just do:

Aggregate({[Incident].[Incident].&[1]} *

{[Date].[Year - Quarter].[Quarter].&[2005-01-01T00:00:00] : [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]},

[Measures].[TOTAL])

Whether Aggregate() should be used depends on the definition of the KPI - what are the business rules for it?

Also, {[Date].[Year - Quarter].[Quarter].&[2005-01-01T00:00:00] : [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]} is probably equivalent to LastPeriods(5, [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]) - so should 4 or 5 quarters be included?

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?

LastNonEmpty vs MDX Script solve order

Hi,

I'm using the Account Intelligence and some account members use the aggregation function LastNonEmpty.

Witch evaluates before the LastNonEmpty function or my MDX Script that I put in the calculation inside the cube?

If my MDX Script change some values only in the the leaves of the time dimension in one account (taht uses LastNonEmpty function). What will be evaluate first? My MDX Script and after the LastNonEmpty function or the oposite?

Handerson

LastNonEmpty is applied to the members above the leaves of Time dimension by aggregation the leaves through LastNonEmpty. Therefore if you will apply some calculation to the leaves of Time - it will be done before the LastNonEmpty aggregation.sql

LastNonEmpty and AverageOfChildren

Hello,

we are using SQL 2005 with SP1 and we have quite small cube (<1GB).

We have a couple of measures with aggregate function AverageOfChildren or LastNonEmpty.

End users have very poor performances while browsing cube using these measures.

When we replace agg. function with SUM, for example, performances become normal.

I saw simillar discusion on OLAP forum, but I did not understand what is the best recommendation for this situation?

Is this known issue, and how it can be avoided?

Best regards

Borko

Borko,

What's your aggregation strategy for this measure group? Queries to semi-additive measures are resolved by initially going down to granularity for the measure group's time dimension and subsequently computing the aggregate. Because of this, it's important to insure that you have an aggregation that includes the granularity attribute. If not already there, you can generally force the attribute to be included by setting the AggregationUsage property to "Full" in the cube editor. However, since this setting applies across all measure groups, you may want to set it, design aggregations for this measure group, and then rest the value.

I'm not positive that this will solve your issue, but this is where I would start.

-rob

Last() Function?

Greetings,
I'm having some trouble to create a report. In this report I'm supposed to get the last row for each group of customers, ordered by date. Let me explain you a bit further using an example:
The table contains these fields, amongst others: CustomerID, Car, LastVisit.
So, for each customer there are many rows, each one with a different value in LastVisit. What I need to get, and before I started it looked quite easy, is a recordset with the last visit of each customer, given a specific car. For example, the last visit of each customer that has a Ford.
Now, I think there's a function LAST() in Access that actually gets the last row in a group by. That would be great, because the only thing I would have to do is grouping by CustomerID and getting the said last row, but unfortunatelly looks like SQL Server 2000 doesn't support that function.
The question is, how can I get that last row in each group? I've already tried using cursors in a stored procedure and temporary tables but I'm afraid I need a less time consuming solution as I'm dealing with more than 1.5 million rows.
If that's not possible, I guess I could "mark" the rows that I need and filter it later, but I'm not sure how to do it either...
If anyone around can give me some advice I would really appreciate it. I apologize if I was not very clear. I'll try to clarify it if you have doubts.
Thanks in advance,
Ricardo Lopez.

Two thoughts:

1. Try using MIN and MAX (whichever gives the correct value in this case) it will work on values which are non-numeric aswell.

2. Otherwise, use top with an order by, and use desc or asc as appropriate, and write a series of sql statments to get each group, and then combine using the UNION ALL operator.

|||

This will get you the results. It is using a derived table to get the last visit date for a customer id and then joining it with the same table to get the car of that visit.

select v.CustomerID, v.Car
from Visits v
inner join (select CustomerID as CustomerID, max(LastVisit) as LastVisit
from Visits
group by CustomerID) vlast
on (vlast.CustomerID = v.CustomerID and vlast.LastVisit = v.LastVisit)


I have assumed that LastVisit is a datetime column.

Last Year Month to Date Function

I have been spoiled by some report writing tools that have intrinsic
functions like Last Year Month-to-date. I'm looking for a way to emulate
this in SQL Server now with my fields that are date/time.

I'm thinking I need to develop a user defined function to accept a date
input parameter, but I don't know where to start.

Help/Examples appreciated.

Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***Hi, Frank

If you have a date and you want to obtain the corresponding date in the
previous year, you can use the DATEADD function, like this:
DATEADD(year, -1, @.TheDate). For more informations, see:
http://msdn.microsoft.com/library/e..._fa-fz_2c1f.asp

Razvan

last is not a recognised function name

Hi all,
ive tried to run a query in a view and am getting this error message:
'last' is not a recognised function name
i was sure that last was used in sql, how can i get the same result if it wont accept 'last'?
greguse MAX

LAST is a microsoft access function|||oh yeah!! forgot the syntax changed-

cheers

greg

Last Function in SQL Server ?

Hi there,
--Access Query that Doesn't work in SQL
SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
FROM Employees
--Access Query that work with SQL if we have an EmployeeID
SELECT TOP 1 FirstName,LastName
FROM Employees
ORDER BY EmployeeID DESC
--SQL only if we don't have an EmployeeID
Declare @.FirstName varchar(20)
Declare @.LastName varchar(20)
SELECT @.FirstName = FirstName, @.LastName = LastName
from Employees
SELECT @.FirstName, @.LastName
--Test1 to make sure that we looking for last record
Select * FROM Employees
What is the equivalent Last access function in T-SQL
Thanks
Oded Dror
Email: odeddror@.cox.netA table in SQL has no inherent logical order so there is no "first" or
"last" row. How do you want to define which row should be the last?
I believe that if you don't specify ORDER BY then Access just picks the
last record based on insertion order. However, SQL Server doesn't
preserve the information about insertion order unless you explicitly
create a column or columns to record that information.
For example, if you have a modification_date in your table you could
take the latest date:
SELECT first_name, last_name
FROM Employees
WHERE modification_date =
(SELECT MAX(modification_date)
FROM Employees)
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--|||First or Last concept can be applied just to an ordered set.
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by employeeid desc
-- first
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname, lastname
-- last
select top 1 @.fn = firstname, @.ln = lastname
from dbo.employees
order by firstname desc, lastname desc
AMB
"Oded Dror" wrote:

> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>
>|||The concept of "last" does not exist in SQL nor SQL Server as SQL thinks in
terms of sets. Last must derived from the data on which you are querying usi
ng
things like alphabetic sorting or datetime values. Access is able to provide
this functionality because Access data is stored sequentially in the order i
n
which it was entered. No such guarantee exists in most database products lik
e
SQL Server or Oracle.
Thomas
"Oded Dror" <odeddror@.cox.net> wrote in message
news:%23gtUM9sYFHA.2996@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> --Access Query that Doesn't work in SQL
> SELECT Last([FirstName]) AS First_Name, Last([LastName]) AS Last_Name
> FROM Employees
> --Access Query that work with SQL if we have an EmployeeID
> SELECT TOP 1 FirstName,LastName
> FROM Employees
> ORDER BY EmployeeID DESC
> --SQL only if we don't have an EmployeeID
> Declare @.FirstName varchar(20)
> Declare @.LastName varchar(20)
> SELECT @.FirstName = FirstName, @.LastName = LastName
> from Employees
> SELECT @.FirstName, @.LastName
> --Test1 to make sure that we looking for last record
> Select * FROM Employees
> What is the equivalent Last access function in T-SQL
> Thanks
> Oded Dror
> Email: odeddror@.cox.net
>

Last Function

Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature?

Example:

Item_Code Time
123456 11:40
123456 11:41
123456 11:42

By grouping on item code and using the last function on the Time column, the third record would be returned.

Item_Code Time
123456 11:42

All, help is appreciated.Last is not as reliable as you may think, and SQL Server doesn't have it. Try Max instead.|||well, i really cannot use max because it will give me a max each cloumn. For example:

Item Date Time
12345 7/10/06 11:21
12345 7/10/06 11:45
12345 7/12/06 09:45

If i group on item and use max for date and time i would get

Item Date Time
12345 7/12/06 11:45

I basically need to find a way o get the last record of a set.

Maybe there is a better way to approach this.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.

I think that would probbaly be my best bet. Thanks for all your help.|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:

http://support.microsoft.com/kb/208190/en-us|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:

http://support.microsoft.com/kb/208190/en-us

Sweet, I really appreciate all the help and notes.

Last Function

SQL Server has no Last() and Max() pulls the Max not the last. Is there a way to pull the Last payment amount with the date of that payment, in SQL Server??

tblClients - ClientID (PK)
tblPayments - PaymentID (PK)

Thanks for any help,You can add a timestamp column to the table and then use Max(TimestampCol) to return the last inserted record (which i assume you want to return), e.g.

SELECT
 *
FROM
 myTable
WHERE
 myTable.TimestampCol = (SELECT Max(TimestampCol) FROM myTable)

|||You're going to have to date stamp your transactions.

LAST FUNCTION

I have a sql database that I am trying to make this query work. The
problem is that I can't get the right info out, right'
So, I exported two tables to an Access database, and this is my query
that works beautifully.. Question is, I don't want to keep exporting
this info everytime, I want to make this query in SQL and leave it there
or use it for SQL Reporting Services..
Note that the table names changes from Access to SQL, but I have made
all those mods already.. The problem is with the "LAST" function on line
5 from the Access Query. and this is the error I'm getting when
applying to SQL:
Server: Msg 195, Level 15, State 10, Line 1
'Last' is not a recognized function name.
ACCESS QUERY:
SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
AS LastLogin, Results.UserID
FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
GROUP BY Users.last_name, Users.first_name, Results.UserID
HAVING (((Last(Results.EventStartDtTm))<Date()-90))
ORDER BY Users.last_name, Users.first_name;
========================================
===========================
SQL Query::
SELECT prod9eps.portal.Users.last_name, rod9eps.portal.Users.first_name,
Last(prod9phy.dbo.UtilLogRecord.EventStartDtTm) AS LastLogin,
prod9phy.dbo.UtilLogRecord.UserID FROM prod9phy INNER JOIN
prod9eps.portal.Users ON prod9phy.dbo.UtilLogRecord.UserID =
prod9eps.portal.Users.user_name
GROUP BY prod9eps.portal.Users.last_name,
prod9eps.portal.Users.first_name, prod9phy.dbo.UtilLogRecord.UserID
HAVING (((Last(prod9phy.dbo.UtilLogRecord.EventStartDtTm))<Date()-90))
ORDER BY prod9eps.portal.Users.last_name, prod9eps.portal.Users.first_name;Please post DDL, sample data and required end results, otherwise we can only
guess.
http://www.aspfaq.com/etiquette.asp?id=5006
The Access version of your query doesn't look like a well-defined result.
The ORDER BY list is what should drive the result of the LAST function in
Access but in your case the ORDER BY columns are also grouped so the result
of the LAST function will be determined by the physical record order in the
table. Since you haven't specified what that order is it's not clear just
what result you want.
In SQL Server there is no direct equivalent of the Access FIRST and LAST
functions. A table in SQL is not logically ordered so there is no first or
last row - you have to specify the column/expression that determines which
rows you want.
Here's an example taken from the Pubs database giving the first and last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in which
they were entered into a table then you need to add a DATETIME column to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||David, thank you for your explanation, but there is a DATETIME field.
The "Results.EventStartDtTm" is a DATETIME
I can't use MAX because there is number, the same goes for MIN. 99% of
the data is text.. Except for the "Results.EventStartDtTm" that is
(01/01/00 00:00:00)
Again, my query works great in Access but not in SQL.
David Portas wrote:
> Please post DDL, sample data and required end results, otherwise we can on
ly
> guess.
> http://www.aspfaq.com/etiquette.asp?id=5006
> The Access version of your query doesn't look like a well-defined result.
> The ORDER BY list is what should drive the result of the LAST function in
> Access but in your case the ORDER BY columns are also grouped so the resul
t
> of the LAST function will be determined by the physical record order in th
e
> table. Since you haven't specified what that order is it's not clear just
> what result you want.
> In SQL Server there is no direct equivalent of the Access FIRST and LAST
> functions. A table in SQL is not logically ordered so there is no first or
> last row - you have to specify the column/expression that determines which
> rows you want.
> Here's an example taken from the Pubs database giving the first and last
> title based on the MIN and MAX values of Title_id.
> SELECT
> (SELECT title
> FROM Titles
> WHERE title_id
> = (SELECT MIN(title_id)
> FROM Titles)) AS First_Title,
> (SELECT title
> FROM Titles
> WHERE title_id
> = (SELECT MAX(title_id)
> FROM Titles)) AS Last_Title
> A common requirement is to extract a single row of a group based on some
> criteria. The query below retrieves values from the "Last" (maximum
> title_id) row for each Type in the Titles table.
> SELECT T1.type, T1.title_id, T1.title, T1.price
> FROM titles AS T1
> JOIN
> (SELECT MAX(title_id) AS title_id
> FROM titles
> GROUP BY type) AS T2
> ON T1.title_id = T2.title_id
> If you need to retrieve the first or last rows based on the order in which
> they were entered into a table then you need to add a DATETIME column to t
he
> table to record the entry date:
> date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
>|||> I can't use MAX because there is number
Please explain that statement. Sounds to me like MAX is exactly what you
want but if you won't give us a better description of your problem then how
can we help you? (see the link I posted previously).
David Portas
SQL Server MVP
--|||Ok, here we go..
I have the following file, of course, there is plenty more entries than
this, but just sample..
Tablename = UtilLogRecord
FieldNames::: Event,UserID,EventStartDtTm
Sign-In,aabraham,08/26/04 11:49:58
Sign-In,aabraham,08/26/04 12:13:28
Sign-In,aabraham,08/27/04 10:16:16
Sign-In,aabraham,08/27/04 10:17:53
Sign-In,aabraham,08/27/04 10:12:31
Sign-In,aabraham,08/27/04 11:33:34
Sign-In,aabraham,08/27/04 11:58:09
Sign-In,aabraham,08/27/04 11:59:24
Sign-In,aabraham,08/27/04 12:00:05
Sign-In,aabraham,08/27/04 13:11:58
Sign-In,aabraham,08/27/04 13:38:00
Sign-In,aabraham,08/27/04 13:39:11
Sign-In,aabraham,08/27/04 13:36:27
I also have the following:
Tablename = Users
FieldNames:: user_name,first_name,last_name,role
fherdoci,Filiberto,Herdocia,404
mromano,Mary,Romano,404
sparker,Susan,Parker,506
ltidwell,Liana,Tidwell,506
When I do:
============================
SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
AS LastLogin, Results.UserID
FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
GROUP BY Users.last_name, Users.first_name, Results.UserID
HAVING (((Last(Results.EventStartDtTm))<Date()-90))
ORDER BY Users.last_name, Users.first_name;
=========================
I get
Sign-In,aabraham,08/27/04 13:36:27 (which is the last time this user
logged in to the system 08/27/04)
In MSSQL, I tried the MAX and it does not work at all. If this were
you, how would you write that statement..
Again, it works in Access and I can keep on doing it that way, but I
rather not.
./ray medina
David Portas wrote:
>
> Please explain that statement. Sounds to me like MAX is exactly what you
> want but if you won't give us a better description of your problem then ho
w
> can we help you? (see the link I posted previously).
>|||Ray,
Your code does not match your data,
and your answer does not give the last
time the user logged in. The most recent
login is
Sign-In,aabraham,08/27/04 13:39:11
Your result is wrong, if you want the
most recent login, but using MAX should
work.
The information you posted is not much help.
Your query refers to a table called [Results],
which you don't give the structure of, and it
joins UserID to user_name, which for this
sample data gives no match. You say your
query returns Sign-In,aabraham,08/27/04 13:36:27,
and you say this is the most recent login, but it
is not, and you don't give data that shows
how the query returns this.
It is also not helpful at all for you to say
"I tried the MAX and it does not work at all."
without explaining at all what you mean. When
you replace LAST with MAX, what doesn't work?
Wrong answers, no results, computer turns into
a piece of burned toast? Please give more information
in a form that is useful to someone trying to help.
If you learn to present your problems clearly and
thoroughly, you may soon discover that you have
fewer problems, too.
Steve Kass
Drew University
Ray Medina wrote:
> Ok, here we go..
> I have the following file, of course, there is plenty more entries
> than this, but just sample..
> Tablename = UtilLogRecord
> FieldNames::: Event,UserID,EventStartDtTm
> Sign-In,aabraham,08/26/04 11:49:58
> Sign-In,aabraham,08/26/04 12:13:28
> Sign-In,aabraham,08/27/04 10:16:16
> Sign-In,aabraham,08/27/04 10:17:53
> Sign-In,aabraham,08/27/04 10:12:31
> Sign-In,aabraham,08/27/04 11:33:34
> Sign-In,aabraham,08/27/04 11:58:09
> Sign-In,aabraham,08/27/04 11:59:24
> Sign-In,aabraham,08/27/04 12:00:05
> Sign-In,aabraham,08/27/04 13:11:58
> Sign-In,aabraham,08/27/04 13:38:00
> Sign-In,aabraham,08/27/04 13:39:11
> Sign-In,aabraham,08/27/04 13:36:27
> I also have the following:
> Tablename = Users
> FieldNames:: user_name,first_name,last_name,role
> fherdoci,Filiberto,Herdocia,404
> mromano,Mary,Romano,404
> sparker,Susan,Parker,506
> ltidwell,Liana,Tidwell,506
> When I do:
> ============================
> SELECT Users.last_name, Users.first_name, Last(Results.EventStartDtTm)
> AS LastLogin, Results.UserID
> FROM Results INNER JOIN Users ON Results.UserID = Users.user_name
> GROUP BY Users.last_name, Users.first_name, Results.UserID
> HAVING (((Last(Results.EventStartDtTm))<Date()-90))
> ORDER BY Users.last_name, Users.first_name;
> =========================
> I get
> Sign-In,aabraham,08/27/04 13:36:27 (which is the last time this user
> logged in to the system 08/27/04)
> In MSSQL, I tried the MAX and it does not work at all. If this were
> you, how would you write that statement..
> Again, it works in Access and I can keep on doing it that way, but I
> rather not.
> ./ray medina
> David Portas wrote:
>

Last Day of Month Function

Hello all,
I am trying to avoid using wordy logic to get date values meeting criteria
for the last day of the each month in a date field. Is anyone aware of a
last day of the month function? I know that Oracle offers this.
Thanks for your help.
J.Hi,
There is no direct function in SQL Server to get last day of the month. You
will have to use Datepart and DateAdd function to achive this.
Thanks
Hari
SQL Server MVP
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||If you use SQL Server 2000, you can write your own LastDayOfMonth(month) UDF
easily.
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||Unfortunately you have to do it yourself... Easiest way is to take one day
prior to first day of next month
Declare @.D DateTime
Set @.D = getdate()
Select DateAdd(day, -1, convert(varchar(6),
DateAdd(month,1,@.D),112) + '01')
"j" wrote:

> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||There is no such function directly, but you can use the following:
DECLARE @.dt SMALLDATETIME
SET @.dt = GETDATE()
SELECT DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd,1-DAY(@.dt), @.dt)))
Or you can use a calendar table: http://www.aspfaq.com/show.asp?id=2519
Jacco Schalkwijk
SQL Server MVP
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||Use a Calender table.
Or use dateadd and datepart:
select dateadd(d, -datepart(d, getdate()), dateadd(m, 1, getdate()))
"j" <j@.discussions.microsoft.com> wrote in message
news:82F8CA8D-18EF-476B-8006-A14C0DE20586@.microsoft.com...
> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.|||Thank you everyone so very much!! ;->
"j" wrote:

> Hello all,
> I am trying to avoid using wordy logic to get date values meeting criteria
> for the last day of the each month in a date field. Is anyone aware of a
> last day of the month function? I know that Oracle offers this.
> Thanks for your help.
> J.

Last Aggregate function

Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.
How to replace this functionality?
Thanks.Hi

You can get this using row_number with an OVER clause in 2005. Check it out in BoL and see how you get on. HINT - you will need to order descending.

HTH|||Thanks for the quick response. I however am not too sure how to do it.

Let's say I have a query like this:

select
last(firstname),
lastname

from
users

group by
lastname

but the last() function does not work, so how to do it using your method?

I am quite new to the SQL so sorry for asking obvious things maybe :-)|||Ok, I think the answer is:

with A as (
select
row_number()
over (partition by lastname order by firstname desc) as 'row'
firstname
lastname
from
users
)
select
firstname
lastname
from
A
where
row = 1|||Now I am not sure that returns what you want. Does is return the same as this?
SELECT lastname
, MAX(firstname) AS firstname
FROM dbo.A
GROUP BY lastname

Largest string for execute

Hello there
I have function that build sring to execute.
The function create string on 6000 characters size.
Because it's size i had to build it with two strings
How can i execute this size of string?
any help would be usefulYou should be able to execute strings like this:
declare @.sql1 varchar(8000),@.sql2 varchar(8000)
set @.sql1=dbo.your_udf(para1)
set @.sql2=dbo.your_udf(para2)
exec(@.sql1+@.sql2)
-oj
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:OKCY8OSAFHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello there
> I have function that build sring to execute.
> The function create string on 6000 characters size.
> Because it's size i had to build it with two strings
> How can i execute this size of string?
> any help would be useful
>