query for reporting - 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

Transact-SQL (2000)

query for reporting

Author

Topic

trilobyte
Starting Member

1 Post

Posted - 2010-01-09 : 13:06:47

hi all; i was wondering if someone would be kind enough to point me in the right direction...I am still noobing at SQL and would really appreciate it...

I have 3 tables i need to generate a report from...

File
-----
File_ID (PK)
File_name
File_Date

Order
------
order_ID(PK)
order_Qty
file_ID (FK)
Batch_ID (FK)
Order_piece_value (this * order_Qty would give my my total order_ID value)

Batch
------
Batch_ID (PK)

so basically, everytime i get in a new file, i have a bunch of orders that need to be seperated into new batchs...

I want to build a one-stop query that would generate the data for this report for a specific file: I would prefer to build a view that houses all the files, and i can use a Select * from vw_file_Details where file_ID = 123123 in my code....

File ID: 123123
File Name: xxxxx.txt
File Date: 1/1/2010
Total Quantity: 12,520 <this would be a sum of all order_qty associated with file_ID in question>
Number of Orders: 25 <count of all order_ID associated with file_ID in question>
Number of Batches: 4 <count of all batch_ID associated with all orders associated with File_ID in question>
Total Value: $240,000 <Sum (order_qty*order_piece_value) of all orders associated with file_ID in question>

Can someone please help me get on track here?

THANKS!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-10 : 00:56:09

its pretty straightforward


CREATE VIEW vw_FileDetails
AS
SELECT f.File_ID,f.File_name,f.File_Date,
o.TotalQty,o.[No of Orders],o.[No of Batches],o.[Total Value]
FROM File f
INNER JOIN (SELECT File_ID,
            SUM(order_Qty) AS TotalQty,
            COUNT(order_ID) AS [No of Orders],
            COUNT(DISTINCT Batch_ID) AS [No of Batches],
            Sum (order_qty*order_piece_value) AS [Total Value]
            FROM Order
            GROUP BY File_ID)o
ON o.File_ID = f.File_ID
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources