Identify missing record on accounts

• Briceston

  SSCrazy

  Points: 2899

  More actions

  March 8, 2019 at 11:23 pm

  #382956

  Hello  guys,

  I have a table with accounts that have numbers in sequence. I would like to identify the account(s) when charge category 360 is missing from the sequence for the associated account.   Thanks in advance.
  Example:
  AccountNumber     Charge Category
  00001XXA               220
  00001XXA               250
  00001XXA               360
  00001XXB               220
  00001XXB               250
  00001XXB               360
  00001XXC              220
  00001XXC              250
  00001XXC              361

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  March 9, 2019 at 12:02 am

  #2024018

  There are several ways of doing this, here is one
  😎

  
USE TEEST;
GO
SET NOCOUNT ON;

  ;WITH SAMPLE_DATA([AccountNumber],[Charge Category]) AS
(
  SELECT '00001XXA',220 UNION ALL
  SELECT '00001XXA',250 UNION ALL
  SELECT '00001XXA',360 UNION ALL
  SELECT '00001XXB',220 UNION ALL
  SELECT '00001XXB',250 UNION ALL
  SELECT '00001XXB',360 UNION ALL
  SELECT '00001XXC',220 UNION ALL
  SELECT '00001XXC',250 UNION ALL
  SELECT '00001XXC',361
)
,CHECK_360 AS
(
SELECT
  ROW_NUMBER() OVER
   (
    PARTITION BY SD.AccountNumber
    ORDER BY  @@VERSION
   ) AS AC_RID
 ,SD.AccountNumber
 ,SD.[Charge Category]
 ,SUM(CASE
   WHEN SD.[Charge Category] = 360 THEN 1
   ELSE 0
  END) OVER
    (
      PARTITION BY SD.AccountNumber
      ORDER BY  @@VERSION
      ROWS BETWEEN UNBOUNDED PRECEDING
       AND  UNBOUNDED FOLLOWING
    ) AS HAS_360
FROM  SAMPLE_DATA SD
)
SELECT
*
FROM CHECK_360 CK
WHERE CK.HAS_360 = 0
AND CK.AC_RID = 1;


• Jonathan AC Roberts

  SSCoach

  Points: 18362

  More actions

  March 9, 2019 at 12:20 pm

  #2024025

  or this:
  ;WITH SAMPLE_DATA([AccountNumber],[Charge Category]) AS
(
    SELECT '00001XXA',220 UNION ALL
    SELECT '00001XXA',250 UNION ALL
    SELECT '00001XXA',360 UNION ALL
    SELECT '00001XXB',220 UNION ALL
    SELECT '00001XXB',250 UNION ALL
    SELECT '00001XXB',360 UNION ALL
    SELECT '00001XXC',220 UNION ALL
    SELECT '00001XXC',250 UNION ALL
    SELECT '00001XXC',361
)
SELECT DISTINCT AccountNumber
  FROM SAMPLE_DATA A
 WHERE NOT EXISTS(SELECT * FROM SAMPLE_DATA B WHERE B.AccountNumber = A.AccountNumber AND B.[Charge Category]=360)


• Briceston

  SSCrazy

  Points: 2899

  More actions

  March 9, 2019 at 10:24 pm

  #2024028

  Eirikur Eiriksson - Saturday, March 9, 2019 12:02 AM

  There are several ways of doing this, here is one
  😎

  
USE TEEST;
GO
SET NOCOUNT ON;

  ;WITH SAMPLE_DATA([AccountNumber],[Charge Category]) AS
(
  SELECT '00001XXA',220 UNION ALL
  SELECT '00001XXA',250 UNION ALL
  SELECT '00001XXA',360 UNION ALL
  SELECT '00001XXB',220 UNION ALL
  SELECT '00001XXB',250 UNION ALL
  SELECT '00001XXB',360 UNION ALL
  SELECT '00001XXC',220 UNION ALL
  SELECT '00001XXC',250 UNION ALL
  SELECT '00001XXC',361
)
,CHECK_360 AS
(
SELECT
  ROW_NUMBER() OVER
   (
    PARTITION BY SD.AccountNumber
    ORDER BY  @@VERSION
   ) AS AC_RID
 ,SD.AccountNumber
 ,SD.[Charge Category]
 ,SUM(CASE
   WHEN SD.[Charge Category] = 360 THEN 1
   ELSE 0
  END) OVER
    (
      PARTITION BY SD.AccountNumber
      ORDER BY  @@VERSION
      ROWS BETWEEN UNBOUNDED PRECEDING
       AND  UNBOUNDED FOLLOWING
    ) AS HAS_360
FROM  SAMPLE_DATA SD
)
SELECT
*
FROM CHECK_360 CK
WHERE CK.HAS_360 = 0
AND CK.AC_RID = 1;


  Jonathan AC Roberts - Saturday, March 9, 2019 12:20 PM

  or this:
  ;WITH SAMPLE_DATA([AccountNumber],[Charge Category]) AS
(
    SELECT '00001XXA',220 UNION ALL
    SELECT '00001XXA',250 UNION ALL
    SELECT '00001XXA',360 UNION ALL
    SELECT '00001XXB',220 UNION ALL
    SELECT '00001XXB',250 UNION ALL
    SELECT '00001XXB',360 UNION ALL
    SELECT '00001XXC',220 UNION ALL
    SELECT '00001XXC',250 UNION ALL
    SELECT '00001XXC',361
)
SELECT DISTINCT AccountNumber
  FROM SAMPLE_DATA A
 WHERE NOT EXISTS(SELECT * FROM SAMPLE_DATA B WHERE B.AccountNumber = A.AccountNumber AND B.[Charge Category]=360)


  Thank you both. Your provided examples guided me to my intended output.