Post reply

TSQL Help

  • October 6, 2010 at 5:32 am

    #230078

    Hi,

    Please help...

    I have a basic qry:

    SELECTEmployeeCode, LeaveDateFrom, LeaveDateTo

    FROMLeaveTable

    which gives me the result (___ = spaces):

    AA123456___2010-10-01 00:00:00.000___2010-10-06 00:00:00.000

    now here is were I need help please:

    I need to get the rows automaticaly from the LeaveDateFrom (2010-10-01 00:00:00.000) to the LeaveDateTo (2010-10-06 00:00:00.000) per row per day; the output I am looking for:

    AA123456___2010-10-01 00:00:00.000

    AA123456___2010-10-02 00:00:00.000

    AA123456___2010-10-03 00:00:00.000

    AA123456___2010-10-04 00:00:00.000

    AA123456___2010-10-05 00:00:00.000

    AA123456___2010-10-06 00:00:00.000

    Hope it makes sense...Thanks

  • October 6, 2010 at 5:34 am

    #1232392

    Use could use a calendar table

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

    or a tally table

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


    Clear Sky SQL
    My Blog[/url]

  • October 6, 2010 at 5:53 am

    #1232408

    Thanks for the quick response Dave - will have a look at the links when I get home - company is blocking the site :crying:

  • October 6, 2010 at 6:55 am

    #1232464

    Sorry, but I am not quite understanding...

    You are selecting 3 columns, but are ending up with spaces...? Also not quite sure what you want out in the end...?

  • October 6, 2010 at 7:32 am

    #1232515

    grahamc (10/6/2010)

    Sorry, but I am not quite understanding...

    You are selecting 3 columns, but are ending up with spaces...? Also not quite sure what you want out in the end...?

    Spaces???

    Dave undersood my req - have visit the links and got it working - thanks

  • October 6, 2010 at 2:24 pm

    #1233040

    Hi

    Declare @temp table (Rid int identity,Name varchar(50),fdate datetime, todate datetime)

    insert into @temp

    Select 'A','10-12-2010','10-18-2010'

    Union Select 'B','09-06-2010','09-13-2010'

    Union Select 'C','08-30-2010','09-03-2010'

    declare @i int ,@diff int ,@k int ,@Name varchar(50)

    set @i=1

    While @i<=(Select COUNT(rid) From @temp)

    begin

    select @Name=Name from @temp where rid=@i

    Select @diff=DATEDIFF(dy,fdate,todate) from @temp where rid=@i

    set @k=1

    while @k<=@diff

    begin

    Select @Name ,DATEADD(dy,@k,fdate) from @temp where rid=@i

    set @k=@k+1

    end

    set @i=@i+1

    end

    Thanks

    Parthi

    Thanks
    Parthi

  • October 7, 2010 at 12:43 am

    #1233274

    Thanks Parthi - much appreciated

  • October 7, 2010 at 1:16 am

    #1233282

    Scalabilty is ALWAYS something to consider.

    Parthi's solution , though perfectly adequate for three rows, will die when using one million rows.

    Overkill ? Perhaps ? But smaller problems are practice for larger problems.


    Clear Sky SQL
    My Blog[/url]

  • October 7, 2010 at 10:14 pm

    #1234021

    vilonel (10/7/2010)

    Thanks Parthi - much appreciated

    Here's what Dave is trying to get at... Compare the following set based code to the While Loop...

    --===== Create and populate a test table (this is NOT a part of the solution)

    DECLARE @LeaveTable TABLE (EmployeeCode CHAR(1), FromDate DATETIME, ToDate DATETIME)

    INSERT INTO @LeaveTable

    (EmployeeCode, FromDate, ToDate)

    SELECT 'A','10-12-2010','10-18-2010' UNION ALL

    SELECT 'B','09-06-2010','09-13-2010' UNION ALL

    SELECT 'C','08-30-2010','09-03-2010'

    --===== Once you've built a Tally table, compare this solution to the While Loop

    -- that parthi built.

    SELECT EmployeeCode, DATEADD(dd,(t.N-1),FromDate)

    FROM @LeaveTable d

    INNER JOIN dbo.Tally t

    ON t.N <= DATEDIFF(dd,FromDate,ToDate)

    If you don't already have a Tally Table or you don't know how it works, please see the following URL... http://www.sqlservercentral.com/articles/T-SQL/62867/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 8, 2010 at 12:36 am

    #1234033

    Great article Jeff (The "Numbers" or "Tally" Table: What it is and how it replaces a loop.) - have used your example and my query works and the results are processed really fast - Thank you.

    This little grasshoper still have a lot to learn from the masters on SeverCentral.com:-D

  • October 8, 2010 at 6:33 am

    #1234187

    vilonel (10/8/2010)

    Great article Jeff (The "Numbers" or "Tally" Table: What it is and how it replaces a loop.) - have used your example and my query works and the results are processed really fast - Thank you.

    This little grasshoper still have a lot to learn from the masters on SeverCentral.com:-D

    Thanks for the feedback, Vilonel.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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