SELECT i.ITEM_ID,
             i.ITEM_TYPE,
             i.START_DATETIME,
             i.END_DATETIME,
             MAX (CASE WHEN ip.PROPERTY_TYPE = 'NAME' THEN ip.PROPERTY_STRING END)
                AS FIELD_NAME,
             MAX (CASE WHEN ip.PROPERTY_TYPE = 'STATE' THEN ip.PROPERTY_STRING END)
                AS FIELD_STATE,
             CL_STATE.CODE_TEXT AS STATE_TEXT,
             MAX (
                CASE WHEN ip.PROPERTY_TYPE = 'COUNTY' THEN ip.PROPERTY_STRING END)
                AS FIELD_COUNTY,
             CL_COUNTY.CODE_TEXT AS COUNTY_TEXT
        FROM ITEM i
             INNER JOIN DATE_INFO DI
               ON DI.DATETIME = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AND DI.DATE_TYPE = 'D' 
             LEFT OUTER JOIN ITEM_PROPERTY ip ON (i.ITEM_ID = ip.ITEM_ID)
    
    
             LEFT OUTER JOIN ITEM_PROPERTY ip_STATE
                ON     ip_STATE.ITEM_ID = i.ITEM_ID
                   AND ip_STATE.START_DATETIME <= DI.DATETIME
                   AND ip_STATE.END_DATETIME > DI.DATETIME
                   AND ip_STATE.PROPERTY_TYPE = 'STATE'
             LEFT OUTER JOIN CODE_LIST CL_STATE
                ON     CL_STATE.CODE = ip_STATE.PROPERTY_STRING
                   AND CL_STATE.LIST_TYPE = 'STATE'
                   AND CL_STATE.CULTURE = 'en-US'
    
    
    
             LEFT OUTER JOIN ITEM_PROPERTY ip_COUNTY
                ON     ip_COUNTY.ITEM_ID = i.ITEM_ID
                   AND ip_COUNTY.START_DATETIME <= DI.DATETIME
                   AND ip_COUNTY.END_DATETIME > DI.DATETIME
                   AND ip_COUNTY.PROPERTY_TYPE = 'COUNTY'
             LEFT OUTER JOIN CODE_LIST CL_COUNTY
                ON     CL_COUNTY.CODE = ip_COUNTY.PROPERTY_STRING
                   AND CL_COUNTY.LIST_TYPE = 'COUNTY'
                   AND CL_COUNTY.CULTURE = 'en-US'
    
    
    
       WHERE     i.START_DATETIME <= DI.DATETIME
             AND i.END_DATETIME > DI.DATETIME
             AND ip.START_DATETIME <= DI.DATETIME
             AND ip.END_DATETIME > DI.DATETIME
             AND i.ITEM_TYPE LIKE 'FIELD'
    GROUP BY i.ITEM_ID,
             i.ITEM_TYPE,
             i.START_DATETIME,
             i.END_DATETIME,
             CL_STATE.CODE_TEXT,
             CL_COUNTY.CODE_TEXT