Please help with some sql ....

[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

 

[John W. Vinson]

I want to answer the following questions with a sql queries for each.

First give us some sort of assurance that you're not just trying to get your
homework done without the requirement of doing any thinking.

John W. Vinson [MVP]

 

[davidstevans]

First give us some sort of assurance that you're not just trying to get your
homework done without the requirement of doing any thinking.

John W. Vinson [MVP]

Ha Ha Very Funny,

No I am just a poor manager who's computer guy just quit and left us
hanging with month end reports.

So please help out a poor suffering about to pull his hair out guy..


thanks

 

[John Spencer]

In line:
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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.

SELECT Min(MailDate) FROM YourTable

2, What is the oldest maildate with an operator assigned to it.

SELECT Min(MailDate) FROM YourTable WHERE Operator is Not Null

3, What is the oldest maildate without an operator assigned to it

SELECT Max(MailDate) FROM YourTable WHERE Operator is Null

4, What is the oldest maildate for the current year

SELECT Min(MailDate) FROM YourTable WHERE [Year] = "07"

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.

SELECT Min(MailDate) FROM YourTable WHERE [CURRENT] Like "714*"

7, How many records are between 0 to 30 days with an operator.

SELECT Count(*) FROM YourTable WHERE [Days] Between 0 and 30 and
Operator is not Null

Vary the criteria for 8 to 16

18, How many records are between 150 & older without an operator
assigned to it

SELECT Count(*) FROM YourTable WHERE [Days] >=150 and Operator is
Null

27. Percentage of data starting with 714 in the CURRENT column
againist all other data in the same column.

SELECT Count(IIF(Current Like "714*",1,Null))/ Count(IIF(Current Not
Like "714*",1,Null)) as result FROM YourTable

28, Top 3 error codes for data starting with 714 in the CURRENT
column

SELECT Top 3 Error1 FROM YourTable WHERE Current like "714*" GROUP BY
Error1 ORDER BY Count(Error1)

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

How do you decide how old a record is? The first or last maildate? The
average number of days?