January 17, 2019 at 11:50 am
I have a result set that I need to apply grouping within. However, the grouping is not just a simply group by clause (at least that I can figure out).
Some of our animals (I work at a very large animal shelter) require a veterinarian to check on them. Someone will enter "VET CHECK" on the animal's record. The history is stored so when I'm visually looking at the history, I can determine when the animal was first placed on VET CHECK and when it was removed. The issue I'm having is that I need to determine this in code and can't seem to come up with a solution. The code below will create the table and the sample data is a good representation of what most animals who require to see the vet will look like.
After creating the table and inserting the data, select * and sort by stamp desc. You'll see a VET CHECK (looking in the outcome_request field) was entered on 1/15 at 7:18 AM. If I run the report before 7:18 am, it shouldn't show the animal on VET CHECK status at all (note the NULL in the previous record). After 7:18 am (and until 1/16 at 2:07 pm), it should show the animal on VET CHECK status. If there was ONLY one VET CHECK during the animal's stay, I could simply use MIN(stamp) to get the date the animal was placed on status. However, you'll notice this animal had several different times it was on status.
Looking further down the result set, you'll it was placed on status on 1/10 at 12:07 pm and removed from status on 1/14 at 4:02 pm. If I run the report prior to 1/10 at 12:07 pm, the animal should not show on status. Running the report ANY time between 1/10 at 12:07 pm and 1/14 at 4:02 pm, the animal should show on status and the first date on status should show as 1/10. Again, I can't just use MIN(stamp) for this since there are multiple VET CHECK statuses.CREATE TABLE kennel_history(kh_identity int NOT NULL,
kennel_identity int NOT NULL,
stamp datetime NULL,
userid varchar(8) NULL,
impound_no varchar(10) NOT NULL,
kennel_no varchar(10) NULL,
kennel_stat varchar(10) NULL,
hold_notify varchar(1) NULL,
outcome_request varchar(10) NULL
);
INSERT INTO kennel_history
VALUES (9471697,881929,'2019-01-17 08:05:41','CHEITMAN','K18-847522','QCB','UNAVAILABL',NULL,NULL),
(9471254,881929,'2019-01-16 14:07:18','THUTCHIN','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469550,881929,'2019-01-15 07:18:36','BBUSCEMI','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469390,881929,'2019-01-14 16:56:02','LRAYNER','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469302,881929,'2019-01-14 16:02:41','SHUNT','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9467613,881929,'2019-01-13 08:30:46','DEADS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465923,881929,'2019-01-11 10:16:52','DEADS','K18-847522','QRL06','UNAVAILABL','Y','VET CHECK'),
(9465225,881929,'2019-01-10 12:07:17','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465224,881929,'2019-01-10 12:07:07','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9463051,881929,'2019-01-08 06:43:19','CSILVEY','K18-847522','QRL03','AVAILABLE',NULL,NULL),
(9461197,881929,'2019-01-06 09:24:07','APENAZUR','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9460067,881929,'2019-01-05 08:57:46','APENAZUR','K18-847522','QRL07','AVAILABLE',NULL,NULL),
(9459250,881929,'2019-01-04 10:13:45','DEADS','K18-847522','QRL01','AVAILABLE',NULL,NULL),
(9458551,881929,'2019-01-03 12:30:42','ACLARK','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9458499,881929,'2019-01-03 11:51:48','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9458484,881929,'2019-01-03 11:48:32','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9454810,881929,'2018-12-29 12:20:01','ACLARK','K18-847522','DHC04','UNAVAILABL',NULL,NULL),
(9454683,881929,'2018-12-29 11:08:39','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454680,881929,'2018-12-29 11:06:32','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454511,881929,'2018-12-29 09:13:22','BBUSCEMI','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9453649,881929,'2018-12-28 08:46:12','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453648,881929,'2018-12-28 08:46:07','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET CHECK'),
(9453624,881929,'2018-12-28 08:03:19','BBUSCEMI','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453533,881929,'2018-12-27 17:45:22','DEADS','K18-847522','DO03','UNAVAILABL','Y','VET TECH'),
(9453405,881929,'2018-12-27 15:28:02','DEADS','K18-847522','DO02','UNAVAILABL','Y','VET TECH'),
(9452597,881929,'2018-12-26 15:27:48','SSUTTON','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452426,881929,'2018-12-26 13:05:26','THUTCHIN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452121,881929,'2018-12-26 10:18:55','THUTCHIN','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451959,881929,'2018-12-26 08:09:21','BBUSCEMI','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451886,881929,'2018-12-25 14:12:49','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451884,881929,'2018-12-25 14:11:58','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451870,881929,'2018-12-25 13:28:15','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9450863,881929,'2018-12-23 17:24:17','BLAEHLE','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9449482,881929,'2018-12-22 13:12:34','V-KTAYLO','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448808,881929,'2018-12-21 16:23:03','SBUCKMAN','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448111,881929,'2018-12-21 09:10:49','CHEITMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9448069,881929,'2018-12-21 08:36:47','BMERMAN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9447864,881929,'2018-12-20 17:32:53','DEADS','K18-847522','DO07','UNAVAILABL','Y','VET TECH'),
(9446090,881929,'2018-12-19 09:54:33','MGELTZ','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9445884,881929,'2018-12-19 07:25:51','ZKNOX','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9444928,881929,'2018-12-18 08:22:30','EKNEPPER','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9438860,881929,'2018-12-12 09:15:33','CMCCANN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9438820,881929,'2018-12-12 08:21:33','JAUSEC','K18-847522','DO07','UNAVAILABL',NULL,NULL);
Somehow, I have to take into account the records on either side of the status where outcome_request IS NULL so I know when the status starts and stops. This one has me stumped.
Any help in figuring this out would be greatly appreciated.
January 17, 2019 at 12:10 pm
OK, couple questions on this to clarify things:
January 17, 2019 at 1:18 pm
This sample data represents just one animal. There is an animal_id field which I didn't include here. The kennel_identity is created when the animal is brought in to us (also referred to as impounded) which is why kennel_identity and impound_no are the same for all of the history records. kh_identity is an identity field which is why you see it higher for each record.
Here is part of my overall SQL Query. The overall query, which is a command object in a Crystal Report, has multiple select statements with different criteria all UNIONed to create a single dataset. All of the different statements have two fields in them that I need this date for: FirstHoldDate and DaysOnHold. These are inline select statements. Once I have FirstHoldDate correct, DaysOnHold is child's play.
SELECT 'VET CHECK Holds - Outcomed' AS RecordType,
k.KENNEL_NO AS KennelNumber,
ISNULL(k.KENNEL_STAT, '') AS KennelStatus,
ISNULL(k.kennel_substat, '') AS KennelSubstatus,
k.TAG_NO AS TagNumber,
'' AS ReviewDate,
'' AS Diagnosis,
'' AS TreatmentDescription,
a.ANIMAL_ID AS AnimalId,
a.ANIMAL_TYPE AS AnimalType,
a.age_long AS AgeLong,
CASE
WHEN a.SECONDARY_BREED IS NULL THEN a.PRIMARY_BREED
ELSE a.PRIMARY_BREED + ' / ' + a.SECONDARY_BREED
END + ' / ' +
CASE
WHEN a.SECONDARY_COLOR IS NULL THEN a.PRIMARY_COLOR
ELSE a.PRIMARY_COLOR + ' / ' + a.SECONDARY_COLOR
END AS BreedColor,
a.ANIMAL_NAME AS AnimalName,
a.SEX AS AnimalSex,
m.MEMO_DATE AS MemoDate,
m.memo_author AS MemoAuthor,
m.MEMO_NO AS MemoNumber,
m.MEMO_TEXT AS MemoText,
ISNULL(m.MEMO_SUBTYPE, '') AS MemoSubtype,
(SELECT MAX(stamp) AS FirstDate
FROM SYSADM.kennel_history AS kh
WHERE (kh.animal_id = k.ANIMAL_ID)
AND (kh.OUTCOME_REQUEST = 'VET CHECK')) AS FirstHoldDate,
DATEDIFF(DD, (SELECT MAX(stamp) AS FirstDate
FROM SYSADM.kennel_history AS kh
WHERE (kh.animal_id = k.ANIMAL_ID)
AND (kh.OUTCOME_REQUEST = 'VET CHECK')), GETDATE()) AS DaysOnHold,
(SELECT TOP 1 t.[WEIGHT]
FROM SYSADM.TREATMENT AS t
WHERE (ISNULL(t.[WEIGHT], 0) > 0)
AND (t.animal_id = k.ANIMAL_ID)
ORDER BY t.TREATMENT_DATE DESC,
t.TREATMENT_TIME DESC) AS LastWeight,
(SELECT TOP 1 t.TREATMENT_DATE
FROM SYSADM.TREATMENT AS t
WHERE (ISNULL(t.[WEIGHT], 0) > 0)
AND (t.animal_id = k.ANIMAL_ID)
ORDER BY t.TREATMENT_DATE DESC,
t.TREATMENT_TIME DESC) AS LastWeightDate,
us.TreatmentDate AS URIDate,
us.TreatmentType AS URIType,
us.TreatmentSubtype AS URIScore,
k.extra3 AS [Level],
ISNULL(k.OUTCOME_TYPE, '') AS OutcomeType
FROM SYSADM.KENNEL AS k
INNER JOIN SYSADM.ANIMAL AS a ON k.ANIMAL_ID = a.ANIMAL_ID
LEFT JOIN SYSADM.MEMO AS m ON a.ANIMAL_ID = m.MEMO_ID
AND m.MEMO_TYPE = 'MEDICAL'
LEFT JOIN URIScore AS us ON k.ANIMAL_ID = us.AnimalId
WHERE (k.OUTCOME_REQUEST = 'VET CHECK')
AND (k.OUTCOME_TYPE <> 'FOSTER')
AND (k.OUTCOME_DATE >= DATEADD(YY, -5, GETDATE()))
AND (CASE
WHEN k.KENNEL_NO LIKE 'B%' THEN 'Buddy Center'
ELSE 'Quebec Street'
END = '{?Shelter Location}')
The KENNEL table is where the kennel_history records are coming from. KENNEL contains a single record for each time an animal is impounded with us. Most animals are here only once but sometimes they come back for various reasons. Each time KENNEL is updated, it writes the new row to kennel_history via a trigger on the KENNEL table. The ANIMAL table contains a single record for each animal (pretty straight forward for this). MEMO contains all of the notes recorded for each animal. URIScore is a CTE that computes the latest upper respiratory infection score the animal has recorded.
To answer your 3rd question, you are correct. They want to see the first date the animal was placed on the current vet check and then the number of days the animal has been on check status. Of course, they only want to see the most current vet check and not include the others.
January 18, 2019 at 2:19 pm
I tried with CTE and cross apply, but cross apply may need to be changed to full outer join on animal id
CREATE TABLE #kennel_history(kh_identity int NOT NULL,
kennel_identity int NOT NULL,
stamp datetime NULL,
userid varchar(8) NULL,
impound_no varchar(10) NOT NULL,
kennel_no varchar(10) NULL,
kennel_stat varchar(10) NULL,
hold_notify varchar(1) NULL,
outcome_request varchar(10) NULL
);
INSERT INTO #kennel_history
VALUES (9471697,881929,'2019-01-17 08:05:41','CHEITMAN','K18-847522','QCB','UNAVAILABL',NULL,NULL),
(9471254,881929,'2019-01-16 14:07:18','THUTCHIN','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469550,881929,'2019-01-15 07:18:36','BBUSCEMI','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469390,881929,'2019-01-14 16:56:02','LRAYNER','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469302,881929,'2019-01-14 16:02:41','SHUNT','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9467613,881929,'2019-01-13 08:30:46','DEADS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465923,881929,'2019-01-11 10:16:52','DEADS','K18-847522','QRL06','UNAVAILABL','Y','VET CHECK'),
(9465225,881929,'2019-01-10 12:07:17','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465224,881929,'2019-01-10 12:07:07','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9463051,881929,'2019-01-08 06:43:19','CSILVEY','K18-847522','QRL03','AVAILABLE',NULL,NULL),
(9461197,881929,'2019-01-06 09:24:07','APENAZUR','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9460067,881929,'2019-01-05 08:57:46','APENAZUR','K18-847522','QRL07','AVAILABLE',NULL,NULL),
(9459250,881929,'2019-01-04 10:13:45','DEADS','K18-847522','QRL01','AVAILABLE',NULL,NULL),
(9458551,881929,'2019-01-03 12:30:42','ACLARK','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9458499,881929,'2019-01-03 11:51:48','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9458484,881929,'2019-01-03 11:48:32','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9454810,881929,'2018-12-29 12:20:01','ACLARK','K18-847522','DHC04','UNAVAILABL',NULL,NULL),
(9454683,881929,'2018-12-29 11:08:39','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454680,881929,'2018-12-29 11:06:32','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454511,881929,'2018-12-29 09:13:22','BBUSCEMI','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9453649,881929,'2018-12-28 08:46:12','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453648,881929,'2018-12-28 08:46:07','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET CHECK'),
(9453624,881929,'2018-12-28 08:03:19','BBUSCEMI','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453533,881929,'2018-12-27 17:45:22','DEADS','K18-847522','DO03','UNAVAILABL','Y','VET TECH'),
(9453405,881929,'2018-12-27 15:28:02','DEADS','K18-847522','DO02','UNAVAILABL','Y','VET TECH'),
(9452597,881929,'2018-12-26 15:27:48','SSUTTON','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452426,881929,'2018-12-26 13:05:26','THUTCHIN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452121,881929,'2018-12-26 10:18:55','THUTCHIN','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451959,881929,'2018-12-26 08:09:21','BBUSCEMI','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451886,881929,'2018-12-25 14:12:49','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451884,881929,'2018-12-25 14:11:58','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451870,881929,'2018-12-25 13:28:15','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9450863,881929,'2018-12-23 17:24:17','BLAEHLE','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9449482,881929,'2018-12-22 13:12:34','V-KTAYLO','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448808,881929,'2018-12-21 16:23:03','SBUCKMAN','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448111,881929,'2018-12-21 09:10:49','CHEITMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9448069,881929,'2018-12-21 08:36:47','BMERMAN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9447864,881929,'2018-12-20 17:32:53','DEADS','K18-847522','DO07','UNAVAILABL','Y','VET TECH'),
(9446090,881929,'2018-12-19 09:54:33','MGELTZ','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9445884,881929,'2018-12-19 07:25:51','ZKNOX','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9444928,881929,'2018-12-18 08:22:30','EKNEPPER','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9438860,881929,'2018-12-12 09:15:33','CMCCANN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9438820,881929,'2018-12-12 08:21:33','JAUSEC','K18-847522','DO07','UNAVAILABL',NULL,NULL);
Declare @startDate datetime = '2019-01-10 12:07:07.000',
@EndDate datetime = '2019-01-14 16:02:41.000'
--set @EndDate = '2019-01-14 16:02:00.000'
;with cteOnStatus as (
select Min(Stamp) OnStatusDate from #kennel_history
where stamp between @startDate and @EndDate
and Outcome_Request = 'VET CHECK'),
cteOffStatus as (
select Min(Stamp) OffStatusDate from #kennel_history
where stamp between @startDate and @EndDate
and Outcome_Request is null)
Select Case when OnStatusDate > OffStatusDate or OffStatusDate is null then 'OnStatus' else 'Off Status' end as [Status],
Case when OnStatusDate > OffStatusDate or OffStatusDate is null then OnStatusDate else OffStatusDate end
from cteOffStatus
cross apply cteOnStatus
For better, quicker answers, 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/
January 21, 2019 at 1:48 pm
I wanted to share my ultimate solution. Using feedback from Mike (thank you Mike for spurring the creative juices) above combined with feedback on another forum and what I got back from our vendor, my ultimate solution was to create a function to get the desired date:
CREATE FUNCTION StartDateOnOutcomeRequest (@KennelIdentity INT,
@OutcomeRequest VARCHAR(10),
@DBStamp DATETIME)
RETURNS DATE
AS
BEGIN
/* This section for testing. Leave commented out for normal execution.
DECLARE @KennelIdentity INT = 881929;
DECLARE @OutcomeRequest VARCHAR(10) = 'ADOPTIONS';
DECLARE @DBStamp DATETIME = '2019-01-21 12:56:17.457';
*/
DECLARE @CurrentRowNum INT;
DECLARE @StartRowNum INT;
DECLARE @StartDate DATETIME;
DECLARE @RowNumTmp TABLE (RowNumber INT IDENTITY(1, 1),
KHIdentity INT,
OutcomeRequest VARCHAR(10),
Stamp DATETIME);
-- Load kennel history records into temp table with seqential row number.
INSERT INTO @RowNumTmp (KHIdentity,
OutcomeRequest,
Stamp)
SELECT kh_identity,
ISNULL(outcome_request, 'None'),
stamp
FROM SYSADM.kennel_history
WHERE (kennel_identity = @KennelIdentity)
ORDER BY kh_identity;
-- Identify the history record matching the database timestamp and outcome request type. Store that record''s row number in a variable.
SELECT @CurrentRowNum = RowNumber
FROM @RowNumTmp
WHERE (Stamp = @DBStamp)
AND (OutcomeRequest = @OutcomeRequest);
-- Identify the first row number on the current outcome request by looking at lesser rows where the outcome request does not match.
SELECT @StartRowNum = MAX(RowNumber) + 1
FROM @RowNumTmp
WHERE (RowNumber <= @CurrentRowNum)
AND (OutcomeRequest <> @OutcomeRequest);
-- Finally, get the date using the starting row number for the outcome request and return it below.
SELECT @StartDate = Stamp
FROM @RowNumTmp
WHERE (RowNumber = @StartRowNum);
RETURN(@StartDate);
END;
Usage:
SELECT k.KENNEL_NO AS KennelNumber,
ISNULL(k.KENNEL_STAT, '') AS KennelStatus,
ISNULL(k.kennel_substat, '') AS KennelSubstatus,
a.ANIMAL_ID AS AnimalId,
a.ANIMAL_TYPE AS AnimalType,
a.age_long AS AgeLong,
CASE
WHEN a.SECONDARY_BREED IS NULL THEN a.PRIMARY_BREED
ELSE a.PRIMARY_BREED + ' / ' + a.SECONDARY_BREED
END + ' / ' +
CASE
WHEN a.SECONDARY_COLOR IS NULL THEN a.PRIMARY_COLOR
ELSE a.PRIMARY_COLOR + ' / ' + a.SECONDARY_COLOR
END AS BreedColor,
dbo.StartDateOnOutcomeRequest(k.kennel_identity, k.OUTCOME_REQUEST, k.STAMP) AS FirstHoldDate,
DATEDIFF(DD, dbo.StartDateOnOutcomeRequest(k.kennel_identity, k.OUTCOME_REQUEST, k.STAMP), GETDATE()) AS DaysOnHold,
k.extra3 AS [Level],
ISNULL(k.OUTCOME_TYPE, '') AS OutcomeType
FROM SYSADM.KENNEL AS k
INNER JOIN SYSADM.ANIMAL AS a ON k.ANIMAL_ID = a.ANIMAL_ID
January 21, 2019 at 2:28 pm
I think you will half the calls to your function if you use a cross apply:SELECT k.KENNEL_NO AS KennelNumber,
ISNULL(k.KENNEL_STAT, '') AS KennelStatus,
ISNULL(k.kennel_substat, '') AS KennelSubstatus,
a.ANIMAL_ID AS AnimalId,
a.ANIMAL_TYPE AS AnimalType,
a.age_long AS AgeLong,
CASE
WHEN a.SECONDARY_BREED IS NULL THEN a.PRIMARY_BREED
ELSE a.PRIMARY_BREED + ' / ' + a.SECONDARY_BREED
END + ' / ' +
CASE
WHEN a.SECONDARY_COLOR IS NULL THEN a.PRIMARY_COLOR
ELSE a.PRIMARY_COLOR + ' / ' + a.SECONDARY_COLOR
END AS BreedColor,
F.StartDateOnOutcomeRequest AS FirstHoldDate,
DATEDIFF(DD, F.StartDateOnOutcomeRequest, GETDATE()) AS DaysOnHold,
k.extra3 AS [Level],
ISNULL(k.OUTCOME_TYPE, '') AS OutcomeType
FROM SYSADM.KENNEL AS k
INNER JOIN SYSADM.ANIMAL AS a ON k.ANIMAL_ID = a.ANIMAL_ID
CROSS APPLY(VALUES (dbo.StartDateOnOutcomeRequest(k.kennel_identity, k.OUTCOME_REQUEST, k.STAMP))) F(StartDateOnOutcomeRequest)
January 22, 2019 at 4:15 pm
This sounds like a standard gaps and island problem. I think that you can rewrite this to use an inline table-valued function which will be MUCH, MUCH faster than the multi-line table-valued function that you came up with.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2019 at 12:58 pm
jim.powers - Thursday, January 17, 2019 11:50 AMI have a result set that I need to apply grouping within. However, the grouping is not just a simply group by clause (at least that I can figure out).
Some of our animals (I work at a very large animal shelter) require a veterinarian to check on them. Someone will enter "VET CHECK" on the animal's record. The history is stored so when I'm visually looking at the history, I can determine when the animal was first placed on VET CHECK and when it was removed. The issue I'm having is that I need to determine this in code and can't seem to come up with a solution. The code below will create the table and the sample data is a good representation of what most animals who require to see the vet will look like.
After creating the table and inserting the data, select * and sort by stamp desc. You'll see a VET CHECK (looking in the outcome_request field) was entered on 1/15 at 7:18 AM. If I run the report before 7:18 am, it shouldn't show the animal on VET CHECK status at all (note the NULL in the previous record). After 7:18 am (and until 1/16 at 2:07 pm), it should show the animal on VET CHECK status. If there was ONLY one VET CHECK during the animal's stay, I could simply use MIN(stamp) to get the date the animal was placed on status. However, you'll notice this animal had several different times it was on status.
Looking further down the result set, you'll it was placed on status on 1/10 at 12:07 pm and removed from status on 1/14 at 4:02 pm. If I run the report prior to 1/10 at 12:07 pm, the animal should not show on status. Running the report ANY time between 1/10 at 12:07 pm and 1/14 at 4:02 pm, the animal should show on status and the first date on status should show as 1/10. Again, I can't just use MIN(stamp) for this since there are multiple VET CHECK statuses.
CREATE TABLE kennel_history(kh_identity int NOT NULL,
kennel_identity int NOT NULL,
stamp datetime NULL,
userid varchar(8) NULL,
impound_no varchar(10) NOT NULL,
kennel_no varchar(10) NULL,
kennel_stat varchar(10) NULL,
hold_notify varchar(1) NULL,
outcome_request varchar(10) NULL
);INSERT INTO kennel_history
VALUES (9471697,881929,'2019-01-17 08:05:41','CHEITMAN','K18-847522','QCB','UNAVAILABL',NULL,NULL),
(9471254,881929,'2019-01-16 14:07:18','THUTCHIN','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469550,881929,'2019-01-15 07:18:36','BBUSCEMI','K18-847522','QCE','UNAVAILABL','Y','VET CHECK'),
(9469390,881929,'2019-01-14 16:56:02','LRAYNER','K18-847522','QCE','UNAVAILABL',NULL,NULL),
(9469302,881929,'2019-01-14 16:02:41','SHUNT','K18-847522','QRL03','UNAVAILABL',NULL,NULL),
(9467613,881929,'2019-01-13 08:30:46','DEADS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465923,881929,'2019-01-11 10:16:52','DEADS','K18-847522','QRL06','UNAVAILABL','Y','VET CHECK'),
(9465225,881929,'2019-01-10 12:07:17','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9465224,881929,'2019-01-10 12:07:07','KMORRIS','K18-847522','QRL03','UNAVAILABL','Y','VET CHECK'),
(9463051,881929,'2019-01-08 06:43:19','CSILVEY','K18-847522','QRL03','AVAILABLE',NULL,NULL),
(9461197,881929,'2019-01-06 09:24:07','APENAZUR','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9460067,881929,'2019-01-05 08:57:46','APENAZUR','K18-847522','QRL07','AVAILABLE',NULL,NULL),
(9459250,881929,'2019-01-04 10:13:45','DEADS','K18-847522','QRL01','AVAILABLE',NULL,NULL),
(9458551,881929,'2019-01-03 12:30:42','ACLARK','K18-847522','QRL08','AVAILABLE',NULL,NULL),
(9458499,881929,'2019-01-03 11:51:48','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9458484,881929,'2019-01-03 11:48:32','AGARFIAS','K18-847522','DHC04','AVAILABLE',NULL,NULL),
(9454810,881929,'2018-12-29 12:20:01','ACLARK','K18-847522','DHC04','UNAVAILABL',NULL,NULL),
(9454683,881929,'2018-12-29 11:08:39','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454680,881929,'2018-12-29 11:06:32','AGARFIAS','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9454511,881929,'2018-12-29 09:13:22','BBUSCEMI','K18-847522','SXA24','UNAVAILABL',NULL,NULL),
(9453649,881929,'2018-12-28 08:46:12','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453648,881929,'2018-12-28 08:46:07','TSIMONS','K18-847522','SXA24','UNAVAILABL','Y','VET CHECK'),
(9453624,881929,'2018-12-28 08:03:19','BBUSCEMI','K18-847522','SXA24','UNAVAILABL','Y','VET TECH'),
(9453533,881929,'2018-12-27 17:45:22','DEADS','K18-847522','DO03','UNAVAILABL','Y','VET TECH'),
(9453405,881929,'2018-12-27 15:28:02','DEADS','K18-847522','DO02','UNAVAILABL','Y','VET TECH'),
(9452597,881929,'2018-12-26 15:27:48','SSUTTON','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452426,881929,'2018-12-26 13:05:26','THUTCHIN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9452121,881929,'2018-12-26 10:18:55','THUTCHIN','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451959,881929,'2018-12-26 08:09:21','BBUSCEMI','K18-847522','SXA04','UNAVAILABL',NULL,NULL),
(9451886,881929,'2018-12-25 14:12:49','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451884,881929,'2018-12-25 14:11:58','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9451870,881929,'2018-12-25 13:28:15','SBUCKMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9450863,881929,'2018-12-23 17:24:17','BLAEHLE','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9449482,881929,'2018-12-22 13:12:34','V-KTAYLO','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448808,881929,'2018-12-21 16:23:03','SBUCKMAN','K18-847522','DO02','UNAVAILABL','Y','VET CHECK'),
(9448111,881929,'2018-12-21 09:10:49','CHEITMAN','K18-847522','DO02','UNAVAILABL',NULL,NULL),
(9448069,881929,'2018-12-21 08:36:47','BMERMAN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9447864,881929,'2018-12-20 17:32:53','DEADS','K18-847522','DO07','UNAVAILABL','Y','VET TECH'),
(9446090,881929,'2018-12-19 09:54:33','MGELTZ','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9445884,881929,'2018-12-19 07:25:51','ZKNOX','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9444928,881929,'2018-12-18 08:22:30','EKNEPPER','K18-847522','DO07','UNAVAILABL','Y','PRIORITY 1'),
(9438860,881929,'2018-12-12 09:15:33','CMCCANN','K18-847522','DO07','UNAVAILABL',NULL,NULL),
(9438820,881929,'2018-12-12 08:21:33','JAUSEC','K18-847522','DO07','UNAVAILABL',NULL,NULL);Somehow, I have to take into account the records on either side of the status where outcome_request IS NULL so I know when the status starts and stops. This one has me stumped.
Any help in figuring this out would be greatly appreciated.
Good luck with animal rescue. My wife worked for two different groups over the years as an employee. This is why we have two undesirable dogs, and I've had leftover cats. There was the blind cat, the three-legged cat, the seriously neurotic cat, and my favorite, Ms. Pistol, the cat that had to be removed from PetSmart for attacking the staff.
Status is a state of being, which implies a start and end timestamp. Search around in Google up an article I did on state transition constraints. Your animals should move through a series of status codes over time to get them into the shelter, processed and released. Having actually worked in this area. It's a little more complicated than you initially think, thanks to local laws, quarantine times and other things.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply