
CREATE SNAPSHOT SNAP_PREPREQUESTS 
REFRESH	COMPLETE NEXT SYSDATE + 1 AS
SELECT 	DR.ID_REQUEST, 
	C.CLONENAME,
	C.SPECIESNAME,
	CD.CHROMOSOME,
	DS.STATUS,
	DS.STATUSDATE,
	G.ID_GLYSTOCK,
	CR.PRIORITY,
	P.DESCRIPTION
FROM	DNAREQUEST_STATUS DS, DNAPREP_REQUEST DR, GLYSTOCK G,
	CLONE C, CHROMOSOMEDICT CD, CLONE_PRIORITY CR, CLONEWELL W,
	CLONEPLATE P, CLONE_PROJECT CP, PROJECT_STATUS S
WHERE	NOT S.STATUS	IN (24,26)
AND	S.ISCURRENT	= 1
AND	S.PROJECTNAME	= CP.PROJECTNAME
AND	CP.CLONENAME	= C.CLONENAME
AND	P.ID_CLONEPLATE = W.ID_CLONEPLATE
AND	W.ID_GLYSTOCK	= G.ID_GLYSTOCK
AND	CD.ID_DICT (+)	= C.CHROMOSOME
AND	CR.IS_CURRENT	= 1
AND	CR.CLONENAME	= C.CLONENAME
AND	C.CLONENAME	= G.CLONENAME
AND	G.ID_GLYSTOCK	= DR.ID_GLYSTOCK
AND	DR.ID_REQUEST	= DS.ID_REQUEST
AND	DS.STATUS	IN (1,3,6)
AND	DS.ISCURRENT	= 1
;


## Use this one to determine clones streaked to day, Then subtract from total
	
SELECT	P.DESCRIPTION, COUNT(CC.ID_REQUEST)
FROM 	CLONE_CULTURE CC, CLONEWELL W, CLONEPLATE P
WHERE	P.ID_CLONEPLATE	= W.ID_CLONEPLATE
AND	W.ID_GLYSTOCK	= CC.ID_GLYSTOCK
AND	TO_CHAR(CC.DATE_STREAKED, 'DD-MON-YYYY') = TO_CHAR(SYSDATE,'DD-MON-YYYY')
GROUP BY P.DESCRIPTION
;






SELECT	DESCRIPTION Grid,
	COUNT(DECODE(STATUS,1,1,NULL)) New,
	COUNT(DECODE(STATUS,3,1,NULL)) Recp,
	COUNT(DECODE(STATUS,6,1,NULL)) QC,
	COUNT(ID_REQUEST) Total,
	COUNT(DECODE(PRIORITY,0,NULL,1,NULL,1)) Priority
FROM	SNAP_PREPREQUESTS
GROUP BY DESCRIPTION
ORDER BY Priority, Grid
;
	
