Re: date gaps
Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance
I might try something like the following to get the list of member id's
SELECT [MemberID]
FROM [YourTable] as YT
WHERE DateDiff("d"
,(SELECT Max([EndDate])
FROM [YourTable] as Temp
WHERE Temp.[MemberID] = YT.[MemberID]
AND Temp.[EndDate] < YT.[EffectiveDate])
,[EffectiveDate]) >30
"buckpeace" <buckpeace@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6AB9BF75-4276-4502-9B10-4CFD4363D0CD@xxxxxxxxxxxxxxxx
I am trying to count members that may have had a gap in enrollment of 30
days. How can I write a query to show me which members had gaps in
enrollment. Here are the fileds I am working with. (see below for
example....1 member has diff lines..."spans" of enrollment if you
will.......) I only want the query to be able to place the number of days
of
the gap in a field to see if they were enrolled.
member ID effective date end date
12345678 10/1/1999 10/31/2000
12345678 11/1/2000 11/30/2000
12345678 05/1/2002 09/1/2002
With much appreciation,
--
Buck
.
Relevant Pages
- Re: Complex Count, Avg, Min and Max
... I grab the for each Enrollment in context and sum them up. ... I only want to sum up the Max Days To Date from each actual Enrollment once and don't want to sum in anything from the All member. ...
(microsoft.public.sqlserver.olap) - Re: Querying AD for Group information (type/scope)
... To Query for Distribution groups: ... To query for all users that have some group other than "Domain Users" ... users with primaryGroupID equal to 513 belong to the "Domain Users" group. ... The reason the member attribute of the "Domain users" and some other ...
(microsoft.public.windows.server.active_directory) - RE: Hotel Booking Project Delemma
... you can add the DISTINCT clause in the query serving data to the report. ... > I am having a dilemma with a Hotel Booking Project which is ... > In the tblHotelBookings I have fields to capture Number of Nights, ... > If one member is booked, but has a second person ...
(microsoft.public.access.tablesdbdesign) - Re: ADAM Subset
... Yep this would be a case for an attribute scoped query and as JoeK indicates would require a base level query, ... Joe Richards Microsoft MVP Windows Server Directory Services ... There is a trick called attribute scope query that ADAM supports that allows you to search within the values of a DN-syntax attribute (like member) to filter the results and return attributes on the objects therein. ...
(microsoft.public.windows.server.active_directory) - Re: 2nd layer of filtering?
... With DAO, I can rewrite a query using VBA and a querydef object. ... It basically says in psuedo SQL, Select the Member if the member has a ... >> FROM qMembershipSelect AS Y ...
(microsoft.public.dotnet.framework.adonet) |
|
