Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

MS SQL Server PIVOT QUERY

I have SQL below that returns valid data of seconds lag between rows entered by userid
I need a separate query I guess that shows how many records are entered PER 5 minute period PER AddedUserID
And it needs to be a pivot of course



AddedUsedID   [+5]   [+10]... etc...
101                       23      21

SELECT  u.FirstName + ' ' + u.LastName, q.AddedUserID,DATEDIFF(second, pDataDate, q.DateAdded) SecondsDiff
FROM    (
        SELECT  *,
                LAG(DateAdded) OVER (PARTITION BY AddedUserID ORDER BY AddedUserID,DateAdded) pDataDate
        FROM    Individuals WHERE CAST(DateAdded AS DATE) = CAST(GETDATE() AS DATE)
        ) q
		JOIN Users u ON u.UserID = q.AddedUserID
WHERE   pDataDate IS NOT NULL

Open in new window


Data Returned
User generated image
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Getting the column names is the tricky part. 5 minutes columns could mean a LOT of columns. 12 per hour and 8 hour shifts (just guessing) means spelling out nearly 100 columns.... And assume it is for a given day ( based on dateadded= getdate() )

Some sample data would make it easier. Just need userid and dateadded.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Larry Brister

ASKER

I'll be at my desk this afternoon
Perfect. Thanks