declare @FromDate as datetime; declare @ToDate as datetime; declare @OperID as varchar(20) = 'OP1'; declare @Year as int = 2018 declare @Month as int = 1 set @FromDate = convert(date,convert(varchar,@Year) + '-' + convert(varchar,@Month) + '-01') set @ToDate = dateadd(d,-1,DATEADD(m, 1, @FromDate)) select Branch_no, operid, clock_date, [I], [O] from ( select Branch_no, operid, convert(date, clock_date) as clock_date, convert(time, clock_date) as clock_time, clock_type, Workstation_no from ROSTER_TIMECLOCK where Clock_date >=CONVERT(DATETIME, @FromDate, 102) and Clock_date <=CONVERT(DATETIME, @ToDate, 102) and OperID=@OperID ) as TheClock PIVOT ( min(clock_time) FOR clock_type in ([I],[O]) ) as ThePivot
@FromDate
@ToDate
datetime
CONVERT
WHERE MONTH(Clock_Date) = @Month AND YEAR(Clock_Date) = @Year AND OperID=@OperID
declare @Year as int = 2018 declare @Month as int = 1 select day(dateadd(dd,-1,dateadd(mm,1,datefromparts(@Year,@Month,1))))
SELECT Day(EOMonth(DateFromParts(@Year, @Month, 1)))
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)