In code below I am trying to filter by date range fromDate & ToDate but it gives me only 10 data but there are 37 data by fromdate todate , i have also tried to make
@DisplayLength optional but it shows nothing for paging sorting searching, range wise Search
What I have tried:
ALTER procedure [dbo].[spGetRetailerDealerServiceCenterList]
(
@DisplayLength int = NULL,
@DisplayStart int = NULL,
@SortCol int = NULL,
@SortDir nvarchar(10) = NULL,
@Search nvarchar(255) = NULL,
@FromDate VARCHAR(20) = null,
@ToDate VARCHAR(20) = null,
@Action char(1) = null
)
as
begin
Set @FromDate = ISNULL (@FromDate,'')
Set @ToDate = ISNULL (@ToDate,'')
--if(@FromDate != '' and @ToDate != '')
--begin
-- set @DisplayLength = 100
--end
Declare @FirstRec int, @LastRec int,@OffsetValue int,@PagingSize int
Set @FirstRec = @DisplayStart;
Set @LastRec = @DisplayStart + @DisplayLength;
--print @FromDate
--print @ToDate
if(@Action='A')
begin
With CTE_RetailerList as
(
Select Distinct ROW_NUMBER() over (order by
case when (@SortCol = 0 and @SortDir='asc')
then DM.DealerId
end asc,
case when (@SortCol = 0 and @SortDir='desc')
then DM.DealerId
end desc,
case when (@SortCol = 1 and @SortDir='asc')
then DM.DealerName
end asc,
case when (@SortCol = 1 and @SortDir='desc')
then DM.DealerName
end desc,
case when (@SortCol = 2 and @SortDir='asc')
then DM.Code
end asc,
case when (@SortCol = 2 and @SortDir='desc')
then DM.Code
end desc,
case when (@SortCol = 3 and @SortDir='asc')
then DM.[Address]
end asc,
case when (@SortCol = 3 and @SortDir='desc')
then DM.[Address]
end desc,
case when (@SortCol = 4 and @SortDir='asc')
then DM.ContactNo
end asc,
case when (@SortCol = 4 and @SortDir='desc')
then DM.ContactNo
end desc,
case when (@SortCol = 5 and @SortDir='asc')
then DM.EmailId
end asc,
case when (@SortCol = 5 and @SortDir='desc')
then DM.EmailId
end desc,
case when (@SortCol = 6 and @SortDir='asc')
then ZM.ZONE_NM
end asc,
case when (@SortCol = 6 and @SortDir='desc')
then ZM.ZONE_NM
end desc,
case when (@SortCol = 7 and @SortDir='asc')
then SM.STATE_NM
end asc,
case when (@SortCol = 7 and @SortDir='desc')
then SM.STATE_NM
end desc,
case when (@SortCol = 8 and @SortDir='asc')
then ADM.District_NM
end asc,
case when (@SortCol = 8 and @SortDir='desc')
then ADM.District_NM
end desc,
case when (@SortCol = 9 and @SortDir='asc')
then CM.CITY_NM
end asc,
case when (@SortCol = 9 and @SortDir='desc')
then CM.CITY_NM
end desc,
case when (@SortCol = 10 and @SortDir='asc')
then BM.BRANCH_Name
end asc,
case when (@SortCol = 10 and @SortDir='desc')
then BM.BRANCH_Name
end desc,
case when (@SortCol = 11 and @SortDir='asc')
then DM.CreatedDate
end asc,
case when (@SortCol = 11 and @SortDir='desc')
then DM.CreatedDate
end desc
)
as RowNum,
COUNT(*) over() as TotalCount,
ISNULL (DM.DealerName,'NA') as DEALERNAME,
ISNULL (DM.DealerId,0 )as DEALERID,
ISNULL (DM.Code,0) as DEALERCODE,
ISNULL (DM.[Address],'NA') as [ADDRESS],
ISNULL (DM.ContactNo,'NA') as CONTACTNO,
ISNULL (DM.EmailId,'NA')as EMAIL ,
ISNULL (ZM.ZONE_NM,'NA') as ZONENAME,
ISNULL (SM.STATE_NM ,'NA')as STATENAME,
ISNULL (ADM.District_NM,'NA' )as DISTRICTNAME,
ISNULL (CM.CITY_NM,'NA') as CITYNAME,
ISNULL (BM.BRANCH_Name,'NA') as BRANCHNAME,
ISNULL(convert(varchar(20), DM.CreatedDate,103)+' '+convert(varchar(20),convert(time, DM.CreatedDate),100),0)CREATEDDATE,
ISNULL(DATENAME(MONTH, DM.CreatedDate),0)Months,
ISNULL(CONVERT(VARCHAR(10), CAST( DM.CreatedDate AS TIME), 0),0) times
FROM [dbo].[Dealer_Master] DM WITH(NOLOCK)
INNER JOIN dbo.ZoneMaster ZM WITH(NOLOCK) on ZM.ZONE_ID=DM.ZoneId
INNER JOIN [dbo].state_master SM WITH(NOLOCK) on SM.STATE_ID=DM.StateId
INNER JOIN [dbo].[A_DistrictMaster] ADM WITH(NOLOCK) on ADM.District_ID=DM.DistrictId
INNER JOIN [dbo].[A_CITY_MASTER] CM WITH(NOLOCK) on CM.CITY_ID=DM.CityId
INNER JOIN [dbo].branch_master BM WITH(NOLOCK) on BM.BRANCH_ID=DM.BranchId
where ((DM.DealerName IS NOT NULL)
AND
(
CONVERT(VARCHAR,DM.CreatedDate,112) >= CONVERT(VARCHAR,CONVERT(DATETIME,@FromDate),112) OR CONVERT(VARCHAR,CONVERT(DATETIME,@FromDate),112) IS NULL
AND CONVERT(VARCHAR,DM.CreatedDate,112) <= CONVERT(VARCHAR,CONVERT(DATETIME,@ToDate),112) OR CONVERT(VARCHAR,CONVERT(DATETIME,@ToDate),112) IS NULL )
AND ( @Search IS NULL
Or DM.DealerId like '%' + @Search + '%'
Or DM.DealerName like '%' + @Search + '%'
Or DM.Code like '%' + @Search + '%'
Or DM.[address] like '%' + @Search + '%'
Or DM.contactno like '%' + @Search + '%'
Or DM.EmailId like '%' + @Search + '%'
Or SM.STATE_NM like '%' + @Search + '%'
Or ADM.District_NM like '%' + @Search + '%'
Or CM.CITY_NM like '%' + @Search + '%'
Or BM.BRANCH_Name like '%' + @Search + '%'
)
))
Select *
from CTE_RetailerList
where RowNum > @FirstRec and RowNum <= @LastRec
order by CreatedDate desc
end
end