Solved

Using SQL command within a formula field

Posted on 2003-12-07
5
608 Views
Last Modified: 2012-05-04
hi

I am using Basic syntax to write the formula. I am basically going through some dates eg from 1st to 5th using a while loop. For each date i want to run a SQL count records query to get a total number of record for the period.

But i am not sure how to add an SQL query inside a formula field.

Would appreciate any help...

Thanks
David
0
Comment
Question by:iam_david_lee
  • 2
  • 2
5 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 9894274
I don't believe you can.

Which version and edition of Crystal?

mlmcc
0
 

Author Comment

by:iam_david_lee
ID: 9895538
hi

thanks for the reply...

I am using Crystal Reports9 with mysql.

I am using the following code

Dim tempdate As Date
Dim x As Number
tempdate = {house.MovingInDate}
x =0
While  tempdate <= {house.MovingOutDate}
tempdate=tempdate+1
wend
formula =x

What I need to do is to count how many ppl are in the house during each day for the looping period. I tried to use SQL count command there but it doesnt allow SELECT commands there. The number of ppl are in another table called rent. I am not sure how to use SQL Expressions, if i can use SQL Expressions i need to send the date and get back the count from rent table.

But i am not sure how to do this.

would really appreciate the help

thanks
david



0
 

Author Comment

by:iam_david_lee
ID: 9899517
hi

I have two tables.

House Rent table

HouseID  RentID   StarDate EndDate    Amout
1        1        1/11/03  30/11/03   100.00
1        2        1/12/03  31/11/03   200.00

Basically this table has house rent details for different periods. From the above data there is a house which chrages a rent of 100 for October and 200 for December.

Occupant Table
RentID   CusID    CheckinDate  CheckoutDate
1        5        1/11/03      15/12/03        
1        6        1/11/03      30/11/03
1        7        20/12/03     31/12/03

This table has customers checkindate and checkoutdate. RentId is linked.

What I want in the report is to display the following details for each record in the House Rent table.

RentID CusID  Amout

The problem is the amout needs to automatically calculated. The amout depends on how many ppl are sharing the house on everyday during period of stay.

Basically in the example data above, for the first month upto 15th two ppl are sharing the house so half the rent ($50) is divided between two ppl ($25 each). Then for the rest of that month there is only one peron so he should be allocated $50.

What I tried to do was for each House Rent record I went in a loop from House Rent Start Date to End Date for each cusomter to see which days he was in the house, this was working, I was also able to calculate amout per day but i am not sure how to check how many ppl occupied the house for a certain day so I can divided the days cost correctly between the occupants. This needs do be done while i am inside the loop check each day.

Would very much appreciate the HELP....

Thanks

David
0
 
LVL 1

Accepted Solution

by:
NatashaV earned 150 total points
ID: 9901831
Hi David!

I don't think you can do it the way you're trying to.  This is a little bit more complicated then using Crystal formula.  You have to do some work on a database side prior to displaying your results in Crystal.  I carefully examined you sample and made some corrections (sorry if I'm wrong, but that's how I understand you problem):

HouseRent
HouseID  RentID   StarDate EndDate    Amount
1            1           1/11/03  30/11/03   100.00
1            2           1/12/03  31/12/03   200.00 (EndDate is 31/12/03 - December)

Occupant
RentID   CusID    CheckinDate  CheckoutDate
1           5          1/11/03         15/11/03  (not 15/12/03,  based on "first month upto 15th two ppl are sharing the house")      
1           6          1/11/03         30/11/03
2           7          20/12/03       31/12/03 (RentiD=2 not, based on House Rent table where December is rent 2)

It's hard to give you the exact recipe here, but in general what I would do is:
1. Add RentPerDay column to HouseRent table, since your task is to calculate the amount based on day-by-day sharing (Amount/(EndDate - StartDate), with 3-4 decimals)
2. Create another table (let's call it DailyRent) with RentId, Date, and OccupantsPerDay column.  
3. Write a script to initially populate this table, update it every time you add a customer (you have rentId in Occupant table)
4. Do final calculation using DailyRent, and HouseRent.RentPerDay.

This is not step-by-step instructions, there are probably other solutions to this problem.  Good luck!

Natasha
0
 
LVL 1

Expert Comment

by:NatashaV
ID: 9901835
Hi David!

I don't think you can do it the way you're trying to.  This is a little bit more complicated then using Crystal formula.  You have to do some work on a database side prior to displaying your results in Crystal.  I carefully examined you sample and made some corrections (sorry if I'm wrong, but that's how I understand you problem):

HouseRent
HouseID  RentID   StarDate EndDate    Amount
1            1           1/11/03  30/11/03   100.00
1            2           1/12/03  31/12/03   200.00 (EndDate is 31/12/03 - December)

Occupant
RentID   CusID    CheckinDate  CheckoutDate
1           5          1/11/03         15/11/03  (not 15/12/03,  based on "first month upto 15th two ppl are sharing the house")      
1           6          1/11/03         30/11/03
2           7          20/12/03       31/12/03 (RentiD=2 not, based on House Rent table where December is rent 2)

It's hard to give you the exact recipe here, but in general what I would do is:
1. Add RentPerDay column to HouseRent table, since your task is to calculate the amount based on day-by-day sharing (Amount/(EndDate - StartDate), with 3-4 decimals)
2. Create another table (let's call it DailyRent) with RentId, Date, and OccupantsPerDay column.  
3. Write a script to initially populate this table, update it every time you add a customer (you have rentId in Occupant table)
4. Do final calculation using DailyRent, and HouseRent.RentPerDay.

This is not step-by-step instructions, there are probably other solutions to this problem.  Good luck!

Natasha
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now