Help with simple query involving 3 tables

Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:

create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go

create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go

create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go

I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @.MinDate - @.MaxDate)

Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--- ------ ----- ------ --
HR 5 7 12 .84
Finance 10 15 25 1.75

So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.

One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!SELECT U.name, sum(J.NumberPrintedPages), sum(J.NumberPrintedPages * P.PaperCost)
FROM Jobs J INNER JOIN User_Tbl U ON J.User_ID = U.ID
INNER JOIN Printer_Tbl P ON J.Printer_ID = P.ID
GROUP BY U.Name

This example shows, how to join your tables, and how to return two of your fields. Since a user may have used 0 to n printers in his jobs, there isn't a clear indication of your fields #PagesPrinter1 and #PagesPrinter2.|||He wants a crosstab query.

Nicomachus, look up CROSSTAB in Books Online and you will see an excelent example of how to accomplish this using CASE statements. Unfortunately, it requires considerable programming to make your crosstab queries dynamic as the number of columns (printers) changes.
Supposedly this feature will be built into the next version of SQL Server, but in any case when you make your output dynamic you are going to have a hard time building reports around it, because the output format will not be consistent.

You are really best served by outputting your data in a standard normalized format and then letting your reporting application (Crystal, Access, whatever...) handle formatting as a crosstab.

Help with Query please

I have a requirement involving writing a report that at first appeared basic, but is driving me crazy.

I have to deliver a financial report that contains 4 aggregate columns - 2 averages, and 2 SUMs.

I am running into issues with my query when I add a particular WHERE clause.

I need to filter the report based on one of two conditions. Either an item has been shipped, or an item has been marked "ready" to ship.

There's an audit table that I am using as part of my join, because it contains the dates these statuses were marked. I need these dates because the report has a column that will designate what day of the week the status was marked.

The "shipped" items show up correctly, but the "ready to ship" items ALWAYS end up including those records that are between ready to ship and ship.

So, for example's sake, there is a "prepared" state. This state is between "ready to ship" and "shipped". My query returns shipped also, and I dont want those records, just those where the latest status is "ready to ship". As soon as its flagged "prepared" it should no longer appear.

I've been using MAX on the ID of the states (theyre stored in a separate table) but this is not working.

I know this is a vague explanation, but I am hoping someone will offer some insight into the correct SQL needed to complete this query.

Here is what I have so far. This inner most query returns the IDs that I want, distinctly. So I'm comfortable with this.

SELECT 'ConfReceived' as 'Status', cdq.UniversalOrderID FROM CustomerDoorQuotes cdq
INNER JOIN CustomerInformation ci
ON ci.CustomerID = cdq.CustomerID
INNER JOIN PipelineAudit pa
ON pa.UniversalOrderID = cdq.UniversalOrderID
LEFT JOIN ProductionScheduleStatuses pss
ON cdq.UniversalOrderID = pss.UniversalOrderID
GROUP BY ALL cdq.UniversalOrderID
HAVING MAX(PipelineStatusID) = 3
UNION

SELECT 'Shipped' as 'Status', cdq.UniversalOrderID FROM CustomerDoorQuotes cdq
INNER JOIN CustomerInformation ci
ON ci.CustomerID = cdq.CustomerID
INNER JOIN PipelineAudit pa
ON pa.UniversalOrderID = cdq.UniversalOrderID
LEFT JOIN ProductionScheduleStatuses pss
ON cdq.UniversalOrderID = pss.UniversalOrderID
GROUP BY ALL cdq.UniversalOrderID
HAVING MAX(PipelineStatusID) = 6

Its when I wrap another query around it that I get too many records. PipelineAudit is the offending table for sure. This is the table that holds all the statuses, and when my outer query sees the in between status of "prepared" it includes it in the result set.

If you r using crystal report for reporting purpose, then try to do avg and sum in there it will ease you work