Multiple Cross Apply

_{Tuesday, November 14, 2006 8:09 PM}

Good Afternoon,

I'm attempting to leverage SQL's new 'APPLY" operator and I guess I don't fully understand it proper usage.

This is a relatively simple request, first i want to count the models produced within a valid period of time. The first 'Cross Apply' gets the valid starting and ending dates and looks ups the number of models produced for the period of time. This section of code works perfectly fine.

The problem appears to be with the second "Cross Apply".  What I'm attempting to accomplish is to count all models produced, regardless of time frame.

When executed the query appears to go into an loop and I end up canceling out the request.

Any ideas where I went wrong?? Any help is greatly appreciated!

select b1.model                            as Model 
      ,b1.MinDate                            as Mfg_Str_Date
      ,b1.MaxDate                           as Mfg_End_Date
      ,Count(b2.Model+B2.Serial) as Mfg_Date_Valid
      ,Count(b3.Model+B3.Serial) as All_Units

from        (select b.model, min(b.build_date) as MinDate ,max(b.build_date) as MaxDate
             from etbl_models_Serial as b
             group by b.model) as b1

--These are Units produced within Valid Window
cross apply (select b2.model,b2.Serial
             from etbl_Production as b2
             where b2.Model = b1.Model
               and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2

--These are all units produced
cross apply (select b3.model,b3.Serial
             from etbl_Production as b3
             where b3.Model = b2.Model) as b3
      
Group by b1.Model, b1.MinDate, b1.MaxDate
Order by b1.Model

_{Wednesday, November 15, 2006 3:55 PM ✅Answered}

Steve,

Your answer provided the exact solution I was hoping to obtain. Mucho many thanks for your quick response!!! The only problem was the readabilty of the response. So for those of you who have trouble decifiering Steve's response. I'll post the working code below:

With Mminmax(Model, MinDate, MaxDate) as

(Select M.Model as Model,min(M.build_date), max(M.build_date)
 From etbl_Models_Serial as M
 Group by M.Model)

Select  Model
           ,MinDate             as Mfg_Str_Date
           ,MaxDate            as Mfg_End_Date
           ,count(case when P.Mfg_Date >= Mminmax.MinDate and P.Mfg_Date <= Mminmax.MaxDate then 1 end)
                                       as Mfg_Date_Valid
          ,count(P.Serial) as All_Units
From Mminmax
Cross Apply (Select  P.Serial,P.Mfg_Date
             From etbl_Production as P
             Where P.Model = Mminmax.Model ) as P
group by Mminmax.Model, Mminmax.MinDate, Mminmax.MaxDate
Order by Mninmax.Model