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 Administration
 sql queries to provide the following

Author  Topic 

Davidmoss
Starting Member

2 Posts

Posted - 2013-12-05 : 02:40:37
I am new to SQL and need to write the following sql enq - can someone please help

Tables

Clients
FIELD_NAME FIELD_TYPE FIELD_LEN FIELD_DEC
CLIENT C 12 0
CLIENTNAME C 50 0
LIASON C 10 0

Dets
DET C 10 0
LAB C 1 0
DETNAME C 45 0
REPNAME C 50 0
DETREF C 10 0
UKAS L 1 0
DETORDER N 3 0
TEST C 10 0
SECTION C 14 0
DET C 10 0
LAB C 1 0
DETNAME C 45 0
REPNAME C 50 0
DETREF C 10 0
UKAS L 1 0


Jobs
JOB C 8 0
CLIENT C 12 0
JOBSTATUS C 0
NO_CHEM N 3 0
NO_MICRO N 3 0
LOGDATE D 8 0
LOGTIME C 4 0
DUEDATE D 8 0
REPDATE D 8 0





Results
SAMPNUM N 8 0
DET C 10 0
TEST C 10 0
SUITE C 10 0
UKAS L 1 0
INPUT C 15 0
OUTPUT C 15 0
DATATYPE C 1 0
GLTHAN C 1 0
RESULT N 15 4
DETORDER N 3 0
ANALYST C 3 0
CHECKEDBY C 3 0
DETDATE D 8 0
DETTIME C 4 0

Samples
CLIENT C 12 0
SAMPNUM N 8 0
JOB C 8 0
SAMPSTATUS C 0
LAB C 1 0
DUEDATE D 8 0
TURNAROUND N 2 0
LOGDATE D 8 0
LOGTIME C 4 0
LOGGEDBY C 3 0
REPDATE D 8 0
SUITE C 10 0
UKAS L 1 0
DEVIATING C 5 0








Work
SAMPNUM N 8 0
DET C 10 0
DILUTIONS C 10 0
TEST C 10 0
SUITE C 10 0
REPEAT L 1 0
SCHEDULED L 1 0
DETORDER N 3 0
ANALYST C 3 0
SECTION C 14 0
AQC_TYPE N 1 0
LOGDATE D 8 0
DEVIATING C 5 0

Davidmoss
Starting Member

2 Posts

Posted - 2013-12-05 : 02:48:39
Missed out what information I needed on my previous blog:

1) Number of Chemistry samples and the number of Micro samples per Client for each month for the last 12 months. Ensure Clientname is returned.

For micro samples Lab = B and Chemistry samples Lab = G

2) Average number of UKAS accredited results per sample for each client per month for the last 12 months. Again ensure Clientname is returned.

3) Number of each Det outstanding at present grouped by Duedate. Ensure that the Detname is returned.

4) Total number of outstanding dets for each client grouped by Duedate.

5) how many dets are completed for “ACTIVE” jobs and how many are still outstanding.
Go to Top of Page
   

- Advertisement -