Crosstabs and grouping - don't know which I need 1

[kmerenda]

I have a table that tracks people coming into our offices at various locations. The important fields are as follows:
ID (auto generated integer)
SignInDate (date/time)
LocationID (integer)

so a typical record would look like:
1 - 12/30/2008 8:00 AM - 75

If each person that comes in is a record, I need to be able to get an average count of people that came into each office within the past 60 minutes, sampling only the past 60 days of data, and grouping the results by office.

I am designing a page that will allows users to compare this historical data the the actual count of customers that came into each office today, so they can see if the customer traffic is light, average, or heavy at each office.

I was looping through each location, and then looping my query through each date of the past 60 days, and that worked, but it was taking quite a while to process. I'm trying to find a more efficient way.

 

[RiverGuy]

Would you post a sample of the desired output? Be sure to use the

 tag for readability.

 

[kmerenda]

I would like the output to look like:

LOCATIONID - AVG TRAFFIC - CURRENT TRAFFIC
75                10               15

Keep in mind that...
Current traffic = the total records with a SignInDate that falls between right now and the past 60 minutes, grouped by locationID

AVG Traffic = the average number of records with a SignInDate that falls in the same time period, but samples the past 60 days worth of records.

 

[RiverGuy]

Well, I have a couple of thoughts.

It might be easier and better performing if you are able to analyze this data in defined slices of time. What I mean by this is that instead of analyzing 1:48 PM to 2:47 PM average traffic, it might be more meaningful to analyze traffic by the hour (1 PM to 2 PM, 2 PM to 3 PM, etc). Or half hour, or fifteen minute intervals, etc. By doing this, you can assign "intervals" to your records based on SignInDate. This could be in a temporary table in your query or you could move the records over into a reporting structure where the interval attribute is added to the permanent table.

In addition, you wouldn't be constricted to running scenarios based on the current time. You could very easily run scenarios based on different time intervals of different grains. Having a time table in your database has many uses.

But anyways, back to your code. I'm not sure how well this will perform over your current query. But the key here is to get the time portion of the SignInDate to help with filtering the past 60 days. You can see where I have this in the second portion of the WHERE clause in the lower half of the query. I'm not sure of your table name, but I was using @Visits to test with. You should be able to see the logic.

DECLARE @TimeStart DATETIME
DECLARE @TimeEnd DATETIME

SELECT @TimeEnd = GETDATE()
SELECT @TimeStart = DATEADD(minute, -60, @TimeEnd)


SELECT cur.*, his.AVGTraffic
FROM
	(SELECT LocationID, COUNT(*) AS CurrentTraffic
	FROM @Visits
	WHERE SignInDate BETWEEN @TimeStart AND @TimeEnd
	GROUP BY LocationID) cur
LEFT OUTER JOIN
	(SELECT a.LocationID,
	AVG(CurrentTraffic) AS AVGTraffic
	FROM
		(SELECT LocationID, 
		CONVERT(DATETIME, CONVERT(CHAR(10), SignInDate, 112)) AS SignInDate, 
		COUNT(*) AS CurrentTraffic
		FROM @Visits
		WHERE (SignInDate BETWEEN DATEADD(day, -60, CONVERT(DATETIME, CONVERT(CHAR(10), @TimeEnd, 112))) AND @TimeEnd)
		AND (CONVERT(VARCHAR(20), SignInDate, 108) BETWEEN CONVERT(VARCHAR(20), @TimeStart, 108) AND CONVERT(VARCHAR(20), @TimeEnd, 108))
		GROUP BY LocationID, CONVERT(DATETIME, CONVERT(CHAR(10), SignInDate, 112))) a
	GROUP BY LocationID) his
ON cur.LocationID = his.LocationID

 

[kmerenda]

RiverGuy - in terms of using defined slices of data - we were already doing that with various reports, but we needed something that gave our administrators a live view of the traffic in each office so they could adjust staffing levels as needed.

Your query worked PERFECT! I added just a few lines so that I could resolve the locationID to the actual location name, but it worked perfect, and (the best part) I actually get it. There is stuff in there that is new to me - but I get it. Thanks soooooo much!

FYI - final code:

                    DECLARE @TimeStart DATETIME
                    DECLARE @TimeEnd DATETIME
                    
                    SELECT @TimeEnd = GETDATE()
                    SELECT @TimeStart = DATEADD(minute, -60, @TimeEnd)
                    
                    
                    SELECT cur.*, his.AVGTraffic
                    FROM
                        (SELECT nameshort, COUNT(*) AS CurrentTraffic
                        FROM wl_waitlist
                        INNER JOIN emp_location ON wl_waitlist.locationid = emp_location.id
                        WHERE SignInDate BETWEEN @TimeStart AND @TimeEnd
                        GROUP BY nameshort) cur
                    LEFT OUTER JOIN
                        (SELECT a.nameshort,
                        AVG(CurrentTraffic) AS AVGTraffic
                        FROM
                            (SELECT nameshort,
                            CONVERT(DATETIME, CONVERT(CHAR(10), SignInDate, 112)) AS SignInDate,
                            COUNT(*) AS CurrentTraffic
                            FROM wl_waitlist
                            INNER JOIN emp_location ON wl_waitlist.locationid = emp_location.id
                            WHERE (SignInDate BETWEEN DATEADD(day, -60, CONVERT(DATETIME, CONVERT(CHAR(10), @TimeEnd, 112))) AND @TimeEnd)
                            AND (CONVERT(VARCHAR(20), SignInDate, 108) BETWEEN CONVERT(VARCHAR(20), @TimeStart, 108) AND CONVERT(VARCHAR(20), @TimeEnd, 108))
                            GROUP BY nameshort, CONVERT(DATETIME, CONVERT(CHAR(10), SignInDate, 112))) a
                        GROUP BY nameshort) his
                    ON cur.nameshort = his.nameshort

 

[RiverGuy]

No problem. I was afraid it would be too slow with the casting of SignInDate to VARCHAR in the WHERE clause. Glad to hear it works well.

 

[SQLSister]

Just for your general knowldge, you almost never want to loop through records as it is the least efficient way to do things. RiverGuy's solution is a set-based solution (it operates over the whole set of data rather than one at a time as in looping) and so would perform better than any looping process. In the future stop and rethink anytime you consider looping through a recordset.

"NOTHING is more important in a database than integrity." ESquared