Query Help

G

Guest

I have a scenario that requires a little more knowledge than I have at this
point. The Table below contains gross revenue information for our facility.
I basically need to calculate Gross Revenue per day using a 3 month average.
The formula is as follows:

Prior 3 Months total revenue divided by number of days in those three
months. This is a fairly straitforward calculation that I can do. The
problem is I need to calculate this for a rolling 12 month time period. So
in essence, March's revenue per day equals January, February, and March total
revenue divided by 90 days. I also need revenue per day for February which
would be December, january and February divided by 90 days. I have a text
box on a form that calculates the days but I can't figure out how to do this
for 12 month rolling timeframe when March Gross Revenue would need to be
included in 3 months of calculations. If I am not making sense please let me
know as this isn't the easiest thing to explain. Please let me know if you
can come up with an answer. Thank you.

Table
FileDate Hospital GrossRevenue

03/31/06 T 63,922.00
02/28/06 T 164,291.00
01/31/06 T 151,654.00
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you truly store the FileDate as the last day of the month, then you
can, possibly, do this:

SELECT Hospital, Sum(GrossRevenue) / 90 As DailyAvg,
Sum(GrossRevenue) As TotalGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,dateadd("m",1, date() -
day(date()) + 1) -1) And dateadd("m",1, date() - day(date()) + 1) -1

The formula dateadd("m",1, date() - day(date()) + 1) -1 finds the last
date of the current month. So, if the current month is Apr, this query
will find the sum of the GrossRevenue for the months of Feb, Mar, and
Apr.

If you want it to find the 3 month total for specific months use this
query:

PARAMETERS [Enter last day of month] Date;
SELECT Hospital, Sum(GrossRevenue) / 90 As 3MonthAvg, Sum(GrossRevenue)
As 3MonthGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,[Enter last day of month]) And
[Enter last day of month]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE+0n4echKqOuFEgEQJbbACgy0mbRmxqc3m2B9hfw2LMs4Vmsi4AoKT/
QozVbpUK6Rs23nHzNuvAO8L8
=KPa/
-----END PGP SIGNATURE-----
 
G

Guest

Thanks for the reply I really appreciate it. There are a couple of issues
with this logic . First the number of days are going to be different
depending on how many days are in each month. The example you use of Feb,
Mar, and April there are only 89 days in that month. Second, I need this
logic for 12 months of data. So if the File Date is April - Sum Feb, Mar,
and Apr and divide by 89. If the File Date is March- Sum Jan, Feb, Mar and
divide by 90. Thanks.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you truly store the FileDate as the last day of the month, then you
can, possibly, do this:

SELECT Hospital, Sum(GrossRevenue) / 90 As DailyAvg,
Sum(GrossRevenue) As TotalGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,dateadd("m",1, date() -
day(date()) + 1) -1) And dateadd("m",1, date() - day(date()) + 1) -1

The formula dateadd("m",1, date() - day(date()) + 1) -1 finds the last
date of the current month. So, if the current month is Apr, this query
will find the sum of the GrossRevenue for the months of Feb, Mar, and
Apr.

If you want it to find the 3 month total for specific months use this
query:

PARAMETERS [Enter last day of month] Date;
SELECT Hospital, Sum(GrossRevenue) / 90 As 3MonthAvg, Sum(GrossRevenue)
As 3MonthGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,[Enter last day of month]) And
[Enter last day of month]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE+0n4echKqOuFEgEQJbbACgy0mbRmxqc3m2B9hfw2LMs4Vmsi4AoKT/
QozVbpUK6Rs23nHzNuvAO8L8
=KPa/
-----END PGP SIGNATURE-----


I have a scenario that requires a little more knowledge than I have at this
point. The Table below contains gross revenue information for our facility.
I basically need to calculate Gross Revenue per day using a 3 month average.
The formula is as follows:

Prior 3 Months total revenue divided by number of days in those three
months. This is a fairly straitforward calculation that I can do. The
problem is I need to calculate this for a rolling 12 month time period. So
in essence, March's revenue per day equals January, February, and March total
revenue divided by 90 days. I also need revenue per day for February which
would be December, january and February divided by 90 days. I have a text
box on a form that calculates the days but I can't figure out how to do this
for 12 month rolling timeframe when March Gross Revenue would need to be
included in 3 months of calculations. If I am not making sense please let me
know as this isn't the easiest thing to explain. Please let me know if you
can come up with an answer. Thank you.

Table
FileDate Hospital GrossRevenue

03/31/06 T 63,922.00
02/28/06 T 164,291.00
01/31/06 T 151,654.00
Click to expand...
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This formula will get the number of days in the 3 month time period for
current date:

(dateadd("m",1, date() - day(date()) + 1) -1) - dateadd("m",
3,dateadd("m",1, date() - day(date()) + 1) -1)

This formula will get the number of days in the 3 month time period for
specific dates:

DateAdd("m",-2,[Enter last day of month]) - [Enter last day of month]
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE+8VYechKqOuFEgEQK6EACfVdpcDZEngRa/BnUZUcOomZEi+N0An10V
5/LfXitn3qOpsySsmg9N3C2Q
=gYnp
-----END PGP SIGNATURE-----
Thanks for the reply I really appreciate it. There are a couple of issues
with this logic . First the number of days are going to be different
depending on how many days are in each month. The example you use of Feb,
Mar, and April there are only 89 days in that month. Second, I need this
logic for 12 months of data. So if the File Date is April - Sum Feb, Mar,
and Apr and divide by 89. If the File Date is March- Sum Jan, Feb, Mar and
divide by 90. Thanks.

:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you truly store the FileDate as the last day of the month, then you
can, possibly, do this:

SELECT Hospital, Sum(GrossRevenue) / 90 As DailyAvg,
Sum(GrossRevenue) As TotalGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,dateadd("m",1, date() -
day(date()) + 1) -1) And dateadd("m",1, date() - day(date()) + 1) -1

The formula dateadd("m",1, date() - day(date()) + 1) -1 finds the last
date of the current month. So, if the current month is Apr, this query
will find the sum of the GrossRevenue for the months of Feb, Mar, and
Apr.

If you want it to find the 3 month total for specific months use this
query:

PARAMETERS [Enter last day of month] Date;
SELECT Hospital, Sum(GrossRevenue) / 90 As 3MonthAvg, Sum(GrossRevenue)
As 3MonthGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,[Enter last day of month]) And
[Enter last day of month]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE+0n4echKqOuFEgEQJbbACgy0mbRmxqc3m2B9hfw2LMs4Vmsi4AoKT/
QozVbpUK6Rs23nHzNuvAO8L8
=KPa/
-----END PGP SIGNATURE-----


I have a scenario that requires a little more knowledge than I have at this
point. The Table below contains gross revenue information for our facility.
I basically need to calculate Gross Revenue per day using a 3 month average.
The formula is as follows:

Prior 3 Months total revenue divided by number of days in those three
months. This is a fairly straitforward calculation that I can do. The
problem is I need to calculate this for a rolling 12 month time period. So
in essence, March's revenue per day equals January, February, and March total
revenue divided by 90 days. I also need revenue per day for February which
would be December, january and February divided by 90 days. I have a text
box on a form that calculates the days but I can't figure out how to do this
for 12 month rolling timeframe when March Gross Revenue would need to be
included in 3 months of calculations. If I am not making sense please let me
know as this isn't the easiest thing to explain. Please let me know if you
can come up with an answer. Thank you.

Table
FileDate Hospital GrossRevenue

03/31/06 T 63,922.00
02/28/06 T 164,291.00
01/31/06 T 151,654.00
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I still don't believe this is what I need. I would like the data to look
like this:
Facility FileDate RevenuePerDay
T 03/31/06 2,500,000
T 02/28/06 2,300,000
T 01/31/06 2,450,000
T 12/31/05 2,.350,000
T 11/30/05 2,500,000
T 10/31/05 2,500,000
T 09/30/05 2,500,000
T 08/31/05 2,500,000

Revenue per day for whichever FileDate would use the formula listed in the
initial question. I really appreciate you responding but the response
doesn't seem to do what I need it to. Please let me know if you know how
this can be done. Thank you.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you truly store the FileDate as the last day of the month, then you
can, possibly, do this:

SELECT Hospital, Sum(GrossRevenue) / 90 As DailyAvg,
Sum(GrossRevenue) As TotalGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,dateadd("m",1, date() -
day(date()) + 1) -1) And dateadd("m",1, date() - day(date()) + 1) -1

The formula dateadd("m",1, date() - day(date()) + 1) -1 finds the last
date of the current month. So, if the current month is Apr, this query
will find the sum of the GrossRevenue for the months of Feb, Mar, and
Apr.

If you want it to find the 3 month total for specific months use this
query:

PARAMETERS [Enter last day of month] Date;
SELECT Hospital, Sum(GrossRevenue) / 90 As 3MonthAvg, Sum(GrossRevenue)
As 3MonthGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,[Enter last day of month]) And
[Enter last day of month]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE+0n4echKqOuFEgEQJbbACgy0mbRmxqc3m2B9hfw2LMs4Vmsi4AoKT/
QozVbpUK6Rs23nHzNuvAO8L8
=KPa/
-----END PGP SIGNATURE-----


I have a scenario that requires a little more knowledge than I have at this
point. The Table below contains gross revenue information for our facility.
I basically need to calculate Gross Revenue per day using a 3 month average.
The formula is as follows:

Prior 3 Months total revenue divided by number of days in those three
months. This is a fairly straitforward calculation that I can do. The
problem is I need to calculate this for a rolling 12 month time period. So
in essence, March's revenue per day equals January, February, and March total
revenue divided by 90 days. I also need revenue per day for February which
would be December, january and February divided by 90 days. I have a text
box on a form that calculates the days but I can't figure out how to do this
for 12 month rolling timeframe when March Gross Revenue would need to be
included in 3 months of calculations. If I am not making sense please let me
know as this isn't the easiest thing to explain. Please let me know if you
can come up with an answer. Thank you.

Table
FileDate Hospital GrossRevenue

03/31/06 T 63,922.00
02/28/06 T 164,291.00
01/31/06 T 151,654.00
Click to expand...
Click to expand...
 
J

John Spencer

Perhaps the following would work.

---UNTESTED SQL ---

SELECT Distinct Hospital
, FileDate
,
DateDiff("d",DateSerial(Year(FileDate),Month(FileDate)-2,1),DateSerial(Year(FileDate),Month(FileDate)=1,0))
as DaysInPeriod
, (SELECT Sum(T.GrossRevenue)
FROM YourTable as T
WHERE T.FileDate Between
DateSerial(Year(S.FileDate),Month(S.FileDate)-2,1)
AND S.FileDate
AND T.Facility = S.Facility) as TotalRevenue
, (SELECT Sum(T.GrossRevenue)
FROM YourTable as T
WHERE T.FileDate Between
DateSerial(Year(S.FileDate),Month(S.FileDate)-2,1)
AND S.FileDate
AND T.Facility = S.Facility) /
DateDiff("d",DateSerial(Year(FileDate),Month(FileDate)-2,1),DateSerial(Year(FileDate),Month(FileDate)=1,0))
as AvgRevenue
FROM YourTable as S

I've included the calculations for number of day and total revenue as
separate fields so you can check whether or not they are working and whether
or not they are giving correct results. Then I've attempted to put them
together in one calculation to give you the avg revenue for the period



shoe said:
I still don't believe this is what I need. I would like the data to look
like this:
Facility FileDate RevenuePerDay
T 03/31/06 2,500,000
T 02/28/06 2,300,000
T 01/31/06 2,450,000
T 12/31/05 2,.350,000
T 11/30/05 2,500,000
T 10/31/05 2,500,000
T 09/30/05 2,500,000
T 08/31/05 2,500,000

Revenue per day for whichever FileDate would use the formula listed in the
initial question. I really appreciate you responding but the response
doesn't seem to do what I need it to. Please let me know if you know how
this can be done. Thank you.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you truly store the FileDate as the last day of the month, then you
can, possibly, do this:

SELECT Hospital, Sum(GrossRevenue) / 90 As DailyAvg,
Sum(GrossRevenue) As TotalGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,dateadd("m",1, date() -
day(date()) + 1) -1) And dateadd("m",1, date() - day(date()) + 1) -1

The formula dateadd("m",1, date() - day(date()) + 1) -1 finds the last
date of the current month. So, if the current month is Apr, this query
will find the sum of the GrossRevenue for the months of Feb, Mar, and
Apr.

If you want it to find the 3 month total for specific months use this
query:

PARAMETERS [Enter last day of month] Date;
SELECT Hospital, Sum(GrossRevenue) / 90 As 3MonthAvg, Sum(GrossRevenue)
As 3MonthGross
FROM table_name
WHERE FileDate BETWEEN DateAdd("m",-2,[Enter last day of month]) And
[Enter last day of month]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE+0n4echKqOuFEgEQJbbACgy0mbRmxqc3m2B9hfw2LMs4Vmsi4AoKT/
QozVbpUK6Rs23nHzNuvAO8L8
=KPa/
-----END PGP SIGNATURE-----


I have a scenario that requires a little more knowledge than I have at
this
point. The Table below contains gross revenue information for our
facility.
I basically need to calculate Gross Revenue per day using a 3 month
average.
The formula is as follows:

Prior 3 Months total revenue divided by number of days in those three
months. This is a fairly straitforward calculation that I can do. The
problem is I need to calculate this for a rolling 12 month time period.
So
in essence, March's revenue per day equals January, February, and March
total
revenue divided by 90 days. I also need revenue per day for February
which
would be December, january and February divided by 90 days. I have a
text
box on a form that calculates the days but I can't figure out how to do
this
for 12 month rolling timeframe when March Gross Revenue would need to
be
included in 3 months of calculations. If I am not making sense please
let me
know as this isn't the easiest thing to explain. Please let me know if
you
can come up with an answer. Thank you.

Table
FileDate Hospital GrossRevenue

03/31/06 T 63,922.00
02/28/06 T 164,291.00
01/31/06 T 151,654.00
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate difference in rows in a query 1
Trailing 30 days sum calculation with excel 1
Getting wrong information in query 6
Spread Revenue Over Time - Excel Help 0
Creating a "grid" of data 3
Revenue divided by Day How? 4
Working out a value within a certain month. 14
Calculation based upon date 1

Top