dcsimg
Results 1 to 1 of 1
  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    41

    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.
    Name:  sssss.jpg
Views: 157
Size:  12.6 KB
    Attached Images Attached Images  

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width