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.EmployeeListDescriptio
n = @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
Start Free Trial
