how to split the days between two month

  • hi i am developing a web application dot net..i am struck with the sql server query...if the person is taking a leave from 25/3/2013 to 3/02/2013,i want to split the 6days in month and 3 days in febrarury,,,can anyone tell me pls

  • Can you please describe how exactly you want the Output.

    like :- 1 month and 6 days Or

    you want to do calculation on working days (6 days)

  • From your brief description, I'd say you need a calendar table. Have a read about that - there should be some stuff on this site about it.

    John

  • in my webpage,if i select the month january,,it will call the query and retrive as 6days in the month of january,the remaining 3days will be retrived in the month of febraury...

    25/01/2013 to 03/02/2013,,if i select the month jan it shows 6days and if i select the month febraury it shows 3 days...

  • rifayeem (3/14/2013)


    in my webpage,if i select the month january,,it will call the query and retrive as 6days in the month of january,the remaining 3days will be retrived in the month of febraury...

    25/01/2013 to 03/02/2013,,if i select the month jan it shows 6days and if i select the month febraury it shows 3 days...

    Please take a few minutes and read the article found at the first link in my signature. There just are not enough details posted to have much chance of providing any decent assistance.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I would have been easier for people to help you had you provided the DDL, sample data and expected results in ready to use format

    But as you are a probably a new member in SSC, I will take some extra steps to help you out

    Check how I have prepared the DDL and sample data

    DECLARE@tbl_Leaves TABLE

    (

    EmployeeID INT,

    LeaveFromDate DATETIME,

    LeaveToDate DATETIME

    )

    INSERT@tbl_Leaves

    SELECT1, '2013-01-25 00:00:00.000', '2013-02-03 00:00:00.000' UNION ALL

    SELECT1, '2013-01-01 00:00:00.000', '2013-01-15 00:00:00.000' UNION ALL

    SELECT2, '2012-12-25 00:00:00.000', '2013-02-15 00:00:00.000' UNION ALL

    SELECT2, '2013-02-01 00:00:00.000', '2013-02-15 00:00:00.000'

    SELECT*

    FROM@tbl_Leaves

    --======================================================================

    DECLARE@Month VARCHAR(20)

    DECLARE@Year SMALLINT

    DECLARE@StartDate DATETIME

    DECLARE @EndDate DATETIME

    --The below 2 variables needs to be passed

    SET@Month = 'January'

    SET@Year = 2013

    --The below variables will be calculated

    SET@StartDate = '01-' + @Month + CAST( @Year AS VARCHAR(4))

    SET@EndDate = DATEADD( DAY, -1, DATEADD( MONTH, 1, @StartDate ) )

    SELECTL.EmployeeID, L.LeaveFromDate, L.LeaveToDate,

    DATEDIFF(

    DAY,

    CASE WHEN L.LeaveFromDate > @StartDate THEN L.LeaveFromDate ELSE @StartDate END,

    CASE WHEN L.LeaveToDate < @EndDate THEN L.LeaveToDate ELSE @EndDate END

    ) + 1 AS NoOfDays

    FROM@tbl_Leaves AS L

    WHEREL.LeaveFromDate BETWEEN @StartDate AND @EndDate

    ORL.LeaveToDate BETWEEN @StartDate AND @EndDate

    OR@StartDate BETWEEN L.LeaveFromDate AND L.LeaveToDate

    OR@EndDate BETWEEN L.LeaveFromDate AND L.LeaveToDate

    Edit: Made some formatting changes to the code


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 6 posts - 1 through 5 (of 5 total)

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