SQL Help

[t121anf]

i need a little SQL help as I'm stumped and i've stumped one of my colleagues whose a little geeky at this kind of thing.

another colleague has a DB for a laptop booking system which when returned late produces fines, problem is no one can pay them online so I need to get them into my DB.

The laptop DB has numerous tables but then ones I am interested in are PurseAudit and Users.

Users holds a Username field which I need to link to my DB.
PurseAudit contains a running balance field, UserID Key (linking it to Users) and CreatedDate which might be handy.

So far I have the following.

[mjn51]

Have you tried select DISTINCT users

?

[t121anf]

produces the same 252 rows :-S

[panrider_uk]

Use Group By?

Mark
____________________
Current bikes: Honda ST1100 Pan European. Moto Guzzi V85 TT Travel

[t121anf]

geeky colleague had tried that and it returned an error message,

i think he tried group by username, but it complained about not having everything that was in the select in the group by. (seems my sql allows this , ms doesn't)

[Kickstart]

Hi

Should be pretty easy, but the problem is that you are now returning details from the non unique rows (ie, creation date, which one would you like back?).

[t121anf]

Hi Keith,

I think you are on the right lines, however the SUM rightly is adding each row of Balance together giving an incorrect figure.

This is probably my fault as I didn't really explain the issue fully.
User 1, borrows laptop, returns late, fine added (Balance in PurseAudit), this could be paid off and user 1 repeats being naughty adding a second load of rows into Balance. i.e. fine 1, £25, fine 2 £25 table would look like this

£25
0
£25

the SUM returns £50, rather than £25.

I don't need CreatedDate so if it helps this can be dropped, all I really need to the current balance and username. the ID field is of no use to me as it is unique to this application, username applies to everything a user does.

[Kickstart]

Hi

I used sum thinking there was a current record for each "debt". From your reply seems it is only the latest debt you care about.

[t121anf]

Thanks Keith,

I ran this through as a quick test prior to going home and it seems to work.

i'll check the results tomorrow all bieng well i'll more onto how to make this useful as i have no idea how to run this automatically and produce a sensible output (i.e. text file)

(Karma cookies to follow tomorrow if it works btw)

[Wabby]

[gazzeh]

[t121anf]

[t121anf]

Keith, as promised you have Karma not that you need it lol.

I tested the output earlier, thankfully not too many results so I check them all and they are correct. I made a few simple changes to improve the output but on the whole I have retained your work.

Also worked out how to run this outside of the MS SQL client, much easier than expected.

[Kickstart]

Hi

Glad it worked. 90% of the SQL I do is either using MySQL (quite nice) or Access ( ), but the basics of most flavours of SQL are the same.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing