Pulling Random Distinct Percentage

Having trouble coming up with a fast and efficient sproc for this.
Thanks in advance for any help!
tblRecords
record_id (int) [key]
location_id (int)
owned_by_id (int) [default = 0]
is_complete (int) [default = 0]
date_entered (datetime) [default = getDate()]
date_completed (datetime)
I'm trying to populate a Datagrid with ((Total - total completed -
total owned) * 7%) of random non-complete records and all owned records
within a given date range for each site sorted by date_entered.
I figure I'll need to create a temp table holding all the totals
(total, total completed & total owned) for each site (using distinct
and count). And then for each row (or site) loop through the math
shown above and then select random top X where is_complete = 0 AND
owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
BETWEEN startDate AND stopDate. Then somehow merge the result... then
merge again with the looping table. Once complete sort table by
date_entered and return. I'm not sure if that is the best/easiest way
to do this... so I'm wondering if someone else knows a better way, and
can provide an example, as my SQL skills are not up to par for this.
Thanks again!
~GregHi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data. Also posting the required resultset from your data would be
helpful.
If your is_completed column can only have values of 0 or 1 then you could do
something like:
SELECT location_id, owned_by_id, SUM(is_complete) as Completed,
SUM(1-is_complete)AS incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
to get the number completed. You will need to add the date constriction in a
WHERE CLAUSE. If you can have other values you can do something like:
SELECT location_id, owned_by_id, SUM(CASE WHEN is_complete = 1 THEN 1 ELSE 0
END) as Completed, SUM(CASE WHEN is_complete = 0 THEN 1 ELSE 0 END)AS
incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
It is not clear if your owned_by_id is actually an is_owned binary value!.
You could use this query in a view or derived table instead of using a
temporary table.
HTH
John
"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136405669.374006.39790@.g44g2000cwa.googlegroups.com...

> Having trouble coming up with a fast and efficient sproc for this.
> Thanks in advance for any help!
> tblRecords
> record_id (int) [key]
> location_id (int)
> owned_by_id (int) [default = 0]
> is_complete (int) [default = 0]
> date_entered (datetime) [default = getDate()]
> date_completed (datetime)
> I'm trying to populate a Datagrid with ((Total - total completed -
> total owned) * 7%) of random non-complete records and all owned records
> within a given date range for each site sorted by date_entered.
> I figure I'll need to create a temp table holding all the totals
> (total, total completed & total owned) for each site (using distinct
> and count). And then for each row (or site) loop through the math
> shown above and then select random top X where is_complete = 0 AND
> owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
> Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
> BETWEEN startDate AND stopDate. Then somehow merge the result... then
> merge again with the looping table. Once complete sort table by
> date_entered and return. I'm not sure if that is the best/easiest way
> to do this... so I'm wondering if someone else knows a better way, and
> can provide an example, as my SQL skills are not up to par for this.
> Thanks again!
> ~Greg
>