locked
How To Change Month And Year To Fromdate And Todate In Sp RRS feed

  • Question

  • User-940083561 posted

    Dear all,

    Can somebody help me to revise below original SP where it would display cardno for the month period. What I want is to display cardno by date range Eg, txtLog_date and txtCompleted_date
    FYI, asp will call ashx file and from ashx pass the 3 parameter to sp as below

    http://172.20.100.12:888/rpt_vehicle.ashx?cardno=0000055799&month=2&year=2018

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    
    -- Batch submitted through debugger: db-deploy.SQL|6|0|C:\CorpServer\ATTD\Report\db-deploy.SQL
    Alter PROC [dbo].[$_Rpt_vehicle]
    @Card VARCHAR(16), -- Employee Card No.
    @Year INT, -- Trx. Year
    @Mnth INT, -- Trx. Month
    @txtLog_date datetime, ------ FromDate newly added field passed from asp 
    @txtCompleted_date datetime ------------ ToDate newly added field passed from asp
    
    AS SET NOCOUNT ON
    BEGIN
    DECLARE @Temp TABLE ( [Id] INT IDENTITY(1,1), [Date] DATETIME, [WDay] NVARCHAR(100) )
    DECLARE @DtFr DATETIME, @DtTo DATETIME
    SET @DtFr = CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME)
    SET @DtTo = DATEADD(month, 1,CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME))
    ;WITH tData AS
    ( SELECT @DtFr [Date] UNION ALL SELECT DATEADD(DAY,1,[Date]) FROM tData WHERE DATEADD(DAY,1,[Date]) < @DtTo )
    INSERT INTO @Temp ( [Date], [WDay] ) SELECT mt.[Date], DATENAME(WEEKDAY,mt.[Date]) FROM tData mt
    SELECT
    '',
    CardNo [EmplCard], Name [EmplName], [DepartmentDesc] [EmplSrcs], [InOut] [InOutDes],
    TrDate [TrnxDate], TrTime [TrnxTmMn], TrController [TrnxTmMx],
    ''
    FROM VW_RPT_van2
    WHERE
    CardNo = @Card AND YEAR(TrDate) = @Year AND MONTH(TrDate) = @Mnth
    GROUP BY
    TrDate, [TrDay], TrTime, CardNo, Name, [DepartmentDesc], [InOut] , TrController
    END
    ------------------- end of SP ---------------------------------

    Now, my requirement is is to change the parameter type from month(int) and year(int) to txtLog_date(datetime) and txtCompleted_date(datetime)

    because I already revised my asp parameter as this http://172.20.100.12:888/rpt_vehicle.ashx?cardno=0000055799&txtLog_Date='2/1/2018'&txtCompleted_Date='2/22/2018'

    The problem is that when it goes to above SP, it failed to show data ..

    any advice how to revise above SP ?

    Thanks a lot,

    Thursday, February 22, 2018 4:27 AM

All replies

  • User283571144 posted

    Hi Ipohtech,

    Now, my requirement is is to change the parameter type from month(int) and year(int) to txtLog_date(datetime) and txtCompleted_date(datetime)

    because I already revised my asp parameter as this http://172.20.100.12:888/rpt_vehicle.ashx?cardno=0000055799&txtLog_Date='2/1/2018'&txtCompleted_Date='2/22/2018'

    The problem is that when it goes to above SP, it failed to show data ..

    any advice how to revise above SP ?

    According to your SP, I found the sql variable @Year and @Month is used to generate the variable @DtTo and @DtFr.

    I suggest you could try to replace the @DtTo and @DtFr with "txtLog_date " and "txtCompleted_date".

    Notice:Since I don't have your database, I could just give some suggestion.

    You should firstly know what result you want and how to query to result.

    Best Regards,

    Brando

    Friday, February 23, 2018 7:12 AM
  • User77042963 posted

    Try this to start:

    Alter PROC [dbo].[$_Rpt_vehicle]
    @Card VARCHAR(16), -- Employee Card No.
    
    @txtLog_date datetime, ------ FromDate newly added field passed from asp 
    @txtCompleted_date datetime ------------ ToDate newly added field passed from asp
    
    AS 
    SET NOCOUNT ON
    BEGIN
    
    /*
    
    */
    SELECT DISTINCT
    '',
    CardNo [EmplCard], Name [EmplName], [DepartmentDesc] [EmplSrcs], [InOut] [InOutDes],
    TrDate [TrnxDate], TrTime [TrnxTmMn], TrController [TrnxTmMx],''
    FROM VW_RPT_van2
    WHERE
    CardNo = @Card AND TrDate>= @txtLog_date  AND  TrDate<= @txtCompleted_date 
    
    END

    Tuesday, February 27, 2018 3:19 PM