• 0

[SQL] Inserting BOTH the month and date


Question

This is for an assignment, but I know I know do your own homework - but I'm completely stuck. The scenario is a new carpark requires a database built in MS Access.

I'm reconstructing an Account table based on historic data from the Transaction table. The Account table basically stores a "billing statement" for each RegistrationID for each month. I need to insert 3 attributes into Account: RegistrationID, AccountDate, AmountDue

Problem is...

- to get Registration ID is a piece of cake

- to get the AccountDate requires that you look at the Transaction.DateTimeOUT attribute and work it out using that - this is my problem

- to get the AmountDue I need to Sum() up the Duration, round it up to the nearest 15mins and multiply it by 50cents - no probs

Here's my SQL code: [Note: RoundUpDown() simply rounds it up to the nearest 15]

INSERT INTO Account ( RegistrationID, AccountDate, AmountDue )
SELECT Transaction.RegistrationID, Month([DateTimeOUT]) AS MonthOfAcc, (RoundUpDown(Sum(Duration),+15)/15*0.5) AS AmountDue
FROM [Transaction]
GROUP BY RegistrationID, Month([DateTimeOUT]);

My results look like:

RegistrationID	MonthOfAcc	AmountDue
P00001	1	3
P00001	6	3.5
P00001	9	17.5
P00002	4	2.5
P00002	8	2.5
P00002	9	14
P00003	5	0.5
P00003	9	14.5
P00004	2	4
P00004	3	3.5
P00004	9	17.5
P00005	7	3.5
P00005	9	18

As you can see, I need to group it by Registration ID and then the Month - this will give me monthly statements (records) for each Registration to store in the Account table. Of course, this won't work cause I'm trying to insert the month (an integer) into the AccountDate (which is of Date format). Plus the AccountDate is meant to be set to 01/mm/yyyy.

It's seems impossible to get the AccountDate 01/mm/yyyy if you're grouping it by the Month, cause that wouldn't make sense in SQL anyway. Any ideas on how I could possibly fix this? :ninja:

Cheers for any help, this is a toughy. :devil:

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Fixed it. Don't know if this is the best way to do it, but it's pretty cheap and does the trick...

Wrote my own monthToDate() function in VBA that just concatenates the integers and forms a Variant. From there, the SQL was a piece of cake...

INSERT INTO Account ( RegistrationID, AccountDate, DueDate, AmountDue )
SELECT 
    Transaction.RegistrationID, 
    MonthToDate(1,Month([DateTimeOUT]),2004) AS AccountDate, 
    MonthToDate(14,Month([DateTimeOUT]),2004) AS DueDate, 
    (RoundUpDown(Sum(Duration),+15)/15*0.5) AS AmountDue
FROM [Transaction]
GROUP BY RegistrationID, Month([DateTimeOUT]);

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.