2 Attachment(s)
how to count records entered in my table
Hi All ,
I have this table name DQC3422
code below is to show you what the data of table .
Code:
SELECT TEST_DTTM, MODEL,Location as DEVICE_NO,MO_SN,Def_item,station FROM DQC342 WHERE STATION = 'PCA ICT' and
LOCATION='ICTNO21'and TEST_DTTM BETWEEN TO_DATE('20180620','yyyymmdd') AND TO_DATE('20180620 235959','yyyymmdd hh24miss')
sample output below: this can have thousands or hundred thousands records . depends on date query.
TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 8:02 PMPU75 ICTNO21 S41M582306067 FAIL PCA ICT
6/20/2018 8:11 PMPU75 ICTNO21 S41M582407291 OK PCA ICT
6/20/2018 8:12 PMPU75 ICTNO21 S41M5823064AA OK PCA ICT
6/20/2018 8:13 PMPU75 ICTNO21 S41M58240748C OK PCA ICT
6/20/2018 8:13 PMPU75 ICTNO21 S41M582407584 OK PCA ICT
6/20/2018 8:14 PMPU75 ICTNO21 S41M58240749B OK PCA ICT
6/20/2018 8:15 PMPU75 ICTNO21 S41M5824075CA OK PCA ICT
6/20/2018 8:15 PMPU75 ICTNO21 S41M5824074D2 OK PCA ICT
6/20/2018 8:16 PMPU75 ICTNO21 S41M5824074D3 OK PCA ICT
6/20/2018 8:17 PMPU75 ICTNO21 S41M5824074E6 OK PCA ICT
6/20/2018 8:18 PMPU75 ICTNO21 S41M5824074D1 OK PCA ICT
6/20/2018 8:19 PMPU75 ICTNO21 S41M5824074CE OK PCA ICT
6/20/2018 8:21 PMPU75 ICTNO21 S41M582306067 OK PCA ICT
6/20/2018 8:22 PMPU75 ICTNO21 S41M582407432 OK PCA ICT
6/20/2018 8:24 PMPU75 ICTNO21 S41M5824074F0 OK PCA ICT
6/20/2018 8:25 PMPU75 ICTNO21 S41M5824074D0 OK PCA ICT
6/20/2018 8:26 PMPU75 ICTNO21 S41M582407572 OK PCA ICT
6/20/2018 8:27 PMPU75 ICTNO21 S41M5824074DF OK PCA ICT
6/20/2018 8:28 PMPU75 ICTNO21 S41M582407573 OK PCA ICT
6/20/2018 8:29 PMPU75 ICTNO21 S41M5824074F8 OK PCA ICT
6/20/2018 8:29 PMPU75 ICTNO21 S41M582407226 OK PCA ICT
6/20/2018 8:30 PMPU75 ICTNO21 S41M5824074D7 OK PCA ICT
6/20/2018 8:31 PMPU75 ICTNO21 S41M582407439 OK PCA ICT
6/20/2018 8:32 PMPU75 ICTNO21 S41M582402E42 OK PCA ICT
6/20/2018 8:33 PMPU75 ICTNO21 S41M582402E41 OK PCA ICT
6/20/2018 8:34 PMPU75 ICTNO21 S41M58240735D OK PCA ICT
6/20/2018 8:35 PMPU75 ICTNO21 S41M5824072A2 FAIL PCA ICT
Details of data as below:
1. test_dttm(date and time of test)
2. Model(10 different model)-can be test in any device.
3. DEVICE_NO(5 device no.21-25)- 1 device can test more than 1 model.
4. MO_SN(serial no of each items)- Users scan all serial no. of items to system and add in database.
5. DEF_ITEM(items has 2 results OK & FAIL),- has 3 conditions.
5.1. first test Pass = OK.
5.2. first test Fail and second test Pass = OK but both serial number
is kept in database with different result so this are called RE-TEST.
5.3. first test fail ,second test fail and third test fail = FAIL called TRUE FAIL. will fall in column FAIL.
this are the serial number that has Def_Item = FAil only, no have OK/Pass.
I want to count all that data, OK,RETEST and Fail.
6. STATION(5 station but I focus to 1 station PCA ICT)
what i want to accomplish is to count the following for 1 day:
[B]1. INPUT [/B]
I used code below to count INPUT .
these are the total items inputted by users, counting Distinct only, no duplicates.
Code:
SELECT MODEL,station,location,count(distinct MO_SN) as TOTAL_INPUT from dqc342 where station='PCA ICT' and TEST_DTTM BETWEEN TO_DATE('20180620','yyyymmdd') AND TO_DATE('20180620 235959','yyyymmdd hh24miss')
and location='ICTNO21'
group by model,station,location
[B]2. OK [/B]
this are the MO_SN no duplicate & DEF_ITEM = OK.
Example:
TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 0:23 PMPG18 ICTNO21 SOTR582407395 OK PCA ICT
6/20/2018 0:23 PMPG18 ICTNO21 SOTR582407399 OK PCA ICT
6/20/2018 0:23 PMPG18 ICTNO21 SOTR582407398 OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR582407394 OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR58240739A OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR58240739E OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR582407396 OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR5825002CE OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR5825002CD OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR5825002CB OK PCA ICT
[B]3. FAIL [/B]
this are the MO_SN with no duplicate & DEF_ITEM = FAIL.
example:
TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 3:52 PMPG18 ICTNO21 SOTR5824047F3 FAIL PCA ICT
6/20/2018 4:20 PMPG18 ICTNO21 SOTR582407320 FAIL PCA ICT
6/20/2018 14:17 PMPG18 ICTNO21 SOTR582407057 FAIL PCA ICT
6/20/2018 14:54 PMPG18 ICTNO21 SOTR582405604 FAIL PCA ICT
6/20/2018 15:46 PMPG18 ICTNO21 SOTR580403F29 FAIL PCA ICT
6/20/2018 15:46 PMPG18 ICTNO21 SOTR580403F2A FAIL PCA ICT
[B]4.RE-TEST [/B]
these are the MO_SN with DUPLICATE & DEF_ITEM are 1 = OK and 1 = FAIL.
example:
TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 0:34 PMPG18 ICTNO21 SOTR582500580 FAIL PCA ICT
6/20/2018 0:35 PMPG18 ICTNO21 SOTR582500580 OK PCA ICT
6/20/2018 0:35 PMPG18 ICTNO21 SOTR582500582 FAIL PCA ICT
6/20/2018 0:35 PMPG18 ICTNO21 SOTR582500582 OK PCA ICT
6/20/2018 0:42 PMPG18 ICTNO21 SOTR582407D77 FAIL PCA ICT
6/20/2018 0:43 PMPG18 ICTNO21 SOTR582407D77 OK PCA ICT
6/20/2018 0:44 PMPG18 ICTNO21 SOTR582407D60 FAIL PCA ICT
6/20/2018 0:45 PMPG18 ICTNO21 SOTR582407D60 OK PCA ICT
6/20/2018 0:47 PMPG18 ICTNO21 SOTR582407DA5 FAIL PCA ICT
[B]4.YIELD RATE [/B]
will be calculated using this formula 100-(Fail/Input)* 100
below will be my final output.
by the way i am using PL/SQL DEVELOPER.
thanks in advanced.
Attachment 159953
