sql - Select first and last record each day -

sql - Select first and last record each day -

i have table engineerid, datetimecreated datetime, jobid , audittypeid

i need query shows first (engineerid, jobid audittypeid 1) , lastly (engineerid, jobid audittypeid 2) on each row of query.

select top (100) percent dbo.audittrail.engineerid, dbo.audittrail.audittypeid, dbo.engineers.name, dbo.engineers.engineertypecode, dbo.audittrail.jobid, cast(dbo.audittrail.datetimecreated date) _date dbo.audittrail inner bring together dbo.engineers on dbo.audittrail.engineerid = dbo.engineers.engineerid (dbo.audittrail.audittypeid = 1) , (dbo.engineers.engineertypecode = 'p') , (dbo.engineers.engineertypecode = 'p') , (datepart(mm, dbo.audittrail.datetimecreated) = 6) , (datepart(yyyy, dbo.audittrail.datetimecreated) = 2014) grouping audittrail.engineerid, jobid, audittypeid, engineers.name, engineers.engineertypecode, cast(dbo.audittrail.datetimecreated date) order dbo.audittrail.engineerid desc

for first part of query. unfortunatly cannot see select first record each day

any help appreciated

first info need, including create date. grouping info date, select min of each day. finally, bring together 2 sets, selecting minimum of each day -- is, first occurrence of each day.

with allmonth( engineerid, audittypeid, name, engineertypecode, jobid, datetimecreated )as( select top (100) percent a.engineerid, a.audittypeid, e.name, e.engineertypecode, a.jobid, a.datetimecreated dbo.audittrail bring together dbo.engineers e on e.engineerid = a.engineerid , e.engineertypecode = a.engineertypecode a.audittypeid = 1 , a.engineertypecode = 'p' , a.datetimecreated >= dateadd( mm, datediff( mm, 0, getdate()), 0) , a.datetimecreated < dateadd( mm, datediff( mm, 0, getdate()) + 1, 0) ), firstbyday( mindate )as( select min( datetimecreated ) allmonth grouping cast( datetimecreated date ) ) select * allmonth bring together firstbyday f on f.mindate = a.datetimecreated order a.engineerid desc;

to lastly item of each day, add together max firstbyday , add together join. work 1 long row if want to.

btw, didn't hear few years later versions of mssql ignored top (100) percent? don't work much these days, , memory is...well, just...somewhere around here...

sql sql-server sql-server-2008