Post reply

QUERY T-SQL

  • May 26, 2010 at 6:16 am

    #219361

    Hello ! Guys

    Please supply a query regarding the table and the conditions.

    Report Name: EJ4 - End Journey not received from the To Location

    Report Date (GMT)4/25/2009 23:04

    Container IDTag IDFrom LocationTo LocationEnd Journey TimeErrorAction Item

    Enterprise: AEGIS

    CAXU1234567A12345HoustonChicago6/22/2009 11:02Verify the To Location coordinatesVerify the To Location coordinates

    CMMU1234567B12345HoustonChicago6/22/2009 11:02Verify the To Location coordinatesVerify the To Location coordinates

    Enterprise: BEGIS

    BMMU1234567C12345LABoston6/22/2009 11:02Verify the To Location coordinatesVerify the To Location coordinates

    Requirements:

    1) Provide report of all container that Ended Journey within the last 5 days, but did not have not a EJ transaction from the To Location.

    2) Select Containers in EJ status, and check for the following

    2A. IF container has a manual End Journey

    Report error as "Manual End Journey", Action Item as "Review End Journey Process"

    2B. ELSE IF Container has an Unmounted event with blank lat/long

    Report error as "No GPS fix for End Journey Transaction", Action Item as "Review GPS Coverage"

    2C.ELSE IF Container has an Unmounted event lat/long values, that did not fall in valid IRIS location

    Report error as "End Journey at Unknown location", Action Item as "Review To Location Geofence"

    Criteria to check.

    2A - Check Update Type = "MANUAL', Event Type = 'END'

    2B - Context = 'Unmounted', Event Type = 'END' AND (Latitude = NULL or Longitude = NULL)

    2C - Context = 'Unmounted', Event Type = 'END' AND (Latitude NOT NULL and Longitude NOT NULL) AND Event_Location_key = NULL

    Thanks

    Subhro

  • May 26, 2010 at 6:24 am

    #1172639

    Well, I don't know how much help you'll get from this. Two points that'll make it more likely you get some: -

    1. Please read this[/url] to find out how to post data.

    2. Post what you've tried so people don't just feel that they're doing your work for you. If you haven't tried, why should someone else?

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 26, 2010 at 6:31 am

    #1172647

    I echo skcadavre here ; Long verbal questions wont attract anyone; but when u post the following, i tell you, you will receicve excellent tested code back:

    so, Please post;

    1. Table Structures - CREATE TABLE scripts

    2. Constraints or Indexes in the table, - CREATE SCRIPTS

    3. SAMPLE DATA - INSERT INTO TABLE scripts

    4. Desired output - some visual representation of this.

    C'est Pras!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply