SQLTeam.com | Weblogs | Forums

Max min subquery

sql2012

#1

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')

#2

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?


#3

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
;

#4

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"


#5

You didn't answer this:


#6
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
;

#7

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?


#8

That could potentially make your queries go haywire.

Give it a try :thumbsup:


#9

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

#10

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

#11

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
;


#12

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
;

#13

You legend bitsmed!
Thank you