CONNECT BY result

  • iniyavan

    SSC Journeyman

    Points: 77

    Hi friends,

    How can we get multiple dates and days for the given number? In this example, 10 is given with '01-Jul-2020'. So 10 dates and days from 01-Jul-2020 are displayed. Could you please help on this?

    level_sql

     

  • Jeffrey Williams

    SSC Guru

    Points: 88593

    This is a SQL Server forum and you have posted Oracle code...are you looking to convert that Oracle code to T-SQL?  If so, then something like this:

    Declare @interval int = 10
    , @fromDate datetime = '2020-07-01';

    With t(n)
    As (
    Select t.n
    From (
    Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
    )
    , iTally (n)
    As (
    Select Top (@interval)
    checksum(row_number() over(Order By @@spid))
    From t t1, t t2
    )
    Select S_No = it.n
    , dt.Dates
    , Days = datename(weekday, dt.Dates)
    From iTally it
    Cross Apply (Values (dateadd(day, it.n - 1, @fromDate))) dt(Dates);

    Or - you can do this:

    Declare @interval int = 10
    , @fromDate datetime = '2020-07-01';

    With t(n)
    As (
    Select t.n
    From (
    Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
    )
    , dateRange (Dates)
    As (
    Select Top (@interval)
    dateadd(day, checksum(row_number() over(Order By @@spid)) - 1, @fromDate)
    From t t1, t t2
    )
    Select S_No = datediff(day, @fromDate, dr.Dates) + 1
    , dr.Dates
    , Days = datename(weekday, dr.Dates)
    From dateRange dr;

    But - based on your other posts I think you really need a calendar table/function.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • iniyavan

    SSC Journeyman

    Points: 77

    Thanks for the suggestions, Jeffery.

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

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