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
>

Query on date/time data type

I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
I've been trying CONVERT and CAST but to no avail. There's no examples on
how to utilize the TIME part of this smalldatetime data type.
Thx,
Don
SQL2000
WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
AND MyDateTimeColumn <= '2006-10-24 10:55:00'
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
>I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
> Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
> I've been trying CONVERT and CAST but to no avail. There's no examples on
> how to utilize the TIME part of this smalldatetime data type.
> Thx,
> Don
> SQL2000
>
|||DOH! )
I was trying to make it too compliated!
Thanks!
Don
"Arnie Rowland" wrote:
[vbcol=seagreen]
> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
|||I recommend that you use either of below two formats for the datetime strings:
'20061024 01:00:00'
'2006-10-24T01:00:00'
If you use any other format, the app will be language dependent
(http://www.karaszi.com/SQLServer/inf...asp#DtFormats). as shown below:
SET LANGUAGE german
SELECT CAST('2006-10-24 01:00:00' AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:98D78D54-EFF9-4097-BBD5-80D338D2663F@.microsoft.com...[vbcol=seagreen]
> DOH! )
> I was trying to make it too compliated!
> Thanks!
> Don
>
> "Arnie Rowland" wrote: