October 25, 2011 at 1:46 am
Hi all
I need a help from u guys for date diffenreces
Using 10-Sep-2011
Result should be 1-sep-2011 to 9-sep-2011 & 10-sep-2011 to 31-sep-2011
Can anyone help on this subject
October 25, 2011 at 2:30 am
farooq.hbs (10/25/2011)
Hi allI need a help from u guys for date diffenreces
Using 10-Sep-2011
Result should be 1-sep-2011 to 9-sep-2011 & 10-sep-2011 to 31-sep-2011
Can anyone help on this subject
I guess this is the sort of thing you're after?
DECLARE @funTime DATETIME
SET @funTime = '2011-09-10'
SELECT DATEADD(mm, DATEDIFF(m,0,@funTime),0), DATEADD(DAY, 0, DATEDIFF(DAY, 0, @funTime-1)),
DATEADD(DAY, 0, DATEDIFF(DAY, 0, @funTime)), DATEADD(dd, -1, DATEADD( mm, DateDiff(mm , 0,@funTime)+1 , 0))
/*----------------------- ----------------------- ----------------------- -----------------------* Results in
----------------------- ----------------------- ----------------------- -----------------------
2011-09-01 00:00:00.000 2011-09-09 00:00:00.000 2011-09-10 00:00:00.000 2011-09-30 00:00:00.000
\*----------------------- ----------------------- ----------------------- -----------------------*/
GO
DECLARE @funTime DATETIME
SET @funTime = '2011-09-10'
SELECT REPLACE(LEFT(CONVERT(VARCHAR(20),DATEADD(mm, DATEDIFF(m,0,@funTime),0),113),11),' ','-') +' & '+
REPLACE(LEFT(CONVERT(VARCHAR(20),DATEADD(DAY, 0, DATEDIFF(DAY, 0, @funTime-1)),113),11),' ','-'),
REPLACE(LEFT(CONVERT(VARCHAR(20),DATEADD(DAY, 0, DATEDIFF(DAY, 0, @funTime)),113),11),' ','-') +' & '+
REPLACE(LEFT(CONVERT(VARCHAR(20),DATEADD(dd, -1, DATEADD( mm, DateDiff(mm , 0,@funTime)+1 , 0)),113),11),' ','-')
/*------------------------- -------------------------* Results in
------------------------- -------------------------
01-Sep-2011 & 09-Sep-2011 10-Sep-2011 & 30-Sep-2011
\*------------------------- -------------------------*/
If not, read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
October 25, 2011 at 3:40 am
Thnxzzz
October 25, 2011 at 5:21 am
Hi Guy Thnkz for the support..
But i want in the Different scenario..this is for Payroll Calculation ..I have listed example table
person_id Assign_id Effective DAte Temp_id_old temp_id_new updated _date
8076 143 15 Jul 2011 47 48 5 sep 2011
8076 143 15 Aug 2011 48 49 8 Sept 2011
8076 143 10 Sep 2011 49 50 10 sep 2011
Open Period is 1-sep-2011 to 30-sep-2011
For this Above Table, Ihe Result should be below
1) 15 jul 2011 14 Aug 2011 47 48
2) 15 Aug 2011 31 Aug 2011 48 49
3) 1 sep 2011 9 Sep 2011 49 49
4)10 Sep 2011 31 Sep 2011 49 50
Thankz in advance
October 26, 2011 at 4:33 am
This might be useful for ordering and defining the general periods;
-- Replace #EffectiveDate with your source of period date delimiters.
CREATE TABLE #EffectiveDate (
Effective_Date DATETIME);
INSERT INTO #EffectiveDate VALUES ('20110715'),('20110815'),('20110910');
-- Find period pairs by ordering and assigning row numbers.
WITH cteEffectiveDateOrdered (EffectiveDate,OrderNo)
AS
(
SELECT Effective_Date,ROW_NUMBER() OVER(ORDER BY Effective_Date)
FROM #EffectiveDate)
-- Turn period pairs into required results set.
SELECT
a.EffectiveDate AS [Period Start]
,DATEADD(Day,-1,b.EffectiveDate) AS [Period End]
FROM cteEffectiveDateOrdered a
INNER JOIN cteEffectiveDateOrdered b
ON a.OrderNo + 1 = b.OrderNo
Giving results as follows;
Period Start Period End
2011-07-15 00:00:00.000 2011-08-14 00:00:00.000
2011-08-15 00:00:00.000 2011-09-09 00:00:00.000
I note that you have temp ids which appear also to link the dates which might alleviate the need for the CTE (although this technique might prove useful for others).
A question I have is why is September special in that it has a period which splits the beginning and end of month into two periods instead of running from one effective date to the next.
Perhaps you require a flag in your table (or another table) identifying special months which are then used to halve those months into two periods.
From what I understand currently your periods are defined as running from one effective date to the next, unless the next date sits in a special month in which case period runs from previous effective date to previous month end, then special month start to effective date - 1 day, effective day to month end, then next month start to next effective date.
What happens if your effective date is the first day of the month in a special month, ie 1st Sept 2011. Do you then only have one period?
An exact set of business rules will greatly assist in coming to a correct solution.
October 27, 2011 at 10:02 am
Date1 is the lower date. Date2 is greater. This formula does NOT account for date1 being greater than date2.
Declare @Date1 Datetime
Declare @Date2 Datetime
Select @Date1 = Orders.Prom_Ship_date
Select @Date2 = Orders.Actual_Ship_date
Select datediff(dd,@Date1,@date2),datepart(dd,@date1),datepart(dw,@date1)
-- if this is > 0:
select case when (((datediff(dd,@date1@date2)-(6-datepart(dw,@date1)))/7) *5 >0
then
(((datediff(dd,@date1,@date2)-(6-datepart(dw,@date1)))/7)*5) +(6-datepart(dw,@date1))+datepart(dw,@date2)-1
else -- cross only one weekend
case when datepart(dw,@date1)>datepart(dw,@date2) then
6-datepart(dw,@date1)+datepart(dw,@date2) - 1
else -- crosses no weekends
datediff(dd,@date1,@date2)
end
end
end
Hope this will help..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply