locked
SQL Query to trigger email based on Time. RRS feed

  • Question

  • Hi,

    I have written a stored procedure which is executed by a window service to send an email. the email will be sent if the vehicle is not arrived to location with in <= 1 hour  from ExpectedArrivalShippingDateTime.  The ExpectedArrivalShippingDateTime value available in the table column is  12 hour format  (i.e.  say 12/12/2016 10:25 AM  )

    But my below query is not working. please need inputs to correct the below query and also need query to check if the vehicle is not arrived to location within <= 10 minutes  from ExpectedArrivalShippingDateTime


    CREATE PROC [dbo].[SP_GetShipping_NotArrivedList] 
    AS 
      BEGIN  

     select TR.Id,'Shipping' AS RouteType,TR.ROUTENAME,TR.ExpectedArrivalShippingDateTime  

     FROM TBLROUTE  TR 

     WHERE TR.RouteTypeId=2     and      DATEDIFF(hh, TR.ExpectedArrivalShippingDateTime, GETDATE())<=1 

     END


    Similarly,

    I have written a stored procedure to send an email if the vehicle is stayed in the location more than 1 hour from EntranceDatetime.  The EntranceDatetime value available in the table column is  12 hour format  (i.e.  say 12/13/2016 10:25 AM  )

    But my below query is also not working. please need inputs to correct the below query.


    CREATE PROC [dbo].[SP_GetShipping_BeenLongerList] 
    AS 
      BEGIN  

     select TR.Id,'Shipping' AS RouteType,TR.ROUTENAME,TR.EntranceDatetime
      
     FROM TBLROUTE TR 

     WHERE TR.RouteTypeId=2 and DATEDIFF(hh, TR.EntranceDatetime, GETDATE())>1 


    END

    Thanks

    Vamshi


    Vamshi Janagama

    Tuesday, December 13, 2016 3:56 AM

Answers

  • Erland,

    Thank you for the inputs. It was very helpful and I was able to get the accurate results.  Thank you.

    Below is SQL QUERY

      

    select   TR.Id,'Expedite' AS RouteType, TR.ExpectedArrivalShippingDateTime,

     CASE WHEN charindex('(', TR.ROUTECONTACTPERSONWDBID) > 1 AND
               charindex(')', TR.ROUTECONTACTPERSONWDBID) >
               charindex('(', TR.ROUTECONTACTPERSONWDBID) THEN
           substring(TR.ROUTECONTACTPERSONWDBID,charindex('(',TR.ROUTECONTACTPERSONWDBID)+len('('),charindex(')',TR.ROUTECONTACTPERSONWDBID)-charindex('(',TR.ROUTECONTACTPERSONWDBID)-len('(')) 
    END    AS   WORKERID

     FROM TBLROUTE TR 

    Thanks


    Vamshi Janagama

    • Marked as answer by Vamshi K J Wednesday, December 14, 2016 2:48 AM
    Wednesday, December 14, 2016 2:47 AM
  • Thank you Erland for sharing the information. I am working on a project which was developed by others long back. I will correct the table structure and use proper data types. Thanks

    Vamshi Janagama

    • Marked as answer by Vamshi K J Saturday, December 17, 2016 5:28 PM
    Saturday, December 17, 2016 5:28 PM

All replies

  • @vamshi, Janagama, 

    Can you please provide a sample data,table structure and expected output. So it will be easy for us to understand the issue.

    Tuesday, December 13, 2016 4:37 AM
  • Good day Vamshi,

    It reminds me a story: someone had issue with his car, so he went to the garage and requested from the mechanic to fix his car... The mechanic asked him which car is his, and he answer that his car is not here but at his home. He came with bus, but he just want that the mechanic will tell him what wrong with the car...

    As desperadomar said, please post

    1) Queries to CREATE your table(s) including indexes
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, December 13, 2016 5:35 AM
  • Thank you for reply.

     if the vehicle is stayed in the location more than 1 hour from EntranceDatetime. then fetch the record.  i.e.    if the vehicle is stayed more than 1 hour in location till  2016-12-12 12:17:24 then fetch the record to further process.

    Below is the table structure for reference.

    Similarly, if the vehicle is not arrived to location by 1 hour  from ExpectedArrival_ShippingDateTime, then fetch the record.  i.e.    if the vehicle is not arrived to location by  12/12/2016 11:25 AM then fetch the record to further process.

    Below is the table structure for reference.


    Vamshi Janagama

    Tuesday, December 13, 2016 5:52 AM
  • As others have pointed out, your question is not crystal clear, and particularly you forgot to tell us what "not working" means. Do you get an error, unexpected results etc?

    However, I did notice one flaw which is not so obvious for the uninitiated:

     WHERE TR.RouteTypeId=2     and      DATEDIFF(hh, TR.ExpectedArrivalShippingDateTime, GETDATE())<=1 

    The datediff function is not entirely intuitive. It counts number of boundary transitions. This means that

        SELECT datediff(hh, '11:59:59.900', '12:00:00')

    returns 1, as does:

        SELECT datediff(hh, '11:00:00.900', '12:59:59')

    So to check for an interval of an hour you will need

       datediff(minute, TR.ExpectedArrivalShippingDateTime, GETDATE())

    and compare it with 60, or even use seconds and compare with 3600. Now as for the comparison as such, there is also an error, but this one is more obvious, so I leave as an exercise to figure that out.

    And, oh, one more thing. Don't call your procedures sp_something. The sp_ prefix is reserved for system procedures.

    Tuesday, December 13, 2016 8:27 AM
  • Thank you for information Erland.

    I  have tried and updated the DATEDIFF function  as below and found working,  Thanks.

    DATEDIFF(mi, TR.ExpectedArrival_ShippingDateTime, GETDATE()) > 60   

    SQL QUERY ERROR

    I  am trying to execute the below query and i am getting an error message "Invalid length parameter passed to the LEFT or SUBSTRING function."   Need help on it.

     select TR.Id,

              'Shipping' AS RouteType,

              TR.ROUTENAME,

              TR.ExpectedArrivalShippingDateTime, 

           (substring(TR.ROUTECONTACTPERSONWDBID,charindex('(',TR.ROUTECONTACTPERSONWDBID)+len('('),charindex(')',TR.ROUTECONTACTPERSONWDBID)-charindex('(',TR.ROUTECONTACTPERSONWDBID)-len('(')))   AS   WORKERID
     

    FROM TBLROUTE  TR  


    Thanks


    Vamshi Janagama

    Tuesday, December 13, 2016 9:03 PM
  • Continuation.....

    I forgot to include the data format present in ROUTECONTACTPERSONWDBID  column. The data is in below format

    DANIEL LEONHARDTT (110255)

    Thanks


    Vamshi Janagama

    Tuesday, December 13, 2016 9:10 PM
  • When you run code to extract data in formatted fields the first observation is that the data model is flawed. With a correct data model, two pieces of data should be stored in different cells.

    But if we overlook that, you must always write such extractions defensively. That is, you must be able to handle the case that the data is not formatted as you expect, because you can bet you rear parts that there will be such data. So you need something like this:

     CASE WHEN charindex('(', TR.ROUTECONTACTPERSONWDBID) > 1 AND
               charindex(')', TR.ROUTECONTACTPERSONWDBID) >
               charindex('(', TR.ROUTECONTACTPERSONWDBID) THEN
           substring(...)
     END

    Tuesday, December 13, 2016 10:50 PM
  • Erland,

    Thank you for the inputs. It was very helpful and I was able to get the accurate results.  Thank you.

    Below is SQL QUERY

      

    select   TR.Id,'Expedite' AS RouteType, TR.ExpectedArrivalShippingDateTime,

     CASE WHEN charindex('(', TR.ROUTECONTACTPERSONWDBID) > 1 AND
               charindex(')', TR.ROUTECONTACTPERSONWDBID) >
               charindex('(', TR.ROUTECONTACTPERSONWDBID) THEN
           substring(TR.ROUTECONTACTPERSONWDBID,charindex('(',TR.ROUTECONTACTPERSONWDBID)+len('('),charindex(')',TR.ROUTECONTACTPERSONWDBID)-charindex('(',TR.ROUTECONTACTPERSONWDBID)-len('(')) 
    END    AS   WORKERID

     FROM TBLROUTE TR 

    Thanks


    Vamshi Janagama

    • Marked as answer by Vamshi K J Wednesday, December 14, 2016 2:48 AM
    Wednesday, December 14, 2016 2:47 AM
  • Erland,

    I have a question regarding DATEDIFF Function.

    The column ScheduledArrivalTime in Table TBLROUTE  has the data in below format     i.e. only time in AM or PM format with no date.  

    Say   11:15 AM

    Since  The column ScheduledArrivalTime has only time and no date, I have written below DATEDIFF function  using GETDATE() to check if the vehicle is not arrived to location by 2 hours (i.e. 120 minutes )  from ScheduledArrivalTime . 

    DATEDIFF(mi, TR.ScheduledArrivalTime, LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)))>120

    please suggest me if it is right approach.   if not need inputs to correct the
    DATEDIFF function.

    Thanks


    Vamshi Janagama

    Wednesday, December 14, 2016 3:02 AM
  • I have a question regarding DATEDIFF Function.
    The column ScheduledArrivalTime in Table TBLROUTE  has the data in below format     i.e. only time in AM or PM format with no date.  

    You mean that the column is a varchar column? Storing date/time data in text columns is an utterly bad idea, and you should never permit it to happen. It will always end in tears when bad data sneaks in.

    And in the context of the return time for a rental car, having only the time when return is due is not going to help you. You need to have the date as well.

    • Proposed as answer by Naomi N Sunday, December 18, 2016 5:37 AM
    Wednesday, December 14, 2016 8:35 AM
  • Thank you Erland for sharing the information. I am working on a project which was developed by others long back. I will correct the table structure and use proper data types. Thanks

    Vamshi Janagama

    • Marked as answer by Vamshi K J Saturday, December 17, 2016 5:28 PM
    Saturday, December 17, 2016 5:28 PM