Help Query

Transact-SQL
1

Dear Friends
I Have table
DECLARE @TableDate TABLE (TrxDate date )
INSERT INTO @TableDate
(
TrxDate
)
VALUES( '2018-07-13'),( '2018-07-14'),( '2018-07-15'),( '2018-07-17'),( '2018-07-18'),( '2018-07-19'),( '2018-07-20')
i have column count days serialized
SELECT *, IIF( ISNULL( DATEDIFF( DAY , LAG( TrxDate ) OVER (ORDER BY TrxDate ),TrxDate ),0) > 1 , 0 ,ISNULL( DATEDIFF( DAY , LAG( TrxDate ) OVER (ORDER BY TrxDate ),TrxDate ),0)) AS Nw , NULL AS Conut FROM @TableDate
the result is

TrxDate Nw Count
2018-07-13 0 NULL
2018-07-14 1 NULL
2018-07-15 1 NULL
2018-07-17 0 NULL
2018-07-18 1 NULL
2018-07-19 1 NULL
2018-07-20 1 NULL

I need update Count

TrxDate Nw Conut
2018-07-13 0 0
2018-07-14 1 1
2018-07-15 1 2
2018-07-17 0 0
2018-07-18 1 1
2018-07-19 1 2
2018-07-20 1 3
2

You haven't explained what you need. Please describe what you are trying to do.

DECLARE @TableDate TABLE (TrxDate date );
INSERT INTO @TableDate( TrxDate )
VALUES ( '2018-07-13')
      ,( '2018-07-14')
      ,( '2018-07-15')
      ,( '2018-07-17')
      ,( '2018-07-18')
      ,( '2018-07-19')
      ,( '2018-07-20');

WITH t (TrxDate, Nw)
AS
   (
   SELECT TrxDate
        , IIF( IsNull ( DateDiff( DAY, LAG( TrxDate ) OVER (ORDER BY TrxDate ), TrxDate ), 0 ) > 1 
              , 0 
              , IsNull( DateDiff( DAY , LAG( TrxDate ) OVER (ORDER BY TrxDate ) , TrxDate ), 0 )
             ) AS Nw 
   FROM @TableDate
   )
SELECT
   t.TrxDate
 , t.Nw
 , [Count] = CASE WHEN TrxDate IN( '2018-07-15','2018-07-19' ) THEN Nw + 1 ELSE Nw END
FROM t ;

image

3

I posted on another forum because it wasn't as easy as I thought it would be. Here's the results

DECLARE @TableDate TABLE (TrxDate date PRIMARY KEY CLUSTERED);
INSERT INTO @TableDate ( TrxDate)
VALUES ('2018-07-13'),('2018-07-14'),('2018-07-15')
  ,('2018-07-17'),('2018-07-18'),('2018-07-19'),('2018-07-20')
  ,('2018-07-23'),('2018-07-24'),('2018-07-25');

WITH cteData AS (
  -- Find the contiguous dates, and Group them together
  SELECT TrxDate, DATEADD(day, -1 * DENSE_RANK() OVER(ORDER BY TrxDate), TrxDate) AS Grouper
  FROM @TableDate
)
, cteIslands AS (
  -- Find the Start/End date of each Island
  SELECT Grouper
   , MIN(TrxDate) AS IslandStart
   , MAX(TrxDate) AS IslandEnd
  FROM cteData
  GROUP BY Grouper
)

-- Get the running total for each Island
SELECT data.TrxDate, isl.IslandStart
   , RunTot = DateDiff(day, isl.IslandStart, data.TrxDate) 

FROM @TableDate AS data
INNER JOIN cteIslands AS isl 
ON data.TrxDate between isl.IslandStart and isl.IslandEnd
ORDER BY data.TrxDate;
4

Maybe?

DECLARE @TableDate TABLE (TrxDate date )
INSERT INTO @TableDate
(
TrxDate
)
VALUES( '2018-07-13'),( '2018-07-14'),( '2018-07-15'),( '2018-07-17'),( '2018-07-18'),( '2018-07-19'),( '2018-07-20');


with tst as
(
select a.TrxDate , ROW_NUMBER()  over(order by TrxDate )  [rn]
from @TableDate a
)

,cte
as
(
select top 1  a.TrxDate , cast(0 as int) [rn] , cast(1 as int) cnt , cast(0 as int) [x]
from tst a
order by rn asc

union all

select a.TrxDate ,

(
case when DATEDIFF(day,  b.TrxDate ,a.TrxDate) > 1
then 0
else cast(b.rn + 1 as int)
end
)



,cast(b.cnt + 1 as int)

,
(
case when DATEDIFF(day,  b.TrxDate ,a.TrxDate) > 1
then 0
else cast(DATEDIFF(day,  b.TrxDate ,a.TrxDate) as int)
end
)


from tst a
, cte b
, tst c

where 

a.rn = b.cnt + 1
and c.rn > b.rn 
and c.rn = a.rn 

 
)

select  a.TrxDate
,a.x [Nw]
,a.rn [Count]
from cte  a