D
davidstevans
Hi gurus,
I have the following table structure with 50000 rows .
MAILDATE CURRENT OPERATOR YEAR. DAYS ERROR1 TYPE
4/15/1997 71400000000 ABC123 97 2
122 2
3/26/2003 71400000000 DEF432 02 5
222 3
5/18/2004 21300000000 XYX456 03 10
314 5
The TYPE column has the following possible codes
1,2,3,4,5,6,8
I want to answer the following questions with a sql queries for each.
1, What is the oldest maildate.
2, What is the oldest maildate with an operator assigned to it.
3, What is the oldest maildate without an operator assigned to it
4, What is the oldest maildate for the current year
5,What is the oldest maildate for the prior years(anything that is not
07)
6,What is the oldest maildate where data in CURRENT starts with 714.
7, How many records are between 0 to 30 days with an operator.
8, How many records are between 0 to 30 days without an operator
assigned to it
9.How many records are between 31 to 60 days with an operator assigned
to it
10, How many records are between 31 to 60 days without an operator
assigned to it
11,How many records are between 61 to 90 days with an operator
assigned to it
12, How many records are between 61 to 90 days without an operator
assigned to it
13,How many records are between 91 to 120 days without an operator
assigned to it
14, How many records are between 91 to 120 days without an operator
assigned to it
15,How many records are between 121 to 150 days with an operator
assigned to it
16,How many records are between 121 to 150 days without an operator
assigned to it
17,How many records are between 150 & older with an operator assigned
to it
18, How many records are between 150 & older without an operator
assigned to it
19,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is between 0
to 10 days
20,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is between
0 to 10 days
21,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is between 10
to 20 days
22,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is between
10 to 20 days
23,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is between 20
to 30 days
24,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is between
20 to 30 days
25,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is over 30
days
26,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is over
30 days
27. Percentage of data starting with 714 in the CURRENT column
againist all other data in the same column.
28, Top 3 error codes for data starting with 714 in the CURRENT
column
29,Top 3 error codes for data without starting with 714 in the
CURRENT column
30 , How old are the records for each TYPE(1=refunds,2=paid,3=part
paid,4=even return,5=noremit,6=data entry,8=garbage) where CURRENT
column has number that starts with 714
I have the following table structure with 50000 rows .
MAILDATE CURRENT OPERATOR YEAR. DAYS ERROR1 TYPE
4/15/1997 71400000000 ABC123 97 2
122 2
3/26/2003 71400000000 DEF432 02 5
222 3
5/18/2004 21300000000 XYX456 03 10
314 5
The TYPE column has the following possible codes
1,2,3,4,5,6,8
I want to answer the following questions with a sql queries for each.
1, What is the oldest maildate.
2, What is the oldest maildate with an operator assigned to it.
3, What is the oldest maildate without an operator assigned to it
4, What is the oldest maildate for the current year
5,What is the oldest maildate for the prior years(anything that is not
07)
6,What is the oldest maildate where data in CURRENT starts with 714.
7, How many records are between 0 to 30 days with an operator.
8, How many records are between 0 to 30 days without an operator
assigned to it
9.How many records are between 31 to 60 days with an operator assigned
to it
10, How many records are between 31 to 60 days without an operator
assigned to it
11,How many records are between 61 to 90 days with an operator
assigned to it
12, How many records are between 61 to 90 days without an operator
assigned to it
13,How many records are between 91 to 120 days without an operator
assigned to it
14, How many records are between 91 to 120 days without an operator
assigned to it
15,How many records are between 121 to 150 days with an operator
assigned to it
16,How many records are between 121 to 150 days without an operator
assigned to it
17,How many records are between 150 & older with an operator assigned
to it
18, How many records are between 150 & older without an operator
assigned to it
19,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is between 0
to 10 days
20,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is between
0 to 10 days
21,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is between 10
to 20 days
22,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is between
10 to 20 days
23,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is between 20
to 30 days
24,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is between
20 to 30 days
25,How many records are there where CURRENT column has number that
starts with 714 with an operator assigned to it and DAYS is over 30
days
26,How many records are there where CURRENT column has number that
starts with 714 without an operator assigned to it and DAYS is over
30 days
27. Percentage of data starting with 714 in the CURRENT column
againist all other data in the same column.
28, Top 3 error codes for data starting with 714 in the CURRENT
column
29,Top 3 error codes for data without starting with 714 in the
CURRENT column
30 , How old are the records for each TYPE(1=refunds,2=paid,3=part
paid,4=even return,5=noremit,6=data entry,8=garbage) where CURRENT
column has number that starts with 714