How can I get similar results from a query like this >
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
format'
The Field is Misc it's contents are (100, 101, 102) and so on. I need to
display a row for each occurrence of 3 digits in that field and replacing
('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
WHERE royaltyper < 50) is not the same thing..
I'm thinking I need some type of variable length array, but I am out of
practice and not sure, can some one please help me.."WANNABE" <breichenbach AT istate DOT com> wrote in message
news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
> How can I get similar results from a query like this >
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
> format'
> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
> display a row for each occurrence of 3 digits in that field and replacing
> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
> WHERE royaltyper < 50) is not the same thing..
> I'm thinking I need some type of variable length array, but I am out of
> practice and not sure, can some one please help me..
>
I'm not completely following here, but you can use either an IN clause or a
WHERE EXISTS clause.
Perhaps something like:
SELECT au_id
FROM titleauthor
WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
somecondition)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for you response Rick, but I think what you have described below is
what I have been trying to get to work. When I run this >>
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
I get a long list of records. I would like to get the same long list of
records by running something like the following query, AFTER I HAVE MODIFIED
THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
MD) into that field for the record where stor_id is equal to 7067.
When I run this next query AFTER I have made the modifications described
above, I get only column headers>>
SELECT au_lname, state
FROM authors
WHERE state IN
(SELECT stid
FROM stores
WHERE stor_id = '7067')
This is all done in testing using the PUBS database, and here are the
queries used to modify that db
alter table pubs.dbo.stores add stid char(50)
UPDATE stores
SET [stid] = '(CA, IN, MD)'
where stor_id = '7067'
======================================="Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23OwzMOKwGHA.1288@.TK2MSFTNGP02.phx.gbl...
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
>> How can I get similar results from a query like this >
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN ('CA', 'IN', 'MD')
>> Except to replace ('CA', 'IN', 'MD') With a field that will have a
>> similar format'
>> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
>> display a row for each occurrence of 3 digits in that field and replacing
>> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
>> WHERE royaltyper < 50) is not the same thing..
>> I'm thinking I need some type of variable length array, but I am out of
>> practice and not sure, can some one please help me..
> I'm not completely following here, but you can use either an IN clause or
> a WHERE EXISTS clause.
> Perhaps something like:
> SELECT au_id
> FROM titleauthor
> WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
> somecondition)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||WANNABE wrote:
> Thanks for you response Rick, but I think what you have described below is
> what I have been trying to get to work. When I run this >>
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> I get a long list of records. I would like to get the same long list of
> records by running something like the following query, AFTER I HAVE MODIFIED
> THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
> MD) into that field for the record where stor_id is equal to 7067.
> When I run this next query AFTER I have made the modifications described
> above, I get only column headers>>
> SELECT au_lname, state
> FROM authors
> WHERE state IN
> (SELECT stid
> FROM stores
> WHERE stor_id = '7067')
> This is all done in testing using the PUBS database, and here are the
> queries used to modify that db
> alter table pubs.dbo.stores add stid char(50)
> UPDATE stores
> SET [stid] = '(CA, IN, MD)'
> where stor_id = '7067'
You're looking for a way to parse a comma-delimited string and use its
elements in a query. Start by reading this:
http://www.realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, but that is the opposite of what I am trying to do, which is
to parse a delimited string from a table. Can someone tell me how'
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31A57.9030809@.realsqlguy.com...
> WANNABE wrote:
>> Thanks for you response Rick, but I think what you have described below
>> is what I have been trying to get to work. When I run this >>
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN ('CA', 'IN', 'MD')
>> I get a long list of records. I would like to get the same long list of
>> records by running something like the following query, AFTER I HAVE
>> MODIFIED THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE
>> (CA, IN, MD) into that field for the record where stor_id is equal to
>> 7067.
>> When I run this next query AFTER I have made the modifications described
>> above, I get only column headers>>
>> SELECT au_lname, state
>> FROM authors
>> WHERE state IN
>> (SELECT stid
>> FROM stores
>> WHERE stor_id = '7067')
>> This is all done in testing using the PUBS database, and here are the
>> queries used to modify that db
>> alter table pubs.dbo.stores add stid char(50)
>> UPDATE stores
>> SET [stid] = '(CA, IN, MD)'
>> where stor_id = '7067'
> You're looking for a way to parse a comma-delimited string and use its
> elements in a query. Start by reading this:
> http://www.realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Showing posts with label state. Show all posts
Showing posts with label state. Show all posts
Friday, March 30, 2012
query question
This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,City, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,City, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
query question
This is my current query that I have in an App I wrote:
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,Ci
ty, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
MySQL = "select DateEntered,Shipper,PickupDate,PUTime,Ci
ty, State, Zip,
Consignee, Destination, DState, DZip, PickupNumber, ShippersNumber,
PONumber, Consignee_Ref_Number, Weight, Number_Packages, Carrier,
Carrier_Number, Trailer_Number, ApptDate, ApptTime, IDFProNumber,
DeliveredDate, DeliveredTime, FreightCharges, TransitTime, Comments,
LastUpdate, lastcomment from IntermodalTracingMasterFile where " &
tmpMyShippers & " Order by [" & strSort & "] desc "
This works fine.
I need to modify it a little I need to have one query that will return when
the DeliveredDate is empty
and anohter query to return the ones that have somethign in the
DeliveredDate field.
ThanksOn Wed, 17 Nov 2004 15:58:44 -0800, johnfli wrote:
>This is my current query that I have in an App I wrote:
(snip)
>I need to modify it a little I need to have one query that will return when
>the DeliveredDate is empty
>and anohter query to return the ones that have somethign in the
>DeliveredDate field.
Hi johnfli,
Add "WHERE DeliveredDate IS NULL" or "WHERE DeliveredDate IS NOT NULL"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Query Question
How can I get similar results from a query like this >
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
format'
The Field is Misc it's contents are (100, 101, 102) and so on. I need to
display a row for each occurrence of 3 digits in that field and replacing
('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
WHERE royaltyper < 50) is not the same thing..
I'm thinking I need some type of variable length array, but I am out of
practice and not sure, can some one please help me.."WANNABE" <breichenbach AT istate DOT com> wrote in message
news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
> How can I get similar results from a query like this >
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
> format'
> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
> display a row for each occurrence of 3 digits in that field and replacing
> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
> WHERE royaltyper < 50) is not the same thing..
> I'm thinking I need some type of variable length array, but I am out of
> practice and not sure, can some one please help me..
>
I'm not completely following here, but you can use either an IN clause or a
WHERE EXISTS clause.
Perhaps something like:
SELECT au_id
FROM titleauthor
WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
somecondition)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for you response Rick, but I think what you have described below is
what I have been trying to get to work. When I run this >>
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
I get a long list of records. I would like to get the same long list of
records by running something like the following query, AFTER I HAVE MODIFIED
THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
MD) into that field for the record where stor_id is equal to 7067.
When I run this next query AFTER I have made the modifications described
above, I get only column headers>>
SELECT au_lname, state
FROM authors
WHERE state IN
(SELECT stid
FROM stores
WHERE stor_id = '7067')
This is all done in testing using the PUBS database, and here are the
queries used to modify that db
alter table pubs.dbo.stores add stid char(50)
UPDATE stores
SET [stid] = '(CA, IN, MD)'
where stor_id = '7067'
=======================================
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23OwzMOKwGHA.1288@.TK2MSFTNGP02.phx.gbl...
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
> I'm not completely following here, but you can use either an IN clause or
> a WHERE EXISTS clause.
> Perhaps something like:
> SELECT au_id
> FROM titleauthor
> WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
> somecondition)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||WANNABE wrote:
> Thanks for you response Rick, but I think what you have described below is
> what I have been trying to get to work. When I run this >>
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> I get a long list of records. I would like to get the same long list of
> records by running something like the following query, AFTER I HAVE MODIFI
ED
> THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
> MD) into that field for the record where stor_id is equal to 7067.
> When I run this next query AFTER I have made the modifications described
> above, I get only column headers>>
> SELECT au_lname, state
> FROM authors
> WHERE state IN
> (SELECT stid
> FROM stores
> WHERE stor_id = '7067')
> This is all done in testing using the PUBS database, and here are the
> queries used to modify that db
> alter table pubs.dbo.stores add stid char(50)
> UPDATE stores
> SET [stid] = '(CA, IN, MD)'
> where stor_id = '7067'
You're looking for a way to parse a comma-delimited string and use its
elements in a query. Start by reading this:
http://www.realsqlguy.com/serendipi...r />
able.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, but that is the opposite of what I am trying to do, which is
to parse a delimited string from a table. Can someone tell me how'
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31A57.9030809@.realsqlguy.com...
> WANNABE wrote:
> You're looking for a way to parse a comma-delimited string and use its
> elements in a query. Start by reading this:
> http://www.realsqlguy.com/serendipi.../>
-Table.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
format'
The Field is Misc it's contents are (100, 101, 102) and so on. I need to
display a row for each occurrence of 3 digits in that field and replacing
('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
WHERE royaltyper < 50) is not the same thing..
I'm thinking I need some type of variable length array, but I am out of
practice and not sure, can some one please help me.."WANNABE" <breichenbach AT istate DOT com> wrote in message
news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
> How can I get similar results from a query like this >
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> Except to replace ('CA', 'IN', 'MD') With a field that will have a similar
> format'
> The Field is Misc it's contents are (100, 101, 102) and so on. I need to
> display a row for each occurrence of 3 digits in that field and replacing
> ('CA', 'IN', 'MD') with something like (SELECT au_id FROM titleauthor
> WHERE royaltyper < 50) is not the same thing..
> I'm thinking I need some type of variable length array, but I am out of
> practice and not sure, can some one please help me..
>
I'm not completely following here, but you can use either an IN clause or a
WHERE EXISTS clause.
Perhaps something like:
SELECT au_id
FROM titleauthor
WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
somecondition)
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for you response Rick, but I think what you have described below is
what I have been trying to get to work. When I run this >>
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
I get a long list of records. I would like to get the same long list of
records by running something like the following query, AFTER I HAVE MODIFIED
THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
MD) into that field for the record where stor_id is equal to 7067.
When I run this next query AFTER I have made the modifications described
above, I get only column headers>>
SELECT au_lname, state
FROM authors
WHERE state IN
(SELECT stid
FROM stores
WHERE stor_id = '7067')
This is all done in testing using the PUBS database, and here are the
queries used to modify that db
alter table pubs.dbo.stores add stid char(50)
UPDATE stores
SET [stid] = '(CA, IN, MD)'
where stor_id = '7067'
=======================================
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23OwzMOKwGHA.1288@.TK2MSFTNGP02.phx.gbl...
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:e34iWyJwGHA.1216@.TK2MSFTNGP03.phx.gbl...
> I'm not completely following here, but you can use either an IN clause or
> a WHERE EXISTS clause.
> Perhaps something like:
> SELECT au_id
> FROM titleauthor
> WHERE SomeValue IN (SELECT myLookupValues FROM sometable WHERE
> somecondition)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||WANNABE wrote:
> Thanks for you response Rick, but I think what you have described below is
> what I have been trying to get to work. When I run this >>
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> I get a long list of records. I would like to get the same long list of
> records by running something like the following query, AFTER I HAVE MODIFI
ED
> THE stores TABLE TO INCLUDE THE stid FIELD and ENTERED THE VALUE (CA, IN,
> MD) into that field for the record where stor_id is equal to 7067.
> When I run this next query AFTER I have made the modifications described
> above, I get only column headers>>
> SELECT au_lname, state
> FROM authors
> WHERE state IN
> (SELECT stid
> FROM stores
> WHERE stor_id = '7067')
> This is all done in testing using the PUBS database, and here are the
> queries used to modify that db
> alter table pubs.dbo.stores add stid char(50)
> UPDATE stores
> SET [stid] = '(CA, IN, MD)'
> where stor_id = '7067'
You're looking for a way to parse a comma-delimited string and use its
elements in a query. Start by reading this:
http://www.realsqlguy.com/serendipi...r />
able.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, but that is the opposite of what I am trying to do, which is
to parse a delimited string from a table. Can someone tell me how'
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44E31A57.9030809@.realsqlguy.com...
> WANNABE wrote:
> You're looking for a way to parse a comma-delimited string and use its
> elements in a query. Start by reading this:
> http://www.realsqlguy.com/serendipi.../>
-Table.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Wednesday, March 28, 2012
Query Problem
I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESC
I found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESC
I found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>
Monday, March 26, 2012
Query Problem
I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:
> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
> >I have the following query that is giving me an error that I can't quite
> >figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
> >Internal SQL Server error. I think it is related to the sub-query or the
> >group by. The sub-query executes successfully if I run it by itself.
> >
> > Any Ideas as to what I can try?
> >
> > Thanks
> >
> > Jeff
> >
> >
> >
> > Declare @.StartDate VarChar(12)
> > SET @.StartDate = '04/07/03'
> >
> > SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> > @.StartDate as StartDate
> > FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> > CASE
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> > DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> > /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> > And DateAdd(Day, 13, @.StartDate) Then 4 */
> > End AS Period
> > FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> > WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> > AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> > AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> > AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> > AND A.EMPLOYEEID = B.EMPLOYEEID
> > AND B.PayRuleName <> 'All Exempt'
> > AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate)
> > GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> > GROUP BY Plant, Period
> > ORDER BY Plant, Period DESC
> >
>
>
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:
> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
> >I have the following query that is giving me an error that I can't quite
> >figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
> >Internal SQL Server error. I think it is related to the sub-query or the
> >group by. The sub-query executes successfully if I run it by itself.
> >
> > Any Ideas as to what I can try?
> >
> > Thanks
> >
> > Jeff
> >
> >
> >
> > Declare @.StartDate VarChar(12)
> > SET @.StartDate = '04/07/03'
> >
> > SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> > @.StartDate as StartDate
> > FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> > CASE
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> > DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> > WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> > /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> > And DateAdd(Day, 13, @.StartDate) Then 4 */
> > End AS Period
> > FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> > WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> > AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> > AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> > AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> > AND A.EMPLOYEEID = B.EMPLOYEEID
> > AND B.PayRuleName <> 'All Exempt'
> > AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> > DateAdd(Day, 55, @.StartDate)
> > GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> > GROUP BY Plant, Period
> > ORDER BY Plant, Period DESC
> >
>
>
Query Problem
I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:
> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
>
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:
> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
>
Subscribe to:
Posts (Atom)