SQL if breaking number pattern, mark record?

I have the following query:

SELECT AccountNumber, RptPeriod
FROM dbo.Report
ORDER BY AccountNumber, RptPeriod.

I get the following results:

123   200801
123   200802
123   200803
234   200801
344   200801
344   200803

I need to mark the record where the rptperiod doesnt flow concurrently for the account. For example 344 200803 would have an X next to it since it goes fro开发者_C百科m 200801 to 200803.

This is for about 19321 rows and I want it on a company basis so between different companies I dont care what the numbers are, I just want the same company to show where there is breaks in the number pattern.

Any Ideas??

Thanks!

OK, this is kind of ugly (double join + anti-join) but it gets the work done, AND is pure portable SQL:

SELECT * 
FROM   dbo.Report R1
     , dbo.Report R2
WHERE  R1.AccountNumber = R2.AccountNumber
AND    R2.RptPeriod - R1.RptPeriod > 1
-- subsequent NOT EXISTS ensures that R1,R2 rows found are "next to each other",
-- e.g. no row exists between them in the ordering above
AND    NOT EXISTS
        (SELECT 1 FROM dbo.Report R3
        WHERE  R1.AccountNumber = R3.AccountNumber
        AND    R2.AccountNumber = R3.AccountNumber
        AND    R1.RptPeriod < R3.RptPeriod
        AND    R3.RptPeriod < R2.RptPeriod
        )

Something like this should do it:

--  cte lists all items by AccountNumber and RptPeriod, assigning an ascending integer
--  to each RptPeriod and restarting at 1 for each new AccountNumber
;WITH cte (AccountNumber, RptPeriod, Ranking)
 as (select
        AccountNumber
       ,RptPeriod
       ,row_number() over (partition by AccountNumber order by AccountNumber, RptPeriod) Ranking
      from dbo.Report)
 --  and then we join each row with each preceding row based on that "Ranking" number
 select
    This.AccountNumber
   ,This.RptPeriod
   ,case
      when Prior.RptPeriod is null then ''  --  Catches the first row in a set
      when Prior.RptPeriod = This.RptPeriod - 1 then ''  --  Preceding row's RptPeriod is one less that This row's RptPeriod
      else 'x'  --  --  Preceding row's RptPeriod is not less that This row's RptPeriod
    end  UhOh
  from cte This
   left outer join cte Prior
    on Prior.AccountNumber = This.AccountNumber
     and Prior.Ranking = This.Ranking - 1

(Edited to add comments)

WITH T
     AS (SELECT *,
                /*Each island of contiguous data will have 
                  a unique AccountNumber,Grp combination*/
                RptPeriod - ROW_NUMBER() OVER (PARTITION BY AccountNumber 
                                               ORDER BY RptPeriod ) Grp,
                /*RowNumber will be used to identify first record
                per company, this should not be given an 'X'. */
                ROW_NUMBER() OVER (PARTITION BY AccountNumber 
                                       ORDER BY RptPeriod ) AS RN
         FROM   Report)
SELECT AccountNumber,
       RptPeriod,
       /*Check whether first in group but not first over all*/
       CASE
         WHEN ROW_NUMBER() OVER (PARTITION BY AccountNumber, Grp 
                                     ORDER BY RptPeriod) = 1
              AND RN > 1 THEN 'X'
       END AS Flag
FROM   T  

SELECT  *
FROM   report r
LEFT   JOIN report r2
ON     r.accountnumber = r.accountnumber
AND    {r2.rptperiod is one day after r.rptPeriod}
JOIN   report r3
ON     r3.accountNumber = r.accountNumber
AND    r3.rptperiod > r1.rptPeriod
WHERE  r2.rptPeriod IS NULL
AND    r3 IS NOT NULL

I'm not sure of sql servers date logic syntax, but hopefully you get the idea. r will be all the records where the next rptPeriod is NULL (r2) and there exists at least one greater rptPeriod (r3). The query isn't super straight forward I guess, but if you have an index on the two columns, it'll probably be the most efficent way to get your data.

Basically, you number rows within every account, then, using the row numbers, compare the RptPeriod values for the neighbouring rows.

It is assumed here that RptPeriod is the year and month encoded, for which case the year transition check has been added.

;WITH Report_sorted AS (
  SELECT
    AccountNumber,
    RptPeriod,
    rownum = ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY RptPeriod)
  FROM dbo.Report
)
SELECT
  AccountNumber,
  RptPeriod,
  CASE ISNULL(CASE WHEN r1.RptPeriod / 100 < r2.RptPeriod / 100 THEN 12 ELSE 0 END
              + r1.RptPeriod - r2.RptPeriod, 1) AS Chk
    WHEN 1 THEN ''
    ELSE 'X' 
  END
FROM Report_sorted r1
  LEFT JOIN Report_sorted r2
    ON r1.AccountNumber = r2.AccountNumber AND r1.rownum = r2.rownum + 1

It could be complicated further with an additional check for gaps spanning a year and more, if you need that.