April 2, 2010 at 6:46 pm
I'm sorry... this one got lost in the 4000 emails I got the last 10 days or so... do you still need help on this?
--Jeff Moden
April 2, 2010 at 8:39 pm
Jeff Moden (3/21/2010)Like Dave said, find the first of the month for the given date, then add one month and substract a day. Here's how to do it using GETDATE() as the current datetime value...
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)-1
I like this better - probably because of my negative personality :w00t:
SELECT DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1); -- end of this month
SELECT DATEADD(month, DATEDIFF(month, -1, GETDATE()) - 1, -1) -- end of previous month
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 2, 2010 at 9:03 pm
🙂
--Jeff Moden
April 2, 2010 at 10:18 pm
thanks Jeff,
I solved that later in One+ hours..
Try Try Try Again,
One Day u vl succeed..................
April 2, 2010 at 10:29 pm
Ok... thanks for the feedback.
--Jeff Moden
February 16, 2011 at 6:44 am
hi Jeff
i came across this post:
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '20070429',
@DateEnd = '20081201'
--===== Find the dates using a Tally table as a counter.
-- The outer select formats it. Once cached, it's incredibly fast.
;WITH
cteTally AS
(--==== Returns a value of 1 to the number of months in date range
SELECT TOP (DATEDIFF(mm,
DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month
DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month
N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT N,
DateStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0),
NextStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)
FROM cteTally t
you posted it long time back and i was wondering if you can help me i'm looking for something similar. in my case i'm doing something like this:
SELECT distinct
B.CompletionDate ,ROW_NUMBER() OVER (ORDER BY B.ID)
FROM dbo.vw_wrkWorkOrders B
WHERE B.CompletionDate between
DATEADD(mm,DATEDIFF(mm,0,CONVERT(DATETIME,'01/01/2010',103))+ -1,0) and
DATEADD(mm,DATEDIFF(mm,0,CONVERT(DATETIME,'28/02/2011',103))+ 1,0)
and the results should be like this
RowNo CompletionDate
1 2010-01-01 00:00:00.000
2 2010-02-01 00:00:00.000
3 2010-03-01 00:00:00.000
4 2010-04-01 00:00:00.000
5 2010-05-01 00:00:00.000
6 2010-06-01 00:00:00.000
7 2010-07-01 00:00:00.000
8 2010-08-01 00:00:00.000
9 2010-09-01 00:00:00.000
10 2010-10-01 00:00:00.000
11 2010-11-01 00:00:00.000
12 2010-12-01 00:00:00.000
13 2011-01-01 00:00:00.000
14 2011-02-01 00:00:00.000
February 16, 2011 at 6:59 am
I'm on my way to work. If someone else doesn't get to this first, I post a coded reply tonight.
--Jeff Moden
February 16, 2011 at 7:00 am
Actually, if you use just the inner select in the code example and get rid of the next start date, you'll have your answer. Give it a try.
--Jeff Moden
June 13, 2011 at 4:00 am
Hi Jeff,
Thank you so much for your post. I did some modifying but you code work PERFECTLY. Thanks again
:-):-):-)
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2011 at 8:16 am
taybre (6/13/2011)
Hi Jeff,Thank you so much for your post. I did some modifying but you code work PERFECTLY. Thanks again
:-):-):-)
Wow. Old post, too. Thank you very much for the feedback, Taybre. If you have any questions on the code, please post back.
--Jeff Moden
June 19, 2011 at 6:54 am
Hi Jeff;
I am wiping the blood and hair off the monitor trying to adapt what i read here to my own problem but need to be dragged from the darkness into the light. I dont understand how the vars in your example are declared datetime, but look like characters, yet it works that way but not with a date time? i dont understand why.
thanks very much for your time and attention
June 19, 2011 at 11:13 am
drew.georgopulos (6/19/2011)
Hi Jeff;I am wiping the blood and hair off the monitor trying to adapt what i read here to my own problem but need to be dragged from the darkness into the light. I dont understand how the vars in your example are declared datetime, but look like characters, yet it works that way but not with a date time? i dont understand why.
thanks very much for your time and attention
So here I am looking at your code to try to form a response and all of your code and examples suddenly disappear. It's kinda hard to hit a rolling donut, Drew. 😉
To answer this current question, the DATETIME datatype will implicitly convert text that looks like a date into a DATETIME. It's convenient for humans because if that weren't allowed, you'd have to calculate the underlying date/time serial number to be able to manually assign dates and times to a variable.
Getting back to your post before you removed most of the "meat" of the post... to best help me help you, take a look at the first link in my signature line below. It'll tell you how to post data and the necessary test table. That's essential on problems like what you asked because the CREATE TABLE statement answers all the questions like what the datatype of each piece of data is and the readily consumable data gives me something to work with instead of having to spend the time trying to make some usable data (which, of course, could be wrong). Instead, I can spend the time solving your problem and give you actual tested working code. 🙂
--Jeff Moden
June 20, 2011 at 2:32 pm
I thought i could grind it out based on one of your earlier examples...i can see its there but could not think my way out of it....this worked with a date dimension (to stub up a fact table before we get int keys for it) but i know its ridiculous.
thanks in advance for your help
the meat of the table structure (it goes on forever...i think this is just the relevant piece)
CREATE TABLE [dbo].[melig](
[subno] [char](12) NOT NULL,
[persno] [char](2) NOT NULL,
[estat] [char](1) NULL,
[effdt] [datetime] NULL,
[termdt] [datetime] NULL
)
sample data from the eligibility table
SELECT '1001020 ','02','E','Mar 1 2009 12:00AM','Feb 28 2011 12:00AM' UNION ALL
SELECT '1001020 ','02','E','Apr 1 2008 12:00AM','Feb 28 2009 12:00AM' UNION ALL
SELECT '1001020 ','02','E','Feb 1 2008 12:00AM','Mar 31 2008 12:00AM' UNION ALL
SELECT '1001020 ','02','E','Jan 1 2008 12:00AM','Jan 31 2008 12:00AM' UNION ALL
SELECT '1001020 ','02','E','Jul 1 2006 12:00AM','Dec 31 2007 12:00AM' UNION ALL
SELECT '1001020 ','02','E','Sep 1 2005 12:00AM','Feb 28 2006 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Jul 1 2005 12:00AM','Nov 30 2005 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Feb 1 2005 12:00AM','Jun 30 2005 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Jan 1 2005 12:00AM','Jan 31 2005 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Jul 1 2004 12:00AM','Dec 31 2004 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Apr 1 2004 12:00AM','Jun 30 2004 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Mar 1 2004 12:00AM','Mar 31 2004 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Feb 1 2004 12:00AM','Feb 29 2004 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Nov 1 2003 12:00AM','Jan 31 2004 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Oct 1 2003 12:00AM','Oct 31 2003 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Apr 1 2003 12:00AM','Sep 30 2003 12:00AM' UNION ALL
SELECT '1001022 ','01','E','Oct 1 2002 12:00AM','Mar 31 2003 12:00AM'
--the date dim (abridged)
CREATE TABLE [dbo].[tblservice_date_dim](
[service_date_key] [int] NOT NULL,
[service_date_full_date] [datetime] NOT NULL,
) ON [PRIMARY]
--didnt want to go too crazy with this....
SELECT '6666','Apr 1 2008 12:00AM',UNION ALL
SELECT '6667','Apr 2 2008 12:00AM',UNION ALL
SELECT '6668','Apr 3 2008 12:00AM',UNION ALL
SELECT '6669','Apr 4 2008 12:00AM',UNION ALL
SELECT '6670','Apr 5 2008 12:00AM',UNION ALL
SELECT '6671','Apr 6 2008 12:00AM',UNION ALL
SELECT '6672','Apr 7 2008 12:00AM'
--make one row for each month of a members term of enrollment
--wrap it in a batch so we dont blow the log again
--truncate table odsmembermonthfact
--this will tell how many rows this is going to take...
select sum(dates) rownums from
(
select datediff(mm,effdt, isnull(termdt,'2011-06-30')+1)dates ,effdt, isnull(termdt,'2011-06-30')term
from hsdxbase.dbo.melig
where estat in ('e','a','r' )
)x
declare @memberid char(9)
declare c1 cursor for
select distinct Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)) AS memberid
from hsdxbase.dbo.melig
where estat in ('E','A','R')
open c1
fetch next from c1 into @memberid
while @@fetch_status =0
begin
-- print @memberid
insert odsMemberMonthFact
SELECT distinct --COUNT(*) 238,261,003
Service_YearMo,
Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)) + ltrim(rtrim(eligno)) AS eligid
,Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)) AS memberid
,CASE WHEN grup = '' or grup is null then 'n/a' +ltrim(rtrim(substring(convert(varchar,effdt,112),1,6) ))
else ltrim(rtrim(grup)) +ltrim(rtrim(substring(convert(varchar,effdt,112),1,6) )) end as mmkey
,Isnull(lob, 'n/a') AS LOB
,[eligno]
,[effdt]
,[termdt]
,[estat]
,[mplan]
,[panel]
,[pcp]
,[recert]
,[trsn]
,[feeaapp]
,[ForeverID]
,0 IsFirstMonthEnrolled --if this is a one, you can never have a one again in this column
,0 MonthIsInFirst6MonthsOfEnrollment --is the month in the members first six months of enrollment in any segment
,0 IsEnrollmentContinuousForPrior6Months
,0 IsEnrollmentContinuousForPrior12Months
,0 IsLastMonthEnrolled
,0 TotalTenure --datediff (mm, min service year, max service year)
,0 TotalBreaks --was there a break in coverage of atleast one month
,0 BreakInCoverageDays --show me everyone who has been disenrolled over thirty days
,0 CapPayment
--into ODSMemberMonthFact
FROM [Hsdxbase].[dbo].[melig]
cross join ODS.dbo.tblService_Date_Dim
WHERE Service_Date_Full_Date
between (effdt) and (termdt)
and Ltrim(Rtrim(subno)) + Ltrim(Rtrim(persno)) = @memberid
fetch next from c1 into @memberid
end
close c1
deallocate c1
I apologize for being so long winded about it, but was trying to follow your directions to get the best help. my instinct is that the volume of example to to the volume of answer is going to be like 1000 to 1!!
Thanks again
June 20, 2011 at 10:15 pm
Heh... No cursor required. I've not finished yet and there are some simple columns you can add that you didn't have in the test data. Comments are in the code. The two "ContinuousPrevious" columns will require a little prestiditation but no RBAR.
--========================================================================================
-- The current tblservice_date_dim table, as posted, does us no good.
-- So, instead, we'll build one on the fly. Even though we're building month dates
-- for 2 centuries, this happens in the a blink of an eye.
--========================================================================================
--===== Conditionally drop the temporary calendar table to make reruns in SSMS easier
IF OBJECT_ID('TempDB..#Calendar','U') IS NOT NULL DROP TABLE #Calendar
;
--===== Create the temporary calendar table on the fly
-- The ISNULL is to create a NOT NULL column
SELECT TOP (DATEDIFF(mm, '19000101', '21000101'))
Dt = ISNULL(DATEADD(mm,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'1900'),0)
INTO #Calendar
FROM sys.all_columns sc1,
sys.all_columns sc2
;
--===== Add a clustered index in the form of a PK for speed.
-- NEVER name a PK that's being used on a Temp Table or concurrency will suffer.
ALTER TABLE #Calendar
ADD PRIMARY KEY CLUSTERED (Dt) WITH FILLFACTOR = 100
;
--========================================================================================
-- Started working on the problem but falling asleep. Will try to finish tomorrow.
--========================================================================================
WITH
cteExplodedMonths AS
(
SELECT DISTINCT
ExplodedDate = c.DT,
Service_YearMo = CONVERT(CHAR(6),c.Dt ,112),
MemberID = LTRIM(RTRIM(subno))+LTRIM(RTRIM(persno)),
src.effdt,
src.termdt,
src.estat
FROM dbo.Melig src
CROSS JOIN #Calendar c
WHERE c.Dt >= src.effdt AND c.Dt <= src.termdt
AND src.estat IN ('E','A','R')
)
SELECT Service_YearMo,
MemberID,
effdt,
termdt,
estat,
IsFirstMonthEnrolled =
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY ExplodedDate ASC) = 1
THEN 1
ELSE 0
END,
MonthIsInFirst6MonthsOfEnrollment =
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY MemberID, effdt ORDER BY ExplodedDate ASC) <= 6
THEN 1
ELSE 0
END,
IsLastMonthEnrolled =
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY ExplodedDate DESC) = 1
THEN 1
ELSE 0
END
FROM cteExplodedMonths
ORDER BY MemberID, ExplodedDate
;
--Jeff Moden
June 21, 2011 at 10:44 am
looking forward to it!
I want you to know i am standing up at my desk to say Thank You immensely for your time and effort Mr. Moden!
drew
ps. this sucka runs like the wind!
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply