SELECT Count & Select Query

[tonyfitz]

I am developing a recruitment web site. I have 2 tables.

Customers purchase Job Slots which last 12 months. A slot holds one job at a time. The table called PAYMENT holds information about the purchases of job slots made by customers. The table called JOBS shows the jobs posted by the customer to my site and includes a field called "slot used" which referrs to the job slot that the job has been posted into

Table: PAYMENT
Slot Ref username slots Date purchased Date expire
56 ABC Co 10 01 Dec 2003 31 Nov 2004
57 XYZ Co 5 16 Apr 2003 15 Apr 2004
58 ABC Co 2 04 Jun 2003 03 Jun 2004

Table: JOBS
username job name slot used
ABC Co Manager 56
ABC Co Storeman 56
XYZ Co Engineer 57
ABC Co Secretary 56
ABC Co Driver 58

How do I structure a SELECT query to show how many slots a customer (ie ABC Co) has purchased, used and left available to it with the appropriate expiry dates.

In the above example ABC Co has posted 4 Jobs to the web site. It has purchased 12 job Slots into which to post these jobs. It can therefore post a further 8 jobs. I want to be able to show ABC Co the following information

Slots Jobs posted to Slots Slot Expiry
purchased to this slot Available Date
10 3 7 31 Nov 2004
2 1 1 3 Jun 2004

Thanks for your help

Tony

 

[DanChard]

I've written two possible solutions for this problem. The first (below) should give you the details you want for any particular customer but you will have to manually enter the customer's name in 3 places:

select username, slots as [Slots Purchased],
(select count(*)
from JOBS
where username = 'ABC Co') as [Jobs Posted],
slots-(select count(*)
from JOBS
where username = 'ABC Co') as [Slots Available],
[Date expire]
from PAYMENT
where username = 'ABC Co';

Could be a bit of a pain.

The other solution, which I'm not entirely sure on, is as follows:

select username, slots as [Slots Purchased],
(select count(*)
from JOBS
where username = PAYMENT.username) as [Jobs Posted],
slots-(select count(*)
from JOBS
where username = PAYMENT.username) as [Slots Available],
[Date expire]
from PAYMENT
order by username;

The difference is subtle but hopefully this query should return the details you want for all companies. You can then pick whichever company you want the details for or add the line "where PAYMENT.username = 'ABC Co'" just before the 'order by' line.

Hope this is of some use, please post again if it isn't.

Good luck!

 

[mungara]

Hi,
i am using the following query in my java class,but mySQL is not
supporting.can you please provide the solution to me.we are using type
4 odbc driver
query="INSERT INTO AWB_DETAILS (AWB_NO, AWB_PREFIX, SHIPMENT_TYPE,
ORIGIN, DESTINATION, WEIGHT_CODE, WEIGHT, TOTAL_PIECES, TIMESTAMP )
VALUES ( '43779831',232,'T','HAM','KUL','K',0.2,1,SYSDATE );SELECT
LAST_INSERT_ID();"

Statement insertStmt = conn.createStatement();
insertStmt.execute(awbDetailsQuery.toString());

thanks & regards
REddy

 

[DanChard]

Hi mungara. You might want to start a new thread for this question, it doesn't really have anything to do with tonyfitz's question and less people will read it down here.

 

[tonyfitz]

Thanks Dan (Genius) Chard

I tried your solution and I'm all sorted now

Thanks a million

Tony

 

[tonyfitz]

Hi Dan or anyone else

I was a little hasty in thinking everything worked.
The code produces an answer however not the one i'm looking for.

Basically the code you wrote will tell me the jobs slots purchased in the manner in which they were purchased plus the total amount of jobs posted and the overall balance of available slots.
My problem is that as a job slot has a life span of 12 months slots expire at different dates. So if the customer purchases 10 slots on 01 Dec 2003 and 2 slots on 4 June 2003 then I need to be able to show them that they have x amount of slots remaining that expire on 31 Nov 2004 and Y slots available that expire on 03 June 2004.

I think I have to use the "Slot Ref" field from the PAYMENT table as a unique value to determine the information I require from the JOBS table.
The session user will be the customer for these queries so I will always know who the usernname is.

Thanks

Tony

 

[DanChard]

Hello again. Sorry about taking so long to get back to you but I decided to create the tables so I could have a proper play with it. Anyway, the following SQL should do what you were hoping for...

SELECT payment.username, date_purchased, slots, count(jobname) as slots_used, slots-count(jobname) as free_slots
from payment left join jobs on payment.slot_ref = jobs.slot_used
group by payment.username, payment.date_purchased, slots
order by payment.username, date_purchased;

You might have to change a couple of the column names but it's obvious what they're used for and what you will need to change them to.

Good luck!

 

[tonyfitz]

The example I used at the begining was a simplistic version of what I actually have. I tried to work your code into what I have but without success. I have therefore included the actual table data here. I would really appreciate it if you could show me the code to work with this
Thanks.
Sorry for being such a pain



PAYMENT table

slotgroup username slots date slotexpire
37 admin 10 12/10/2002 30/06/2004
38 admin 1 12/10/2002 30/10/2003
45 coffee 5 31/03/2003 30/09/2003
46 tony 1 31/03/2003 30/09/2003
68 admin 6 16/06/2003 30/09/2003
69 admin 6 16/06/2003 30/04/2004




JOBS table

id username category type salary slotexpire slotgroup slots
28 admin Accountancy Permanent 20000 30/06/2004 37 10
29 admin Banking Permanent 25000 30/06/2004 37 10
30 admin Hotel Permanent 30000 30/09/2003 68 6
31 admin Engineering Permanent 30000 30/04/2003 69 6
99 coffee Accountancy Permanent 45000 30/09/2003 45 5
100 tony Accountancy Permanent 30000 30/09/2003 45 5
101 admin Accountancy Permanent €80,000 30/06/2004 37 10
102 admin Accountancy Permanent €20,000 30/10/2003 38 1
216 admin Accountancy Permanent €10,000 30/09/2003 68 6
217 admin Accountancy Permanent €10,000 30/04/2003 69 6
218 admin Accountancy Permanent €10,000 30/09/2003 68 6
219 admin Accountancy Permanent €10,000 30/06/2004 37 10
223 admin Accountancy Permanent €10,000 30/06/2004 37 10
224 admin Accountancy Permanent €10,000 30/06/2004 37 10


The user (ie admin) has to login to see their jobs and view the members area. This means that when ‘admin’ is logged in they are only to get the info relevant to them.

I want to show the user the following

Slots Purchased Slots Used Slots remaining Expiry of Slots
10 6 4 30/06/2004
1 1 0 30/10/2003
6 3 3 30/09/2003
6 2 4 30/04/2004

I have included the Primary key from the payment table into the job table. This should allow me do an inner join or other type of join. However I cant get it to work. I tried your code but without success.

 

[DanChard]

Hello, thanks for the info. I've set up a new database in exactly the same way as you described and put in all the data you've given. Below is the SQL for a query based on your two tables which should give the result you're after (it works for 'admin' anyway and the output matches that which you gave):

select payment.slots as [Slots Purchased], temp.total_num as [Slots Used], payment.slots-temp.total_num as [Slots Remaining], payment.slot_expire as [Expiry of Slots]
from payment inner join
(SELECT slotgroup, count(*) as total_num from jobs where username = 'admin'
group by slotgroup) as temp on payment.slotgroup = temp.slotgroup;

Please let me know whether this is OK...

 

[tonyfitz]

Thanks

That works fine

Regards

Tony