Dear All, Following this tutorial https://www.aspsnippets.com/Articles/Custom-Paging-in-ASPNet-GridView-using-SQL-Server-Stored-Procedure.aspx
I am having trouble creating my stored procedure, because my statement involves multiple join tables. How do I get the Row Number
that is required to do the custom pagination.
Below is my Stored Procedure
USE [EWACS]
GO
/****** Object: StoredProcedure [dbo].[Transaction.Attendance] Script Date: 12/03/2018 21:49:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE GetTransactionPageWise
@PageIndex INT = 1
,@PageSize INT = 50
,@RecordCount INT OUTPUT
,@StartDate DATETIME
,@EndDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
WITH attendance AS ( SELECT a.StaffNo,a.DateLog, { fn DAYOFWEEK(DateLog) } AS dayID,MIN(a.timelog) as TimeIn, MAX(a.timelog) as TimeOut,a.TransType
FROM dbo.[Transaction.Attendance] a
WHERE a.DateLog Between @StartDate and @EndDate and a.TransType = '1'
GROUP BY a.StaffNo,a.DateLog,a.TransType),
test AS ( SELECT a.StaffNo,a.DateLog, { fn DAYOFWEEK(DateLog) } AS dayID,MIN(a.timelog) as TimeIn1, MAX(a.timelog) as TimeOut2,a.TransType
FROM dbo.[Transaction.Attendance] a
WHERE a.DateLog Between @StartDate and @EndDate and a.TransType = '1'
GROUP BY a.StaffNo,a.DateLog,a.TransType)
SELECT e.StaffNo, e.Name AS StaffName, j.UnitName as Dept,a.dayID,Convert(nvarchar(12),b.Date,106) as DateLog,b.DayName, { fn DAYOFWEEK(b.Date) } AS dayID2,
TimeIn,k.TimeOut2 AS TimeOut,c.Reason As lateIn,d.Reason As EarlyOut,h.Name As Leave, f.StartHour,f.EndHour,f.flagIncludeBrakeTime, f.BreakStartHour,
f.BreakEndHour,f.flagWorkingDay, f.HalfdayStartHour,f.HalfdayEndHour,f.flagIncludeHalfday, f.OTStartHour,f.OTEndHour,f.flagIncludeOvertime,c.Status as Stat1,
c.Remarks as RemarksLate,d.Status as Stat2,d.Remarks as RemarksEarly,g.Status as Stat3, Convert(nvarchar(12),c.ApprovedDate,106) AS ApprovedDate1,
Convert(nvarchar(12),d.ApprovedDate,106) AS ApprovedDate2
INTO #Results
FROM attendance a
RIGHT JOIN dbo.[DimDate] b ON a.DateLog = b.Date
LEFT JOIN test k On a.DateLog = k.DateLog and a.StaffNo = k.StaffNo
INNER JOIN dbo.[User.Profile] e On a.StaffNo = e.StaffNo
LEFT JOIN dbo.[Attendance.LateIn] c On b.Date = c.DateLog and e.StaffNo = c.StaffNo
LEFT JOIN dbo.[Attendance.EarlyOut] d On b.Date = d.DateLog and e.StaffNo = d.StaffNo
LEFT JOIN dbo.[Attendance.Leave] g on e.StaffNo = g.StaffNo and b.Date = g.DateLog
LEFT JOIN dbo.[Leave.Type] h ON g.Type = h.Id
LEFT JOIN dbo.[Working.Hour] f On e.WorkingHourId = f.Id AND f.DayId = a.dayID
INNER JOIN dbo.[Hierarchy] j ON e.DeptId = j.UnitCode
WHERE b.Date Between @StartDate and @EndDate
ORDER BY Dept, Staffno ASC, Datelog
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
GO
Thank you in advance