Jump to content

Restricted Fields In Select Clause Question


vafo
 Share

Recommended Posts

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...