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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
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

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
Larry Bristersr. DeveloperAuthor Commented:
I'll be at my desk this afternoon
0
Larry Bristersr. DeveloperAuthor Commented:
Perfect. Thanks
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.