Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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

0
Data-Man
Asked:
Data-Man
  • 4
  • 3
1 Solution
 
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-ManAuthor 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Data-ManAuthor 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-ManAuthor 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
 
Data-ManAuthor Commented:
Worked Perfectly...Thanks for taking the time to make it work.

Kind Regards,
Mike
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now