Sql-server – Return Bit Value based on previous records by Category

sql serversql server 2014

How would I return TRUE or FALSE if four of the last six records failed for a given MAKEID? So there has to be 4 failures (or FALSE records) in the last six

MAKEID       FAILURES    CREATEDATE
101          TRUE        12/1/2015
101          FALSE       12/5/2015
101          TRUE        12/6/2015
101          FALSE       12/8/2015
101          TRUE        12/10/2015
101          FALSE       12/15/2015
101          FALSE       1/1/2016
101          FALSE       1/2/2016
102          TRUE        12/2/2015
102          TRUE        12/4/2015
102          FALSE       12/5/2015
102          TRUE        12/6/2015
102          FALSE       12/7/2015
102          TRUE        1/1/2016

Query would return the following:

MAKEID    FOUROFLASTSIXFAILED
101       TRUE
102       FALSE

Requirement is there needs to be 4 or more failures in last six records. So if there were 4, 5, or 6 failures in the last six records FOUROFLASTSIXFAILED would return TRUE. If there were only 1, 2, 3 records for a given MAKEID that would return FALSE because there at least needs to be 4 failures in previous six records.

Best Answer

Here is one approach (but please, stop changing your requirements):

DECLARE @x TABLE(MAKEID INT, FAILURES varchar(5), CREATEDATE date);

INSERT @x VALUES
(101,'TRUE ','20151201'),    (101,'FALSE','20151205'),    (101,'TRUE ','20151206'),
(101,'FALSE','20151208'),    (101,'TRUE ','20151210'),    (101,'FALSE','20151215'),
(101,'FALSE','20160101'),    (101,'FALSE','20160102'),    (102,'TRUE ','20151202'),
(102,'TRUE ','20151204'),    (102,'FALSE','20151205'),    (102,'TRUE ','20151206'),
(102,'FALSE','20151207'),    (102,'TRUE ','20160101');

;WITH x AS
(
  SELECT MAKEID, FAILURES,
    -- allow us to identify the last 6 rows:
    rn = ROW_NUMBER() OVER (PARTITION BY MAKEID ORDER BY CREATEDATE DESC)
  FROM @x
)
SELECT MAKEID, FOUROFLASTSIXFAILED = CASE 
  -- make sure there have been at least 6 tests - though this may change:
    WHEN COUNT(*) >= 6 
  -- make sure *at least* 4 of those have failed *within the last 6*
  -- though this may change (again), too:
    AND COUNT(CASE WHEN rn <= 6 AND FAILURES = 'TRUE' THEN 1 END) >= 4 
  THEN 'TRUE' ELSE 'FALSE' END 
FROM x 
GROUP BY MAKEID
ORDER BY MAKEID;

Results (I think these are right, as opposed to the desired output stated in your question, which seem to conflict with your stated requirements):

MAKEID    FOUROFLASTSIXFAILED
------    -------------------
101       FALSE
102       TRUE

It would seem to me that MAKEID = 102 is the only one that has failed exactly four times out of its last six entries, unless the FAILURES column in the original table really represents NOT_FAILURES.

Related Solutions

Sql-server – SQL Server 2008 – Return records in chronological order, given a separation factor

Prior to SQL 2008, the most common solution was to use a UDF to calculate the great-circle distance between two points on a sphere. The Haversine formula is probably the most commonly used method.

Of course the Earth is not actually a perfect sphere, but this was considered "good enough" for most uses.

In SQL 2008, as you anticipated, such calculations are simplified and made more accurate by the introduction of the Geography and Geometry data types. Here's a brief sample of how you can use them to simplify distance calculations.

DECLARE @locations TABLE(locname VARCHAR(100), coord geography)
DECLARE @loc1 geography
DECLARE @loc2 geography


INSERT INTO @locations 
VALUES('HOME', geography::Point(-81.810194, 41.478156, 4326))   --Note: Lat, Long, SRID
                                                                --The 4326 is the SRID (spatial reference id) used by SQL as 
                                                                --a reference to the WGS 84 Standard. This is the same reference
                                                                --used by the GPS system
INSERT INTO @locations 
VALUES('WORK', geography::Point(-81.687771, 41.498227, 4326))

SELECT * FROM @locations

SELECT @loc1 = coord FROM @locations WHERE locname = 'HOME'
SELECT @loc2 = coord FROM @locations WHERE locname = 'WORK'

SELECT @loc1.STDistance(@loc2) * 3.2808399  --STDistance is in meters so we multiply to convert to feet 

SELECT @loc1.STIntersects(@loc2.STBuffer(300 / 3.2808399)) as isWithin300Ft  --This formula returns True when the point @loc1 intersects with the 300ft buffer zone around @loc2

The SRID is the key to the improved accuracy. The WGS 84 specification to which it refers includes a standardized coordinate system and a reference ellipsoid. In other words, it accounts for the non-spherical nature of the Earth, giving better results than a pure spherical Great Circle calculation.

If GIS accuracy is important to your work, this is the simplest way to implement it in SQL 2008.

Sql-server – Set Value Based on Number of Records with Certain Values using SQL Server

This query will return PASS if there are at least three unique levels, otherwise FAIL.

SELECT PRODUCT, (CASE
       WHEN COUNT(DISTINCT [LEVEL])>=3 THEN 'PASS'
       ELSE 'FAIL' END) AS [STATUS]
FROM tablenamegoeshere
GROUP BY PRODUCT, [CONTROL];

COUNT(DISTINCT ..) counts the number of unique values of a column or expression. CASE returns different values depending on the outcome of different conditions. In this case, if the number of unique values is equal to or greater than three, we want to return 'PASS' otherwise 'FAIL'.

Obviously, if you want to return a bit value (i.e. boolean), you could change the strings 'PASS' and 'FAIL' in the code into 1 and 0 respectively and cast them to bit using CAST(... AS bit).

Best Answer

Related Solutions

Sql-server – SQL Server 2008 – Return records in chronological order, given a separation factor

Sql-server – Set Value Based on Number of Records with Certain Values using SQL Server

Related Question