1

The solution to this question might be simple, but I can't translate other posts about this topic into my own script.

I'm looking for a query to select the highest delivery time for each consignment number, since a consignment can have more than one delivery time's, because it can have more than one parcels.

I came up with this query, and it works fine when I'm using SQL server.

select 
    DELIVERYTIME 
from (
    select 
        h_parcel.CONSIGNMENT, S_PARCEL.DELIVERYTIME,
        (row_number() over(partition by h_parcel.CONSIGNMENT order by S_PARCEL.DELIVERYTIME desc)) as rn
    from 
        S_PARCEL 
    inner join 
        h_parcel on h_parcel.h_parcel = s_parcel.h_parcel) as t
where 
    t.rn = 1

This code is used to fill a column in an ETL process, which is done in Visual Studio. Visual Studio does not support the function over(partition by....), so this code has to be translated into a code without the partition function. Can someone please help me :)?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AndreBandre
  • 11
  • 1
  • 1
    "Visual Studio" is an IDE, it doesn't understand *any* SQL directly. Specify the exact component / library / context / datasource / thing - which is *not* "VS" - that "does not support this function". Including the actual error message(s), if any, may also be useful. – user2864740 Dec 29 '14 at 16:13
  • An integration services project from the Business Intelligence pack is used in VS. The actual error is the following: "The OVER SQL construct or statement is not supported.". Pretty useless one – AndreBandre Dec 29 '14 at 16:19
  • http://stackoverflow.com/questions/9028966/substitute-command-for-over-in-ssrs , https://social.msdn.microsoft.com/Forums/en-US/4b7fe6f4-abf9-46ce-be5c-d655f1f87fbb/the-over-sql-construct-or-statement-is-not-supported - seems to be a designer issue, possibly built in the era of a superseded SQL Server version. Considering editing the tags and specifying the version of the tool(s) used. – user2864740 Dec 29 '14 at 16:29
  • Well, I'm just looking for an alternative code with basic used functions to give the same results – AndreBandre Dec 29 '14 at 16:42

0 Answers0