0

If a package is shown as leaving the warehouse when the warehouse is not available (between its working hours) then the package's leave date should be the next day right when the warehouse's program starts.

Below is the code that needs reviewing. This code is from a procedure that is called with multiple parameters including warehouses. The warehouse parameter is under this form: "5, 45".

There are multiple deposits with different opening and closing hours. I am using "TOP(1)" for my SELECTS which is a problem as this will only take the opening and closing hours of the first warehouse listed thus assuming that all warehouses have the same schedule.

My question: any ideas to replace the "TOP(1)"?

Code:

WITH CTE AS (
  SELECT LeaveDate = CASE
    WHEN CAST(LeaveDateTBL.LeaveDate AS time)
         BETWEEN (
           SELECT TOP(1) dbo.tblAdmPartners_Schedule.WorkStartTime
             FROM  dbo.tblAdmPartners_Schedule WITH (NOLOCK)
             WHERE dbo.tblAdmPartners_Schedule.IdAdmPartner = (
               SELECT TOP(1) dbo.tblAdmWarehouses.IdAdmPartner
                 FROM  dbo.tblAdmWarehouses WITH (NOLOCK)
                  JOIN dbo.tblAdmWarehouses_Aliases WITH (NOLOCK)
                    ON dbo.tblAdmWarehouses.Id = dbo.tblAdmWarehouses_Aliases.IdAdmWarehouse
         )   ) AND (
           SELECT TOP(1) dbo.tblAdmPartners_Schedule.WorkStopTime
             FROM  dbo.tblAdmPartners_Schedule WITH (NOLOCK)
             WHERE dbo.tblAdmPartners_Schedule.IdAdmPartner = (
               SELECT TOP(1) dbo.tblAdmWarehouses.IdAdmPartner
                 FROM  dbo.tblAdmWarehouses WITH (NOLOCK)
                  JOIN dbo.tblAdmWarehouses_Aliases WITH (NOLOCK)
                    ON dbo.tblAdmWarehouses.Id = dbo.tblAdmWarehouses_Aliases.IdAdmWarehouse
        )   )
      THEN (LeaveDateTBL.LeaveDate)
    ELSE
      (DATEADD(DAY, 1, leaveDateTBL.LeaveDate))
    END
outis
  • 75,655
  • 22
  • 151
  • 221
Rares-Gabriel Negru
  • 1
  • what about using order by and limit 1 – WillSmith May 09 '22 at 08:12
  • 1
    Please, please, format your code so it is readable! – HoneyBadger May 09 '22 at 08:20
  • 1
    Which dbms are you using? Also, TOP 1 but no ORDER BY will give you a random row. – jarlh May 09 '22 at 08:43
  • Since SQL includes data definition, a [mcve] for an [SQL question](//meta.stackoverflow.com/q/333952/90527) should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. – outis May 09 '22 at 19:48
  • The sample query uses the [old join syntax](https://stackoverflow.com/q/894490/90527), which is long deprecated. It should be rewritten using the "new" join syntax (introduced in 1992). – outis May 09 '22 at 19:52
  • What's the criteria for picking a warehouse? The question mentions a package; isn't the warehouse for a package already determined, based on stock availability? Or are you trying to create a package record, and thus want any warehouse that is open at `LeaveDateTBL.LeaveDate` and has the items in the package in stock? If the latter, schema & information related to items should be included. – outis May 09 '22 at 19:56
  • I mentioned the work package as it helped me describe the problem better. In short, rephrasing the question: I have multiple warehouses each with their own working hours. I must check if the leave dates are inside these working hours. If they are not, then I must add a day to the leave date associated with that specific warehouse. The problem with "top" is that this takes the working hours of a random warehouse, not for each individual warehouse that is a parameter when calling the procedure, and applies the code for all warehouses. Thus, either all warehouses get modified or none at all. – Rares-Gabriel Negru May 10 '22 at 07:22
  • Please [edit] clarifications into the question, rather than leaving them as [comments](//stackoverflow.com/help/privileges/comment). For one thing, a question should be understandable without reading comments. For another, SO is a Q&A site, not a forum, and comments aren't intended (nor are they well suited) for discussions. – outis May 23 '22 at 12:29

1 Answers1

0

maybe you can use Row_Number and then get the first row

Soudabeh Parsa
  • 109
  • 7
  • The thing is I am thinking about a condition to add or something in order to take the schedule start and end times for each warehouse individually as every one of them has a different schedule. Row_Number would work the same as TOP and that wouldn't solve my issue sadly. – Rares-Gabriel Negru May 09 '22 at 08:33
  • how about sorting on start and end times and taking the max or min? – Soudabeh Parsa May 09 '22 at 08:37