Saturday, May 12, 2012 10:14 AM
i have two relational tables like
I want to create supplier from these two tables
ReceivingID <-----------------> ReceivingID
following is my query
SELECT TOP (100) PERCENT dbo.Requistion.ReceivingID, dbo.Requistion.Date, dbo.Requistion.SupplierName, dbo.RequisitionDetail.ItemName, dbo.RequisitionDetail.Accept, dbo.RequisitionDetail.UnitPrice, dbo.RequisitionDetail.Cost, dbo.RequisitionDetail.TotalCost AS TotalAmountFROM dbo.Requistion INNER JOIN dbo.RequisitionDetail ON dbo.Requistion.ReceivingID = dbo.RequisitionDetail.ReceivingID AND dbo.Requistion.ReceivingID = dbo.RequisitionDetail.ReceivingID CROSS JOIN dbo.SuppliersGROUP BY dbo.Requistion.ReceivingID, dbo.Requistion.Date, dbo.Requistion.SupplierName, dbo.Requistion.OrderNo, dbo.Requistion.OrderDate, dbo.RequisitionDetail.ItemName, dbo.RequisitionDetail.Accept, dbo.RequisitionDetail.UnitPrice, dbo.RequisitionDetail.Cost, dbo.RequisitionDetail.TotalCostORDER BY dbo.Requistion.ReceivingID
now i want to create Suppliers Ledger date wiseplease help me
Saturday, May 12, 2012 11:20 AM
I don't have the tables + data to test it, but try it this way.
SELECT R.ReceivingID, R.Date, R.SupplierName, RD.ItemName, RD.Accept, RD.UnitPrice, SUM(RD.Cost) AS CostSum, SUM(RD.TotalCost) AS TotalAmount FROM dbo.Requistion AS R INNER JOIN dbo.RequisitionDetail AS RD ON R.ReceivingID = RD.ReceivingID AND R.ReceivingID = RD.ReceivingID GROUP BY R.ReceivingID, R.Date, R.SupplierName, R.OrderNo, R.OrderDate, RD.ItemName, RD.Accept, RD.UnitPrice, ORDER BY R.ReceivingID
You should use alias like here R + RD.
What do you suppose to do with the CROSS JOIN on supplier? The table isn't used in your script.
Sunday, May 13, 2012 4:05 AMPlease post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
Most of your data element names are vague garbage reserved words or verbs! You duplicated a join condition. Suppliers is never used in the query, but it will make a huge cross join. This is the usual pattern for this type of query.
SELECT R.receipt_nbr, R.receipt_date, R.sup_name,
RD.item_name, RD.accepted_qty, RD.unit_price, RD.unit_cost
FROM Requisitions AS R,
Requisition_Details AS RD,
WHERE R.receipt_nbr = RD.receipt_nbr
>> now I want to create Suppliers Ledger date wise <<
That makes no sense and we have no spec. Also “date wise” is bad English. Try again if you really want help.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
- Marked As Answer by Iric WenModerator Monday, May 21, 2012 1:35 AM