Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm using this code for now, and it works just fine :
[WorkplaceStateHistoryTemp]:
SQL SELECT
"workplace",
"startDate",
"stateType",
"endDate",
"imputationDate" as [Keydate]
FROM "Serv"."dbo"."WorkplaceStateHistory";
[WorkplaceStateHistory]:
Load
[workplace],
Date([Keydate]) as [Keydate],
[stateType],
Sum(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration]
Resident [WorkplaceStateHistoryTemp]
group by workplace, stateType, [Keydate];
Drop table [WorkplaceStateHistoryTemp];
But the loading time is huge, since I'm loading a massive database THEN using it again as a resident table before dropping it.
I found this way of doing the loading that saves me 20% of loading time, but I can't figure put how to use the group by:
[WorkplaceStateHistory]:
Load
[workplace],
imputationDate as [Keydate],
[stateType],
SUM(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration];
Select
"workplace",
"startDate",
"stateType",
"endDate",
"imputationDate"
FROM "Serv"."dbo"."WorkplaceStateHistory"
group by workplace, stateType, imputation;
How am I suppose to do the group by in the second solution ? Is there even a faster way to get what I'm doing ?
Well, I guess I got your point now. Try like:
[WorkplaceStateHistory]: Load [workplace], imputationDate as [Keydate], [stateType], SUM(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration] group by workplace, stateType, imputationDate; Select "workplace", "startDate", "stateType", "endDate", "imputationDate" FROM "Serv"."dbo"."WorkplaceStateHistory" ;
One alternative way you can try using sorting the table in resident load before grouping them, like suggested here:
It's worth a try, thanks!
But that means there's no way to do a group by without going for a resident table ?
Ok, so what's wrong in this script:
[WorkplaceStateHistory]:
Load
[workplace],
imputationDate as [Keydate],
[stateType],
SUM(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration];
Select
"workplace",
"startDate",
"stateType",
"endDate",
"imputationDate"
FROM "Serv"."dbo"."WorkplaceStateHistory"
group by workplace, stateType, imputationDate;
It sends me back an error message:
Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 8120, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'Serv.dbo.WorkplaceStateHistory.startDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Well, I guess I got your point now. Try like:
[WorkplaceStateHistory]: Load [workplace], imputationDate as [Keydate], [stateType], SUM(Interval([endDate]-[startDate],'hh:mm:ss')) as [Duration] group by workplace, stateType, imputationDate; Select "workplace", "startDate", "stateType", "endDate", "imputationDate" FROM "Serv"."dbo"."WorkplaceStateHistory" ;
Works perfectly!
I'm still struggling on how to order THEN group by, but I will probably get it soon.
Thanks a lot for the help!