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
SP
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
While waiting for sample data, have a look at
;with CTE as
(  SELECT  u.FirstName + ' ' + u.LastName UserName, u.UserID,dateadded,
           min(dateadded) OVER (PARTITION BY AddedUserID ORDER BY AddedUserID,DateAdded) MinDate
   FROM    Individuals 
   INNER JOIN Users u ON u.UserID = AddedUserID
   WHERE CAST(DateAdded AS DATE) = CAST(GETDATE() AS DATE)
)  select * from 
   (Select UserName, userID, count(dateadded) as Num_Entries, datediff(minute,MinDate,dateadded) / 5 * 5 as Grp_5_Mins
    from CTE c
    group by c.username,c.userid, datediff(minute,MinDate,dateadded) / 5 * 5) src
  PIVOT
   (sum(Num_Entries) for grp_5_mins in ([0],[5],[10],[15],[20],[25],[30],[35],[40],[45],[50],[55],[60],[65],[70],[75],[80],[85],[90],[95],[100])) pvt

Open in new window

now, those derived column names would need to be extended to however many 5 minute blocks are needed and regarded as a discrete block not cumulative e.g. [10] doesnt include [0] and [5]
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
Larry Bristersr. DeveloperAuthor Commented:
I'll be at my desk this afternoon
0
 
Larry Bristersr. DeveloperAuthor Commented:
Perfect. Thanks
0
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.

All Courses

From novice to tech pro — start learning today.