Solved

sql increment a date

Posted on 2013-01-14
19
499 Views
Last Modified: 2013-02-07
so i need to increment a date and this works by today's date but when i change the startdate say to 1/20/2013 then it doesn't change the date.

if startdate = 1/14/2013 then it should go 7 days out to 1/20/2013 and this works.
however, if startdate = 1/20/2013 then it should go to 1/27/2013 but it doesn't.

declare @StartDate as date
declare @EndDate as date
set @StartDate = '1/14/2013 2:18:28 PM'
set @EndDate = '1/14/2013 2:18:28 PM'

select
 day,
case when sum(unschedmx) < 5 then 5 else sum(unschedmx) end +sum(schedmx) as Forecasted_EventCount,
count(day) as Current_EventCount,
sum(schedmx) as SchedMx,
sum(unschedmx) as UnSchedMxActual,
case when sum(unschedmx) < 5 then 5 else sum(unschedmx) end as UnSchedMxForecast,
(select count(registration) as FleetTotal

from tl_aircraft
where outofservicedate is null and availablefordispatch = 1) as ForecastFleetTotal,(select count(registration) as FleetTotal
   from tl_aircraft where outofservicedate is null and availablefordispatch = 1)- (case when sum(unschedmx) < 5 then 5 else sum(unschedmx) end +sum(schedmx)) as ForecastedAvailAC
from (

SELECT      
tl_Aircraft.Registration, 
tm_AircraftDutySchedules.Description AS Mx_Event_Description, 
CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) AS EventStartTime_EST, 
CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, 
GETDATE() AS CurrentTime_EST, 
tl_Airports.AirportICAOCode, 
tl_Vendors.VendorName, 
tl_Lookups.LookupValue, 
tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         convert(date,getdate()) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= @StartDate) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < @EndDate) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < @StartDate) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > @EndDate) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < @StartDate) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN @StartDate AND @EndDate) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  @StartDate AND @EndDate) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > @EndDate) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)


union all 
SELECT 
       tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,1,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,1,dateadd(dd,1,@StartDate))) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,1,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,1,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,1,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,1,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,1,@StartDate) AND dateadd(dd,1,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,1,@StartDate) AND dateadd(dd,1,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,1,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)



union all 
SELECT      
tl_Aircraft.Registration, 
tm_AircraftDutySchedules.Description AS Mx_Event_Description, 
CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, 
                         CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,2,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,2,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,2,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,2,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,2,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,2,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,2,@StartDate) AND dateadd(dd,2,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,2,@StartDate) AND dateadd(dd,2,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,2,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)


union all 
SELECT       tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,3,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,3,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,3,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,3,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,3,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,3,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,3,@StartDate) AND dateadd(dd,3,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,3,@StartDate) AND dateadd(dd,3,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,3,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

union all 
SELECT      tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,4,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,4,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,4,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,4,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,4,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,4,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,4,@StartDate) AND dateadd(dd,4,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,4,@StartDate) AND dateadd(dd,4,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,4,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)



union all 
SELECT      tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,5,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,5,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,5,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,5,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,5,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,5,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,5,@StartDate) AND dateadd(dd,5,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,5,@StartDate) AND dateadd(dd,5,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,5,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)





union all 
SELECT       tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,6,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,6,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,6,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,6,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,6,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,6,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,6,@StartDate) AND dateadd(dd,6,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,6,@StartDate) AND dateadd(dd,6,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,6,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

) as MxEvents

group by Day

order by day

Open in new window


I tried this:
select
Row_Number() over(order by day) as Increment,
case when day != @StartDate
then
--DATEADD(dd, 1, @StartDate)
cast(dateadd(day,-1+Row_Number() over(order by day),getdate()) as date)
else day
end as dayV,

to see if the dates would increment but no luck
0
Comment
Question by:fwstealer
  • 5
  • 5
  • 3
  • +2
19 Comments
 

Author Comment

by:fwstealer
ID: 38776070
what about this?

declare @StartDate as date
declare @EndDate as date
declare @today as date
set @StartDate = '1/14/2013 2:18:28 PM'
set @EndDate = '1/14/2013 2:18:28 PM'
set @today = getdate()

select
Row_Number() over(order by day) as Increment,
case when @today != @StartDate
then
      --begin with @startdate and increment
      dateadd(day, 1, @StartDate)
else day
end as day,

that works for this condition:  today = startdate

but when i change the startdate to 1/20 it begins a 1/21 for all 7 rows. i need it to begin at 1/20
1/21
1/22
1/23
1/24
1/25
1/26
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38777644
Should EndDate always be StartDate + 7?  If that's the case:

declare @StartDate as date
declare @EndDate as date
set @StartDate = '2013-01-14 14:18:28'

select @EndDate = DATEADD(DAY, 7, @StartDate)

select @StartDate StartDate, @EndDate EndDate

Open in new window

Please also note how I've changed the hardcoded date's format.  This is recommended above the one you used because it works in all situations.

If the above is not what you need, please write down some more requirements.  I don't think anyone is going to analyze the query in your question to find out what you're actually trying to achieve.  If they are, I applaud and would like congratulate them for their bravery and persistence! :)
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 38777706
If you only want to add days, it is more easier to add the number of days directly. Example of adding 7 days:

set @StartDate = @StartDate  + 7
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38777777
VMontalvao: true, that's easier to implement.  However, there are a couple of issues with it:

 o only works with datetime field/variables.  Try the following:

declare @StartDate as date
declare @EndDate as date
set @StartDate = '2013-01-14 14:18:28'

select @EndDate = @StartDate + 7

select @StartDate StartDate, @EndDate EndDate

Open in new window

You'll notice it throws an operand type clash error.

  o assumes that everyone knows that this expression adds days and not minutes, seconds, months, years, ...

(not being critical, just providing extra info :)
0
 

Author Comment

by:fwstealer
ID: 38778159
so if i go with:

declare @StartDate as date
declare @EndDate as date
--set @StartDate = '1/15/2013'
set @EndDate = '1/15/2013'
declare @today as date
set @StartDate = '2013-01-15 14:18:28'
select @EndDate = DATEADD(DAY, 7, @StartDate)

--select @StartDate StartDate, @EndDate EndDate

select
day,
rest of the query....

How do I get the 7 days into the day column?

so for today it would be
1/15/2013 to 1/22/2012
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 38778297
select @EndDate = DATEADD(DAY, 7, @StartDate) ----> should give you the 1/22/2012

So,

SELECT @EndDate, ...
FROM ....
WHERE ....

should return the date as you want.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38778320
I think we're possible not fully understanding your requirement.  Do you need to duplicate data somehow for each of those 7 days?  Or are you just retrieving data while filtering on StartDate and EndDate?

Please post an example using some sample data to demonstrate expected outcome.
0
 

Author Comment

by:fwstealer
ID: 38778334
so I've got the following but how to I get the data from the temp table to union all with the rest of the data?


declare @StartDate as date
declare @EndDate as date
declare @today as date
set @StartDate = '1/20/2013 2:18:28 PM'
set @EndDate = '1/20/2013 2:18:28 PM'
set @today = CURRENT_TIMESTAMP --getdate()
declare @myCounter as int
set @myCounter = 0

IF OBJECT_ID('tempdb..#tempDates') IS NOT NULL
   TRUNCATE TABLE #tempDates
else
CREATE TABLE #tempDates(myDate date NULL)


if(@today != @StartDate)
begin
      while (@myCounter <= 6)
      begin
            insert into #tempDates values (@StartDate)
            set @StartDate = dateadd(dd, 1, @StartDate)
            set @myCounter = @myCounter + 1  
      end
end
else
begin
      while (@myCounter <= 6)
      begin
            insert into #tempDates values (@StartDate)
            set @StartDate = dateadd(dd, 1, @StartDate)
            set @myCounter = @myCounter + 1
      end
end

select 
myDate as day
,Forecasted_EventCount = ''
,Current_EventCount = ''
,SchedMx = ''
,UnSchedMxActual = ''
,UnSchedMxForecast = ''
,ForecastFleetTotal = ''
,ForecastedAvailAC = ''
 from #tempDates 

union all

select
--day,
case when sum(unschedmx) < 5 then 5 else sum(unschedmx) end +sum(schedmx) as Forecasted_EventCount,
count(day) as Current_EventCount,
sum(schedmx) as SchedMx,
sum(unschedmx) as UnSchedMxActual,
case when sum(unschedmx) < 5 then 5 else sum(unschedmx) end as UnSchedMxForecast,
(select count(registration) as FleetTotal
from tl_aircraft
where outofservicedate is null and availablefordispatch = 1) as ForecastFleetTotal,(select count(registration) as FleetTotal
   from tl_aircraft where outofservicedate is null and availablefordispatch = 1)- (case when sum(unschedmx) < 5 then 5 else sum(unschedmx) end +sum(schedmx)) as ForecastedAvailAC
from (

SELECT      
tl_Aircraft.Registration, 
tm_AircraftDutySchedules.Description AS Mx_Event_Description, 
CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) AS EventStartTime_EST, 
CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, 
GETDATE() AS CurrentTime_EST, 
tl_Airports.AirportICAOCode, 
tl_Vendors.VendorName, 
tl_Lookups.LookupValue, 
tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         convert(date,getdate()) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= @StartDate) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < @EndDate) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < @StartDate) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > @EndDate) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < @StartDate) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN @StartDate AND @EndDate) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  @StartDate AND @EndDate) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > @EndDate) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

union all 

SELECT 
       tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,1,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,1,dateadd(dd,1,@StartDate))) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,1,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,1,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,1,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,1,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,1,@StartDate) AND dateadd(dd,1,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,1,@StartDate) AND dateadd(dd,1,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,1,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

union all 

SELECT      
tl_Aircraft.Registration, 
tm_AircraftDutySchedules.Description AS Mx_Event_Description, 
CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, 
                         CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,2,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,2,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,2,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,2,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,2,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,2,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,2,@StartDate) AND dateadd(dd,2,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,2,@StartDate) AND dateadd(dd,2,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,2,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

union all 

SELECT       tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,3,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,3,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,3,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,3,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,3,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,3,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,3,@StartDate) AND dateadd(dd,3,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,3,@StartDate) AND dateadd(dd,3,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,3,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

union all 

SELECT      tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,4,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,4,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,4,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,4,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,4,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,4,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,4,@StartDate) AND dateadd(dd,4,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,4,@StartDate) AND dateadd(dd,4,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,4,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

union all 

SELECT      tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,5,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,5,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,5,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,5,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,5,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,5,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,5,@StartDate) AND dateadd(dd,5,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,5,@StartDate) AND dateadd(dd,5,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,5,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

union all 

SELECT       tl_Aircraft.Registration, tm_AircraftDutySchedules.Description AS Mx_Event_Description, CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() 
                         - GETUTCDATE()) AS EventStartTime_EST, CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) AS EventEndTime_EST, GETDATE() 
                         AS CurrentTime_EST, tl_Airports.AirportICAOCode, tl_Vendors.VendorName, tl_Lookups.LookupValue, tm_AircraftDutySchedules.AircraftDutyLookupID, 
                         tm_AircraftDutySchedules.AircraftDutyScheduleID, DATEDIFF(DD, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS DAYS_IN_MX, 
                         DATEDIFF(HH, tm_AircraftDutySchedules.StartTime, tm_AircraftDutySchedules.EndTime) AS HRS_IN_MX, tl_Aircraft.OutOfServiceDate,
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration) AS Next_Sched_DepTime_EST, DATEDIFF(mi, GETDATE(),
                             (SELECT        MIN(F.DepartureTime + GETDATE() - GETUTCDATE()) AS NextSchedFltDepTime
                               FROM            tm_Flights AS F INNER JOIN
                                                         tl_Aircraft AS A ON F.AircraftID = A.AircraftID INNER JOIN
                                                         ts_StatusTypes AS S ON F.StatusTypeID = S.StatusTypeID
                               WHERE        (F.DepartureTime >= CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE())) AND (F.StatusTypeID <> 13) AND 
                                                         (A.Registration = tl_Aircraft.Registration)
                               GROUP BY A.Registration)) / 60.0 AS HrsTillNextDep, 
                         CASE WHEN tl_Lookups.UserColumn1 = 1 THEN 1 else 0 END AS SchedMx, 
                         CASE WHEN tl_Lookups.UserColumn1 = 2 THEN 1 else 0 END AS UnSchedMx,
                         tl_Aircraft.AvailableForDispatch,
                         dateadd(dd,6,convert(date,getdate())) as Day                      
                         
                         
FROM            tl_Aircraft AS tl_Aircraft INNER JOIN
                         tm_AircraftDutySchedules AS tm_AircraftDutySchedules ON tl_Aircraft.AircraftID = tm_AircraftDutySchedules.AircraftID INNER JOIN
                         tl_Airports AS tl_Airports ON tm_AircraftDutySchedules.StartAirportID = tl_Airports.AirportID INNER JOIN
                         tl_Vendors AS tl_Vendors ON tm_AircraftDutySchedules.StartVendorID = tl_Vendors.VendorID INNER JOIN
                         tl_Lookups AS tl_Lookups ON tm_AircraftDutySchedules.AircraftDutyLookupID = tl_Lookups.LookupID
WHERE        
tl_Lookups.UserColumn1 in (1,2)

AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) >= dateadd(dd,6,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) < dateadd(dd,6,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) 
OR
tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,6,@StartDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,6,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE())
AND (tl_Aircraft.AvailableForDispatch = 1) OR
tl_Lookups.UserColumn1 in (1,2)AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) < dateadd(dd,6,@StartDate)) 
AND  (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) BETWEEN dateadd(dd,6,@StartDate) AND dateadd(dd,6,@EndDate)) AND (tl_Aircraft.OutOfServiceDate IS NULL) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) 
AND (tl_Aircraft.AvailableForDispatch = 1) OR

tl_Lookups.UserColumn1 in (1,2)
AND (CONVERT(datetime, tm_AircraftDutySchedules.StartTime + GETDATE() - GETUTCDATE()) BETWEEN  dateadd(dd,6,@StartDate) AND dateadd(dd,6,@EndDate)) 
AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) > dateadd(dd,6,@EndDate)) 
AND (tl_Aircraft.OutOfServiceDate IS NULL) AND (CONVERT(datetime, tm_AircraftDutySchedules.EndTime + GETDATE() - GETUTCDATE()) >= GETDATE()) AND (tl_Aircraft.AvailableForDispatch = 1)

) as MxEvents
group by Day
order by day

Open in new window


right now the dates are being correct setup in the temp table and i need those to be the first column in the final table as a column name day
0
 

Author Comment

by:fwstealer
ID: 38778668
if i do:

select
myDate as day
,Forecasted_EventCount = ''
,Current_EventCount = ''
,SchedMx = ''
,UnSchedMxActual = ''
,UnSchedMxForecast = ''
,ForecastFleetTotal = ''
,ForecastedAvailAC = ''
 from #tempDates

union all

select
day,
rest of query


then i get a grid with duplicate days and those rows with no values so how would i delete the no rows?

day, Forecasted_EventCount
2013-01-15, 0 -- delete this row
2013-01-15, 21  -- keep this row

i tried to add
where Forecasted_EventCount != 0
at the end of the query thinking it wouldn't return 0 valued rows but it failed.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 38778760
>> Please also note how I've changed the hardcoded date's format ... because it works in all situations. <<

NO, it doesn't.  The only standard date format that always works is:

'YYYYMMDD'

For example:

SET DATEFORMAT dmy
SELECT CAST('20130115' AS datetime) AS YYYYMMDD
SELECT CAST('2013-01-15' AS datetime) AS [YYYY-MM-DD]


You can also include time, like so:
'YYYYMMDD hh:mm[:ss[.sss]]' --where hh = 24-hr format
0
 

Author Comment

by:fwstealer
ID: 38778805
okay but how do i solve this union all issue?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38785969
ScottPletcher: I stand corrected. Somehow I remembered that wrongly... Thanks!

"i tried to add
where Forecasted_EventCount != 0
at the end of the query thinking it wouldn't return 0 valued rows but it failed."


What do you mean with "it failed"? Do you get an error?  Or does it still return the rows where that field is zero as well?  In that case, are you sure you added the WHERE clause to the right query?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38790038
Well, Forecasted_EventCount is a calculated column, and if you are not selecting after it has been calculated (ie as in a subquery) then you would need to use the calculation itself.

Easier to make sure it is part of a subquery even if something like :

select * 
from ( 'the big query goes here'
        , case when sum(unschedmx) < 5 then 5 else sum(unschedmx) end +sum(schedmx) as Forecasted_EventCount ) sq
where sq.Forecasted_EventCount <> 0

Open in new window


But, from the looks of things, the same basic query is being run for each date calc, so, why not use that same base query just the once instead of union all for each day ? You can then use the calculated date with your datetime comparisons (as currently exists in the individual where statements) to get day1,day2.day3 etc... Or, use a case in the query and then check the results...

Maybe if you showed us what output layout you are trying to acheive it might help a lot...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38861758
Just to clarify that "default" date format...

MS SQL now fully supports the ISO 8601 format of YYYY-MM-DD and is the default display
http://msdn.microsoft.com/en-us/library/bb630352(v=sql.100).aspx

So, the comment "The only standard date format that always works is: 'YYYYMMDD' " needs to be extended to include the ISO 8601 seperated format (as well as the ISO unseperated format)

Per MS regarding the seperated format :
The advantage in using the ISO 8601 format is that it is an international standard. Date and time values that are specified by using this format are unambiguous. This format is not affected by the SET DATEFORMAT, SET LANGUAGE, of login default language settings
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38861968
By "standard", I meant w/o letters in the datetime.

ISO 8601 format is:
YYYY-MM-DDTHH:MM:SS

You must include the "T" for that to be a safe format.

I think that's a little odd for people to remember vs. just always using YYYYMMDD.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38862498
"T" For a datetime, yes, for a date, no. But then all the DB's I know also handle without the T seperator.

YYYYMMDD is also the ISO 8601 format - just not seperated.

Problem is, ISO 8601 describes a large number of date and/or time formats. It defines a very basic Basic Format (without punctuation) and Extended Format (with punctuation). It allows elements to be omitted (like the hyphens and ot the "T").

The complete disambiguation of the format is YYYY-MM-DDThh:mm:ss.sTZD (where TZD is the time zone). But again, not every element, or punctuation is required.

And yeah, YYYYMMDD should be easy. I personally prefer "DD MMM YYYY" the alphabetic form - self desciptive but a PITA if doing multi-lingual stuff.

Love the datetime problems, always gets interesting considering it is a dumb floating point number, and what the various databases do with that number...

But as far as a date is concerned, YYYY-MM-DD is just as legit as YYYYMMDD but hasnt always been the case.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38863240
Thanks for the additional info Mark!  As we're dealing with this on a daily basis, we get used to certain habits but after a while forget why we're actually doing things in that specific way.  So every now and then it's good to be reminded about this!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38864448
>> But then all the DB's I know also handle without the T seperator. <<

So you don't know SQL Server?

SET DATEFORMAT dmy --or, say, SET LANGUAGE german

SELECT CAST('20120223 11:11' AS datetime) AS YYYYMMDD

SELECT CAST('2012-02-23 11:11' AS datetime) AS [YYYY-MM-DD]


Are you suggesting using one "standard" format for date-only values:
YYYY-MM-DD

but a different "standard" format for datetime values??:
YYYYMMDD HH:MM...

Even that's unsafe too, aside from being inherently confusing.  For example, when you change a column from a date to a datetime, exisiting code would start failing.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now