February 17, 2017 at 3:58 pm
In a t-sql 2012, I have a table that looks like the following:
CREATE TABLE [dbo].[Enroll](
[enrollID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[calendardate] [int] NOT NULL,
[serviceType] [varchar](1) NOT NULL,
[active] [bit] NOT NULLNOT NULL),
[startDate] [smalldatetime] NOT NULL,
[endDate] [smalldatetime] NULL,
[endYear] [smallint] NOT NULL
)
The table is not really looked at by enrollID. it is look at usually by personID, service type, startdate and enddate.
For each personID there can be lots of records.
For the most current record (selected by start date and end date), I want to look for records
where the service Type= P. If there is no service type=P, then I want to pick a record if service type=N.
Thus would you show me the t-sql 2012 for a way on how to accomplish this goal?
February 17, 2017 at 11:22 pm
You've been here long enough to know what it takes to get a "show me what it takes" answer, Wendy. Readily consumable test data. Please see the following. Thanks for helping us help you. Click on the following to find out how to do that.
How to post code problems
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2017 at 2:28 am
maybe ??
CREATE TABLE #Enroll(
personID INT
,serviceType VARCHAR(1) NOT NULL
,startDate DATE NOT NULL
,EndDate DATE NOT NULL
);
SET DATEFORMAT DMY;
INSERT INTO #Enroll(personID,serviceType,startDate,EndDate) VALUES
(1,'P','14/01/2010','22/01/2010'),(1,'P','31/03/2010','04/04/2010'),(1,'P','31/03/2010','15/04/2010')
,(2,'N','14/01/2010','22/01/2010'),(2,'N','31/03/2010','04/04/2010'),(2,'N','31/03/2010','15/04/2010')
,(3,'P','14/01/2010','22/01/2010'),(3,'N','31/03/2010','04/04/2010'),(3,'N','31/03/2010','15/04/2010')
,(4,'T','14/01/2010','22/01/2010'),(4,'S','31/03/2010','04/04/2010'),(4,'R','31/03/2010','15/04/2010');
WITH latestdates as (
SELECT personID,
serviceType,
startDate,
EndDate,
ROW_NUMBER() OVER(PARTITION BY personID ORDER BY serviceType DESC,
startDate DESC,
EndDate DESC) rn
FROM #Enroll
WHERE(serviceType = 'P')
OR (serviceType = 'N')
)
SELECT *
FROM latestdates
WHERE rn = 1;
DROP TABLE #Enroll
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 20, 2017 at 9:21 pm
How about if the data is sorted by service type in descending order? This way P would sort be for N.
February 20, 2017 at 9:59 pm
Could you post the CREATE TABLE and INSERT scripts to give us some sample data and then an example of the expected output? Answering your questions is just waaaay too much work. If you really want help, make it easy for others to help you. Otherwise, are we to assume you really don't want the help?
February 21, 2017 at 1:03 pm
wendy elizabeth - Monday, February 20, 2017 9:21 PMHow about if the data is sorted by service type in descending order? This way P would sort be for N.
not sure why you are asking the question above..... see code snip below
ROW_NUMBER() OVER(PARTITION BY personID ORDER BY serviceType DESC,
startDate DESC,
EndDate DESC) rn
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 15, 2017 at 4:18 pm
J Livingston SQL - Tuesday, February 21, 2017 1:03 PMwendy elizabeth - Monday, February 20, 2017 9:21 PMHow about if the data is sorted by service type in descending order? This way P would sort be for N.not sure why you are asking the question above..... see code snip below
ROW_NUMBER() OVER(PARTITION BY personID ORDER BY serviceType DESC,
startDate DESC,
EndDate DESC) rn
Wendy Elizabeth......did your resolve this?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply