How To Calculate Based on Row Value

1

Hi All,
I Need Help.

I Have Table Look Like This:
image

I want To add Another Status Row With Condition:
Fail Number will be calculate based on previous record
For Example:
Family A
Date 16 : 1 Fail
Date 17: 1 Fail
Date 18: 1 Fail
Date 19 : 0 Fail
So Will add another row
Date - Family -Fail
Status - Family A - 1

So it will calculate for every zero number,
AnotherExample:
Family B
Date 16 : 1 Fail
Date 17: 0 Fail
Date 18: 0 Fail
Date 19 : 0 Fail

So Will add another row
Date - Family -Fail
Status - Family B - 3

And it will reset to Zero if have zero in last date.

So the result will be something like this
image

Here i attached Sample Table Structure:

CREATE TABLE #tmp(
Date VARCHAR(10),
Family VARCHAR(100),
Fail int
)
INSERT INTO #tmp VALUES('2020-03-16', 'Family A', 1)
INSERT INTO #tmp VALUES('2020-03-17', 'Family A', 1)
INSERT INTO #tmp VALUES('2020-03-18', 'Family A', 1)
INSERT INTO #tmp VALUES('2020-03-19', 'Family A', 0)
INSERT INTO #tmp VALUES('2020-03-16', 'Family B', 1)
INSERT INTO #tmp VALUES('2020-03-17', 'Family B', 0)
INSERT INTO #tmp VALUES('2020-03-18', 'Family B', 0)
INSERT INTO #tmp VALUES('2020-03-19', 'Family B', 0)

Really appreciate any help.
Thanks

2

hi

hope this helps !!! :slight_smile:

SELECT
    CAST([#tmp].[Date] AS VARCHAR)
    , [#tmp].[Family]
    , [#tmp].[Fail]
FROM
    [#tmp]
UNION ALL
SELECT
    'status'
    , [T].[Family]
    , SUM(CASE WHEN [T].[Fail] = 0 THEN 1
              ELSE 0
          END) AS [countfail]
FROM
    [#tmp] AS [T]
GROUP BY
    [T].[Family];

image

3

Hi @harishgg1

based on your Query it will count number of zero values.
But the problem is, i want to reset it to zero if the last date "Fail" number is bigger than zero

For Example if i added another data
Date : 2020-03-20
Family : Family A
Fail : 1

Then the status should be 0

Thanks
Jacky

4

i am not able to understand !!

if the last row is not 0 ..
then reset to 0

what does reset to 0 mean
all rows should be reset to zero for the family !!!

please explain with data example !!!
before how data looks and after how data looks !!

thanks
doing it looks very easy .. :slight_smile:
understanding what you need is the only tough part

5

Hi @harishgg1

I mean something like this
Date - Family - Fail
2020-03-16 Family A 1
2020-03-17 FAmily A 0
2020-03-18 Family A 1

So i want to added another row
Status family A 0

So add the end the data will look like
Date - Family - Fail
2020-03-16 Family A 1
2020-03-17 FAmily A 0
2020-03-18 Family A 1
Status Family A 0

Zero comes because on 2020-03-18 family A have 1 fail

Sorry for my bad explaination.
Thanks
Jacky

6

image

use sqlteam
go

if OBJECT_ID('tempdb..#tmp') is not null
	drop table #tmp;

CREATE TABLE #tmp(
Date VARCHAR(10),
Family VARCHAR(100),
Fail int
)
INSERT INTO #tmp VALUES('2020-03-16', 'Family A', 1)
INSERT INTO #tmp VALUES('2020-03-17', 'Family A', 1)
INSERT INTO #tmp VALUES('2020-03-18', 'Family A', 1)
INSERT INTO #tmp VALUES('2020-03-19', 'Family A', 0)
INSERT INTO #tmp VALUES('2020-03-16', 'Family B', 1)
INSERT INTO #tmp VALUES('2020-03-17', 'Family B', 0)
INSERT INTO #tmp VALUES('2020-03-18', 'Family B', 0)
INSERT INTO #tmp VALUES('2020-03-19', 'Family B', 0)

;with sumFails
as
(
	select 'Status' as Status, Family, count(1) Fail 
from #tmp where Fail = 0 group by family
)
select * 
  from #tmp
union all
select * from sumFails
7

Try the row difference grouping technique:

WITH Grps
AS
(
	SELECT CONVERT(varchar(10), [Date], 23) AS [Date]
		,Family, Fail
		,ROW_NUMBER() OVER (PARTITION BY Family ORDER BY [Date])
			- ROW_NUMBER() OVER (PARTITION BY Family, Fail ORDER BY [Date]) AS Grp
	FROM #tmp
)
,GrpCounts
AS
(
	SELECT [Date], Family, Fail
		,COUNT(1) OVER (PARTITION BY Family, Grp) AS GrpCount
		,MAX([Date]) OVER (PARTITION BY Family) AS MaxDate
	FROM Grps
)
SELECT [Date], Family, Fail
FROM GrpCounts
UNION ALL
SELECT 'Status', Family
	,CASE WHEN Fail = 1 THEN 0 ELSE GrpCount END AS Fail
FROM GrpCounts
WHERE [Date] = MaxDate
ORDER BY [Date], Family;
1 Like
8

hi

i tried row number approach
Hope this helps :slight_smile:
I think i understood what you are saying !!

;WITH [CTE]
AS (
       SELECT
            ROW_NUMBER() OVER (PARTITION BY
                                   [T].[Family]
                               ORDER BY
                                   [T].[Date] DESC) AS [RN]
            , [T].[Date]
            , [T].[Family]
            , [T].[Fail]
       FROM
            [#tmp] AS [T]
   )
SELECT
    CAST([CTE].[Date] AS VARCHAR)
    , [CTE].[Family]
    , [CTE].[Fail]
FROM
    [CTE]
UNION ALL
SELECT
    'STATUS'
    , [CTE].[Family]
    , CASE [CTE].[Fail] WHEN 1 THEN 0
          WHEN 0 THEN 1
      END AS [FAIL]
FROM
    [CTE]
WHERE
    [CTE].[RN] = 1;
GO

image

1 Like
9

I do not see a difference in your code.

10

Once was counting fail

Other one was based on ... Date desc

Top 1 fail

Hope this helps

11

Hi @harishgg1, @Ifor, @yosiasz

Thank you so much for your help. Really appreciate it.
Wish all the best for all of you..

Regards,
Jacky