Trying to avoid a cursor solution. pls hlp!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to avoid a cursor solution. pls hlp!

Author

Topic

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-26 : 15:11:38

Hello all!

I have a table called tsktsrhst with the following columns defined on it.

center char 5
tsr char 4
appl char 4
listid int
call_date datetime
tot_calls int
time_connect int
time_paused int
time_waiting int
time_deassigned int
time_acw int

I want to query the table such when I group by center, tsr, appl, and listid, I get the sum of tot_calls, the sum of all time_xxx together, and the sum of each time_xxx separately as the resulting record set. Here is the hitch. The sum(time_connect + time_paused + time_... + ...) must be as close to 10 as possible and the sum of these totals must come from the most recent dates.

I >know< that I can do this with cursors, but would like to avoid it. However, since this requires that the total for each grouping may be formed throughout different call_date(s), and thus different numbers of records for each grouping, I don't know quite how to approach this. Any suggestions? Thanks.

dKK

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 15:28:26

When you say the totals must come from the most recent dates, do you mean that it should do a max(call_date) based on the 4-part you mentioned? Something like this?:

SELECT Center, TSR, Appl, ListID, sum(time_connect), sum(time_paused), sum(time_waiting), sum(time_deassigned), sum(time_acw)
FROM tsktsrhst t1
WHERE call_date =
(
SELECT max(call_date) from tsktsrhst t2
WHERE t1.Center = t2.Center and t1.TSR = t2.TSR and t1.Appl = t2.Appl and t1.ListID = t2.ListID
)

See if you can get that to work. Then, the rounding to the nearest 10 is a matter of conversion/formatting.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-26 : 16:05:17

Thanks for replying. I'll use a combination of psuedo-code and comments to try to show what I want.

SELECT Center, TSR, Appl, ListID,
sum(time_connect + time_paused + time_waiting + time_deassigned + time_acw),
sum(time_connect), sum(time_paused), sum(time_waiting), sum(time_deassigned), sum(time_acw)
FROM tsktsrhst t1
WHERE call_date in
(
SELECT TOP 30 call_date from tsktsrhst t2
WHERE t1.Center = t2.Center and t1.TSR = t2.TSR and t1.Appl = t2.Appl and t1.ListID = t2.ListID
ORDER BY call_date desc
) and
-- I know you can't do this but..
sum(time_connect + time_paused + time_waiting + time_deassigned + time_acw) <=
(SELECT sum(time_connect + time_paused + time_waiting + time_deassigned + time_acw) FROM tsktsrhst t3
WHERE t2.Center = t3.Center and t2.TSR = t3.TSR and t2.Appl = t3.Appl and t2.ListID = t3.ListID)

Again thanks.

GreatInca
Posting Yak Master

102 Posts

Posted - 2001-12-26 : 16:40:27

sum(time_connect + time_paused + time_waiting + time_deassigned + time_acw) <=
(SELECT sum(time_connect + time_paused + time_waiting + time_deassigned + time_acw) FROM tsktsrhst t3
WHERE t2.Center = t3.Center and t2.TSR = t3.TSR and t2.Appl = t3.Appl and t2.ListID = t3.ListID)

should work in a HAVING clause. It also look like it could be re-written as a derived table.

You will need a group by though and the aggragates have to be in a derived table and the non-aggragates/data not in group by will have to be joined to this derived table.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 16:51:08

Oops!

Thanks Jesse, I was so focused on getting the correlated subquery put together that I forgot about the GROUP BY statement...

KarateKid, are you saying that you want sums only of records in the last 30 days? Or only the last 30 entered dates? And what are you trying to accomplish with that last clause (the sum... <= sum...)?

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 11:39:56

Thks for responding.

...
WHERE call_date in
(
SELECT TOP 30 call_date from tsktsrhst t2
WHERE t1.Center = t2.Center and t1.TSR = t2.TSR and t1.Appl = t2.Appl and t1.ListID = t2.ListID
ORDER BY call_date desc
) and
...

This is my attempt to say that all time_xxx fields used in the aggregate must come from the records with the most recent call_date(s) in descending order of call_date occurrence grouped by center/tsr and by appl and by listid- to track no further than 30 days back. TOP 30 was a poor choice (since each tsr can have multiple entries in a day), but then I am using 'psuedo code' (kind of).

Then what I need to do is return only the records from these call dates up to and including, but not more than sum(time_xx0 + time_xx1 + ... + timexxn) <= XX.

Then I use the fields in these records to calculate percentages. The trick is all of this information has to be grouped by center/tsr and by appl, and by listid. For example, tsr1 might need 10 records to total XX hours, tsr2 might need 15 records to total XX hours, tsr3 might need 98 records to total XX hours in any one given result set.

I can do this as a cursor solution. But I know this can be done as one set based operation! For the life of me I am either too brain cramped or too retarded to figure it out.

dKK

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 12:15:30

Okay, I'm obsessing a little on this "no further than 30 days back". I'm still not clear. Are you saying you want records (1)no older than 30 days before TODAY? If so, then why not use

WHERE call_date >= dateadd(day, -30, getdate())
or
WHERE call_date >= dateadd(month, -1, getdate())

OR (2) "the last 30 dates entered in the table, regardless of how much before today they may be"?

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 12:22:53

Second Question: When you say this:

quote:
Then what I need to do is return only the records from these call dates up to and including, but not more than sum(time_xx0 + time_xx1 + ... + timexxn) <= XX.

do you mean that if, for example XX = 100 and the sum of the last 30 days' worth of time_xxx records is 250 that you want to further restrict the records added together to only add up to something as close to, but less than 100? Would these only be by date in descending order? Would you skip a record that put you over 100, but get the next one if it still fit under 100?

Do you actually have this written cursor form and working? If you do, maybe you should post it to clear up all these questions.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 12:31:26

Actually, the requirements regarding records returned are
1) that for each center, tsr, appl, and listid - return only those records in which sum(time_xx1 + time_xx2 + ...) <= 10.

2) that each record from which the sum of time_xxx comes, the call_date must contiguous in descending order, by center, tsr, appl, and listid.

3) that for each center, tsr, appl, and listid, the call_date should not be any older than 30 days.

I can accomplish requirement 2 by joining a reference from tsktsrhst to a derived table in tsktsrhst. Requirement 3 is easy. Requirement 1 is what is absolutely killing me. Every field I want returned is a sum aggregate, except for the group by(s) (center, tsr, appl, listid).

Thank you for following me this far into this insanity. And sorry for not being concise about formulating my questions.

dKK

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 12:34:46

Every assumption you made is correct!

dKK

Second Question: When you say this:

quote:
--------------------------------------------------------------------------------
Then what I need to do is return only the records from these call dates up to and including, but not more than sum(time_xx0 + time_xx1 + ... + timexxn) <= XX.

--------------------------------------------------------------------------------

do you mean that if, for example XX = 100 and the sum of the last 30 days' worth of time_xxx records is 250 that you want to further restrict the records added together to only add up to something as close to, but less than 100? Would these only be by date in descending order? Would you skip a record that put you over 100, but get the next one if it still fit under 100?

Do you actually have this written cursor form and working? If you do, maybe you should post it to clear up all these questions.

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 12:37:52

I have not approached a cursor solution on this yet, as I >know< I will have to change it. But, if I get some time, I will post the cursor solution. It'll probably be a few hours though. I'm hoping that one of you SQL Gurus will see some quick three line solution!!! Thanks.

dKK

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 13:16:18

OK, I've gotten my vienti soy chai latte, so hopefully I'm thinking more clearly. Although it may not have fully kicked in yet.

So, am I getting closer with this...?


SELECT center, trs, appl, listid, SUM(tot_calls), sum(time_connect), sum(time_paused), sum(time_waiting),
  sum(time_deassigned), sum(time_acw),
  SUM(tot_calls + time_connect + time_paused + time_waiting + time_deassigned + time_acw)
FROM tsktsrhst
WHERE (tot_calls + time_connect + time_paused + time_waiting + time_deassigned + time_acw) <= 10
  AND call_date >= dateadd(day, -30, convert(varchar(11),getdate(),112))
GROUP BY center, trs, appl, listid

I haven't addressed Requirement 2, which you said you could handle.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Edited by - AjarnMark on 12/27/2001 13:17:09

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 13:35:12

Very close. But, I need the WHERE clause to be..

WHERE SUM(tot_calls + time_connect + time_paused + time_waiting + time_deassigned + time_acw) <= 10 AND call_date >= dateadd(day, -30, convert(varchar(11),getdate(),112))
GROUP BY center, trs, appl, listid

HOWEVER, this doesn't work for the following reasons:

1) no aggregate can be present in the WHERE clause (I know that I would use HAVING instead). The real problem is #2.

2) I want the first 10 hours worth of records to be returned in the aggregate select - >>not just those records that sum up to 10 or less<< - as long as the records used in the first 10 hours do NOT exceed 30 days or more.

In my head the problem is that this returns a ragged result set for each grouping (center, tsr, appl, listid). In other words, each grouping will necesarily have a different amount of rows to fulfill #2. However, since I'm returning all aggregate information, it seems like this can be done.

I really appreciate your patience.

dKK

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 14:44:18

OK, so you DO want it to drop out once it hits the magic 10-hour point and ignore all other rows for that group even though they meet the date requirement. This sounds like a variation on the running-subtotal concept, so you might want to read this article: [url]http://www.sqlteam.com/item.asp?ItemID=3856[/url]

In this article, Garth refers to a book by Ken Henderson called The Guru's Guide to Transact-SQL. I would encourage you to get this because it is a FABULOUS book (hey, somebody has to bring it up, and if rob is off vacationing...) By the way, you can buy it here [url]http://www.sqlteam.com/store.asp[/url] and help support SQLTeam.

I'm guessing that you'll probably need to combine a running total type of query into a subquery so that the main query then does the aggregates.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Edited by - AjarnMark on 12/27/2001 14:45:57

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 15:12:08

Great! I'll check out the link. I have "The Guru's Guide...". Can you refer me to a page number? I glanced through and tried to find some reference to what I'm doing, but after about 20 min, I gave up.

I'll either post my solution for everybody to see, or post the cursor version (which I'm working on now), if I hit a road block.

To answer your question, yes I DO want it to drop out once it hits the magic 10-hour point and ignore all other rows for that group even though they meet the date requirement.

Thnx,
dKK

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-27 : 16:05:17

Check page 195-196. Cumulative and Sliding Aggregates

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-27 : 17:35:42

Here is my query so far:


SELECT t0.center, t0.tsr, t0.appl, t0.listid,
	SUM(CAST((t0.time_connect + t0.time_paused + t0.time_waiting + t0.time_deassigned
		+ t0.time_acw) AS DECIMAL(9,2)) / 3600.0) AS sum_totaltime,
	SUM(CAST(t0.time_connect AS DECIMAL(9,2)) / 3600.0) AS sum_connect, 
	SUM(CAST(t0.time_paused AS DECIMAL(9,2)) / 3600.0) AS sum_paused, 
	SUM(CAST(t0.time_waiting AS DECIMAL(9,2)) / 3600.0) AS sum_waiting, 
	SUM(CAST(t0.time_deassigned AS DECIMAL(9,2)) / 3600.0) AS sum_deassigned, 
	SUM(CAST(t0.time_acw AS DECIMAL(9,2)) / 3600.0) AS sum_acw
FROM tsktsrhst t0 
CROSS JOIN
	(
	SELECT center, tsr, appl, listid, call_date,
	CAST((time_connect + time_paused + time_waiting + time_deassigned + time_acw) AS DECIMAL(9,2))
		/ 3600.0 total_hours
	FROM tsktsrhst
	WHERE call_date >= (GETDATE() - 30)
	--FOR DEBUG
	--ORDER BY center, tsr, appl, listid, call_date
	) t1
WHERE t0.center = t1.center AND t0.tsr = t1.tsr AND t0.appl = t1.appl AND
	t0.listid = t1.listid AND 
	t0.call_date >= (GETDATE() - 30) AND --MAY BE REDUNDANT, JUST WANT TO TRIM RESULT SET
	t1.call_date <= t0.call_date --FOR CUMMULATIVE AGGREGATE, REFERENCED IN CROSS JOIN
/*	--FOR SLIDING AGGREGATE, REFERENCED IN CROSS JOIN.  OPTION # 1
	AND t1.total_hours 
	BETWEEN 
	SUM(CAST((t0.time_connect + t0.time_paused + t0.time_waiting + t0.time_deassigned
		+ t0.time_acw) AS DECIMAL(9,2)) / 3600.0) - 10.0
	--10.0 ON THE ABOVE LINE REFERS TO A RANGE OF 10 HOURS 
	AND	
	SUM(CAST((t0.time_connect + t0.time_paused + t0.time_waiting + t0.time_deassigned
		+ t0.time_acw) AS DECIMAL(9,2)) / 3600.0)
*/
GROUP BY t0.center, t0.tsr, t0.appl, t0.listid
/*
--FOR SLIDING AGGREGATE, REFERENCED IN CROSS JOIN.  OPTION # 2
HAVING t1.total_hours 
	BETWEEN 
	SUM(CAST((t0.time_connect + t0.time_paused + t0.time_waiting + t0.time_deassigned
		+ t0.time_acw) AS DECIMAL(9,2)) / 3600.0) - 10.0  
	--10.0 ON THE ABOVE LINE REFERS TO A RANGE OF 10 HOURS 
	AND	
	SUM(CAST((t0.time_connect + t0.time_paused + t0.time_waiting + t0.time_deassigned
		+ t0.time_acw) AS DECIMAL(9,2)) / 3600.0)
*/
ORDER BY t0.center, t0.tsr, t0.appl, t0.listid

I have three hurdles still to jump however.
1) t1.total_hours is not an ordered key. On any given call date, a tsr at a center may have more than one record for any given call_date. call_date is a datetime field but the hh:mm:ss.mmm is always 00:00:00.000. t1.total_hours is not necessarily ordered, and many times is the same for each grouping and call_date. I am trying to implement a combination of the 'Cummulative Aggregates' and 'Sliding Aggregates' solution mentioned in "The Guru's Guide...", but both are key dependent.

2) I have worked out two solutions for the sliding aggregate. Both are commented out in my query. The first version in the where clause does not work because checking if t1.total_hours is between two aggregates (sum()) is a no-no. The second version in the having clause does not work because t1.total_hours is not included in the group by fields. What is the best work-around?

3) I recognize that this type of reporting is best suited for OLAP/AS, but I don't make the decisions here. I am deathly afraid of the performance implications of this query on and OLTP database. Doing a cross join on tsktsrhst, which contains 16 million records, is going to exhibit horrible performance. Will it help to cluster on a compound index to include center, tsr, appl, listid, and call_date? If so, would I order the fields in this index based on best selectivity?

Thnx again,
dKK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources