A difficult Combining Rows problem

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in one hour chunks, like below:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
This involves several things:
- Recognizing that there are variable # of rows (maybe we only have 3
15 minute entries instead of 4)
- Getting a min of those row's min column
- Getting a max of those row's max column
- Getting a total for those row's points column
- Input to any view or whatver would be based on the startTime and
endTime and would always be in whole hours.
I have a feeling that I am going to be doing this all in the C# .NET
end of things, but it's at least worth a shot asking all of you SQL
experts. What I am basically interested in knowing is, do you all
think that this is possible using views or stored procedures or
something else I don't know about. I didn't even know about views
until i started researching how to do this.
Any ideas? Is this possible? Should I just give up and do it on the
C# end of things? Seems to me that it might be possible to do in a
stored procedure, but possible not worth my time. I aprpeciate any
help or suggestions.
JasonTry this:
SELECT groupid,
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'20050101')),
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'2005-01-01T00:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(HH,'20050101',starttime) ;
David Portas
SQL Server MVP
--|||Hi
Check out the dateadd/datepart functions in Books Online for rounding times.
Try:
SELECT GROUPID, DATEADD(mi,-DATEPART(mi,Starttime),Starttime) AS StartTime,
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(mi,-DATEPART(mi,Starttime),Starttime),
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
John
"Factor" wrote:

> Greetings,
> I'm working to combine rows based on a time window and I am hoping to
> be able to write a stored procedure to do this for me, rather than have
> parse through all this data in my program. I'm not very well versed
> with T-SQL syntax.. just enough to get by selecting using inner joins,
> updating and inserting... thats about it. (Hence why I am here.)
> The raw data I have below looks like this:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
> 1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
> 1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
> 1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
> 1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
> 1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
> 1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
> 1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
> Pretty straight forward; you can see each entry is a 15 minute time
> interval. What I want to be able to do is to use a view or a stored
> procedure to view this in one hour chunks, like below:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
> 1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
> This involves several things:
> - Recognizing that there are variable # of rows (maybe we only have 3
> 15 minute entries instead of 4)
> - Getting a min of those row's min column
> - Getting a max of those row's max column
> - Getting a total for those row's points column
> - Input to any view or whatver would be based on the startTime and
> endTime and would always be in whole hours.
> I have a feeling that I am going to be doing this all in the C# .NET
> end of things, but it's at least worth a shot asking all of you SQL
> experts. What I am basically interested in knowing is, do you all
> think that this is possible using views or stored procedures or
> something else I don't know about. I didn't even know about views
> until i started researching how to do this.
> Any ideas? Is this possible? Should I just give up and do it on the
> C# end of things? Seems to me that it might be possible to do in a
> stored procedure, but possible not worth my time. I aprpeciate any
> help or suggestions.
> Jason
>|||John Bell and David Portas,
I will have to read up on these Dateadd/DatePart parameters an actually
interpret what is going on within these statements, but just from what
you gave me here it looks like this will work out very well, and I
really appreciate the insight. This will allow me to vary that time
window fairly easily I do believe, all on a SQL call (that's much
better than bringing back all the data and parsing through it all it.
Thanks again,
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||On 10 Nov 2005 11:41:54 -0800, Factor wrote:

>John
>I have read over those functions and I now understand what they do and
>how to use them, but I am still as to why the min / max /
>total fields actually work. I assume it has something to do with the
>GROUP BY statements, but again, I don't know why.
Hi Jason,
Correct. The GROUP BY tells SQL Server to combine the data from several
rows into one row. This is normally used to report totals, minimum,
maximum per project, per section, etc. But with the appropriate
expression, it cal also be used to combine rows that fit in the same
"period" into one group.
Though John's and David's versions both work, I suggest you go with
Davids version, as this is more flexible. (And, once you get your head
around it, easier to understand as well).
Basically, John's version works by taking each of the date parts you
want to disregard (milliseconds, seconds, minutes), then subtracting
that amount of time from the Starttime. The end result will of course be
the last full hour equal to or before Starttime.
David's version works the other way around - it calculates the number of
full hours that have elapsed since a chosen anchor date, then adds that
number to the chosen anchor date. The result will be the same as John's
expression.
(Note: David chose to just use the number of hours for the group by, and
add it back to the anchor date in the SELECT clause only)

>Assuming black magic happens and thats just how it works, I should just
>be able to change those hh,1 to hh,4 and get 4 hour increments instead.
No. I'll give you two examples how to modify David's query to report on
4-hour intervals and to report on 1/2-hour intervals.
For 4-hour intervals, again calculate the number of hours since an
anchor date. Divide by 4 and truncate, then multiply by 4 again. Add
this number of hours to the anchor date. There you have the start of the
last 4-hour interval
SELECT groupid,
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'20050101')),
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'2005-01-01T03:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
For 1/2-hour intervals, we can't divide the number of hours sice the
anchor date by 0.5, as that won't give us back the precision we already
lost. Instead, we'll have to calculate minutes and divide by 30:
SELECT groupid,
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'20050101')),
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'2005-01-01T00:29:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
In both cases, don't forget to change the shifted anchor value in the
expression for the end point of the interval. Instead of using the same
anchor date, then adding 30 minunte or 4 hours minus one second, the
anchor date is shifted by 30 minutes or 4 hours minus one second.
Now, the above code can still be simplified further. If your table
always has the complete data (as your sample roiws indicate), then you
could change the above queries to:
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
-- or: GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
Note that this might show "holes" in the periods if your real data is
not as complete as the sample you posted indicates. But the advantage is
that you get rid of the "shifted" anchor date for calculating end time.
Final step would be to put it in a stored procedure and use a parameter
for the interval length (in minutes):
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) / @.Interval;
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jason,
What David Provided is an Excellent query .
Let us see if this query can help you.
Select GID , Min(STime) , Max(ETime) ,
Min(Minimum),Max(Maximum),Sum(Points)[co
lor=darkred]
>From yourTableName Group By[/color]
GID,Convert(varchar,STime,112),DatePart(
hh,STime)
Having same name as Functions/ Keyword sound confusing to me so I
changed them.
With Warm Regards
Jatinder Singh|||Hi
This is easier with David's method (see Hugo's reply for an explanation).
Dividing the number of hours by 4 and dropping the remainder will give you 4
hour chunks when they are multiplied back up. You also need to change the en
d
time to give a 4 hour gap.
SELECT groupid,
MIN(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'20050101')
) AS Starttime,
MAX(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'2005-01-01T03:59:59')
) AS Endtime,
MIN(min) AS [Min],
MAX(max) AS [Max],
SUM(points) AS [Total Points]
FROM Readings
GROUP BY groupid,
4*(DATEDIFF(HH,'20050101',starttime)/4)
John
"Factor" wrote:

> John
> I have read over those functions and I now understand what they do and
> how to use them, but I am still as to why the min / max /
> total fields actually work. I assume it has something to do with the
> GROUP BY statements, but again, I don't know why.
> Assuming black magic happens and thats just how it works, I should just
> be able to change those hh,1 to hh,4 and get 4 hour increments instead.
> When I do that, the Starttime and Endtime values do return correctly
> (although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
> the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
> that black magic that is limiting the MIN/MAX/SUM to one hour is still
> limiting them to one hour even with the altered start and end times.
> I'm unsure how to fix or get around this because I don't yet understand
> what is limiting that max to an hour in the first place. How does this
> work? I've been tripped up GROUP BY things before, it's my kryptonite
> for some reason.
> Hope that is not too confusing, I'm all jumbled in my head.
> I really apprecaite the help with this so far, you've all been
> wonderful.
> Jason
>|||Wondeful! Lots ot take in, I thank everyone for their help. I've made
a lot of progress and I've learned a TON about SQL int he past two
days.
I hope I can help you all in the future with something!
Thanks again,
Jason

a complex query needs a solution

I need help with determining which salespeople should be
assigned bonuses based off a relationship between the
salesperson and their manager and also the relationship
between the salesperson and the customer. Below are 3
tables, the salesperson table which contains the
salesperson's number and a joblevel column. The joblevel
you can think of 10 = manager and 5 = worker. There are
more levels but just assume the higher the number the
higher the level of the person. Next is the customer
table. After that is the customer to salesrep assignment
table. If you read the first insert, we insert for
customer 779 the rep 5000 who happens to be a manager, a
date of 1/1/04, and an ownership of 1 (100%). That means
that the rep 5000 would get 100% of his bonus based on the
revenue that customer 779 generates. Next you see for the
same customer 5002 (a subordinate) is inserted. After
that you see at 3/1/04 a new subordinate level person
(level = 5) gets inserted. This means that at 1/1/04
subordinate 5002 is assigned to cusomter 779 but at 3/1/04
subordinate 5003 is assigned to customer 779. Since no
subordinate is assigned at 7/1/04, then we conclude that
5003 is still assigned to 779 at that date.
Skipping down we can se for customer 716 that 2 level=5
subordinates are assigned but one's ownership is 40% and
the other's is 60%. the next month 2 new subordinates are
rotated in. Don't worry about the insert logic to this
table. If 2 subordinates are split on a customer and one
gets replaced the next month, the other gets replaced too
so that in one month the total for each joblevel = 1 (100%)
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
create table customer (custno int not null primary key)
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership int check (ownership between 0 and 1),
primary key (custno, salesrepno, dateassigned))
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004')
insert customer_salesrep values (879, 5002, '1/1/2004')
insert customer_salesrep values (879, 5001, '7/1/2004')
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
I want to run a query where I pass in the manager level
number (in this case 5000) and a date. I want to know who
(the subordinate) is assigned to the customer at any date
I pass in. If another manager is assigned to a customer
(like 5009) then I don't want to see the customer
information for him.
If I run the query at 1/1/2004 for the manager 5000, I
should get in the results
779 5002 1
716 5005 .4
716 5006 .6
879 5002 1
If I run the query at 3/1/2004 for the manager 5000, I
should get in the results
779 5003 1
716 5007 .4
716 5008 .6
879 5002 1
If I run the query at 7/1/2004 for the manager 5000, I
should get in the results (notice customer 879 drops off
because another manager takes over the relationship)
779 5003 1
213 5004 1
716 5007 .4
716 5008 .6
Please let me know if you need any more information"Jerry Fortaine" <anonymous@.discussions.microsoft.com> wrote in message
news:26b001c50d4d$8823af10$a401280a@.phx.gbl...
>I need help with determining which salespeople should be
> assigned bonuses based off a relationship between the
> salesperson and their manager and also the relationship
> between the salesperson and the customer. Below are 3
> tables, the salesperson table which contains the
> salesperson's number and a joblevel column. The joblevel
> you can think of 10 = manager and 5 = worker. There are
> more levels but just assume the higher the number the
> higher the level of the person. Next is the customer
> table. After that is the customer to salesrep assignment
> table. If you read the first insert, we insert for
> customer 779 the rep 5000 who happens to be a manager, a
> date of 1/1/04, and an ownership of 1 (100%). That means
> that the rep 5000 would get 100% of his bonus based on the
> revenue that customer 779 generates. Next you see for the
> same customer 5002 (a subordinate) is inserted. After
> that you see at 3/1/04 a new subordinate level person
> (level = 5) gets inserted. This means that at 1/1/04
> subordinate 5002 is assigned to cusomter 779 but at 3/1/04
> subordinate 5003 is assigned to customer 779. Since no
> subordinate is assigned at 7/1/04, then we conclude that
> 5003 is still assigned to 779 at that date.
> Skipping down we can se for customer 716 that 2 level=5
> subordinates are assigned but one's ownership is 40% and
> the other's is 60%. the next month 2 new subordinates are
> rotated in. Don't worry about the insert logic to this
> table. If 2 subordinates are split on a customer and one
> gets replaced the next month, the other gets replaced too
> so that in one month the total for each joblevel = 1 (100%)
> create table salesrep (salesrepno int not null primary
> key, joblevel int not null)
> insert salesrep values (5000, 10)
> insert salesrep values (5001, 10)
> insert salesrep values (5002, 5)
> insert salesrep values (5003, 5)
> insert salesrep values (5004, 5)
> insert salesrep values (5005, 5)
> insert salesrep values (5006, 5)
> insert salesrep values (5007, 5)
> insert salesrep values (5008, 5)
> insert salesrep values (5009, 10)
> insert salesrep values (5010, 5)
> create table customer (custno int not null primary key)
> insert customer values (234)
> insert customer values (332)
> insert customer values (213)
> insert customer values (716)
> insert customer values (879)
> insert customer values (267)
> create table customer_salesrep (
> custno int not null foreign key references customer,
> salesrepno int not null foreign key references salesrep,
> dateassigned datetime not null,
> ownership int check (ownership between 0 and 1),
> primary key (custno, salesrepno, dateassigned))
> insert customer_salesrep values (779, 5000, '1/1/2004', 1)
> insert customer_salesrep values (779, 5002, '1/1/2004', 1)
> insert customer_salesrep values (779, 5003, '3/1/2004', 1)
> insert customer_salesrep values (332, 5000, '1/1/2004', 1)
> insert customer_salesrep values (213, 5000, '7/1/2004', 1)
> insert customer_salesrep values (213, 5004, '7/1/2004', 1)
> insert customer_salesrep values (716, 5000, '1/1/2004', 1)
> insert customer_salesrep values (716, 5005, '1/1/2004', .4)
> insert customer_salesrep values (716, 5006, '1/1/2004', .6)
> insert customer_salesrep values (716, 5007, '3/1/2004', .4)
> insert customer_salesrep values (716, 5008, '3/1/2004', .6)
> insert customer_salesrep values (879, 5000, '1/1/2004')
> insert customer_salesrep values (879, 5002, '1/1/2004')
> insert customer_salesrep values (879, 5001, '7/1/2004')
> insert customer_salesrep values (267, 5009, '1/1/2004', 1)
> insert customer_salesrep values (267, 5010, '1/1/2004', 1)
> I want to run a query where I pass in the manager level
> number (in this case 5000) and a date. I want to know who
> (the subordinate) is assigned to the customer at any date
> I pass in. If another manager is assigned to a customer
> (like 5009) then I don't want to see the customer
> information for him.
>
> If I run the query at 1/1/2004 for the manager 5000, I
> should get in the results
> 779 5002 1
> 716 5005 .4
> 716 5006 .6
> 879 5002 1
>
> If I run the query at 3/1/2004 for the manager 5000, I
> should get in the results
> 779 5003 1
> 716 5007 .4
> 716 5008 .6
> 879 5002 1
> If I run the query at 7/1/2004 for the manager 5000, I
> should get in the results (notice customer 879 drops off
> because another manager takes over the relationship)
> 779 5003 1
> 213 5004 1
> 716 5007 .4
> 716 5008 .6
> Please let me know if you need any more information
-- Customer and sales rep along with job level of rep
CREATE VIEW AccountReps (custno, salesrepno, joblevel, dateassigned, ownersh
ip)
AS
SELECT CSR.custno, CSR.salesrepno, SR.joblevel, CSR.dateassigned, CSR.owners
hip
FROM customer_salesrep AS CSR
INNER JOIN
salesrep AS SR
ON CSR.salesrepno = SR.salesrepno
-- For each customer account, all overlaps between a manager's and
-- worker's tenure
CREATE VIEW AccountAssignments
(custno, manager, manager_ownership,
manager_dateassigned, manager_dateunassigned,
worker, worker_ownership, worker_dateassigned, worker_dateunassigned)
AS
SELECT M.custno, M.salesrepno, M.ownership,
M.dateassigned, M.dateunassigned,
W.salesrepno, W.ownership, W.dateassigned, W.dateunassigned
FROM (SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R1.joblevel = R2.joblevel AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel = 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS M
INNER JOIN
(SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R2.joblevel < 10 AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel < 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS W
ON W.dateassigned < M.dateunassigned AND
W.dateunassigned > M.dateassigned AND
W.custno = M.custno
-- For manager 5000 on 20040101
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040101' AND
worker_dateunassigned > '20040101' AND
manager_dateassigned <= '20040101' AND
manager_dateunassigned > '20040101'
ORDER BY custno, worker
-- For manager 5000 on 20040301
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040301' AND
worker_dateunassigned > '20040301' AND
manager_dateassigned <= '20040301' AND
manager_dateunassigned > '20040301'
ORDER BY custno, worker
-- For manager 5000 on 20040701
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040701' AND
worker_dateunassigned > '20040701' AND
manager_dateassigned <= '20040701' AND
manager_dateunassigned > '20040701'
ORDER BY custno, worker
-- Note that we can check assignments on an arbitrary date
-- For manager 5000 on 20040515
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040515' AND
worker_dateunassigned > '20040515' AND
manager_dateassigned <= '20040515' AND
manager_dateunassigned > '20040515'
ORDER BY custno, worker
JAG|||Hi Jerry. I see that John already posted an answer to your question. I
took a look and came up with something a bit different. I think that the
problem lies in that you have relationships and data that are implicit in
your table schema. It may make for faster queries if you explicitly set an
owning manager to each line item, rather than calculate it each time you ran
your query. Right now this ownership is burried in the ordering of the
rows which is a bit tricky. It may be possible to create an indexed view
with this information in it. Maintaining "reportsto" relationships would
also make the queries a bit cleaner.
Here is my attempt:
--use master
--go
--DROP DATABASE complexquery
--go
create database complexquery
go
use complexquery
go
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
go
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
go
--delete from salesrep
create table customer (custno int not null primary key)
go
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
insert customer values (779) -- bug!bug! added value
go
--drop table customer_salesrep
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership decimal(5,2) check (ownership between 0 and 1), -- bug!bug! int
truncation, changed to decimal
primary key (custno, salesrepno, dateassigned))
go
delete from customer_salesrep
go
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004', 1)
insert customer_salesrep values (879, 5002, '1/1/2004', 1)
insert customer_salesrep values (879, 5001, '7/1/2004', 1)
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
go
--drop function utvf_whichboss
CREATE FUNCTION utvf_whichboss(@.lookupdate datetime) returns table as return
--
-- This function returns the list of customer accounts
-- that are owned by a given manager at any time. Caller
-- still needs to find the maximum dateassigned since all
-- previous owning managers will also show up.
SELECT
csr.salesrepno AS mgrno, csr.dateassigned, csr.custno
FROM
customer_salesrep csr
JOIN salesrep sr ON sr.salesrepno = csr.salesrepno
WHERE
sr.joblevel = 10
AND dateassigned <= @.lookupdate
GO
--drop function utvf_subs
CREATE FUNCTION utvf_subs( @.mgrno int, @.lookupdate datetime) RETURNS TABLE
AS RETURN
--
-- this function returns all of the rows that match a given mgrno and within
-- the lookupdate range. Caller still must find the max(dateassigned) since
-- it does not track duplicates (change of ownership)
SELECT csr2.custno, csr2.salesrepno, csr2.ownership, csr2.dateassigned
FROM
customer_salesrep csr2
join salesrep sr2 on sr2.salesrepno = csr2.salesrepno
WHERE csr2.custno IN
(
SELECT b.custno
FROM utvf_whichboss(@.lookupdate) b
WHERE b.dateassigned = (
SELECT max(dateassigned)
FROM utvf_whichboss(@.lookupdate)
WHERE custno = b.custno
)
AND b.mgrno = @.mgrno
)
AND sr2.joblevel = 5
AND csr2.dateassigned <= @.lookupdate
GO
--drop procedure usp_getsubs
CREATE PROCEDURE usp_getsubs
@.mgrno INT,
@.lookupdate DATETIME
AS
--
-- This sproc takes all of the rows that belong to
-- a given manager at a certain time and prunes out the
-- duplicates (i.e. customers who's owning subordinate have
-- changed.) It should only return the current subordinate
SELECT s.custno, s.salesrepno, s.ownership
FROM utvf_subs(@.mgrno, @.lookupdate) s
WHERE
s.dateassigned = (
SELECT MAX(dateassigned)
FROM utvf_subs(@.mgrno, @.lookupdate)
WHERE custno = s.custno
)
ORDER BY s.dateassigned
GO
-- Sample tvf output: note that all of the
-- rows are present, even though the ownership
-- has changed to a new subordinate.
SELECT * FROM utvf_subs(5000, '3/1/2004')
GO
-- sample sproc output: these are the samples in your
-- original mail. They all output as you described
-- but I could not preserve your ordering since it does
-- not appear to be encoded in your data anywhere
EXEC usp_getsubs 5000, '1/1/2004'
GO
EXEC usp_getsubs 5000, '3/1/2004'
GO
EXEC usp_getsubs 5000, '7/1/2004'
GO

4/7/2003 vs 04/07/2003

Hi,
I have a parameter query in SQL. The Query selects records in a date range based on the parameters.

my problem is that if I put the dates in the format of 4/7/2003 it won't work and I have to insert the date like 04/07/2003.

I don't know how to fix this. Its important since the parameters are coming froman Access ADP front end through the calendar object which saves the date in the 4/7/2003 format.

Thanksplease post an example or your query!|||Thanks for your time Paul.

Here is my query:
-----------------------
SELECT
dbo.tblComIssue.ComID AS ID,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, ISNULL(dbo.tblContact.FirstName, '') + ' '
+ ISNULL(dbo.tblContact.LastName, '') AS Caller

FROM dbo.tblComIssue LEFT OUTER JOIN
dbo.tblContact ON
dbo.tblComIssue.ContactID = dbo.tblContact.ContactID

WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) BETWEEN @.Date1 AND @.Date2)
ORDER BY dbo.tblComIssue.ComDate DESC

-----------
I also tried this instead of the "BETWEEN" function:

WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) >= @.Date1) AND (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) <= @.Date2)
ORDER BY dbo.tblComIssue.ComDate DESC
-----------

The wired part is that I have another query for "Date" not Date Range and it works fine:

SELECT dbo.tblComIssue.ComID, CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, ISNULL(dbo.tblContact.FirstName, '')
+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS Caller
FROM dbo.tblComIssue LEFT OUTER JOIN
dbo.tblContact ON dbo.tblComIssue.ContactID = dbo.tblContact.ContactID
WHERE (CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) = @.Date)
ORDER BY dbo.tblComIssue.ComDate DESC

------

Thanks|||It looks like your problem is comparing strings not dates. I would convert @.Date1 & @.Date2 to datetime datatype and compare dates.|||Thanks,
Yes that was the problem.
I used Enterprise manages to define the data types.

Thanks paul

4 processors, 8 Gigs RAM, 22 Seconds to process report?

I have a table report based on a single 50,000 row table. The report
doesn't do anything unusual, but there are 5 grouping levels. The
ExecutionLog table shows the report took < 1 second to pull the data,
but 22 seconds to process the report.
Given that my high end server can perform millions of calcs per
second, does anyone know why my report would take so long to process?
The error logs show nothing unusual.
Thanks,
BurtWhat format are you using? From the execution log, where is most of the time
spent, processing, rendering?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> I have a table report based on a single 50,000 row table. The report
> doesn't do anything unusual, but there are 5 grouping levels. The
> ExecutionLog table shows the report took < 1 second to pull the data,
> but 22 seconds to process the report.
> Given that my high end server can perform millions of calcs per
> second, does anyone know why my report would take so long to process?
> The error logs show nothing unusual.
> Thanks,
> Burt|||Hi Tudor,
The format is HTML. Both rendering and the data pull from SQL Server
take less than a second. The report processing takes 22 seconds.
If I use an AS OLAP cube as the data source, the data pull takes 10
seconds, but the report still takes ~22 seconds to process.
Any thoughts?
Thanks,
Burt
"Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> What format are you using? From the execution log, where is most of the time
> spent, processing, rendering?
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > I have a table report based on a single 50,000 row table. The report
> > doesn't do anything unusual, but there are 5 grouping levels. The
> > ExecutionLog table shows the report took < 1 second to pull the data,
> > but 22 seconds to process the report.
> >
> > Given that my high end server can perform millions of calcs per
> > second, does anyone know why my report would take so long to process?
> > The error logs show nothing unusual.
> >
> > Thanks,
> >
> > Burt|||Just to make sure, are you using filters in your report? Filters bring over
all the data (in your case 50,000 rows) prior to doing anything with the
data. Then it works on the data (doing the filtering, grouping etc).
Bruce L-C
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> Hi Tudor,
> The format is HTML. Both rendering and the data pull from SQL Server
> take less than a second. The report processing takes 22 seconds.
> If I use an AS OLAP cube as the data source, the data pull takes 10
> seconds, but the report still takes ~22 seconds to process.
> Any thoughts?
> Thanks,
> Burt
>
> "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > What format are you using? From the execution log, where is most of the
time
> > spent, processing, rendering?
> >
> > --
> > Tudor Trufinescu
> > Dev Lead
> > Sql Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Burt" <burt_5920@.yahoo.com> wrote in message
> > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > I have a table report based on a single 50,000 row table. The report
> > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > ExecutionLog table shows the report took < 1 second to pull the data,
> > > but 22 seconds to process the report.
> > >
> > > Given that my high end server can perform millions of calcs per
> > > second, does anyone know why my report would take so long to process?
> > > The error logs show nothing unusual.
> > >
> > > Thanks,
> > >
> > > Burt|||Thanks, Bruce, but no, no filters. Some additional info:
-There are actually only 18K rows in the table, not 50K, and 6 groups,
not 5.
-A couple of guys on the RS dev team from MS were kind enough to take
a look at the rdl, but didn't spot anything unusual.
-I created a similar 6 group report in MS Access, which took only a
couple of seconds to create.
-I have a 600K zip file with the RDL, table DDL, and actual table data
if anyone wants to play with it.
Thanks,
Burt
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> Just to make sure, are you using filters in your report? Filters bring over
> all the data (in your case 50,000 rows) prior to doing anything with the
> data. Then it works on the data (doing the filtering, grouping etc).
> Bruce L-C
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > Hi Tudor,
> >
> > The format is HTML. Both rendering and the data pull from SQL Server
> > take less than a second. The report processing takes 22 seconds.
> >
> > If I use an AS OLAP cube as the data source, the data pull takes 10
> > seconds, but the report still takes ~22 seconds to process.
> >
> > Any thoughts?
> >
> > Thanks,
> >
> > Burt
> >
> >
> > "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
> news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > What format are you using? From the execution log, where is most of the
> time
> > > spent, processing, rendering?
> > >
> > > --
> > > Tudor Trufinescu
> > > Dev Lead
> > > Sql Server Reporting Services
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > I have a table report based on a single 50,000 row table. The report
> > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > ExecutionLog table shows the report took < 1 second to pull the data,
> > > > but 22 seconds to process the report.
> > > >
> > > > Given that my high end server can perform millions of calcs per
> > > > second, does anyone know why my report would take so long to process?
> > > > The error logs show nothing unusual.
> > > >
> > > > Thanks,
> > > >
> > > > Burt|||I don't think this would make a difference but how hard would it be to start
anew with the report. I have had issues where I have added a group, removed
it etc and everything did not remove from the rdl. Just grasping at straws
here. I would think MS would definitely be interested in what you have here.
Bruce L-C
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408250907.43140876@.posting.google.com...
> Thanks, Bruce, but no, no filters. Some additional info:
> -There are actually only 18K rows in the table, not 50K, and 6 groups,
> not 5.
> -A couple of guys on the RS dev team from MS were kind enough to take
> a look at the rdl, but didn't spot anything unusual.
> -I created a similar 6 group report in MS Access, which took only a
> couple of seconds to create.
> -I have a 600K zip file with the RDL, table DDL, and actual table data
> if anyone wants to play with it.
> Thanks,
> Burt
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> > Just to make sure, are you using filters in your report? Filters bring
over
> > all the data (in your case 50,000 rows) prior to doing anything with the
> > data. Then it works on the data (doing the filtering, grouping etc).
> >
> > Bruce L-C
> >
> > "Burt" <burt_5920@.yahoo.com> wrote in message
> > news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > > Hi Tudor,
> > >
> > > The format is HTML. Both rendering and the data pull from SQL Server
> > > take less than a second. The report processing takes 22 seconds.
> > >
> > > If I use an AS OLAP cube as the data source, the data pull takes 10
> > > seconds, but the report still takes ~22 seconds to process.
> > >
> > > Any thoughts?
> > >
> > > Thanks,
> > >
> > > Burt
> > >
> > >
> > > "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
> > news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > > What format are you using? From the execution log, where is most of
the
> > time
> > > > spent, processing, rendering?
> > > >
> > > > --
> > > > Tudor Trufinescu
> > > > Dev Lead
> > > > Sql Server Reporting Services
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > > I have a table report based on a single 50,000 row table. The
report
> > > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > > ExecutionLog table shows the report took < 1 second to pull the
data,
> > > > > but 22 seconds to process the report.
> > > > >
> > > > > Given that my high end server can perform millions of calcs per
> > > > > second, does anyone know why my report would take so long to
process?
> > > > > The error logs show nothing unusual.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Burt|||Thanks, Bruce. I've actually created a few versions of the report with
similar results.
Per MS, RS is taking a (session) snapshot of the report and saving it
as they process the report, this is the most time consuming part of
this particular report.
Something tells me as their engine matures these reports will get
faster...
Burt
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<OjEnXBtiEHA.3972@.tk2msftngp13.phx.gbl>...
> I don't think this would make a difference but how hard would it be to start
> anew with the report. I have had issues where I have added a group, removed
> it etc and everything did not remove from the rdl. Just grasping at straws
> here. I would think MS would definitely be interested in what you have here.
> Bruce L-C
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408250907.43140876@.posting.google.com...
> > Thanks, Bruce, but no, no filters. Some additional info:
> >
> > -There are actually only 18K rows in the table, not 50K, and 6 groups,
> > not 5.
> >
> > -A couple of guys on the RS dev team from MS were kind enough to take
> > a look at the rdl, but didn't spot anything unusual.
> >
> > -I created a similar 6 group report in MS Access, which took only a
> > couple of seconds to create.
> >
> > -I have a 600K zip file with the RDL, table DDL, and actual table data
> > if anyone wants to play with it.
> >
> > Thanks,
> >
> > Burt
> >
> >
> >
> >
> > "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> > > Just to make sure, are you using filters in your report? Filters bring
> over
> > > all the data (in your case 50,000 rows) prior to doing anything with the
> > > data. Then it works on the data (doing the filtering, grouping etc).
> > >
> > > Bruce L-C
> > >
> > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > > > Hi Tudor,
> > > >
> > > > The format is HTML. Both rendering and the data pull from SQL Server
> > > > take less than a second. The report processing takes 22 seconds.
> > > >
> > > > If I use an AS OLAP cube as the data source, the data pull takes 10
> > > > seconds, but the report still takes ~22 seconds to process.
> > > >
> > > > Any thoughts?
> > > >
> > > > Thanks,
> > > >
> > > > Burt
> > > >
> > > >
> > > > "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
> news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > > > What format are you using? From the execution log, where is most of
> the
> time
> > > > > spent, processing, rendering?
> > > > >
> > > > > --
> > > > > Tudor Trufinescu
> > > > > Dev Lead
> > > > > Sql Server Reporting Services
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > > >
> > > > >
> > > > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > > > I have a table report based on a single 50,000 row table. The
> report
> > > > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > > > ExecutionLog table shows the report took < 1 second to pull the
> data,
> > > > > > but 22 seconds to process the report.
> > > > > >
> > > > > > Given that my high end server can perform millions of calcs per
> > > > > > second, does anyone know why my report would take so long to
> process?
> > > > > > The error logs show nothing unusual.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Burt