how to check SQL table with last one week data in sql server using stored procedure

Question

my transaction_tbl structure like this

transactID   int
TBarcode     varchar(20)
Locid         int
PlateNo       varchar(20)
dtime         datetime
DelEcode     nvarchar(50)
Paydate     datetime
KeyRoomDate  datetime
DelDate      datetime
Status      int

i have a stored procedure like this:

 ALTER procedure [dbo].[IBSFVRIPodiumG]
       @locid INTEGER = NULL
    AS BEGIN
       SET NOCOUNT ON

       DECLARE @TodayMinus7Days DATETIME
    Declare @krrt integer
    Declare @DT integer
       SET @TodayMinus7Days = getdate()-7

       SELECT  
           t.TBarcode, t.PlateNo, t.DelEcode,cast(t.Paydate as Time) [REQ],
           datediff(MINUTE, t.PayDate,
                 CASE t.Status
                    WHEN 3 THEN GETDATE()
                    WHEN 4 THEN t.KeyRoomDate
                    When 5 THEN  t.KeyRoomDate
                    End) as KRRT,

                 datediff(MINUTE,t.PayDate,
                 CASE t.Status
                  WHEN 3 THEN GETDATE()
                  WHEN 4 THEN GETDATE()
                 WHEN 5 THEN t.DelDate
                 END) as DT

       FROM    
           dbo.Transaction_tbl t
       WHERE   

       ( 
           ([status] IN (3,4))
           OR 

           ([status]=5 and DelDate >=DATEADD(minute,-3,getdate()))
       )
       AND locid = @locid AND dtime >= @TodayMinus7Days
       ORDER BY  
           paydate 
    end

In Transaction_tbl i have a Index on locid,and status..and i am taking only last 7 days data from my table..i have more than 2 lack records in transaction table..in my stored procedure i given dtime >= @TodayMinus7Days..dtime is the datetime insertion of each reocrds .i want to confirm becouse of this checking this will check only last 7 days data from my table? actually i want to check only last 7 days data from my transaction table..so i need to change anything in my stored procedure.i dont want to scan all my table..
instead of this AND locid = @locid AND dtime >= @TodayMinus7Days if i give like thisAND locid = @locid AND dtime between DATEADD(DAY, -7, GetDate()) and GetDate() that will scan all table..while executing this stored procedure i want to scan last 7 days records from my sql table,,how i can do this:?

score 1 · Answer 1 · answered May 05 '14 at 05:46

1

Unless I misunderstood the question you can achieve this using DATEADD again:

 ...AND locid = @locid AND dtime between DATEADD(DAY, -7, GetDate()) and GetDate()

answered May 05 '14 at 05:46

Milen

8,697
7
43
57

sir in my case that will check whole table ?? – user3262364 May 05 '14 at 05:47
it will check the entire table for entries having `dtime` within your date range only – Milen May 05 '14 at 05:49
if i give code what you suggested that will check whole table or that will take subset of last 7 days,,then check?? – user3262364 May 05 '14 at 05:49
in your case that will check entire table? – user3262364 May 05 '14 at 05:50
`WHERE` clause is evaluated before 'SELECT' in SQL. It will take just a subset of 7 days. You can try to use this and see the outcome... – Milen May 05 '14 at 05:51
so i have to change my entire stored procedure right? also i want to know in your case that will check entire table? or not? – user3262364 May 05 '14 at 05:52
I don't think so, just the last part of your `WHERE` statement. – Milen May 05 '14 at 05:55
so you are telling your case also searching entire table? right? – user3262364 May 05 '14 at 05:56
sir i already added index to locid and status,,if i add index to dtime,i think my insertion and updation will get slow right?? – user3262364 May 05 '14 at 06:06
in each minute atleast 10 records saving..so..if i give index to dtime..then insertion will slow right? – user3262364 May 05 '14 at 06:07
shouldn't be a problem, I think – Milen May 05 '14 at 06:08
why sir,,? could you please give small explanation? – user3262364 May 05 '14 at 06:10
sir i am waiting for your replay..?? – user3262364 May 05 '14 at 06:27
in each minute atleast 10 records saving..so..if i give index to dtime..then insertion will slow right? –so each time they need to update dtime index also right? – user3262364 May 05 '14 at 06:28

how to check SQL table with last one week data in sql server using stored procedure

1 Answers1