January 16, 2017 at 4:01 am
Hi Guys,
I have challenge i need help with. I need a script to pick the next appropriate record ordered by date.
I have a table with the following columns
ClientID,
ReferralID,
ReferralDate,
AppropriateRecordFlag,
ServiceGroup
See sample data below.
| ClientID | ReferralID | ReferralDate | AppropriateRecordFlag | ServiceGroup |
| 123456 | 10 | 10/01/2016 | 1 | group A |
| 123456 | 13 | 09/02/2016 | 0 | group A |
| 123456 | 12 | 10/03/2016 | 1 | group A |
| 123456 | 15 | 09/04/2016 | 0 | group A |
| 123456 | 11 | 09/05/2016 | 0 | group A |
| 123456 | 14 | 08/06/2016 | 1 | group A |
January 16, 2017 at 4:09 am
OK, and what result do you expect given that sample data?
Incidentally, if you provide your sample data in the form of CREATE TABLE and INSERT statements, it makes it easier for those answering your question to recreate your environment and thus you're more likely to get a better solution in a shorter time.
John
January 16, 2017 at 4:33 am
John Mitchell-245523 - Monday, January 16, 2017 4:09 AMOK, and what result do you expect given that sample data?Incidentally, if you provide your sample data in the form of CREATE TABLE and INSERT statements, it makes it easier for those answering your question to recreate your environment and thus you're more likely to get a better solution in a shorter time.
John
Thanks for your prompt response John.
Here is the create table script
create table #ReferralData
(
ClientID BIGINT,
ReferralID BIGINT,
ReferralDate DateTime,
AppropriateRecordFlag BIT,
ServiceGroup Varchar(255)
);
INSERT INTO #ReferralData (ClientID, ReferralID, ReferralDate, AppropriateRecordFlag, ServiceGroup)
VALUES
(123456,14,08/06/2016,1,'group A'),
(123456,11,09/05/2016,0,'group A'),
(123456,15,09/04/2016,0,'group A'),
(123456,12,10/03/2016,1,'group A'),
(123456,13,09/02/2016,0,'group A'),
(123456,10,10/01/2016,1,'group A');
January 16, 2017 at 4:34 am
John Mitchell-245523 - Monday, January 16, 2017 4:09 AMOK, and what result do you expect given that sample data?Incidentally, if you provide your sample data in the form of CREATE TABLE and INSERT statements, it makes it easier for those answering your question to recreate your environment and thus you're more likely to get a better solution in a shorter time.
John
The expected result are shown in the 2 new columns below.
| ClientID | ReferralID | ReferralDate | AppropriateRecordFlag | ServiceGroup | New Column ReferralIDNew | ReReferralToSameServiceGroupFlag |
| 123456 | 14 | 08/06/2016 | 1 | group A | 12 | 1 |
| 123456 | 11 | 09/05/2016 | 0 | group A | 12 | 1 |
| 123456 | 15 | 09/04/2016 | 0 | group A | 12 | 1 |
| 123456 | 12 | 10/03/2016 | 1 | group A | 10 | 1 |
| 123456 | 13 | 09/02/2016 | 0 | group A | 10 | 1 |
| 123456 | 10 | 10/01/2016 | 1 | group A | 0 |
pt�]H5.
January 16, 2017 at 5:05 am
Are those dates in European or US format? I can't tell. Please supply them in an unambiguous format (such as '20170116') and test your INSERT statement before posting.
Thanks
John
January 16, 2017 at 5:23 am
John Mitchell-245523 - Monday, January 16, 2017 5:05 AMAre those dates in European or US format? I can't tell. Please supply them in an unambiguous format (such as '20170116') and test your INSERT statement before posting.Thanks
John
Hi John,
Please find attached the update script.
create table #ReferralData
(
ClientID BIGINT,
ReferralID BIGINT,
ReferralDate INT,
AppropriateRecordFlag BIT,
ServiceGroup Varchar(255)
);
INSERT INTO #ReferralData (ClientID, ReferralID, ReferralDate, AppropriateRecordFlag, ServiceGroup)
VALUES
(123456,14,20160608,1,'group A'),
(123456,11,20160509,0,'group A'),
(123456,15,20160409,0,'group A'),
(123456,12,20160310,1,'group A'),
(123456,13,20160209,0,'group A'),
(123456,10,20160110,1,'group A');
Thanks
January 16, 2017 at 5:55 am
Thanks, but you didn't test the INSERT statement, did you?
Arithmetic overflow error converting expression to data type datetime.
John
January 16, 2017 at 6:10 am
John Mitchell-245523 - Monday, January 16, 2017 5:55 AMThanks, but you didn't test the INSERT statement, did you?
Arithmetic overflow error converting expression to data type datetime.
John
Hi John,
i tested the insert script.
The error is probably arising because i have updated my script and changed the date time filed to INT in the second script i posted.
best wishes
January 16, 2017 at 6:34 am
akinwandeb - Monday, January 16, 2017 6:10 AMJohn Mitchell-245523 - Monday, January 16, 2017 5:55 AMThanks, but you didn't test the INSERT statement, did you?
Arithmetic overflow error converting expression to data type datetime.
JohnHi John,
i tested the insert script.
The error is probably arising because i have updated my script and changed the date time filed to INT in the second script i posted.
best wishes
Does this work for you ?
;WITH
A AS (SELECT ROW_NUMBER() OVER (partition by 1 order by referralDate) this,* FROM #referraldata)
, B AS (SELECT A.*,A2.ReferralID ReferralIDNew FROM A LEFT OUTER JOIN A AS A2 ON A.this+1 = A2.this)
select * from B
Ben
January 16, 2017 at 7:03 am
@ben that doesn't give the correct answer, no, look at the OP's sample data. You've effectlvely just captured the next ReferralID in your query, something that would be much better done by using LEAD (Transact-SQL).
I believe this provides what the OP is after. I would, however, suggest that you keep your data types when supplying data, rather than changing them as as we wanted a format that we can guarantee works on any language. The original sample data you supplied, for example had dates such as 08/06/2016. Is that 08 June 2016 (as I would read it), or 06 August 2016 (as our friends across the pond would read it). Generally yyyy-mm-dd is the most used. Personally I prefer dd-mmm-yyyy, but either way whoever reads the dates will know what they are.
What John was after was the CREATE statement similar to I have supplied.CREATE TABLE #ReferralData (ClientID BIGINT,
ReferralID BIGINT,
ReferralDate DATE,
AppropriateRecordFlag BIT,
ServiceGroup Varchar(255));
INSERT INTO #ReferralData (ClientID, ReferralID, ReferralDate, AppropriateRecordFlag, ServiceGroup)
VALUES (123456,14,'08-Jun-2016',1,'group A'), --20160608
(123456,11,'09-May-2016',0,'group A'), --20160509
(123456,15,'09-Apr-2016',0,'group A'), --20160409
(123456,12,'10-Mar-2016',1,'group A'), --20160310
(123456,13,'09-Feb-2016',0,'group A'), --20160209
(123456,10,'10-Jan-2016',1,'group A'); --20160110
GO
SELECT *
FROM #ReferralData;
SELECT RD.ClientID,
RD.ReferralID,
RD.ReferralDate,
RD.AppropriateRecordFlag,
RD.ServiceGroup,
RD2.ReferralID AS ReferralIDNew,
CASE WHEN RD2.ReferralID IS NOT NULL THEN 1 ELSE 0 END AS ReReferralToSameServiceGroupFlag
FROM #ReferralData RD
OUTER APPLY (SELECT TOP 1 *
FROM #ReferralData oa
WHERE oa.ReferralDate < RD.ReferralDate
and oa.AppropriateRecordFlag = 1
ORDER BY ReferralDate DESC) RD2
ORDER BY rd.ReferralDate DESC;
GO
DROP TABLE #ReferralData;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 16, 2017 at 7:07 am
ben.brugman - Monday, January 16, 2017 6:34 AMakinwandeb - Monday, January 16, 2017 6:10 AMJohn Mitchell-245523 - Monday, January 16, 2017 5:55 AMThanks, but you didn't test the INSERT statement, did you?
Arithmetic overflow error converting expression to data type datetime.
JohnHi John,
i tested the insert script.
The error is probably arising because i have updated my script and changed the date time filed to INT in the second script i posted.
best wishes
Does this work for you ?
;WITH
A AS (SELECT ROW_NUMBER() OVER (partition by 1 order by referralDate) this,* FROM #referraldata)
, B AS (SELECT A.*,A2.ReferralID ReferralIDNew FROM A LEFT OUTER JOIN A AS A2 ON A.this+1 = A2.this)
select * from BBen
Hi Ben,
Thanks for your response. That does not work because it does not take the AppropriateRecordFlag into consideration
January 16, 2017 at 7:55 am
Thom A - Monday, January 16, 2017 7:03 AM@ben that doesn't give the correct answer, no, look at the OP's sample data. You've effectlvely just captured the next ReferralID in your query, something that would be much better done by using LEAD (Transact-SQL).I believe this provides what the OP is after. I would, however, suggest that you keep your data types when supplying data, rather than changing them as as we wanted a format that we can guarantee works on any language. The original sample data you supplied, for example had dates such as 08/06/2016. Is that 08 June 2016 (as I would read it), or 06 August 2016 (as our friends across the pond would read it). Generally yyyy-mm-dd is the most used. Personally I prefer dd-mmm-yyyy, but either way whoever reads the dates will know what they are.
What John was after was the CREATE statement similar to I have supplied.
CREATE TABLE #ReferralData (ClientID BIGINT,
ReferralID BIGINT,
ReferralDate DATE,
AppropriateRecordFlag BIT,
ServiceGroup Varchar(255));INSERT INTO #ReferralData (ClientID, ReferralID, ReferralDate, AppropriateRecordFlag, ServiceGroup)
VALUES (123456,14,'08-Jun-2016',1,'group A'), --20160608
(123456,11,'09-May-2016',0,'group A'), --20160509
(123456,15,'09-Apr-2016',0,'group A'), --20160409
(123456,12,'10-Mar-2016',1,'group A'), --20160310
(123456,13,'09-Feb-2016',0,'group A'), --20160209
(123456,10,'10-Jan-2016',1,'group A'); --20160110
GOSELECT *
FROM #ReferralData;SELECT RD.ClientID,
RD.ReferralID,
RD.ReferralDate,
RD.AppropriateRecordFlag,
RD.ServiceGroup,
RD2.ReferralID AS ReferralIDNew,
CASE WHEN RD2.ReferralID IS NOT NULL THEN 1 ELSE 0 END AS ReReferralToSameServiceGroupFlag
FROM #ReferralData RD
OUTER APPLY (SELECT TOP 1 *
FROM #ReferralData oa
WHERE oa.ReferralDate < RD.ReferralDate
and oa.AppropriateRecordFlag = 1
ORDER BY ReferralDate DESC) RD2
ORDER BY rd.ReferralDate DESC;
GODROP TABLE #ReferralData;
GO
Thanks Thom A.
Your script seems to solve the challenge.
Many thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply