Can I get assistance please I have the stored procedure below with two date parameters (@datefrom and @dateto) that is receiving from the user when s\he generate the reports on the application. The format of values when the user pass to the parameter is as follow ‘2019-01-01’ to ‘2019-02-31’, on the database the records on the column that the parameters is comparing with is as follow ‘2019-02-28 00:00:00.000’.
My challenge now is when am running this procedure passing values of this format ‘2019-02-31’ is not pulling any record on the database but am passing values of this format ‘2019-02-28 00:00:00.000’ its pulling the records.
Can someone assists what I need to fix on the procedure?
TER PROCEDURE [dbo].[ir_prc_getAgeAnalysis] @datefrom datetime, @dateto datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
--get all registered companies
set dateformat dmy
select @dateto = dateadd(dd,1,@dateto)
--temp table holds status chage date from 60 to 70 for companies
create table #temp (appkey int,datechanged datetime,dtecreated datetime)
insert #temp
select a.appkey, logdte, max(b.dtecreated) from mrapplication a, mrlogitm b
where a.appkey = b.appkey
and b.StatCdFrom <> 70 AND b.StatCdTo = 70
group by a.appkey,logdte
delete dbo.irAgeAnalysis
INSERT dbo.irAgeAnalysis (AO, DateStatus70, DateFeesPaid,DateRegistered,StatCdFrom,StatCdTo,
Cokey,CoNam,CurrentStatus,Regnum,numBranch,CertificateDate)
SELECT DISTINCT mrCompany.AO, #temp.datechanged AS DateStatus70, irEventDate.EventDate as DateFeesPaid,mrLogItm.DteCreated AS DateRegistered, mrLogItm.StatCdFrom,
mrLogItm.StatCdTo,mrCompany.CoKey, mrCompany.CoNam, mrApplication.StatCd AS CurrentStatus, mrCompany.NCRMrcNum,mrapplication.numBranch ,a.EventDate
FROM mrLogItm INNER JOIN
mrApplication ON mrLogItm.AppKey = mrApplication.AppKey INNER JOIN
mrCompany ON mrApplication.CoKey = mrCompany.CoKey INNER JOIN
irEventDate a ON mrCompany.CoKey = a.CoKey INNER JOIN
#temp ON mrApplication.AppKey = #temp.AppKey LEFT OUTER JOIN
irEventDate ON mrCompany.CoKey = irEventDate.CoKey
INNER JOIN mrcobranch ON mrcobranch.COKEY = mrCompany.CoKey
WHERE (mrLogItm.StatCdFrom <> 80) AND (mrLogItm.StatCdTo = 80)
AND (irEventDate.DteCreated >= @datefrom ) AND (irEventDate.DteCreated < @dateto)
AND (irEventDate.TypeCd = 'FEESP')
AND (a.TypeCd = 'CERTI')
order By mrCompany.AO
What I have tried:
When I uncommented number 1. On the procedure under where condition it’s not pulling anything
Number 2 and 3 It’s just duplicating the records and leave other records out
TER PROCEDURE [dbo].[ir_prc_getAgeAnalysistest]
@datefrom datetime,
@dateto datetime
--@datefrom nvarchar(25),
--@dateto nvarchar(25)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
--get all registered companies
set dateformat dmy
select @dateto = dateadd(dd,1,@dateto)
--temp table holds status chage date from 60 to 70 for companies
create table #temp (appkey int,datechanged datetime,dtecreated datetime)
insert #temp
select a.appkey, logdte, max(b.dtecreated) from mrapplication a, mrlogitm b
where a.appkey = b.appkey
and b.StatCdFrom <> 70 AND b.StatCdTo = 70
group by a.appkey,logdte
delete dbo.irAgeAnalysis
INSERT dbo.irAgeAnalysis (AO, DateStatus70, DateFeesPaid,DateRegistered,StatCdFrom,StatCdTo,
Cokey,CoNam,CurrentStatus,Regnum,numBranch,CertificateDate)
SELECT DISTINCT mrCompany.AO, #temp.datechanged AS DateStatus70, irEventDate.EventDate as DateFeesPaid,mrLogItm.DteCreated AS DateRegistered, mrLogItm.StatCdFrom,
mrLogItm.StatCdTo,mrCompany.CoKey, mrCompany.CoNam, mrApplication.StatCd AS CurrentStatus, mrCompany.NCRMrcNum,mrapplication.numBranch ,a.EventDate
FROM mrLogItm INNER JOIN
mrApplication ON mrLogItm.AppKey = mrApplication.AppKey INNER JOIN
mrCompany ON mrApplication.CoKey = mrCompany.CoKey INNER JOIN
irEventDate a ON mrCompany.CoKey = a.CoKey INNER JOIN
#temp ON mrApplication.AppKey = #temp.AppKey LEFT OUTER JOIN
irEventDate ON mrCompany.CoKey = a.CoKey
INNER JOIN mrcobranch ON mrcobranch.COKEY = mrCompany.CoKey
WHERE (mrLogItm.StatCdFrom <> 80) AND (mrLogItm.StatCdTo = 80)
--1. AND (irEventDate.DteCreated >= @datefrom ) AND (irEventDate.DteCreated <= @dateto)
--2. AND irEventDate.DteCreated BETWEEN convert(Datetime,@datefrom,102) AND convert(Datetime,@dateto,102 )
--3. AND (irEventDate.DteCreated >= convert(nvarchar(20), @datefrom)) AND (irEventDate.DteCreated <= convert(nvarchar(20), @dateto))
AND (mrCompany.NCRMrcNum IS NOT NULL)
AND (irEventDate.TypeCd = 'FEESP')
AND (a.TypeCd = 'CERTI')
order By mrCompany.AO