Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: 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!

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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.
    Last edited by DoktorBlue; 01-08-05 at 14:05.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •