CODEWITHSUNDEEP
sql-server-2008t-sqlms-accessvba
HackMaster
HackMaster

Reputation: 109

Access calculates date but sql is making it null?

I have an access query that takes start and stop and returns runtime in minutes then based on the runtime in mins the run time in hour is calculated:

The part of access query used to retrieve the below results are:

 [Net Weight Tracking].[Start Time], [Net Weight Tracking].[Stop Time], 
    Hour([start time]) AS HrStart, Hour([stop time]) AS HrStop, 
    [date] & " " & [start time] AS Start,
     IIf([hrstart]>12 And [hrstop]<12,[date]+1 & " " & [stop time],[date] & " " & [stop time]) AS Stop, 
    DateDiff("n",[start],[stop]) AS [Runtime (Min)], 
    [runtime (Min)]/60 AS [Runtime (Hrs)]

enter image description here

But on my SQL Server Side:

The Query i use to retrieve the same info for sql is:

  SELECT TOP (100) PERCENT Date, [Unit UPC Base Item], [Item (Optional)], [Preset Number], [Product Group], [Product Group Description], 
    Shift, [Rotation Code],BBD,
    [Operator Name], Supervisor, [Production Line],  
    [Production Line Description], [Bagger Number], StartTime, 
    StopTime, HrStart, HrStop,
    Start, Stop, DATEDIFF(MINUTE, Start, Stop) AS [RunTime Mins], 
    DATEDIFF(MINUTE, Start, Stop) / 60.0 AS [RunTime Hrs]
 FROM (SELECT StartTime, StopTime, HrStart,HrStop, Start, 
    DATEADD(DAY, CASE WHEN HrStart > 12 AND HrStop < 12 THEN 1 ELSE 0 END, Stop) 
    AS Stop 
FROM (SELECT LEFT(CONVERT(TIME, dbo.[Net Weight Tracking].[Start Time]), 5) 
AS StartTime, 
LEFT(CONVERT(TIME, dbo.[Net Weight Tracking].[Stop Time]), 5) AS StopTime, 
DATEPART(HOUR, dbo.[Net Weight Tracking].[Start Time]) AS HrStart, 
DATEPART(HOUR, dbo.[Net Weight Tracking].[Stop Time]) AS HrStop, 
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CONVERT(TIME, dbo.[Net Weight Tracking].[Start Time])), dbo.[Net Weight Tracking].Date) AS Start, 
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CONVERT(TIME, dbo.[Net Weight Tracking].[Stop Time])), dbo.[Net Weight Tracking].Date) 
AS Stop

enter image description here

i wonder how is access able to get the runtime in hrs?? plz help??

Upvotes: 1

Views: 66

Answers (2)

HackGod555
HackGod555

Reputation: 134

You query seems fine to me and if this is the only row that is giving u an error, then may be there was an error when trying to update your Stop time, so what i would do is try to manually update the STOP to 10/7/2013 and see if that fixes your issue, my guess is since the stop time is null, it evaluates your query to the below: 

Start, Stop, DATEDIFF(MINUTE, Start, null) AS [RunTime Mins], 
    DATEDIFF(MINUTE, Start, null) / 60.0 AS [RunTime Hrs]

the result of the above is null that is the reason all 3 other columns are null:

RunTimeMins, RunTimeHrs, Stop Time is null because it gets derived from Stop

Upvotes: 1

Parfait
Parfait

Reputation: 107567

In the Access query, [Stop] (used in [Runtime (Min)] is derived from an IIF() function which is simply the MS Access function for if/then/else. In order to reproduce the same result, you need to replicate this if/then/else in SQL Server which is not shown above.

Furthermore, SQL Server's corresponding IF function is the CASE function where you insert the condition in a WHEN clause and apply it to a THEN clause and for its false outcome in an ELSE clause. Aside, SQL Server 2012 does use the IIF function.

Also be aware in SQL Server you cannot reference an alias of a calculated column in the same query, meaning unlike the Access query you will have to re-write out the other formulas used in a particular expression.

Upvotes: 1

Related Questions