sql help (Network Steve Forum)

sql help

hello experts, need your help

below i have 3 bans .. who has workflows created and ordered by CreateTimestamp . i need to count the number of workflows from top to point where there is either a success or failure workflow that occurs at the latest .

1.ban 137108351 has success workflow and prior to that workflow it has 2 workflows (exclude success and failure)

2.ban 104917284 has success workflow as latest (it still has failure but not considered because it is occurred earlier to success ) and prior to that workflow it has 2 workflows (exclude success and failure)

3.ban 107500674 has failure workflow and prior to that workflow it has 0 workflows (exclude success and failure)

below provided code for sample data as well

GO
/****** Object:  Table [Temp].[deleteit]    Script Date: 7/31/2015 3:04:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Temp].[deleteit](
	[ban] [nvarchar](256) NULL,
	[WORKFLOW_NM] [nvarchar](250) NULL,
	[WorkFlow_Create_TM] [datetime] NULL
) ON [PRIMARY]

GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A4900068D2C4 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A4900068E908 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A49000696D74 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_TV_NO_SIGNAL', CAST(0x0000A4900069A5B4 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_TV_TV_ON_DEMAND', CAST(0x0000A499017CD624 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A499017D9780 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_TV_REMOTECONTROL_TROUBLESHOOT', CAST(0x0000A499017DBBD4 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A499017DFD74 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_INTERNET_TROUBLESHOOT', CAST(0x0000A499017E36E0 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A499017E4E50 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A499017E6494 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_INSTALL_WIRELESS_RECEIVER', CAST(0x0000A499017E8A14 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_TV_DVR_PAUSE_LIVE_TV_V2', CAST(0x0000A49A01824E88 AS DateTime))
GO
INSERT [Temp].[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A49A01824E88 AS DateTime))
GO

July 31st, 2015 3:26pm

CREATE TABLE dbo.[deleteit](
	[ban] [nvarchar](256) NULL,
	[WORKFLOW_NM] [nvarchar](250) NULL,
	[WorkFlow_Create_TM] [datetime] NULL
) ON [PRIMARY]

GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A4900068D2C4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A4900068E908 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A49000696D74 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_TV_NO_SIGNAL', CAST(0x0000A4900069A5B4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_TV_TV_ON_DEMAND', CAST(0x0000A499017CD624 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A499017D9780 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_TV_REMOTECONTROL_TROUBLESHOOT', CAST(0x0000A499017DBBD4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A499017DFD74 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_INTERNET_TROUBLESHOOT', CAST(0x0000A499017E36E0 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A499017E4E50 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A499017E6494 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_INSTALL_WIRELESS_RECEIVER', CAST(0x0000A499017E8A14 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_TV_DVR_PAUSE_LIVE_TV_V2', CAST(0x0000A49A01824E88 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A49A01824E88 AS DateTime))
GO


 
 
 ;with mycte as (select [ban], [WORKFLOW_NM], SUM(Case when [WORKFLOW_NM] Like '%SUCCESS' Or  [WORKFLOW_NM] Like '%FAILURE'  Then 1 else 0 END ) 
Over(Partition by [ban]) flg,
SUM(Case when [WORKFLOW_NM] Like '%SUCCESS' Or  [WORKFLOW_NM] Like '%FAILURE'  Then 0 else 1 END ) Over(
Partition by [ban] Order by [WorkFlow_Create_TM])cnt,  [WorkFlow_Create_TM]
  from [deleteit])
 

Select [ban], [WORKFLOW_NM], Case when [WORKFLOW_NM] Like '%FAILURE'  then 0 else cnt End cnt
 FROM mycte
 WHERE [WORKFLOW_NM] Like '%SUCCESS'  Or  ([WORKFLOW_NM] Like '%FAILURE' and flg=1)
  
 Order by [WorkFlow_Create_TM]
drop table [deleteit]
/*
ban	WORKFLOW_NM	cnt
137108351	SS_UVERSE_SUB_SUCCESS	2
104917284	SS_UVERSE_SUB_SUCCESS	2
107500674	SS_UVERSE_SUB_FAILURE	0
*/

Marked as answer by Dkuud 10 hours 53 minutes ago

Free Windows Admin Tool Kit Click here and download it now

July 31st, 2015 3:52pm

Try:

;with cte as (select T.*, 
COALESCE(LastF.dtFailure, '17530101') as dFailure, COALESCE(LastS.dtSuccess, '17530101') as dSuccess
from #deleteit T
OUTER APPLY (select top (1) D1.WorkFlow_Create_TM as dtSuccess 
from #deleteit D1 where D1.ban = T.ban AND
 right(D1.workflow_nm, 8) =  N'_SUCCESS'  order by D1.WorkFlow_Create_TM DESC) LastS
OUTER APPLY (select top (1) D1.WorkFlow_Create_TM as dtFailure 
from #deleteit D1 where D1.ban = T.ban AND
 right(D1.workflow_nm, 8) =  N'_FAILURE'  order by D1.WorkFlow_Create_TM DESC) LastF) ,
 
 

 cte1
 as (select *, case when dFailure > dSuccess then dFailure else dSuccess end as dtStop,
 case when dFailure > dSuccess then 'Failure' else 'Success' end as[Status]
  from cte)

 select ban, dtStop, [Status], count(case when [WorkFlow_Create_TM] < dtStop then 1 end) as countRows from cte1

 group by ban, dtStop, [Status]
 having count(case when [WorkFlow_Create_TM] < dtStop then 1 end) > 0

July 31st, 2015 4:07pm

Genius JingYag...

Technet Articles.. i cannot hardcode bans ..i have millions of them.. but looks great

thank you for the response

Free Windows Admin Tool Kit Click here and download it now

July 31st, 2015 4:17pm

I am not sure what do you mean? There is no hard-coding in my response, I only used default date for cases when we don't have failure or success at all.

July 31st, 2015 4:24pm

Hi Jingyang,

Will you solution cover cases when we have

FAILURE

SUCCESS

FAILURE -- we would want count of everything up to last failure?

in this sequence?

Free Windows Admin Tool Kit Click here and download it now

July 31st, 2015 4:29pm

Here is another solution - simpler than my original:

 ;with cte as (select *, max(case when right(workflow_nm, 8) IN (N'_FAILURE', N'_SUCCESS') then workflow_create_tm else '17530101' end) 
 over (partition by ban) as stopDt from #deleteit),

 cte1 as (select *, count(case when workflow_create_tm < stopDt then 1 end) over (partition by ban) as cntRows from cte)

 select * from cte1 where WorkFlow_Create_TM = stopDt

July 31st, 2015 4:37pm

CREATE TABLE dbo.[deleteit](
	[ban] [nvarchar](256) NULL,
	[WORKFLOW_NM] [nvarchar](250) NULL,
	[WorkFlow_Create_TM] [datetime] NULL
) ON [PRIMARY]

GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A4900068D2C4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A4900068E908 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A49000696D74 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_TV_NO_SIGNAL', CAST(0x0000A4900069A5B4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_TV_TV_ON_DEMAND', CAST(0x0000A499017CD624 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A499017D9780 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_TV_REMOTECONTROL_TROUBLESHOOT', CAST(0x0000A499017DBBD4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A499017DFD74 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_INTERNET_TROUBLESHOOT', CAST(0x0000A499017E36E0 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A499017E4E50 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A499017E6494 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_INSTALL_WIRELESS_RECEIVER', CAST(0x0000A499017E8A14 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_TV_DVR_PAUSE_LIVE_TV_V2', CAST(0x0000A49A01824E88 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A49A01824E88 AS DateTime))
GO


 
 
 ;with mycte as (select [ban], [WORKFLOW_NM], SUM(Case when [WORKFLOW_NM] Like '%SUCCESS' Or  [WORKFLOW_NM] Like '%FAILURE'  Then 1 else 0 END ) 
Over(Partition by [ban]) flg,
SUM(Case when [WORKFLOW_NM] Like '%SUCCESS' Or  [WORKFLOW_NM] Like '%FAILURE'  Then 0 else 1 END ) Over(
Partition by [ban] Order by [WorkFlow_Create_TM])cnt,  [WorkFlow_Create_TM]
  from [deleteit])
 

Select [ban], [WORKFLOW_NM], Case when [WORKFLOW_NM] Like '%FAILURE'  then 0 else cnt End cnt
 FROM mycte
 WHERE [WORKFLOW_NM] Like '%SUCCESS'  Or  ([WORKFLOW_NM] Like '%FAILURE' and flg=1)
  
 Order by [WorkFlow_Create_TM]
drop table [deleteit]
/*
ban	WORKFLOW_NM	cnt
137108351	SS_UVERSE_SUB_SUCCESS	2
104917284	SS_UVERSE_SUB_SUCCESS	2
107500674	SS_UVERSE_SUB_FAILURE	0
*/

Marked as answer by Dkuud Friday, July 31, 2015 8:14 PM

Free Windows Admin Tool Kit Click here and download it now

July 31st, 2015 7:49pm

CREATE TABLE dbo.[deleteit](
	[ban] [nvarchar](256) NULL,
	[WORKFLOW_NM] [nvarchar](250) NULL,
	[WorkFlow_Create_TM] [datetime] NULL
) ON [PRIMARY]

GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A4900068D2C4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A4900068E908 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A49000696D74 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'137108351', N'SS_UVERSE_TV_NO_SIGNAL', CAST(0x0000A4900069A5B4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_TV_TV_ON_DEMAND', CAST(0x0000A499017CD624 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A499017D9780 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_TV_REMOTECONTROL_TROUBLESHOOT', CAST(0x0000A499017DBBD4 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SUB_SUCCESS', CAST(0x0000A499017DFD74 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_VIDEO_INTERNET_TROUBLESHOOT', CAST(0x0000A499017E36E0 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_MY_WIFI_PASSWORD', CAST(0x0000A499017E4E50 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_SMS_RESTORE', CAST(0x0000A499017E6494 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'104917284', N'SS_UVERSE_INSTALL_WIRELESS_RECEIVER', CAST(0x0000A499017E8A14 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_TV_DVR_PAUSE_LIVE_TV_V2', CAST(0x0000A49A01824E88 AS DateTime))
GO
INSERT dbo.[deleteit] ([ban], [WORKFLOW_NM], [WorkFlow_Create_TM]) VALUES (N'107500674', N'SS_UVERSE_SUB_FAILURE', CAST(0x0000A49A01824E88 AS DateTime))
GO


 
 
 ;with mycte as (select [ban], [WORKFLOW_NM], SUM(Case when [WORKFLOW_NM] Like '%SUCCESS' Or  [WORKFLOW_NM] Like '%FAILURE'  Then 1 else 0 END ) 
Over(Partition by [ban]) flg,
SUM(Case when [WORKFLOW_NM] Like '%SUCCESS' Or  [WORKFLOW_NM] Like '%FAILURE'  Then 0 else 1 END ) Over(
Partition by [ban] Order by [WorkFlow_Create_TM])cnt,  [WorkFlow_Create_TM]
  from [deleteit])
 

Select [ban], [WORKFLOW_NM], Case when [WORKFLOW_NM] Like '%FAILURE'  then 0 else cnt End cnt
 FROM mycte
 WHERE [WORKFLOW_NM] Like '%SUCCESS'  Or  ([WORKFLOW_NM] Like '%FAILURE' and flg=1)
  
 Order by [WorkFlow_Create_TM]
drop table [deleteit]
/*
ban	WORKFLOW_NM	cnt
137108351	SS_UVERSE_SUB_SUCCESS	2
104917284	SS_UVERSE_SUB_SUCCESS	2
107500674	SS_UVERSE_SUB_FAILURE	0
*/

Marked as answer by Dkuud Friday, July 31, 2015 8:14 PM

July 31st, 2015 7:49pm

Eric,

Did you check my query, especially the second variation? I think the second variation is really simple one and should cover the OP scenario.

Free Windows Admin Tool Kit Click here and download it now

August 3rd, 2015 11:19pm

Hi Naomi,

I did check your second query, it returned 8 for 104917284 in my supposed scenario rather than 6 which is expected based on my understanding on OP's requirement.

August 3rd, 2015 11:26pm

Do we want to exclude all rows that are either SUCCESS or FAILURE, but calculate everything else from the top row to the latest success or failure? If so, it's easy to change my last query to exclude 'FAILURE' and 'SUCCESS' rows.

Free Windows Admin Tool Kit Click here and download it now

August 3rd, 2015 11:31pm

E.g. (not tested now):

 ;with cte as (select *, max(case when right(workflow_nm, 8) IN (N'_FAILURE', N'_SUCCESS') then workflow_create_tm else '17530101' end) 
 over (partition by ban) as stopDt from #deleteit),

 cte1 as (select *, 
count(case when workflow_create_tm < stopDt  and right(workflow_nm, 8) NOT IN (N'_FAILURE', N'_SUCCESS') then 1

end) over (partition by ban) as cntRows from cte)

 select * from cte1 where WorkFlow_Create_TM = stopDt

August 3rd, 2015 11:33pm

Hi Naomi,

Your query is almost done but the output is not as expected. It returns the below output.

104917284	SS_UVERSE_SUB_SUCCESS	2015-05-15 23:12:47.003	2015-05-15 23:12:47.003	6
107500674	SS_UVERSE_TV_DVR_PAUSE_LIVE_TV_V2	2015-05-16 23:26:30.000	2015-05-16 23:26:30.000	0
107500674	SS_UVERSE_SUB_FAILURE	2015-05-16 23:26:30.000	2015-05-16 23:26:30.000	0
137108351	SS_UVERSE_SUB_SUCCESS	2015-05-06 06:23:51.000	2015-05-06 06:23:51.000	2

Free Windows Admin Tool Kit Click here and download it now

August 4th, 2015 12:27am

Try:

 ;with cte as (select *, max(case when right(workflow_nm, 8) IN (N'_FAILURE', N'_SUCCESS') then workflow_create_tm else '17530101' end) 
 over (partition by ban) as stopDt from deleteit),

 cte1 as (select *, 
count(case when workflow_create_tm < stopDt  and right(workflow_nm, 8) NOT IN (N'_FAILURE', N'_SUCCESS') then 1

end) over (partition by ban) as cntRows from cte)

 select * from cte1 where WorkFlow_Create_TM = stopDt  
 and (WorkFlow_NM LIKE N'%[_]FAILURE' OR WorkFlow_NM LIKE N'%[_]SUCCESS')
 Order by [WorkFlow_Create_TM]
drop table [deleteit]

August 4th, 2015 12:45am

This topic is archived. No further replies will be accepted.