Random Selection from criteria

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

General SQL Server Forums

New to SQL Server Programming

Random Selection from criteria

Author

Topic

davidredden1973
Starting Member

3 Posts

Posted - 2010-12-20 : 15:56:10

I have a query that returns 2 random records based on some criteria
it uses the following:


SELECT *
FROM (SELECT ID, PART_NUMBER, LOT_NUMBER,COMPLETED, COMPLETED_BY, COMPLETED_DATE, reportable, ROW_NUMBER () OVER (PARTITION BY COMPLETED_BY ORDER BY dbms_random.value) rn
from (select distinct (id), part_number, lot_number, completed, completed_by, completed_date, reportable
from table1
where completed date BETWEEN TRUNC (SYSDATE, 'IW') -7 AND TRUNC(SYSDATE, 'IW')
AND completed = 'Y')
WHERE rn <= 2

This returns the 2 random records for each "completed by" as needed.
However, now it is being requested to add a criteria to have at least 1 "reportable" and 1 "non-reportable" and if no "reportables" are found for that week then to return the normal 2 random selections.
I am sure this is done with a Case When statement; but I am at a loss.
If anyone can decypher what I've written here and can help me out, I would be forever grateful.

David Redden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-20 : 17:11:47

This doesn't look like Microsoft SQL Server (which is what SQLTeam supports).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

singularity
Posting Yak Master

153 Posts

Posted - 2010-12-20 : 19:09:59

Maybe something like this:


SELECT *
FROM (SELECT ID, PART_NUMBER, LOT_NUMBER,COMPLETED, COMPLETED_BY, COMPLETED_DATE, reportable, ROW_NUMBER () OVER (PARTITION BY COMPLETED_BY ORDER BY dbms_random.value) rn
from (select distinct (id), part_number, lot_number, completed, completed_by, completed_date, reportable
from table1
where completed date BETWEEN TRUNC (SYSDATE, 'IW') -7 AND TRUNC(SYSDATE, 'IW')
AND completed = 'Y' and reportable = 'Y')
WHERE rn = 1
UNION ALL
SELECT *
FROM (SELECT ID, PART_NUMBER, LOT_NUMBER,COMPLETED, COMPLETED_BY, COMPLETED_DATE, reportable, ROW_NUMBER () OVER (PARTITION BY COMPLETED_BY ORDER BY dbms_random.value) rn
from (select distinct (id), part_number, lot_number, completed, completed_by, completed_date, reportable
from table1
where completed date BETWEEN TRUNC (SYSDATE, 'IW') -7 AND TRUNC(SYSDATE, 'IW')
AND completed = 'Y' and reportable = 'N')
WHERE rn = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources