Select from multiple tables - 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

General SQL Server Forums

New to SQL Server Programming

Select from multiple tables

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2010-04-26 : 10:35:15

hi every 1;
i am developing an application to manage storage for a company
how to write a procedure (that takes one parameter @day date) to select for any given day :
- the quantity of each product that was found in the storage in this given day ,
- the quantity of each product in the day before this day
the Quantity of the products increases by the Adding invoices which stored in the table Adding_Per and Adding_Per_Products
and it is decreases by the Spending invoices which stored in the table Spendine_Per and Spending_Per_Products
i have the following tables

create table products
(
Product_Id int not null identity(1,1) constraint PK_Product_Id primary key,
Product_Name varchar(50) not null unique,
Product_Type_Id int not null constraint FK_Product_Tyidd2 foreign key references Products_types(Type_Idd),
quantity int not null,
Unit_Id int not null constraint FK_tid foreign key references Units(Unit_Id),
Price money not null
)
go
create table Spending_Sides
(
SpendSide_Id int not null identity(1,1) constraint PK_SpendSide_Id primary key,
Spend_Side varchar(50)
)
gocreate table Suppliers
(
Supplier_Id int not null identity(1,1) constraint PK_Supp_Id primary key,
Supplier varchar(50) not null unique
)
go
create table Adding_Per
(
Add_Per_Id int not null identity(1,1) constraint PK_Add_Perid primary key,
Add_Per_Date date,
Supplier_Id int not null constraint FK_Suppid foreign key references Suppliers(Supplier_Id),
Total money
)
go
create table Adding_Per_Products
(
Add_Per_Id int not null constraint FK_Addd foreign key references Adding_Per(Add_Per_Id),
Product_Id int not null constraint FK_Product_Id foreign key references Products(Product_Id),
Quantity int not null,
Unit_Id int not null constraint FK_Unit_Id foreign key references Units(Unit_Id)
)
go
create table Spendine_Per
(
Spend_Per_Id int not null identity(1,1) constraint PK_Spend_Id primary key,
Spend_Per_date date,
Spend_Side int not null constraint FK_Ssepdn foreign key references Spending_Sides(SpendSide_Id),
Total money default (0)
)
go
create table Spending_Per_Products
(
Spend_Per_Id int not null constraint FK_sder foreign key references Spendine_Per(Spend_Per_Id),
Product_Id int not null constraint FK_P12Id foreign key references Products(Product_Id),
Quantity int not null,
Unit_Id int not null constraint FK_Unit_Id15 foreign key references Units(Unit_Id)
)
go

consider that we have the following quantities

Tuesday Monday
current Quantity Q1 Q2
Added quantity A1 A2
Spend Quantity S1 S2

so the quantity of the product in Monday was Q1-A2+S2

i want to select the data in the form

if @day = '2010-04-20'
Product Previous_quantity(in '201-04-19') current_quantity(in 2010-04-20) Added_Quantity Spend_Quantity
a
b

for all products listed in Products table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 01:29:02

something like


CREATE PROC GetProductDetails
@day datetime
AS
SELECT p.Product_Name,
cng.DateVal,
p.Quantity+ SUM(CASE WHEN cng.Category='Add' THEN TotalQty ELSE -TotalQty END) AS ConsolidatedQty
FROM Products p
LEFT JOIN (SELECT spp.Product_Id,DATEADD(dd,DATEDIFF(dd,0,sp.Spend_Per_date)-1,0) AS DateVal,SUM(spp.Quantity) AS TotalQty,'Spend' AS Category
            FROM Spendine_Per sp
            INNER JOIN Spending_Per_Products spp
            ON spp.Spend_Per_Id = sp.Spend_Per_Id
            AND Spend_Per_date>= DATEADD(dd,DATEDIFF(dd,0,@date)-1,0)
            AND Spend_Per_date< DATEADD(dd,DATEDIFF(dd,0,@date)+1,0)
            GROUP BY Product_Id,DATEADD(dd,DATEDIFF(dd,0,ap.Spend_Per_date)-1,0)
UNION ALL            
SELECT app.Product_Id,DATEADD(dd,DATEDIFF(dd,0,ap.Add_Per_date)-1,0) AS DateVal,SUM(app.Quantity) AS TotalQty,'Add'
            FROM Adding_Per ap
            INNER JOIN Adding_Per_Products app
            ON app.Add_Per_Id  = ap.Add_Per_Id 
            AND Add_Per_date>= DATEADD(dd,DATEDIFF(dd,0,@date)-1,0)
            AND Add_Per_date< DATEADD(dd,DATEDIFF(dd,0,@date)+1,0)
            GROUP BY Product_Id,DATEADD(dd,DATEDIFF(dd,0,ap.Add_Per_date)-1,0)
            ) cng
ON cng.Product_Id = p.Product_Id 
GROUP BY p.Product_Id,cng.DateVal,p.Quantity

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources