Hi Guys, I have trouble with using Advance Query, Below is a piece of SQL Query I want to achieve, and should be workable with MS SQL Server, but it can't be done in Service Studio's Advance Query :(

Can anyone translate my code into workable Advance query?

@firstweek DATETIME, @lastweek DATETIME, @numberweek

SET @firstweek = 'SELECT TOP 1 [sdate] FROM {OrderFile} ORDER BY [sdate] ASC'

SET @lastweek = 'SELECT TOP 1 [sdate ] FROM {OrderFile} ORDER BY [sdate] DESC'

SET @numberweek = (@lastweek - @firstweek) / 7

after getting the number week I want to output that to an expression,text, on the page
Hi David,

The output part you just need to create a structure with 1 attribute of the desired type and add it to the query.

As for the query in T-SQL format, a direct translation of that would be:
SELECT ((SELECT TOP 1 [sdate] FROM {OrderFile} ORDER BY [sdate] DESC) - (SELECT TOP 1 [sdate] FROM {OrderFile} ORDER BY [sdate] ASC)) / 7

But I tried your original code in SQL Management Studio and it gives me this error:
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

So you need to fix that first. But you should get the idea on how to do the translation.

I think what you want is this:

SELECT DATEDIFF(WEEK, min([sdate]), max([sdate]))
FROM {OrderFile}

Or this if you want a decimal output (instead of the rounded weeks)

SELECT DATEDIFF(Day, min([sdate]), max([sdate])) / 7.0
FROM {OrderFile}

(DATEDIFF is to fix your query conversions, the min/max is just a optimization)

João Rosado
Hi Joao, once again, you solve my question :)

However, it gave me an error


Unexpected SQL
Unexpected '1' in SQL statement of GetTotalWeeks."

Its highlighting at the first Select "TOP 1" ....

Thats because TOP 1 doesn't exist in oracle. You can change the DB validation type to SQL Server if you select the eSpace in the tree.

Did you get the correct results you wanted with that query? Because I didnt get the "/ 7" part to work on my pc (thats why I wrote the DATEDIFF versions)