UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Add New Column Slq View, SQL Server 2008    
 
   
smyeong
post Mar 13 2019, 02:27 AM
Post#1



Posts: 404
Joined: 6-November 02
From: Malaysia


Hi, I need to add another column in this SQL view (to show lateness report) so that it would have another column to show the total of minutes wasted since last thumbprint at the device

TimeIn - '08:30:00am' = MinuteWasted
Eg 08:40:00 - 08:30:00 = 10 minute late

How can i add into below current slq ?


TQ




SELECT TrDate, TrDay, DepartmentDesc, CardNo, Name, TimeIn, TimeOut, EMPGROUP, DepartmentCode
FROM dbo.VW_RPT_ATTD_RECORD
WHERE (CardNo <= '0000009999') AND (TimeIn > '08:31:00') AND (DepartmentDesc = 'svc (l)') AND (TrDate < '1/1/2019') AND (TrDate > '1/1/2018')
This post has been edited by smyeong: Today, 07:15 AM

--------------------
We should see thing in both side. positive and negative
Pls visit http://ipohtech.blogspot.com for tech solutions around the world..
Malaysia.
Go to the top of the page
 
cheekybuddha
post Mar 13 2019, 05:07 AM
Post#2


UtterAccess VIP
Posts: 10,953
Joined: 6-December 03
From: Telegraph Hill


SQL:
CODE
SELECT
  TrDate,
  TrDay,
  DepartmentDesc,
  CardNo,
  Name,
  TimeIn,
  DATEDIFF(n, '08:30:00', TimeIn) AS MinutesLate,
  TimeOut,
  EMPGROUP,
  DepartmentCode
FROM dbo.VW_RPT_ATTD_RECORD
WHERE (CardNo <= '0000009999')
   AND (TimeIn > '08:31:00')
   AND (DepartmentDesc = 'svc (l)')  
   AND (TrDate < '1/1/2019')
   AND (TrDate > '1/1/2018')
;


(Assumes that TimeIn is a TIME datatype)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
smyeong
post Mar 13 2019, 09:18 PM
Post#3



Posts: 404
Joined: 6-November 02
From: Malaysia


Dear Cheekybuddha

Thanks for the SQL. It works. By the way, how can i have 2 conditons where TimeIN is exceede 13:30:00 , it would use datediff(n, 13:30:00) otherwies, use datediff(n,08:30:00)
below is my attempt for that but it did not work

TQ



SELECT
TrDate,
TrDay,
DepartmentDesc,
CardNo,
Name,
TimeIn,
iif(TimeIn < '13:30:00', DATEDIFF(n, '08:30:00', TimeIn), datediff(n, '13:30:00')) AS MinutesLate,
TimeOut,
EMPGROUP,
DepartmentCode
FROM dbo.VW_RPT_ATTD_RECORD
WHERE (CardNo <= '0000009999')

--------------------
We should see thing in both side. positive and negative
Pls visit http://ipohtech.blogspot.com for tech solutions around the world..
Malaysia.
Go to the top of the page
 
MadPiet
post Mar 13 2019, 10:23 PM
Post#4



Posts: 2,931
Joined: 27-February 09



Thanks for the SQL. It works. By the way, how can i have 2 conditons where TimeIN is exceede 13:30:00 , it would use datediff(n, 13:30:00) otherwies, use datediff(n,08:30:00)
below is my attempt for that but it did not work

TQ



SELECT
TrDate,
TrDay,
DepartmentDesc,
CardNo,
Name,
TimeIn,
iif(TimeIn < '13:30:00', DATEDIFF(n, '08:30:00', TimeIn), datediff(n, '13:30:00')) AS MinutesLate,
TimeOut,
EMPGROUP,
DepartmentCode
FROM dbo.VW_RPT_ATTD_RECORD
WHERE (CardNo <= '0000009999')

Pretty sure you can use IIF() in T-SQL. If not, you can use

CASE WHEN TimeIn<'13:30:00' THEN DATEDIFF(n, '08:30:00', TimeIn) ELSE datediff(n, '13:30:00') END As MinsLate
Go to the top of the page
 
cheekybuddha
post Mar 14 2019, 11:03 AM
Post#5


UtterAccess VIP
Posts: 10,953
Joined: 6-December 03
From: Telegraph Hill


Piet has almost got you to the answer, however he has missed out TimeIn parameter in the new DATEDIFF's.

It should read:
CODE
-- ...
  IIf(TimeIn < '13:30:00', DATEDIFF(n, '08:30:00', TimeIn), DATEDIFF(n, '13:30:00', TimeIn)) AS MinutesLate,
-- ...

Or:
CODE
-- ...
  CASE WHEN TimeIn<'13:30:00' THEN DATEDIFF(n, '08:30:00', TimeIn) ELSE DATEDIFF(n, '13:30:00', TimeIn) END As MinsLate,
-- ...


I prefer to write the DATEDIFF just once, and change the first time comparison parameter according to the condition. It's marginally clearer for me, but that is my own personal perference.

So, you can try:
CODE
SELECT
  TrDate,
  TrDay,
  DepartmentDesc,
  CardNo,
  Name,
  TimeIn,
  DATEDIFF(
    n,
    CASE
      WHEN TimeIn > '13:30:00' THEN '13:30:00'
      ELSE '08:30:00'
    END,
    TimeIn
  ) AS MinutesLate,
  TimeOut,
  EMPGROUP,
  DepartmentCode
FROM dbo.VW_RPT_ATTD_RECORD
WHERE (CardNo <= '0000009999')
   AND (TimeIn > '08:31:00')
   AND (DepartmentDesc = 'svc (l)')  
   AND (TrDate < '1/1/2019')
   AND (TrDate > '1/1/2018')
;

--------------------


Regards,

David Marten
Go to the top of the page
 
smyeong
post Mar 14 2019, 10:40 PM
Post#6



Posts: 404
Joined: 6-November 02
From: Malaysia


Thanks for the reply. THE case condition work well in the SQL view. But the IIF somehow failed due to unknown reason.
Anyway TQ again






--------------------
We should see thing in both side. positive and negative
Pls visit http://ipohtech.blogspot.com for tech solutions around the world..
Malaysia.
Go to the top of the page
 
cheekybuddha
post Mar 15 2019, 04:45 AM
Post#7


UtterAccess VIP
Posts: 10,953
Joined: 6-December 03
From: Telegraph Hill


IIF is not available in all versions of SQL Server.

It is much safer to use CASE ... END, even though it is more cumbersome.

Glad you got it working!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 15 2019, 04:48 AM
Post#8


UtterAccess VIP
Posts: 10,953
Joined: 6-December 03
From: Telegraph Hill


Are 08:30 and 13:30 the start times of different shifts?

What happens if someone is on the early shift and the don't arrive until 13:31? It shows them as 1 minute late instead of 5 hours late!

--------------------


Regards,

David Marten
Go to the top of the page
 
smyeong
post Mar 15 2019, 08:20 PM
Post#9



Posts: 404
Joined: 6-November 02
From: Malaysia


hi, not a shift hour but rather full day or half day calculation..fullday start with 8:30:00 to 17:30:00 , half day (those with leave application ) start with 13:30:00
to 17:30:00....btw, is my condition look logical? o


TQ


--------------------
We should see thing in both side. positive and negative
Pls visit http://ipohtech.blogspot.com for tech solutions around the world..
Malaysia.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th March 2019 - 04:10 AM