SQL Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query help

Author

Topic

rajeshhariharan
Starting Member

2 Posts

Posted - 2004-02-27 : 18:33:59

have two table

Table 1 Forecast

Fields
Accounting Period (pull up from another table Accounting period Master)
Vendor Name (Pull up from vendor Master table)
Resource Name (Pull up from Resource master table)
Sub Code (Pull up from Sub code master
Forecast Amount

Table 2 Invoice

Fields

Accounting Period (pull up from another table Accounting period Master)
Vendor Name (Pull up from vendor Master table)
Resource Name (Pull up from Resource master table)
Sub Code (Pull up from Sub code master
Invoice Amount

There are other fields also in those tables but will not concern the present Purpose

My objective is to get a query that throws up the Forecas & Actuals data in columnar fashion by accouting period. That is
Column 1 Accoting period 1 Forecast, Column 2 accouting period 1 actuals, Column 3 Accting period 2 Forecats, Column 4 Accounting period 2 Actausl and so on. Grouped by sub code, vandor and resource name.

This list i need for all resources who are either forecasted for or for whom there are actuals.

The current query i have written is:

TRANSFORM Sum([Invoice].[Amount]) AS SumOfAmount
SELECT [invoice].[Sub Code], [invoice].[Vendor Name], [invoice].[Resource Name], [invoice].[Accounting Period]
FROM invoice
GROUP BY [invoice].[Sub Code], [invoice].[Vendor Name], [invoice].[Resource Name], [invoice].[Accounting Period], [invoice].[Sub Code], [invoice].[Resource Name]
ORDER BY [invoice].[Accounting Period], [invoice].[Vendor Name]
PIVOT [invoice].[Accounting Period];

This outputs the actuals data the way I want it. Now I need to get the forecast data in columnar form within this query. I have treid some option but have been unsuccessful.

Pls help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-27 : 18:36:12

You posted the same question in two forums (which isn't necessary BTW), one of them being the Access forum. So is this a SQL Server question or an Access question?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources