vafo Posted November 23, 2009 Share Posted November 23, 2009 Hello! I have a particular problem at hand and I'm having some difficulties solving it.The issue is returning a column with a numeric value from an aggregate function counting the number of lines that obey to a certain condition. in this case, I have a table with a time watch and a table with the hours employess are supposed to check in, and I want to return the number of delays per month by making a check on the entry time registered and the table with the schedules (chek in hour table.)As this might be a bit confusing at first, I'll let the code speak for myself: SET DATEFIRST 1 DECLARE @TodayDayOfWeek INT DECLARE @EndOfPrevWeek DateTime DECLARE @StartOfPrevWeek DateTime --get current weekday SET @TodayDayOfWeek = datepart(dw, GetDate()) --get next sunday SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate()) --get monday before last sunday SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate()) BEGIN SELECT NOME, COUNT(CD_USERNAME) as Entradas, SUM(DATEDIFF(hour,[DT_ENTRADA],[DT_SAIDAALMOCO]) + DATEDIFF(hour,[DT_REGRESSOALMOCO],[DT_SAIDA])) as TotalHoras, COUNT(DATEDIFF(minute,[DT_ENTRADA],[DT_HORAENTRADA])) as Atrasos, DT_HORAENTRADA FROM [TOnline].[dbo].[tblPonto] INNER JOIN [TOnline].[dbo].[COLABORADORES] ON [TOnline].[dbo].[tblPonto].CD_USERNAME=[TOnline].[dbo].[COLABORADORES].USERNAME_NETPA INNER JOIN [TOnline].[dbo].[HORARIOS_COLAB] ON [TOnline].[dbo].[COLABORADORES].CD_COLABORADOR=[TOnline].[dbo].[HORARIOS_COLAB].CD_COLABORADOR WHERE [DT_ENTRADA] < @EndOfPrevWeek AND [DT_ENTRADA] > @StartOfPrevWeek GROUP BY NOME, DT_HORAENTRADA ORDER BY NOME END the COUNT(DATEDIFF(minute,[DT_ENTRADA],[DT_HORAENTRADA])) is where my issue resides. I'd like it to be COUNT(DATEDIFF(minute,[DT_ENTRADA],[DT_HORAENTRADA]) > 30), meaning, number of times employees logged in with more than a 30 minutes delay. I tryed using a subquery but apparently I can only build a subquery on the WHERE part of the clause, but I'd like to keep this as one only query.any suggestions?thank you for your attention. Link to comment Share on other sites More sharing options...
vafo Posted November 23, 2009 Author Share Posted November 23, 2009 Also remembered another problem: values are datetime format but I am using datediff to check for a 30 minute condition every day.How can I make this check ignore the day and only consider the time part of the fields? (a cast to shortdatetime might work except my sql server does not recognize the data type... Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.