Help with Grouping in Script

[pkelly]

Hi...

I have an SQL table which has multiple entries for accounts which equal system login dates with the dates.

I want to create a QVD table from this table detailing the last lagin date for each account (this is then going to be appended to the master account table which has one entry per account)

The code I am using (with no success) is...

TempAccessLogPage01:

LOAD

UPPER("account") AS account,

MAX(creationdate) AS creationdate;

SQL SELECT

account,

creationdate

FROM PublicWebData.dbo."AccessLog_tbl"

GROUP BY account

WHERE success = 1

AND internaladdress = 0

AND impersonate = 0;

Any help on where I am going wrong greatly appreciated...

[hector · Accepted solution]

Hi, you forgot the group by in the load, try this:

LOAD
UPPER("account") AS account,
MAX(creationdate) AS creationdate
group by
account;
SQL SELECT....

rgds

[pkelly]

Thanks Hector

Wrongly thought grouping had to be done on sql side...

Regards

Paul

[hector]

Hi, it can be done in the sql side, but the sintax is this:

TempAccessLogPage01:
LOAD
account,
creationdate;
SQL SELECT
account,
max(creationdate) as creationdate
FROM PublicWebData.dbo."AccessLog_tbl"
GROUP BY account
WHERE success = 1
AND internaladdress = 0
AND impersonate = 0

with this way, the SQL Server does the job of grouping instead of QV.

rgds