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
>

Publish with RS gives HTTPS error

Your help is genuinely appreciated!
I'm having trouble using the example script file "PublishReports.rss".
At the command prompt I type in:
rs -i PublishReports.rss -s https://myserver/reportserver
and I get an error:
"The operation you are attempting requires a secure connection. (HTTPS)."
--= Posted using GrabIt =--
--= Binary Usenet downloading made easy =--
-= Get GrabIt for free from http://www.shemes.com/ =-Use https only if you have ssl installed on your server. Change it to be
http instead.
--
| From: "Greg Allan" <gregallan@.NOSPAMPLEASEtvl.com>
| Subject: Publish with RS gives HTTPS error
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Lines: 17
| Message-ID: <HA8Vd.19914$LN5.8021@.edtnps90>
| Date: Wed, 02 Mar 2005 01:23:51 GMT
| NNTP-Posting-Host: 207.216.242.138
| X-Trace: edtnps90 1109726631 207.216.242.138 (Tue, 01 Mar 2005 18:23:51
MST)
| NNTP-Posting-Date: Tue, 01 Mar 2005 18:23:51 MST
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.
sul.t-online.de!t-online.de!news.glorb.com!newsfeed2.telusplanet.net!newsfee
d.telus.net!edtnps90.POSTED!53ab2750!not-for-mail
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:44268
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Your help is genuinely appreciated!
|
| I'm having trouble using the example script file "PublishReports.rss".
|
| At the command prompt I type in:
| rs -i PublishReports.rss -s https://myserver/reportserver
|
| and I get an error:
| "The operation you are attempting requires a secure connection. (HTTPS)."
|
|
|
|
| --= Posted using GrabIt =--
| --= Binary Usenet downloading made easy =--
| -= Get GrabIt for free from http://www.shemes.com/ =-
|
||||Hi Brad,
Sorry, typo in my first message.
I do have SSL installed on my IIS machine and when I try to publish to
https:// it says it can't find the server.
If I publish to http:// is says "The operation you are attempting requires a
secure connection (HTTPS)"
""Brad Syputa - MS"" wrote:
> Use https only if you have ssl installed on your server. Change it to be
> http instead.
> --
> | From: "Greg Allan" <gregallan@.NOSPAMPLEASEtvl.com>
> | Subject: Publish with RS gives HTTPS error
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | Lines: 17
> | Message-ID: <HA8Vd.19914$LN5.8021@.edtnps90>
> | Date: Wed, 02 Mar 2005 01:23:51 GMT
> | NNTP-Posting-Host: 207.216.242.138
> | X-Trace: edtnps90 1109726631 207.216.242.138 (Tue, 01 Mar 2005 18:23:51
> MST)
> | NNTP-Posting-Date: Tue, 01 Mar 2005 18:23:51 MST
> | Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.
> sul.t-online.de!t-online.de!news.glorb.com!newsfeed2.telusplanet.net!newsfee
> d.telus.net!edtnps90.POSTED!53ab2750!not-for-mail
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:44268
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Your help is genuinely appreciated!
> |
> | I'm having trouble using the example script file "PublishReports.rss".
> |
> | At the command prompt I type in:
> | rs -i PublishReports.rss -s https://myserver/reportserver
> |
> | and I get an error:
> | "The operation you are attempting requires a secure connection. (HTTPS)."
> |
> |
> |
> |
> | --= Posted using GrabIt =--
> | --= Binary Usenet downloading made easy =--
> | -= Get GrabIt for free from http://www.shemes.com/ =-
> |
> |
>