asked on

Convert T-SQL to use in UDF (can not use temp tables)

Hello everyone,

Here is what I have
--PID is INT
--SYSTEM_USER is VARCHAR(20)
--StartDate is DATETIME
--EventCount is INT
--EventOffset is INT

            DELETE FROM ztblRotationGroupedData WHERE UserName = SYSTEM_USER
            DELETE FROM ztblEventCountData WHERE UserName = SYSTEM_USER

            SELECT
                  tblEmployee.PID,
                  SYSTEM_USER,
                  e.StartDate,
                  ABS(SUM(ee.EventActive)) AS 'EventCount'
            INTO ztblRotationGroupedData
            FROM
                  tblEvents e
                  INNER JOIN tblEventEmployee ee ON e.EventID = ee.EventID
                  INNER JOIN tblEmployeeListAssignment ela ON ee.PID = ela.PID
                  INNER JOIN tblEmployee ON ee.PID = tblEmployee.PID

            WHERE
                  ela.EmployeeListDescription = @strEmployeeListDesc
                  AND ela.Inactive = 0
                  AND DATEPART(dw, e.StartDate) = 7 --Saturday

            GROUP BY
                  tblEmployee.PID,
                  e.StartDate
            HAVING
                  SUM(ee.EventActive) <> 0


            SELECT a.PID, SYSTEM_USER, Count(a.PID) As EventCount
            INTO ztblEventCountData
            FROM ztblRotationGroupedData a
            GROUP BY PID


            SELECT erv.PID, erv.EventOffset, IsNull(a.EventCount,0)
            FROM tblEventRotationValues erv
                  LEFT JOIN ztblEventCountData a ON erv.PID = a.PID
            WHERE erv.EventRotationID = 7 --Saturday
      END

Unfortunately I can't use this T-SQL in a UDF. So I'm wondering if any of you super savy people can possibly make it all happen from a single SELECT statement or tell me how to work around these issues.

Here is what the data looks like from the first Select Statement without the user name

6072      2006-03-04 00:00:00.000      1
6073      2006-03-04 00:00:00.000      1
6074      2006-03-04 00:00:00.000      1
6075      2006-03-04 00:00:00.000      1
6076      2006-03-04 00:00:00.000      1
6077      2006-03-04 00:00:00.000      1
6078      2006-03-04 00:00:00.000      1
6089      2006-03-11 00:00:00.000      1
6090      2006-03-11 00:00:00.000      1
6091      2006-03-11 00:00:00.000      1
6092      2006-03-11 00:00:00.000      1
6093      2006-03-11 00:00:00.000      1
6072      2006-03-18 00:00:00.000      1
6073      2006-03-18 00:00:00.000      1
6074      2006-03-18 00:00:00.000      1
6075      2006-03-18 00:00:00.000      1
6076      2006-03-18 00:00:00.000      1
6077      2006-03-18 00:00:00.000      1
6078      2006-03-18 00:00:00.000      1
6079      2006-03-18 00:00:00.000      1
6080      2006-03-18 00:00:00.000      1
6081      2006-03-18 00:00:00.000      1
6082      2006-03-18 00:00:00.000      1
6083      2006-03-18 00:00:00.000      1
6084      2006-03-18 00:00:00.000      1
6085      2006-03-18 00:00:00.000      1
6086      2006-03-18 00:00:00.000      1
6087      2006-03-18 00:00:00.000      1
6098      2006-03-18 00:00:00.000      1
6099      2006-03-18 00:00:00.000      1
6100      2006-03-18 00:00:00.000      1
6101      2006-03-18 00:00:00.000      1

Here is what the data looks like after the second SELECT

6072      2
6073      2
6074      2
6075      2
6076      2
6077      2
6078      2
6079      1
6080      1
6081      1
6082      1
6083      1
6084      1
6085      1
6086      1
6087      1
6089      1
6090      1
6091      1
6092      1
6093      1
6098      1
6099      1
6100      1
6101      1

And here is the final set of data...this is what I need
6072      0      2
6073      0      2
6074      0      2
6075      0      2
6076      0      2
6077      0      2
6078      0      2
6079      0      1
6080      0      1
6081      0      1
6082      0      1
6083      0      1
6084      0      1
6085      0      1
6086      0      1
6087      0      1
6088      0      0
6089      0      1
6090      0      1
6091      0      1
6092      0      1
6093      0      1
6094      0      0
6095      0      0
6096      0      0
6097      0      0
6098      0      1
6099      0      1
6100      0      1
6101      0      1

Notice that in the last select statement I use a left join to bring back in any missing PID's from the dataset.

Because I think this is a hard one and I need it yesterday, I've assigned the max point value.

Thanks
Mike

Aneesh

Something like this

SELECT erv.PID, erv.EventOffset, IsNull(aa.EventCount,0)
FROM tblEventRotationValues erv
LEFT JOIN (

SELECT a.PID, SYSTEM_USER, Count(a.PID) As EventCount
FROM ( SELECT
tblEmployee.PID,
SYSTEM_USER,
e.StartDate,
ABS(SUM(ee.EventActive)) AS 'EventCount'
FROM
tblEvents e
INNER JOIN tblEventEmployee ee ON e.EventID = ee.EventID
INNER JOIN tblEmployeeListAssignment ela ON ee.PID = ela.PID
INNER JOIN tblEmployee ON ee.PID = tblEmployee.PID

WHERE
ela.EmployeeListDescription = @strEmployeeListDesc
AND ela.Inactive = 0
AND DATEPART(dw, e.StartDate) = 7 --Saturday

GROUP BY
tblEmployee.PID,
e.StartDate
HAVING SUM(ee.EventActive) <> 0) a
) aa ON erv.PID = aa.PID
WHERE erv.EventRotationID = 7

Data-Man

ASKER

I got rid of the system user stuff...

SELECT erv.PID, erv.EventOffset, IsNull(aa.EventCount,0)
FROM tblEventRotationValues erv
LEFT JOIN (

SELECT aa.PID, Count(a.PID) As EventCount
FROM ( SELECT
tblEmployee.PID,
e.StartDate,
ABS(SUM(ee.EventActive)) AS 'EventCount'
FROM
tblEvents e
INNER JOIN tblEventEmployee ee ON e.EventID = ee.EventID
INNER JOIN tblEmployeeListAssignment ela ON ee.PID = ela.PID
INNER JOIN tblEmployee ON ee.PID = tblEmployee.PID

WHERE
ela.EmployeeListDescription = 'Server - A-List'
AND ela.Inactive = 0
AND DATEPART(dw, e.StartDate) = 7 --Saturday

GROUP BY
tblEmployee.PID,
e.StartDate
HAVING SUM(ee.EventActive) <> 0) a
) aa ON erv.PID = aa.PID

I get this error.

Column 'a.PID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

I tried changing it to aa and adding a group by clause at the end, but still the same problem...hmm..I think we are close. Thanks for taking the time.

Mike

Guy Hengel [angelIII / a3]

please try this:

SELECT erv.PID, erv.EventOffset, IsNull(a.EventCount,0)
FROM (
SELECT
tblEmployee.PID,
SYSTEM_USER,
e.StartDate,
ABS(SUM(ee.EventActive)) AS 'EventCount'
FROM
tblEvents e
INNER JOIN tblEventEmployee ee ON e.EventID = ee.EventID
INNER JOIN tblEmployeeListAssignment ela ON ee.PID = ela.PID
INNER JOIN tblEmployee ON ee.PID = tblEmployee.PID

WHERE
ela.EmployeeListDescription = @strEmployeeListDesc
AND ela.Inactive = 0
AND DATEPART(dw, e.StartDate) = 7 --Saturday

GROUP BY
tblEmployee.PID,
e.StartDate
HAVING
SUM(ee.EventActive) <> 0
) a
LEFT JOIN
(
SELECT a.PID, SYSTEM_USER, Count(a.PID) As EventCount
FROM
GROUP BY PID
) erv
ON erv.PID = a.PID
WHERE erv.EventRotationID = 7 --Saturday

Data-Man

ASKER

AngelIII,
It doesn't like not having a value after the last FROM.

Mike

Aneesh

SELECT erv.PID, erv.EventOffset, IsNull(aa.EventCount,0)
FROM tblEventRotationValues erv
LEFT JOIN (

SELECT aa.PID, Count(aa.PID) As EventCount
FROM ( SELECT
tblEmployee.PID,
e.StartDate,
ABS(SUM(ee.EventActive)) AS 'EventCount'
FROM
tblEvents e
INNER JOIN tblEventEmployee ee ON e.EventID = ee.EventID
INNER JOIN tblEmployeeListAssignment ela ON ee.PID = ela.PID
INNER JOIN tblEmployee ON ee.PID = tblEmployee.PID

WHERE
ela.EmployeeListDescription = 'Server - A-List'
AND ela.Inactive = 0
AND DATEPART(dw, e.StartDate) = 7 --Saturday

GROUP BY
tblEmployee.PID,
e.StartDate
HAVING SUM(ee.EventActive) <> 0) a
) aa ON erv.PID = aa.PID

Data-Man

ASKER

Same problem

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'aa' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'aa' does not match with a table name or alias name used in the query.

Mike

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Data-Man

ASKER

Worked Perfectly...Thanks for taking the time to make it work.

Kind Regards,
Mike