Max min subquery

[Danii_PINK]

Hi

I have the following Data Set (see below)

I am trying to produce the results in the image.

CREATE TABLE #temp
(
ServiceNumber varchar(20) NOT NULL
,Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
);
INSERT INTO #temp
VALUES ('318403853','61030203647','20170512','20170518','De-Activated', 'COMP')
INSERT INTO #temp
VALUES('318616723','61030203647','20170613','20170613','Re-Activattion', 'N-CO')
INSERT INTO #temp
VALUES('318637013','61030203647','20170615','20170615','Re-Activattion', 'REJE')
INSERT INTO #temp
VALUES('318639191','61030203647','20170615','20170616','Re-Activattion', 'COMP')
INSERT INTO #temp
VALUES('318637791','61030203666','20170615','20170616','Re-Activattion', 'Rejected')
INSERT INTO #temp
VALUES('318637792','61030203666','20170614','20170615','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('000318634115','64074558782','20170615','20170615','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318636669','64074558782','20170615','20170615','Re-Activattion','COMP')
INSERT INTO #temp
VALUES('000318636873','64074558782','20170615','20170614','Re-Activattion','Rejected')
INSERT INTO #temp
VALUES('000318623572','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318627678','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318614132','64074558782','20170613','20170613','Re-Activattion','N-CO')

[bitsmed]

This post is a continuation of http://forums.sqlteam.com/t/sql-sub-query-help/10776/5.

Now, as @Ifor mentioned, how can completed_date be greater than created_date (see identifier 000318636873)?

Are the combinations of sn_type and sn_status that you show in your sample data, all combinations we will encounter/should handle?

[bitsmed]

Please verify that this produces the right result (I know it's not complete, but your feedback gives me an indication of, am I on the right track/have I understood your requirements):

select identifier
      ,dateadd(day
              ,case when sum(case when sn_status='COMP' then 1 else 0 end)>0 then 0 else null end
              ,min(case
                      when (sn_type='De-Activated'
                       and  sn_status='COMP'
                           )
                        or (sn_type='Re-Activattion'
                       and  sn_status='N-CO'
                           )
                      then created_date
                      else null
                   end
                  )
              )
       as createddate_deactivated
      ,dateadd(day
              ,case when sum(case when sn_status='COMP' then 1 else 0 end)>0 then 0 else null end
              ,min(case
                      when (sn_type='De-Activated'
                       and  sn_status='COMP'
                           )
                        or (sn_type='Re-Activattion'
                       and  sn_status='COMP'
                           )
                      then completed_date
                      else null
                   end
                  )
              )
       as completeddate_deactivated
      ,case
	      when sum(case when sn_status='COMP' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when (sn_type='De-Activated'
                                    and  sn_status='COMP'
                                        )
                                     or (sn_type='Re-Activattion'
                                    and  sn_status='N-CO'
                                        )
                                   then created_date
                                   else null
                                end
                               )
                           ,min(case
                                   when (sn_type='De-Activated'
                                    and  sn_status='COMP'
                                        )
                                     or (sn_type='Re-Activattion'
                                    and  sn_status='COMP'
                                        )
                                   then completed_date
                                   else null
                                end
                               )
                           )
                  )
          else 'NO DEACT'
       end
       as comp_deactivated
  from SAP_Reporting_Users.dbo.A139075_DAN
 group by identifier
;

[Danii_PINK]

Hi bitsmed

Thanks for responding and helping out.

I had a look, definetley on the right track

However for 64074558782 it should be blank because there is no "De-Activated" SN_Type for it.
Only Comp, Rejected and N-CO. So that should be blank.

So the columns you created should only be summarizing then the Sn_Type is Deactivated and SN_Status is Completed.
Makes sense?

Definitely on the rick track though, just seem to be missing a filter.

I have attached an image to help explain why it should be 'NO DEACT"

[bitsmed]

You didn't answer this:

@bitsmed

Now, as @Ifor mentioned, how can completed_date be greater than created_date (see identifier 000318636873)?

[bitsmed]

select identifier
      ,dateadd(day
              ,case when sum(case when sn_status='COMP' then 1 else 0 end)>0 then 0 else null end
              ,min(case
                      when (sn_type='De-Activated'
                       and  sn_status='COMP'
                           )
                        or (sn_type='Re-Activattion'
                       and  sn_status='N-CO'
                           )
                      then created_date
                      else null
                   end
                  )
              )
       as createddate_deactivated
      ,dateadd(day
              ,case when sum(case when sn_status='COMP' then 1 else 0 end)>0 then 0 else null end
              ,min(case
                      when (sn_type='De-Activated'
                       and  sn_status='COMP'
                           )
                        or (sn_type='Re-Activattion'
                       and  sn_status='COMP'
                           )
                      then completed_date
                      else null
                   end
                  )
              )
       as completeddate_deactivated
      ,case
	      when sum(case when sn_status='COMP' and sn_type='De-Activated' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when (sn_type='De-Activated'
                                    and  sn_status='COMP'
                                        )
                                     or (sn_type='Re-Activattion'
                                    and  sn_status='N-CO'
                                        )
                                   then created_date
                                   else null
                                end
                               )
                           ,min(case
                                   when (sn_type='De-Activated'
                                    and  sn_status='COMP'
                                        )
                                     or (sn_type='Re-Activattion'
                                    and  sn_status='COMP'
                                        )
                                   then completed_date
                                   else null
                                end
                               )
                           )
                  )
          else 'NO DEACT'
       end
       as comp_deactivated
  from SAP_Reporting_Users.dbo.A139075_DAN
 group by identifier
;

[Danii_PINK]

@bitsmed

Now, as @Ifor mentioned, how can completed_date be greater than created_date (see identifier 000318636873)?

Sorry I did see that

Can answer it, just means there is something wrong with the data set which I need to look into.

Wow your script looks to be it.

I am guessing I can use the same logic for the follow up columns, would they deliver the result in the picture?

[bitsmed]

@Danii_PINK

there is something wrong with the data

That could potentially make your queries go haywire.

@Danii_PINK

I am guessing I can use the same logic for the follow up columns, would they deliver the result in the picture?

Give it a try

[Danii_PINK]

Hi bitsmed

Thanks for your help the code was extremely helpful
I was wondering if you could help me make an alteration.
I am trying to add in the case statement below
if sn_status <> 'COMP' and sn_type='De-Activated' then 'NOT COMP'
Could you tell me how I could incorporate that?
Thanks
Dani

   ,case
	      when sum(case when sn_status='COMP' and sn_type='De-Activated' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when (sn_type='De-Activated'
                                    and  sn_status='COMP'
                                        )
                                     or (sn_type='Re-Activattion'
                                    and  sn_status='N-CO'
                                        )
                                   then created_date
                                   else null
                                end
                               )
                           ,min(case
                                   when (sn_type='De-Activated'
                                    and  sn_status='COMP'
                                        )
                                     or (sn_type='Re-Activattion'
                                    and  sn_status='COMP'
                                        )
                                   then completed_date
                                   else null
                                end
                               )
                           )
                  )
          else 'NO DEACT'
       end
       as comp_deactivated

[bitsmed]

Can you illustrate this by providing:

• table description in the form of create statement (no picture)
• sample data in for form of insert statement (no picture)
• expected output from the sample data you provide

[Danii_PINK]

@bitsmed

Can you illustrate this by providing:

table description in the form of create statement (no picture)
sample data in for form of insert statement (no picture)
expected output from the sample data you provide

Hi bitsmed

Please have a look below of what I am trying to achieve.

CREATE TABLE #temp
(
ServiceNumber varchar(20) NOT NULL
,Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
);
INSERT INTO #temp
VALUES ('318403853','61030203647','20170512','20170518','De-Activated', 'COMP')
INSERT INTO #temp
VALUES('318616723','61030203647','20170613','20170613','Re-Activattion', 'N-CO')
INSERT INTO #temp
VALUES('318637013','61030203647','20170615','20170615','Re-Activattion', 'REJE')
INSERT INTO #temp
VALUES('318639191','61030203647','20170615','20170616','Re-Activattion', 'COMP')
INSERT INTO #temp
VALUES('318637791','61030203666','20170615','20170616','Re-Activattion', 'Rejected')
INSERT INTO #temp
VALUES('318637792','61030203666','20170614','20170615','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES ('000318634115','64074558782','20170615','20170615','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318636669','64074558782','20170615','20170615','Re-Activattion','COMP')
INSERT INTO #temp
VALUES('000318636873','64074558782','20170615','20170614','Re-Activattion','Rejected')
INSERT INTO #temp
VALUES('000318623572','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318627678','64074558782','20170614','20170614','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('000318614132','64074558782','20170613','20170613','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('00031861999','64074558799','20170613','20170613','Re-Activattion','N-CO')
INSERT INTO #temp
VALUES('00031861991','64074558791','20170613','20170613','De-Activated','N-CO')


;


select identifier
           ,case
	      when sum(case when SN_STATUS='COMP' and SN_TYPE = 'Re-Activattion' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when (SN_TYPE = 'Re-Activattion'
                                 --   and  SN_STATUS='COMP'
                                        )
                                   then CreatedDate
                                   else null
                                end
                               )
                           ,max(case
                                   when (SN_TYPE = 'Re-Activattion'
                                    and  SN_STATUS='COMP'
                                        )
                                   then CompletedDate
                                   else null
                                end
                               )
                           )
                  )
          else ''
       end
       as RE_ACT_COMPLETION_TIME
  from #temp
 -- WHERE Identifier = '64074558799'
 group by identifier
;

[bitsmed]

Try this:

select identifier
      ,case
          when sum(case when SN_STATUS='COMP' and SN_TYPE = 'Re-Activattion' then 1 else 0 end)>0
          then str(datediff(day
                           ,min(case
                                   when SN_TYPE = 'Re-Activattion'
                                   then Created_Date
                                   else null
                                end
                               )
                           ,max(case
                                   when (SN_TYPE = 'Re-Activattion'
                                    and  SN_STATUS='COMP'
                                        )
                                   then Completed_Date
                                   else null
                                end
                               )
                           )
                  )
          when sum(case when SN_TYPE='Re-Activattion' then 1 else 0 end)>0
          then 'NOT COMP'
          else 'NO RE-ACT'
       end
       as RE_ACT_COMPLETION_TIME
  from SAP_Reporting_Users.dbo.#A139075_DAN
 group by identifier
;

[Danii_PINK]

You legend bitsmed!
Thank you