March 14, 2013 at 6:19 am
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
March 14, 2013 at 6:38 am
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)
March 14, 2013 at 6:53 am
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
March 14, 2013 at 9:46 am
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...
March 14, 2013 at 9:52 am
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/
March 15, 2013 at 1:04 am
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
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
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