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
>

Pulling in data from many tables.

I am trying to pull data from 4 tables. When I query them and us distinct
with three of them I can get the data I need. But when I try adding the
fourth one I get more rows then I need/or many dup roles. Want am I missing
or how can I only return want I need?Hi
Can you show us DDL+ sample data + expected result?
"DPassed" <DPassed@.discussions.microsoft.com> wrote in message
news:69A1A0B4-0F91-46C9-A4EB-B3338D9D1D06@.microsoft.com...
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I
missing
> or how can I only return want I need?|||Hi DPassed
You might have missed the Join Condition when u joined Table-4.
Just check your query once or just post the query that you are facing the
problem
thanks and regards
Chandra
"DPassed" wrote:
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I missing
> or how can I only return want I need?|||select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on rcmast.fpono = pomast.fpono
join poitem on rcmast.fpono = poitem.fpono
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
order by rcmast.fpono, rcitem.fitemno
"Chandra" wrote:
> Hi DPassed
> You might have missed the Join Condition when u joined Table-4.
> Just check your query once or just post the query that you are facing the
> problem
> thanks and regards
> Chandra
>
> "DPassed" wrote:
> > I am trying to pull data from 4 tables. When I query them and us distinct
> > with three of them I can get the data I need. But when I try adding the
> > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > or how can I only return want I need?|||Hi
Try this Query Now:
select
distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on rcmast.fpono = pomast.fpono
--CHANGED HERE
join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
--CHANGED HERE
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
order by rcmast.fpono, rcitem.fitemno
thanks and regards
chandra
"DPassed" wrote:
> select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on rcmast.fpono = pomast.fpono
> join poitem on rcmast.fpono = poitem.fpono
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> order by rcmast.fpono, rcitem.fitemno
>
> "Chandra" wrote:
> > Hi DPassed
> > You might have missed the Join Condition when u joined Table-4.
> >
> > Just check your query once or just post the query that you are facing the
> > problem
> >
> > thanks and regards
> > Chandra
> >
> >
> > "DPassed" wrote:
> >
> > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > with three of them I can get the data I need. But when I try adding the
> > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > or how can I only return want I need?|||Didn't change rows returned. I do thank you for your help.
"Chandra" wrote:
> Hi
> Try this Query Now:
> select
> distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on rcmast.fpono = pomast.fpono
> --CHANGED HERE
> join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> --CHANGED HERE
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> order by rcmast.fpono, rcitem.fitemno
>
> thanks and regards
> chandra
>
> "DPassed" wrote:
> > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > ,poitem.flstpdate, poitem.fordqty
> > from rcmast
> > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > left join pomast on rcmast.fpono = pomast.fpono
> > join poitem on rcmast.fpono = poitem.fpono
> >
> > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > order by rcmast.fpono, rcitem.fitemno
> >
> >
> > "Chandra" wrote:
> >
> > > Hi DPassed
> > > You might have missed the Join Condition when u joined Table-4.
> > >
> > > Just check your query once or just post the query that you are facing the
> > > problem
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > > "DPassed" wrote:
> > >
> > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > with three of them I can get the data I need. But when I try adding the
> > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > or how can I only return want I need?|||Hi,
Can you try this Now:
select
distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on pomast.fpono = rcmast.fpono
join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
regards
Chandra
"DPassed" wrote:
> Didn't change rows returned. I do thank you for your help.
> "Chandra" wrote:
> > Hi
> > Try this Query Now:
> >
> > select
> > distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > ,poitem.flstpdate, poitem.fordqty
> > from rcmast
> > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > left join pomast on rcmast.fpono = pomast.fpono
> > --CHANGED HERE
> > join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> > --CHANGED HERE
> > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> >
> > order by rcmast.fpono, rcitem.fitemno
> >
> >
> > thanks and regards
> > chandra
> >
> >
> > "DPassed" wrote:
> >
> > > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > ,poitem.flstpdate, poitem.fordqty
> > > from rcmast
> > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > left join pomast on rcmast.fpono = pomast.fpono
> > > join poitem on rcmast.fpono = poitem.fpono
> > >
> > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > > order by rcmast.fpono, rcitem.fitemno
> > >
> > >
> > > "Chandra" wrote:
> > >
> > > > Hi DPassed
> > > > You might have missed the Join Condition when u joined Table-4.
> > > >
> > > > Just check your query once or just post the query that you are facing the
> > > > problem
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > > "DPassed" wrote:
> > > >
> > > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > > with three of them I can get the data I need. But when I try adding the
> > > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > > or how can I only return want I need?|||Again thanks for your help, but didn't change the results. I have desided to
just us two of the tables and get as much info out of them as possible.
Thanks again, Have a great day!!
"Chandra" wrote:
> Hi,
> Can you try this Now:
> select
> distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on pomast.fpono = rcmast.fpono
> join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> regards
> Chandra
>
> "DPassed" wrote:
> > Didn't change rows returned. I do thank you for your help.
> >
> > "Chandra" wrote:
> >
> > > Hi
> > > Try this Query Now:
> > >
> > > select
> > > distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > ,poitem.flstpdate, poitem.fordqty
> > > from rcmast
> > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > left join pomast on rcmast.fpono = pomast.fpono
> > > --CHANGED HERE
> > > join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> > > --CHANGED HERE
> > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > >
> > > order by rcmast.fpono, rcitem.fitemno
> > >
> > >
> > > thanks and regards
> > > chandra
> > >
> > >
> > > "DPassed" wrote:
> > >
> > > > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > > ,poitem.flstpdate, poitem.fordqty
> > > > from rcmast
> > > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > > left join pomast on rcmast.fpono = pomast.fpono
> > > > join poitem on rcmast.fpono = poitem.fpono
> > > >
> > > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > > > order by rcmast.fpono, rcitem.fitemno
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > > Hi DPassed
> > > > > You might have missed the Join Condition when u joined Table-4.
> > > > >
> > > > > Just check your query once or just post the query that you are facing the
> > > > > problem
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > > "DPassed" wrote:
> > > > >
> > > > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > > > with three of them I can get the data I need. But when I try adding the
> > > > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > > > or how can I only return want I need?

PTS sp4 causes Distinct Count error

I have Analyisis Sevices service pack 3a. Office xp has been installed on the client machines and the server, which has installed PTS service pack 4. This has resulted in one of the calculated members showing as #1NF in Proclarity. It works perfectly well from client machines still running with PTS service pack 3. The calculated member in question uses a distinct count which is now always returning 0.

Does anyone have any suggestions? Thanks.

If the cube where it happens is a virtual cube - then it is a known regression in SP4 w.r.t. DistinctCount MDX function. There is a hotfix avaialable - please contact product support in order to obtain it.|||

Hello,

Yes it is a virtual cube. Is the hotfix for Analysis services sp4? The server is still on Analysis services sp3a but PTS got upgraded when office xp was installed.

Thanks for your help.

|||Yes, the hotfix is on top of SP4 and should be applied to PTS.

PTS sp4 causes Distinct Count error

I have Analyisis Sevices service pack 3a. Office xp has been installed on the client machines and the server, which has installed PTS service pack 4. This has resulted in one of the calculated members showing as #1NF in Proclarity. It works perfectly well from client machines still running with PTS service pack 3. The calculated member in question uses a distinct count which is now always returning 0.

Does anyone have any suggestions? Thanks.

If the cube where it happens is a virtual cube - then it is a known regression in SP4 w.r.t. DistinctCount MDX function. There is a hotfix avaialable - please contact product support in order to obtain it.|||

Hello,

Yes it is a virtual cube. Is the hotfix for Analysis services sp4? The server is still on Analysis services sp3a but PTS got upgraded when office xp was installed.

Thanks for your help.

|||Yes, the hotfix is on top of SP4 and should be applied to PTS.