Help with SQL please - adding column contents

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Help with SQL please - adding column contents

Postby rogerhwatts » Sat Dec 12, 2015 4:50 pm

Hi,
I have a membership database. Each year we have column for the Date paid (eg Bank2014) and the amount paid ( eg Amt2014).
We run a calendar year membership but members can pay up to 2 months early. However, for Gift Aid we need any subs banked in the given Calendar year, regardless of which subs year they are for.
So, I need a Base Query column which calculates (Bank2014>=1/1/14)* Amt2014 + (Bank2015< 1/1/15)* Amt2015

I'm sure this must be quite simple.

regards
rogerhwatts
 
Posts: 44
Joined: Mon Mar 21, 2011 5:22 pm

Re: Help with SQL please - adding column contents

Postby MTP » Wed Dec 16, 2015 11:13 pm

I think you will need two (sub)queries: one to sum over the rows where Bank2014>=1/1/14 and one to sum over the rows where Bank2015<1/1/15:
Code: Select all   Expand viewCollapse view
SELECT "A"."2014_Subs" + "B"."2015_Subs" AS "2014_Banked_Subs"
   FROM (SELECT SUM("Amt2014") AS "2014_Subs" FROM "YourTableName" WHERE "Bank2014" >= '2014-01-01') "A",
   (SELECT SUM("Amt2015") AS "2015_Subs" FROM "YourTableName" WHERE "Bank2015" < '2015-01-01') "B"


Not really that simple. This would be much easier if you stored your membership dues in rows, in a separate table, instead of in columns in your main table. For example, you could have the following tables where TABLE NAME is in all caps and column names for each table are listed under the name:

MEMBERS
MemberID (primary key)
MemberName
MemberAddress
[Other member identifying information]

SUBSCRIPTIONS
SubID (primary key)
MemberID (foreign key - meaning, it matches a primary key from the MEMBERS table)
SubYear
PaymentDate
AmountPaid


Now, if your database was set up like that, your query would be:
Code: Select all   Expand viewCollapse view
SELECT SUM("AmountPaid")
   FROM "SUBSCRIPTIONS"
   WHERE "PaymentDate"  >= '2014-01-01' AND "PaymentDate" < '2015-01-01'

Much simpler, yes?
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1618
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Help with SQL please - adding column contents

Postby rogerhwatts » Thu Dec 17, 2015 2:14 pm

Thanks. I will try that out.
I realise that this would be much easier if I had a separate Table for subs ie a relational database. My problem is that our group (1000 strong) is generally elderly and few (admit to ) knowing anything about databases. So I keep it so that, if the worst came to the worst, the single main table could be copied (using fairly simple instructions) as a spreadsheet which maybe someone else would be able to comprehend.
OpenOffice 4.00
Windows 7
rogerhwatts
 
Posts: 44
Joined: Mon Mar 21, 2011 5:22 pm


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest