Transfering criteria matching records into one table

I have the following data which are in different tables
          Address.db           Name and Birthday of Husband
          Wife.db              Name and Birthday of Wifw
          Child1.Db            Name and Birthday of First child
          Child2.Db            Name and Birthday of Second child
(due to table design the data have to be in different tables and not just in one table)
There are other tables and other data which are not relevant for the present problem.
What i wanted to do was based on some criteria like 'display name and birthday for birthdays occuring
during the next one week' i want  all the
records satisfying the criteria to be stored and displayed in one dbgrid with two fields
name and birthday.
I have done it in the following manner
Check the criteria against each of the table data(address,wife,child1,child2 etc).If any record
satisfies the criteria, a new record is inserted into the Birthdays.db(name and Birthdate fields) till
all the tables are checked.Finally the data is displayed in the dbgrid.
When the next criteria is required emptyTable is done for Birthdays.db and the whole process is done again.

Even though the above  works fine ,can SQL do a better job? ie by just firing one query
all the work gets done of transfering the criteia matching data from different tables into one table only

thanks
venks

           
LVL 1
venksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
hi venks,

this should do what you need in sql

procedure TForm1.Button1Click(Sender: TObject);
//query1 holds:
//select name, birthday from address where birthday between :FromDate and :ToDate union all
//select name, birthday from wife where birthday between :FromDate and :ToDate union all
//select name, birthday from child1 where birthday between :FromDate and :ToDate union all
//select name, birthday from child2 where birthday between :FromDate and :ToDate
begin
  query1.Close;
  Query1.ParamByName('FromDate').AsDateTime := Trunc(DateTimePicker1.Date);
  Query1.ParamByName('ToDate').AsDateTime := Trunc(DateTimePicker2.Date);
  query1.Open;
end;


try it out

meikl
0
kretzschmarCommented:
hi again venks,

for ordering just append an order by clause like

select name, birthday from address where birthday between :FromDate and :ToDate union all
select name, birthday from wife where birthday between :FromDate and :ToDate union all
select name, birthday from child1 where birthday between :FromDate and :ToDate union all
select name, birthday from child2 where birthday between :FromDate and :ToDate
order by brithday

meikl
0
venksAuthor Commented:
Dear meiki
Thanks for the great help.It is working
beautifully.Hence  i am awarding the points.
The only problem that i am facing is that all the birthdates are input with
the year in which the person was born eg
10/04/1956 etc,Bur in the query we have to check the birthdays for this year ie 2000.
Hence what should i do(ie with out changing the inputted data in the tables)
Even though this was not part of the original question,I would be grateful if you could answer this
thanks once again
venks
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

kretzschmarCommented:
hi venks,

this would be become more complex. well a working solution for paradox (tested)(other databases may have other functions)

procedure TForm1.Button1Click(Sender: TObject);
const
  SQLStatementPart =
    'select name, birthday, Cast((Cast(Extract(Day from Birthday) As Char(2))||''.''||'+
    'Cast(Extract(Month from Birthday) As Char(2))||''.''||%s)as date) As TheBirthDate '+
    'from %s where cast((Cast(Extract(Day from Birthday) As Char(2))||''.''||'+
    'Cast(Extract(Month from Birthday) As Char(2))||''.''||%s) as date) '+
    'between :FromDate and :ToDate %s';
var
  y,m,d : Word;
  S : String;
  SRes : String;
begin
  query1.Close;
  query1.sql.Clear;
  DecodeDate(DateTimePicker1.Date,y,m,d);
  s := QuotedStr(IntToStr(y));
  FmtStr(SRes,SQLStatementPart,[s,'Address',s,'Union all']);
  query1.Sql.Add(SRes);
  FmtStr(SRes,SQLStatementPart,[s,'Wife',s,'Union all']);
  query1.Sql.Add(SRes);
  FmtStr(SRes,SQLStatementPart,[s,'Child1',s,'Union all']);
  query1.Sql.Add(SRes);
  FmtStr(SRes,SQLStatementPart,[s,'Child2',s,'Order by TheBirthDate']);
  query1.Sql.Add(SRes);
  Query1.ParamByName('FromDate').AsDateTime := Trunc(DateTimePicker1.Date);
  Query1.ParamByName('ToDate').AsDateTime := Trunc(DateTimePicker2.Date);
  query1.Open;
end;

a little explaination what i'm doing:

i get name and birthday from one table, calculate TheBirthDate by building day and month from birthday and year from datetimepicker1. Because the year cannot passed as parameter i build a part from the sql statment as const and fill the variant parts by using FmtStr.

restrictions:
you can query only in one year, it is not possible to query birthdays between 1.12.2000 and 1.1.2001

remarks:
depending on your regional settings, you must replace '.' with '/' and/or swap the day<->month order in the const SQLStatementPart.

try it out

meikl ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
venksAuthor Commented:
Dear meikl
Thanks for the superb work.I have already wriiten code to do the required work through table component.
But your code is much shorter and I have learnt a lot
venks
0
kretzschmarCommented:
:-) glad to helped you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.