Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Combine multiple date fields

Posted on 2013-10-22
24
Medium Priority
?
614 Views
Last Modified: 2013-10-29
In my SQL database I have three different table for hours.  Each table has a field called hours date.  What I would like to do is create a report in crystals report that will allow me to take all three of these tables and use the hours date of all 3 tables, but make one parameter instead of 3 separate parameters.

Not sure if this make any sense.
0
Comment
Question by:TCHGirl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 6
  • +2
24 Comments
 

Expert Comment

by:crundle32
ID: 39592987
what about using "Union"?


Select Hours FROM Table_1
Union
Select Hours FROM Table_2
Union
Select Hours FROM Table_3

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39592994
not a lot :)

can you share the table definitions?
some sample data?
and an expected result?

in particular I didn't get this bit: "Each table has a field called hours date"
is the field name [hours] and the data type of that field is date?

(and by the way what type of database? e.g. SQL Server? Oracle? and version if you know it)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39592999
before you adopt a union approach, please note it might need to be UNION ALL

(union by itself could distort the result)

But I don't really know what the expected result is (hint).
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 101

Expert Comment

by:mlmcc
ID: 39593077
If you are asking about how to use a parameter to select records from 3 tables.
You don't need 3 parameters unless you wantto compare to different values.

The parameter can be used in comarisons to all 3 fields.

If you want to use the UNION idea then all select statement must have the same number of fields.  The fields must be in the same order.

Do the tables have the same fields?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39593555
mlmcc,

 Since you're "here", you might want to add the CR zone.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39593886
Thanks for catching that.  I guess I saw the tag.
Crystal Reports topic added

mlmcc
TA
0
 

Author Comment

by:TCHGirl
ID: 39598935
Crystal Reports is a software that I have had to learn on my own.  My tables are located in SQL.  
Let's work with two table first.
Both table have a date in them.  What I want to do is merge these two tables together so I only get on total count.  In my computer table I have 495 records and in the education table I have 3275 records for the year 2012.  I know that when I combine tables there will be duplicates and that is ok, because when they are run in a report that will be grouped.  So how do I accomplish this in a crystal reports.

Computer Lab Table              Education Hours Table            
   TCHID# - number                      TCHID# - number
   Class_lab - text                          DateHours - date/time
   Lab_Date - date/time                 classhrs - number
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39598947
What are you trying to show from the report on these tables?

What do the fields represent?

mlmcc
0
 

Author Comment

by:TCHGirl
ID: 39598981
These fields represent clients who have come into our computer lab and have attended education classes.  So if I combine that tables together I should end up with 3770 records. Then I can set up the parameters that I need to so that I can then group the clients together by TCHID#.
0
 

Author Comment

by:TCHGirl
ID: 39599032
I don't want to use the union, I tried that in Access to see what would happen and I did not get the outcome that I was looking for.  What happened when I used the Union was that it combined both dates into one date and that is not what I want.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39599114
I don't think 3770 is necessarily the correct number of rows

e.g. Is it possible for no education hours to be recorded against a lab?
(perhaps the lab was being renovated or just not used for education)

but; I presume it's not possible for a record to exist in "EducationHours" that does not refer to a lab (well even if they did they wouldn't be relevant to this question)

With those assumptions are you able to run these 2 queries and provide the results please:

SELECT count(*)
FROM ComputerLab AS cl
INNER JOIN EducationHours AS eh ON cl.TCHID# = eh.TCHID#
;

SELECT count(*)
FROM ComputerLab AS cl
LEFT OUTER JOIN EducationHours AS eh ON cl.TCHID# = eh.TCHID#
;

The difference (if any) will indicate if records exist in labs that have no education hours against it. Note, if you are uninterested in this (i.e. your prime interest is only those labs with hours then you want to use the "inner join", but if you are interested in knowing which labs have no hours then you want to use the "left outer join").

I'll assume you need the "INNER JOIN", so a query that combines those 2 tables would be like this:
SELECT
     cl.TCHID#
   , cl.lass_lab
   , cl.Lab_Date
   , eh.DateHours
   , eh.classhrs
FROM ComputerLab AS cl
INNER JOIN EducationHours AS eh ON cl.TCHID# = eh.TCHID#
;

Open in new window

I'm not strong on Crystal Reports (CR) so I'll let others deal with those details.

nb: the syntax used is for MS SQL Server, if the database is Oracle remove the word "as" after each table name. Oh, and the table names are a guess - you have to change those to the real names.

hint: it's always useful to tell us what the database type is

{+ edit, sorry, corrections}
0
 

Author Comment

by:TCHGirl
ID: 39599124
My tables are located in SQL.  I also had an idea of using Access to combine information into a new table and go from there.  Will this work?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39599137
'SQL' could mean just about anything, but I guess you mean Microsoft SQL Server

CR can read directly from your MS SQL Server database - is that correct?
(i.e. you have this permission/security access)

You can use Access if you need to - but is there a good reason to do so?
This does not appear to be a very complex need, and pushing data into Access to merge the data just seems like extra work (and probably makes things slower)
0
 

Author Comment

by:TCHGirl
ID: 39600513
yes, I can read directly from SQL Server into CR.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39601191
DO the LabDate and DateHours fields related?

Computer Lab Table              
   TCHID# - number                      
   Class_lab - text                          
   Lab_Date - date/time                

I assume the TCHID is a student id and the class lab is the class or lab being attended and the LabDate is the date and time of the lab.

Education Hours Table            
TCHID# - number
DateHours - date/time
classhrs - number

IN this one the TCHID is also the student id and DateHours is the datetime of attending and the classhrs is how long the session is or perhaps the credits

If that is the case it would seem that the tables are related through both the TCHID field and the corresponding datetime fields.

mlmcc
0
 

Author Comment

by:TCHGirl
ID: 39601561
The above is correct, the problem is that they are in two separete tables and I am trying to combine them into one table without losing any records.  There are other fields in both tables, but I only want the above fields to work with.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39601664
So if student 1 attends lab1 on 9 Sep 2013 at 11:00 AM
The Education Hours Table will show
Student 1, 9 Sep 11:00 AM 2 hours

mlmcc
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39601681
>> "I am trying to combine them into one table" ; do you mean

a query result?
a temp table?
or permanently change the data?

regarding this: "without losing any records"

have you run the 2 "select count(*)" queries suggested at http:#a39599114 ?
what were the results?
0
 

Author Comment

by:TCHGirl
ID: 39601714
Where is this code suppose to go.  Is it a stored Procedure in Sql server or does it go into crystal reports?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39601730
If you mean the 2 select count queries, you can run them in SQL Server Management Studio

You could use them one at a time as the SQL Command for a report but that seems like a bit of overkill

mlmcc
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39601766
those 2 count queries are going to help us determine how to avoid losing data
they are not the solution, I indicated the purpose as:

>>"The difference (if any) will indicate if records exist in labs that have no education hours against it. "

and the reason we need this is to decide if we use a LEFT OUTER JOIN or an INNER JOIN
0
 

Author Comment

by:TCHGirl
ID: 39606996
That is possible to have clients go to the computer lab and not go to class.
0
 

Author Comment

by:TCHGirl
ID: 39607073
Let's forget combining the tables together, because it looks like this is not going to work.  So here is what I would like to do instead.

In crystal reports I want to pull the dates from both tables, but only use one date parameter for both of these tables.  Is that possible?

I know that in Access my query I would use the "or", but how would I set this up in CR?
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1500 total points
ID: 39607612
SAme basic way as in Access

{DateField1} = {?DateParameter}
OR
{DateField2} = {?DateParameter}

mlmcc
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question