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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply