Showing posts with label operator. Show all posts
Showing posts with label operator. Show all posts

Friday, March 9, 2012

Multiple Cross Apply

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

I'm not sure the second CROSS APPLY is right. It looks like

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

|||Thanks for fixing this up. Glad it worked!

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

>

>

>

>

>

>

>

>

Wednesday, March 7, 2012

multiple columns search with 'AND'

Hi
Can someone tell me why I can search multiple columns in 1 table using a
single CONTAINS with 'OR' operator but it dosn't work for 'AND' queries? I
want to be able to do 'AND' searches accross 50 odd columns in a table.
Here's an example to show you what I mean (A Full-Text index is working on
the table and includes the FirstName and LastName columns):
CREATE TABLE Users
(
UID int IDENTITY(1000,1) PRIMARY KEY,
FirstName varchar(50) DEFAULT '' NOT NULL,
LastName varchar(50) DEFAULT '' NOT NULL
)
INSERT INTO Users(FirstName, LastName) VALUES('John', 'Smith')
INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Smith')
INSERT INTO Users(FirstName, LastName) VALUES('John', 'Brown')
INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Brown')
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John OR Smith')
/* The above gives me the resulst I expect, i.e. 2 records are returned*/
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John AND Smith')
/* This one dosn't return any results even though 'John' and 'Smith' clearly
exists in 1 record but different columns */
Please tell me what I'm doing wrong or what a pracical solution would be to
search 50ish columns in a table using 'AND' operator and 1 CONTAINS
pedicate. I can't find a suitable explaination in BOL
Many thanks
Andrew
this works
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John') and contains (*, 'Smith')
this doesn't
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John AND Smith')
i believe this doesn't work because it is searching each individual column
(firstname and lastname) for both john and smith. there are no rows that have
both john and smith in either the firstname column only or the lastname column
only. same thing as this
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, '"john smith"')
the or works because it finds rows with john or smith in the firstname column
and then finds rows with john or smith in the lastname column.
Andrew Jocelyn wrote:

> Hi
> Can someone tell me why I can search multiple columns in 1 table using a
> single CONTAINS with 'OR' operator but it dosn't work for 'AND' queries? I
> want to be able to do 'AND' searches accross 50 odd columns in a table.
> Here's an example to show you what I mean (A Full-Text index is working on
> the table and includes the FirstName and LastName columns):
> CREATE TABLE Users
> (
> UID int IDENTITY(1000,1) PRIMARY KEY,
> FirstName varchar(50) DEFAULT '' NOT NULL,
> LastName varchar(50) DEFAULT '' NOT NULL
> )
> INSERT INTO Users(FirstName, LastName) VALUES('John', 'Smith')
> INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Smith')
> INSERT INTO Users(FirstName, LastName) VALUES('John', 'Brown')
> INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Brown')
> SELECT UID, FirstName, LastName FROM Users
> WHERE CONTAINS (*, 'John OR Smith')
> /* The above gives me the resulst I expect, i.e. 2 records are returned*/
> SELECT UID, FirstName, LastName FROM Users
> WHERE CONTAINS (*, 'John AND Smith')
> /* This one dosn't return any results even though 'John' and 'Smith' clearly
> exists in 1 record but different columns */
> Please tell me what I'm doing wrong or what a pracical solution would be to
> search 50ish columns in a table using 'AND' operator and 1 CONTAINS
> pedicate. I can't find a suitable explaination in BOL
> Many thanks
> Andrew

Monday, February 20, 2012

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
GerhardHi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
more[vbcol=seagreen]
>|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
> the
> more
>

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
Gerhard
Hi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>
|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
more
>
|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
first
> the
> more
>

Multipe receipients for Operators in SQL 2005?

Using database mail. It works using a single email address for an operator.
Adding a second email address after a semicolon still sends to the first
email address but not the second. Separating them with a comma breaks the
whole thing, no email is sent.
Does this mean I have to create a dl in Exchange, if I need to notify more
than one person for a given event?
GerhardHi Gerhard,
That's usually the easiest way. It also allows who is on the list to be
managed externally easily.
HTH,
Greg
"Gerhard" <gerhardpremovethis@.inch.com> wrote in message
news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Using database mail. It works using a single email address for an
> operator.
> Adding a second email address after a semicolon still sends to the first
> email address but not the second. Separating them with a comma breaks the
> whole thing, no email is sent.
> Does this mean I have to create a dl in Exchange, if I need to notify more
> than one person for a given event?
> Gerhard
>|||Thanks Greg,
So are you saying it cannot be managed from the Operator page?
I would have preferred to handle it in SQL Server rather than Exchange,
Gerhard
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi Gerhard,
> That's usually the easiest way. It also allows who is on the list to be
> managed externally easily.
> HTH,
> Greg
> "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> > Using database mail. It works using a single email address for an
> > operator.
> > Adding a second email address after a semicolon still sends to the first
> > email address but not the second. Separating them with a comma breaks
the
> > whole thing, no email is sent.
> > Does this mean I have to create a dl in Exchange, if I need to notify
more
> > than one person for a given event?
> > Gerhard
> >
> >
>|||Greg,
Forget that question. It works with semicolons. I re-created everything
today and did some more testing. SQL 2005 has been difficult for us.
One thing I've noticed is that things often don't work on the first try, and
then when you blow them away and re-create them they start working.
I have a SRX with Microsoft right now, not on this, on something much more
serious where even they can't get it to work. It is just not working at
all. I guess I keep forgetting that it is an initial release and that it
behaves as such,
Gerhard
"Gerhard Paulman" <gerhardpremovethis@.inch.com> wrote in message
news:uKWQvLaJGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Thanks Greg,
> So are you saying it cannot be managed from the Operator page?
> I would have preferred to handle it in SQL Server rather than Exchange,
> Gerhard
> "Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
> news:e6n1IJWJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> > Hi Gerhard,
> >
> > That's usually the easiest way. It also allows who is on the list to be
> > managed externally easily.
> >
> > HTH,
> >
> > Greg
> >
> > "Gerhard" <gerhardpremovethis@.inch.com> wrote in message
> > news:O$Zd07TJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> > > Using database mail. It works using a single email address for an
> > > operator.
> > > Adding a second email address after a semicolon still sends to the
first
> > > email address but not the second. Separating them with a comma breaks
> the
> > > whole thing, no email is sent.
> > > Does this mean I have to create a dl in Exchange, if I need to notify
> more
> > > than one person for a given event?
> > > Gerhard
> > >
> > >
> >
> >
>