Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

modify query to extended time period

Posted on 2012-09-07
5
Medium Priority
?
361 Views
Last Modified: 2012-09-17
have a ssis package that calls a stored proc and passes a date such as: 2012-09-07
the store proc runs a query for what appears to be a 24 hr period (12:00am to 11:59pm)

change request - need to span for a 30 hr period so 6am the next day

--declare @startDate as datetime
--Set @startDate = '2012-09-07'

SELECT DISTINCT 
                      CAST(f.FlightID AS varchar(100)) + '...' + ao.AirportICAOCode AS FlightIDDepICAO, 
                      f.FlightID, 
                      CAST(f.DepartureTime AS date) AS DepartureTimeLocal, 
                      CAST(f.ArrivalTime AS date) AS ArrivalTimeLocal, 
                      CAST(f.DepartureTime AS time) AS DepartureTimeLocalTime, 
                      CAST(f.ArrivalTime AS time) AS ArrivalTimeLocalTime, 
                      CAST(f.DepartureTime AS date) AS DepartureTimeZulu, 
                      CAST(f.ArrivalTime AS date) AS ArrivalTimeZulu, 
                      f.PaxCount, 
                      upic.DisplayName AS PIC, 
                      usic.DisplayName AS SIC, 
                      CASE 
                       WHEN tr_EntityContactPoints.Value IS NULL 
                        THEN 'NA' ELSE tr_EntityContactPoints.Value END AS ProntoIDLookUp, CASE WHEN tr_EntityContactPoints.Value IS NULL 
                        THEN 'NA' ELSE tr_EntityContactPoints.Value END AS PICProntoID, CASE WHEN tr_EntityContactPoints_1.Value IS NULL 
                        THEN 'NA' ELSE tr_EntityContactPoints_1.Value END AS SICProntoID, 
                      CAST(f.FlightID AS varchar(100)) + '...' + LTRIM(RTRIM(ao.AirportICAOCode))+ ' - ' + LTRIM(RTRIM(ad.AirportICAOCode)) AS DepICAOArrICAO, 
                      ac.Registration, 
                      v.VendorName AS PreferredFBO, 
                      paytype.LookupValue AS FBOPaymentType
					  FROM         tm_Users AS usic WITH (nolock) 
					  RIGHT OUTER JOIN
                      tr_EntityContactPoints AS tr_EntityContactPoints_1 
                      INNER JOIN
                      tl_Lookups ON tr_EntityContactPoints_1.ContactTypeLookupID = tl_Lookups.LookupID ON usic.UserID = tr_EntityContactPoints_1.EntityKeyID 
                      RIGHT OUTER JOIN
                      tm_FlightCrew AS fcsic WITH (nolock) 
                      RIGHT OUTER JOIN
                      tl_Lookups AS tl_Lookups_1 
                      INNER JOIN
                      tr_EntityContactPoints ON tl_Lookups_1.LookupID = tr_EntityContactPoints.ContactTypeLookupID 
                      RIGHT OUTER JOIN
                      tm_Flights AS f WITH (nolock) 
                      LEFT OUTER JOIN
                      tl_Aircraft AS ac WITH (nolock) ON f.AircraftID = ac.AircraftID 
                      LEFT OUTER JOIN
                      tl_Airports AS ao WITH (nolock) ON f.OriginAirportID = ao.AirportID 
                      LEFT OUTER JOIN
                      tl_Vendors AS v WITH (nolock) ON ao.PreferredFBOVendorID = v.VendorID 
                      LEFT OUTER JOIN
                          (SELECT     l.LookupID, l.LookupValue
                            FROM          tl_Lookups AS l INNER JOIN
                                                   ts_LookupTypes AS ts ON l.LookupTypeID = ts.LookupTypeID AND ts.LookupTypeID = 10) AS paytype ON 
                      v.PaymentTypeLookupID = paytype.LookupID LEFT OUTER JOIN
                      tl_Airports AS ad WITH (nolock) ON f.DestinationAirportID = ad.AirportID LEFT OUTER JOIN
                      tm_Legs AS l WITH (nolock) ON f.LegID = l.LegID LEFT OUTER JOIN
                      tm_Trips AS t WITH (nolock) ON l.TripID = t.TripID LEFT OUTER JOIN
                      tm_FlightCrew AS fclookup WITH (nolock) ON f.FlightID = fclookup.FlightID LEFT OUTER JOIN
                      tm_Users AS ulookup ON fclookup.UserID = ulookup.UserID LEFT OUTER JOIN
                      tm_FlightCrew AS fcpic WITH (nolock) ON f.FlightID = fcpic.FlightID AND fcpic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType IN ('PIC', 'Standards Captain'))) INNER JOIN
                      tm_Users AS upic WITH (nolock) ON fcpic.UserID = upic.UserID AND fcpic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType IN ('PIC', 'Standards Captain'))) ON tr_EntityContactPoints.EntityKeyID = upic.UserID ON fcsic.FlightID = f.FlightID AND 
                      fcsic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType = 'SIC')) ON usic.UserID = fcsic.UserID AND fcsic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType = 'SIC'))
WHERE     (CAST(f.DepartureTime AS date) >= @StartDate) AND (CAST(f.DepartureTime AS date) <= DATEADD(hh, 8, DATEADD(dd, 1, @StartDate))) AND (f.StatusTypeID <> 13) AND 
                      (f.FlightID IS NOT NULL) AND (tr_EntityContactPoints.EntityID = 2) AND (tr_EntityContactPoints_1.EntityID = 2) AND (tl_Lookups_1.LookupID = 373) AND 
                      (tl_Lookups.LookupID = 373)

union 

SELECT DISTINCT 
                      CAST(f.FlightID AS varchar(100)) + '...' + ao.AirportICAOCode AS FlightIDDepICAO, f.FlightID, CAST(f.DepartureTime AS date) AS DepartureTimeLocal, 
                      CAST(f.ArrivalTime AS date) AS ArrivalTimeLocal, CAST(f.DepartureTime AS time) AS DepartureTimeLocalTime, CAST(f.ArrivalTime AS time) 
                      AS ArrivalTimeLocalTime, CAST(f.DepartureTime AS date) AS DepartureTimeZulu, CAST(f.ArrivalTime AS date) AS ArrivalTimeZulu, f.PaxCount, 
                      upic.DisplayName AS PIC, usic.DisplayName AS SIC, CASE WHEN tr_EntityContactPoints.Value IS NULL 
                      THEN 'NA' ELSE tr_EntityContactPoints_1.Value END AS ProntoIDLookUp, CASE WHEN tr_EntityContactPoints.Value IS NULL 
                      THEN 'NA' ELSE tr_EntityContactPoints.Value END AS PICProntoID, CASE WHEN tr_EntityContactPoints_1.Value IS NULL 
                      THEN 'NA' ELSE tr_EntityContactPoints_1.Value END AS SICProntoID, CAST(f.FlightID AS varchar(100)) + '...' + LTRIM(RTRIM(ao.AirportICAOCode)) 
                      + ' - ' + LTRIM(RTRIM(ad.AirportICAOCode)) AS DepICAOArrICAO, ac.Registration, v.VendorName AS PreferredFBO, paytype.LookupValue AS FBOPaymentType
FROM         tm_Users AS usic WITH (nolock) RIGHT OUTER JOIN
                      tr_EntityContactPoints AS tr_EntityContactPoints_1 INNER JOIN
                      tl_Lookups ON tr_EntityContactPoints_1.ContactTypeLookupID = tl_Lookups.LookupID ON usic.UserID = tr_EntityContactPoints_1.EntityKeyID RIGHT OUTER JOIN
                      tm_FlightCrew AS fcsic WITH (nolock) RIGHT OUTER JOIN
                      tl_Lookups AS tl_Lookups_1 INNER JOIN
                      tr_EntityContactPoints ON tl_Lookups_1.LookupID = tr_EntityContactPoints.ContactTypeLookupID RIGHT OUTER JOIN
                      tm_Flights AS f WITH (nolock) LEFT OUTER JOIN
                      tl_Aircraft AS ac WITH (nolock) ON f.AircraftID = ac.AircraftID LEFT OUTER JOIN
                      tl_Airports AS ao WITH (nolock) ON f.OriginAirportID = ao.AirportID LEFT OUTER JOIN
                      tl_Vendors AS v WITH (nolock) ON ao.PreferredFBOVendorID = v.VendorID LEFT OUTER JOIN
                          (SELECT     l.LookupID, l.LookupValue
                            FROM          tl_Lookups AS l INNER JOIN
                                                   ts_LookupTypes AS ts ON l.LookupTypeID = ts.LookupTypeID AND ts.LookupTypeID = 10) AS paytype ON 
                      v.PaymentTypeLookupID = paytype.LookupID LEFT OUTER JOIN
                      tl_Airports AS ad WITH (nolock) ON f.DestinationAirportID = ad.AirportID LEFT OUTER JOIN
                      tm_Legs AS l WITH (nolock) ON f.LegID = l.LegID LEFT OUTER JOIN
                      tm_Trips AS t WITH (nolock) ON l.TripID = t.TripID LEFT OUTER JOIN
                      tm_FlightCrew AS fclookup WITH (nolock) ON f.FlightID = fclookup.FlightID LEFT OUTER JOIN
                      tm_Users AS ulookup ON fclookup.UserID = ulookup.UserID LEFT OUTER JOIN
                      tm_FlightCrew AS fcpic WITH (nolock) ON f.FlightID = fcpic.FlightID AND fcpic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType IN ('PIC', 'Standards Captain'))) INNER JOIN
                      tm_Users AS upic WITH (nolock) ON fcpic.UserID = upic.UserID AND fcpic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType IN ('PIC', 'Standards Captain'))) ON tr_EntityContactPoints.EntityKeyID = upic.UserID ON fcsic.FlightID = f.FlightID AND 
                      fcsic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType = 'SIC')) ON usic.UserID = fcsic.UserID AND fcsic.CapacityTypeID IN
                          (SELECT     CapacityTypeID
                            FROM          ts_CapacityTypes
                            WHERE      (CapacityType = 'SIC'))
WHERE     (CAST(f.DepartureTime AS date) >= @StartDate) AND (CAST(f.DepartureTime AS date) <= DATEADD(hh, 8, DATEADD(dd, 1, @StartDate))) AND (f.StatusTypeID <> 13) AND 
                      (f.FlightID IS NOT NULL) AND (tr_EntityContactPoints.EntityID = 2) AND (tr_EntityContactPoints_1.EntityID = 2) AND (tl_Lookups_1.LookupID = 373) AND 
                      (tl_Lookups.LookupID = 373)

Open in new window

0
Comment
Question by:fwstealer
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 38377276
Hi.

What is the exact issue you are having with making the change?

It appears, you are currently using:
BeginTime               EndTime
----------------------- -----------------------
2012-09-07 00:00:00.000 2012-09-08 08:00:00.000

Open in new window


Try this code to see the date and times you are filtering on:
DECLARE @startDate AS DATETIME = '2012-09-07';

SELECT BeginTime = @StartDate
     , EndTime = DATEADD(hh, 8, DATEADD(dd, 1, @StartDate));

Open in new window


As you can see above, you are already using 8AM the next day. I would simply change the 8 to a 6. Note: you probably want to ensure that the @StartDate is always passed without timestamps or explicitly with 00:00:00; otherwise, you will get varying results.

As an aside, why are you casting f.DepartureTime to a DATE? Is it stored as VARCHAR? Just curious as that may hurt performance. If f.DepartureTime is a DATETIME, then this should work just fine:

f.DepartureTime >= @StartDate
AND f.DepartureTime < DATEADD(hh, 6, DATEADD(dd, 1, @StartDate))

If you want 6AM included, then change to <=.

I hope that helps!

Kevin
0
 

Author Comment

by:fwstealer
ID: 38377474
the issue with that when users are in pacific standard time the data isn't present for them since it is based on zulu and the data isn't available.
okay so what if i wanted to go further into the next day say passed 8:00 to 1pm next day - what would I change?

also changing this:
WHERE     (CAST(f.DepartureTime AS date) >= @StartDate) AND (CAST(f.DepartureTime AS date) <= DATEADD(hh, 8, DATEADD(dd, 1, @StartDate)))

to WHERE     (CAST(f.DepartureTime AS date) <= @StartDate) AND (CAST(f.DepartureTime AS date) <= DATEADD(hh, 8, DATEADD(dd, 1, @StartDate)))
returns a very large dataset - not what is needed; want to see data for current day and into half of next day
0
 

Author Comment

by:fwstealer
ID: 38377547
not sure about casting f.DepartureTime to a DATE - just got handed this - not really my cup of tea
0
 

Author Comment

by:fwstealer
ID: 38377714
wouldn't this bump it up to another day?
change this
WHERE     (CAST(f.DepartureTime AS date) >= @StartDate) AND (CAST(f.DepartureTime AS date) <= DATEADD(hh, 8, DATEADD(dd, 1, @StartDate)))

to
WHERE     (CAST(f.DepartureTime AS date) >= @StartDate) AND (CAST(f.DepartureTime AS date) <= DATEADD(hh, 8, DATEADD(dd, 2, @StartDate)))

changing the dd,1 to a 2?

so it would be begintime: 2012-09-07 00:00:00.000
endtime: 2012-09-09 00:00:00.000
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 38378309
Yes. If the issue is you need to adjust everything eight hours because of time zone, then to push an additional six hours add 14 hours. For multiple time zone support, SQL added a time zone offset data type that may help. So it sounds like dateadd(hh, 14, ...) would be fine or you can add two days from start date to cover all your bases. You can always filter the results further if necessary.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question