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

LVL 18
Data-ManCOOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Data-ManCOOAuthor Commented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Data-ManCOOAuthor Commented:
AngelIII,
     It doesn't like not having a value after the last FROM.

Mike
0
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Data-ManCOOAuthor Commented:
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
0
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT erv.PID, erv.EventOffset, IsNull(a.EventCount,0)EventCount
FROM tblEventRotationValues erv
LEFT JOIN (
    SELECT a.PID, SYSTEM_USER SysUser, Count(a.PID) As EventCount
    FROM (
    SELECT      
        tblEmployee.PID,
        SYSTEM_USER SysUser,
        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
    GROUP BY PID ) a ON erv.PID = a.PID
WHERE erv.EventRotationID = 7 --Saturday
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Data-ManCOOAuthor Commented:
Worked Perfectly...Thanks for taking the time to make it work.

Kind Regards,
Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.