Query for Report

[JDSmith14]

Hi
I have a table containing predicted cashflow for a certain project for a certain date.
Example:
ProjNum|Date |Predicted amount|
1 |11/11/2005|2000 |
2 |01/01/2004|3000 |

Then I have a similar actual expenses table:

ProjNum|Date |Spent amount|
1 |10/11/2005|200 |
2 |01/11/2004|300 |

I need to draw up a query that shows the predicted and actual expenses per project on a monthly basis for a certain financial year.
I also need a total for predicedt and actual cashflow for the year, as well as the difference between the two values.

Predict 03/05|Actual 03/05|Predict 04/05|Actual 04/05|......|Total Predicted|Total Actual|Difference|


Any help will be highly appreciated.

Thanks
Johan

 

[druer]

This would be an excellent sample to start using OLAP and SQL Analysis on.

What I would suggest is creating another table either in another database or another server if you have the resources and then putting a job in place that will migrate the data to different tables that contain the following, where you use the Year and Month functions to convert your dates during the move. Then assuming you have a table like:

Year Month Project Predicted
2005 1 1 203.04
2005 1 1 119.07

Year Month Project Spent
2005 1 1 193.03
etc (where each of your date values get converted to just the month/year values, and you move over the project ids and dollar values.)

The idea with porting the data is that it is going to remain static for the end of time, so why bother ever having to deal with it again in future queries.

Put a job in place that will migrate only the previous months data to these 2 new tables. And with tables like above you can easily do selects with Group By's and Sum's to get totals for months, and years. And easily can do the same and join Predicted to Actual for comparisons. If you don't care about tracking back to the original dollar values for certain days you can do the summing up during the port itself so your tables would simply have:

Year Month Predicted Spent
2005 1 394,039.03 387,030.00
2005 2 287,938.99 etc

Where you would only keep the actual sum's of the data, and if you only keep the actual sums for the months you can just combine them together. As with any OLAP type data tables, This does of course assume that you never go back into earlier months data records to change the values.

I will try to come up with a query to show you that you can do this with the live data, but the idea is that each time you run it you will be doing the same exact work you did previously, and could have retained to make the querying go faster.

 

[JDSmith14]

Thanks Druer

I tried your suggestion in a view just to test. In principle it works fine, creating the table with day, month, year, project refrence and amount spent.

My problem is still however to query out a value per project per month for the 12 months of the financial year. I need the following:

Actual month1, actual month2 etc.

I do however need this result as a column in the query, as there are still a lot more information to bew added to this report. Simply having this as a row will not be sufficient.

Thanks!

 

[druer]

Ooooh I got ya now. I don't have access to a server right now or I would test the following for you. I think it should work but if you have problems just let me know and I will have access on Monday morning.

select t1.ProjectNum, @Year as Year, t1.Predicted, t1.Actual, t2.Predicted, t2.Actual, t3.Predicted, t3.Actual, etc for t4 - t12
from
(select ProjectNum, Year, sum(Predicted), sum(Actual) from TableValues where year = @Year and month = 1 group by probjectnum) t1 inner join
(select ProjectNum, Year, sum(Predicted), sum(Actual) from TableValues where year = @Year and month = 1 group by probjectnum) t2 on t1.ProjectNum = t2.ProjectNum
etc for months 3 - 12


Off the top of my head right now I'm thinking this should do it for you. Each of the sub selects returns there results to a TABLE Variable T1 - T12 for each of the months. The inner joins between the Table variables should line things up by project number just fine. You can obviously vary the the Month #s according to what your actual financial year is for. You can either hardcode the Year into the query or use it as a variable in a stored procedure you can then call to pass in which year you want.

Hope this helps, (sorry I missed the point of having it all in one row)

PS - You could do the same above from your orignal tables by altering each of the sub selects as:
(select ProjectNum, sum(Predicted), sum(Actual) from TableValues where Year(ExpenseDate) = @Year and Month(ExpenseDate) = 1 group by probjectnum) t1 inner join
etc

 

[JDSmith14]

Thanks Druer

You are the man!

I eventually got a sample working with the following code:

SELECT
T1.ProjRef,
SUM(T1.IT1) AS JanTotal,
SUM(T2.IT2) AS febTotal

FROM

(select ProjRef, sum(InvoiceTotal) as iT1 from ProjectActual
where
(YEAR(ProjectActual.SourceDocDate) = 2006) AND (MONTH(ProjectActual.SourceDocDate) = 1)
group by projectActual.projRef) as t1

inner join
(select ProjRef, sum(InvoiceTotal) as iT2 from ProjectActual
where
(YEAR(ProjectActual.SourceDocDate) = 2006) AND (MONTH(ProjectActual.SourceDocDate) = 2)
group by projectActual.projRef) as t2 on t1.projref = t2.projref

group by t1.projref

Still dont understand it 100%, but will keep looking at it.

Thanks for your help. It is much appreciated!
Regards
Johan

 

[druer]

Glad I was able to help. Felt bad I was out of reach of server to validate suggestion.