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
b1 cross apply b2 contains one row for every time this model
is produced within the production window. Then you cross
apply b3, so that for each of these very many rows, you
match a row for every time the model is produced at all.
For a given model, I think you'll be counting this many
rows:
(# produced according to etbl_Production
between extreme dates in etbl_Models_Serial)
*
(# produced according to etbl_Production)
If you produced 15,000 xyz-widgets and they were all
within the valid dates, you'll be counting 225,000,000
rows just for that item.
Maybe I'm not getting it, but here's my best guess
at a query that will answer your question, or come close:
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
If this isn't helpful, please post the CREATE TABLE statements
and a modest amount of data as INSERT statements, and give the
desired results. One thing I can't tell is whether you have or
want to count cases where Serial or Mfg_Date is NULL.
Steve Kass
Drew University
http://www.stevekass.com
sfmd@.discussions.microsoft.com wrote:
> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
>
>
> 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
>
>|||
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
SK
sfmd@.discussions.microsoft.com wrote:
> 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
>
>
>
>
>
>
>
>
No comments:
Post a Comment