Post reply

skip rows from result set

  • August 26, 2009 at 1:23 pm

    #135076

    I am looking for a SQL that can fetch me rows for all mondays with in a date range from date_table by skipping 1 week at a time. The SQL must be generic to accept integer parameter to skip 1 week or 2 week or 3 weeks.

    Here is SQL script to create and populate date_table.

    --create table

    CREATE TABLE [dbo].[date_table](

    day_date smalldatetime NULL,

    week_day tinyint)

    --populate data

    declare @x smalldatetime

    select @x = '2008-01-01'

    while @x < '2009-01-01'
    begin
    Insert into dbo.date_table
    Select @x
    ,case when DATENAME(dw , @x) = 'sunday' then 1
    when DATENAME(dw , @x) = 'monday' then 2
    when DATENAME(dw , @x) = 'tuesday' then 3
    when DATENAME(dw , @x) = 'wednesday' then 4
    when DATENAME(dw , @x) = 'thursday' then 5
    when DATENAME(dw , @x) = 'friday' then 6
    when DATENAME(dw , @x) = 'saturday' then 7
    end

    select @x = dateadd(dd,1,@x)
    end

  • August 26, 2009 at 2:14 pm

    #1045163

    You could use DATEDIFF and the Modulus operator.

    Something like, DATEDIFF(ww, CurrentDate, StartDate) % SkipWeek

    So, if you wanted to get the data every 3 weeks, what you would have is

    DATEDIFF(ww, CurrentDate, StartDate) % 3

    EG:

    PRINT DATEDIFF(ww, GETDATE(), GETDATE()) % 3

    -- 0

    PRINT DATEDIFF(ww, GETDATE(), GETDATE() - 7) % 3

    -- 1

    PRINT DATEDIFF(ww, GETDATE(), GETDATE() - 14) % 3

    -- 2

    PRINT DATEDIFF(ww, GETDATE(), GETDATE() - 21) % 3

    -- 0

    DECLARE @MaxDate SMALLDATETIME

    SET @MaxDate = 'SomeDate'

    So, SELECT * FROM TABLE WHERE DATEDIFF(ww, CurrentDate, @MaxDate) % 3 = 0

    would give you back all the records seperated by a 3 week range

  • August 27, 2009 at 2:24 am

    #1045373

    Here's some code that should give you what you want if I understood your question properly.

    You'll want to wrap this up in a stored procedure. Unfortunately doesn't look like the posting's respecting my carriage returns properly, so added comment lines to force blank lines.

    begin

    -- these would be input params for your stored procedure

    declare

    @week_day tinyint,

    @weeks_between tinyint,

    @start_date smalldatetime,

    @end_date smalldatetime

    --

    -- params would normally be passed into your stored proc

    select

    @week_day = 2, -- 1 for Sunday, 2 for Monday, ... 7 for Saturday

    @weeks_between = 3,

    @start_date = '2008-01-01',

    @end_date = '2009-01-01'

    --

    -- declare table variable to hold all rows for desired week_day

    declare @mydates table (

    id int IDENTITY(1,1) NOT NULL, -- this ID column is used later to determine which rows to keep

    day_date smalldatetime,

    week_day tinyint

    )

    --

    insert into @mydates

    (

    day_date, week_day

    )

    (

    select day_date, week_day

    from date_table

    where day_date between @start_date and @end_date -- specifies date range

    and week_day = @week_day -- specifies weekday of interest

    )

    --

    declare

    @id int,

    @max_id int

    -- set @id to 1 because the first record is always retained

    -- @Max_id holds the total number of @week_day days in the date range

    select @id = 1, @max_id = max(id) from @mydates

    --

    -- another table variable to hold the dates that meet requirements

    declare @myfinaldates table (

    id int,

    day_date smalldatetime,

    week_day tinyint

    )

    --

    -- always insert the first row from table @mydates

    insert into @myfinaldates

    (

    id, day_date, week_day

    )

    (

    select id, day_date, week_day from @mydates where id = @id

    )

    --

    -- while there are still dates we haven't looked at

    while @id < @max_id

    begin

    -- determine the next row that we want to keep

    -- Formula is always current row + 1 row + @weeks_between rows

    -- For example, if @weeks_between = 2, rows to keep are row 1, row 4, row 7...

    set @id = @id + 1 + @weeks_between

    --

    -- insert the next row that we want to keep into the second table variable

    insert into @myfinaldates

    (

    id, day_date, week_day

    )

    (

    select id, day_date, week_day from @mydates where id = @id

    )

    end

    --

    -- retrieve all inserted rows

    select * from @myfinaldates

    end

    Riz

  • August 27, 2009 at 3:26 am

    #1045392

    Heres a good resource on calendar tables

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    Its just a simple query DayMask = 1 Means Mondays ,WeekNo is encoded as YYYYWW

    with cteWeeks(dte,WeekNo,RowN)

    as

    (

    Select dte,WeekNo,row_number() over (order by dte)

    from cal

    where dte between '01jan2009' and '30dec2009'

    and DayMask = 1

    )

    Select * from cteWeeks where Rown %3 = 0


    Clear Sky SQL
    My Blog[/url]

  • August 27, 2009 at 11:54 am

    #1045754

    Thanks for all of your efforts. I got what I was looking for.

  • August 27, 2009 at 2:22 pm

    #1045863

    Hi Dave,

    Great article, thanks for pointing it out.

    Can you explain your code a bit please? See comments in the code.

    with cteWeeks(dte,WeekNo,RowN)

    as

    (

    Select dte,WeekNo,row_number() over (order by dte) -- what does row_number() over (order by dte) do?

    from cal

    where dte between '01jan2009' and '30dec2009'

    and DayMask = 1 -- Is this a column in your calendar table?

    )

    Select * from cteWeeks where Rown %3 = 0

    Your code returns every row number that's evenly divisible by 3 correct?

    What if I was looking for the first Monday in the range, then skip 3 Mondays, then get the next Monday, then skip 3 Mondays....

    Thanks!

    Riz

    Riz

  • August 27, 2009 at 2:42 pm

    #1045887

    Hi Riz,

    row_number() is documented in bol and returns an incrementing number.

    DayMask is a column in my table 1=Monday , 2=Tuesday , 4 = Wednesday , 8 =Thurs etc....

    and as for your skip question then that would be "rown % 4 =0"? , again the modulous operator (%) is documented in bol.


    Clear Sky SQL
    My Blog[/url]

  • August 28, 2009 at 12:42 am

    #1046065

    Thanks Dave 🙂 Learned a few things.

    Riz

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy