This should be terribly simply, but I just can't seem to figure out where I'm going wrong. I have a table that logs usage of a few software licenses in my division, so it has columns for the username, the license, and the date/time checked out and date/time checked in. I have written a quick SQL query that totals how much time all the users have spent in three of the licenses during this work week. It works fine. But now I just want to limit that query to those users who have used those licenses for more than two hours. Sounds simple enough, but I must be overlooking something obvious. I have tried [Minutes]>120 in both a WHERE and a HAVING statement, but neither worked. I have also tried changing the name of the column to something other than Minutes in the off case that there might be some kind of keyword confusion, but it didn't work. I have also tried various other unlikely fixes but continually get the error "Invalid Column Name 'Minutes'". Any help would be very appreciated since this is driving me nuts. The code that works is below. Declare @datMin datetime Declare @datMax datetime set @datMin=DateAdd(mi, -(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())), --Gets the current minute of the day DateAdd(dd,-datepart(dw,GetDate())+1,GetDate())) --Gets First Day of the Week at this time set @datMax=DateAdd(mi, 1439-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())), GetDate()) Select UserName, Sum(Case When DateOut<@datMin and DateIn<@datMax and DateIn>@datMax Then datediff(n, @datMin, DateIn) When DateOut<@datMin and DateIn>@datMax Then datediff(n, @datMin, @datMax) When DateOut>@datMin and DateOut<@datMax and DateIn>@datMax Then datediff(n, DateOut, @datMax) Else datediff(n, DateOut, DateIn) End) as [Minutes] From [Log] Where ((DateOut>=@datMin and DateOut<=@datMax) or (DateIn>=@datMin and DateIn<=@datMax) or (DateOut<@datMin and DateIn>@datMax)) And (License='Viewer' or License='ARC/INFO' or License = 'Editor') --And ([Minutes]>120) Group by UserName --Having ([Minutes]>120) Order by [Minutes] desc
Simple? coding problem with a Where statement
Posted - 2008-04-17 : 15:19:11
