Databases
StackOverflow2013
Postsdbo
POSelect max DateTime column value over a year
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect max DateTime column value over a year
    primarykey
    data
    text
    <p>Using SQL Server 2005, I have a table that records the <code>datetime</code> that a user clocks out of their shift. It stores these values in a <code>datetime</code> column.</p> <p>Sometimes people have mistakenly clocked out of their shift early and then they clock out again at the right time.</p> <p>I have been tasked with pulling all of the dates that a user left early for the day. There are some days that have two entries and I have to get the maximum entry for that day. They may have clocked out at the right time for the second time, but their first time is still in there.</p> <p>So, there is a table that is called <code>tblShift</code> that has the time that they are supposed to clock out. This is <code>tblShift</code>. The datetime value is <code>tblShift.ShiftEnd</code>.</p> <p>Then there is the table <code>tblCLock</code> that shows their types of clockouts. These could be breaks or actually clocking out at the end of their shift, or a mistaken clock out.</p> <p>This is the WRONG way I am doing it:</p> <pre><code>SELECT clockDateTime, datediff(n, tblShift.ShiftEnd, tblClock.ClockDateTime) as minutes from tblClock INNER JOIN tblShift ON tblClock.userid = tblShift.userid AND Year(tblClock.ClockDateTime) = Year(tblShift.ShiftEnd) AND Month(tblClock.ClockDateTime) = Month(tblShift.ShiftEnd) AND Day(tblClock.ClockDateTime) = Day(tblShift.ShiftEnd) WHERE tblClock.USERID = 29689 --one of the id's in the table and clocktypeid = 1 and clockstatusid = 2 and tblShift.ShiftEnd between '2011-11-11 12:00:00 AM' and '2012-11-11 11:59:59 PM' and tblShift.ShiftTypeID = 1 and datediff(n, tblShift.ShiftEnd, tblClock.ClockDateTime) &lt; 0 ORDER BY tblClock.clockdatetime desc </code></pre> <p>What happens there is that the mistaken early clock out is used on the days that they clocked out by mistake and then clocked out at the proper time at the end of their shift.</p> <p>I am trying to figure out how to work MAX into this query so that I am only getting the maximum from each day. </p> <p>I have started over a few times and am not succeeding.</p> <p><strong>tblClock</strong>:</p> <pre><code>[clockID] [int] IDENTITY(1,1) NOT NULL, [clockStatusID] [int] NULL, [clockTypeID] [int] NULL, [userID] [int] NULL, [clockDateTime] [datetime] NULL CONSTRAINT [DF_tblClock_clockDateTime] DEFAULT (getdate()), [clockNotes] [text] NULL, </code></pre> <p><strong>tblShift</strong>:</p> <pre><code>[shiftID] [int] IDENTITY(1,1) NOT NULL, [shiftTypeID] [int] NULL CONSTRAINT [DF_tblShift_shiftTypeID] DEFAULT ((1)), [userID] [int] NULL, [shiftStart] [datetime] NULL, [shiftEnd] [datetime] NULL, [shiftNotes] [text] NULL, </code></pre> <p>UPDATE: I have been working on this most of a day, and have gotten it to this point:</p> <pre><code>SELECT max(tblClock.clockdatetime) as clockdatetime, max(tblShift.ShiftEnd), datediff(n, max(tblShift.ShiftEnd), max(tblClock.ClockDateTime)) as minutes FROM tblclock INNER JOIN tblShift ON tblClock.userid = tblShift.userid AND Year(tblClock.ClockDateTime) = Year(tblShift.ShiftEnd) AND Month(tblClock.ClockDateTime) = Month(tblShift.ShiftEnd) AND Day(tblClock.ClockDateTime) = Day(tblShift.ShiftEnd) WHERE tblClock.userid = 29689 AND tblClock.clocktypeid = 1 AND tblClock.clockstatusid = 2 AND tblShift.ShiftEnd between '2011-10-01 12:00:00 AM' AND '2012-10-01 11:59:59 PM' AND tblShift.ShiftTypeID = 1 GROUP BY Year(tblClock.clockdatetime),Month(tblClock.clockdatetime),day(tblClock.clockdatetime) ORDER BY max(tblClock.clockdatetime) desc </code></pre> <p>I am using my scripting language to only display those with minutes less than zero, because I can't get the AND Minutes &lt; 0 into the where portion.</p> <p>Also, thank you for the edit to properly word the question. Marching on...</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdavidj
    1. USdavidj
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COWhat are the possible values for clocktypeid and clockstatusid? You have to use these values to obtain the valid clock-ins, then use this resultset to determine the correct clock-outs.
      singulars
      1. POSelect max DateTime column value over a year
      1. USYván Ecarri
    2. COClockTypeId = 1(shift),2(lunch),3(break). ClockStatusID = 1(clockin),2(clockout). The clocktypeid of the early clockouts is 1 for shift and 2 for clockout. These numbers are stored even if the clockout is done early.
      singulars
      1. POSelect max DateTime column value over a year
      1. USdavidj
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload