SQL Query Problem

Question

Hello.

I Have Two Table From Which I want to Create Query

1) Table - Purchase

PurchaseID,ItemID,PurchaseDate,PurchaseQuantity

2) Table- Sale

SaleID,ItemID,SaleDate,SaleQuantity

Now I want to Create A query to Give this Result Between Two Dates From Both Tables

ItemID

TotalPurchaseQuantity

TotalSaleQuantity

The Problem is Date From Which Table I should Give Date To Query (Sale Table Or Purchase)

Pankaj Patel · Answer

Hi Arifullah Irfann, Can you please try following: select ItemID, sum (PurchaseQuantity) as TotalPurchaseQuantity, sum (SaleQuantity) as TotalSaleQuantity from ( select ItemID,0 as PurchaseQuantity, SaleQuantity,SaleDate as createdDate from Sale union select ItemID, PurchaseQuantity,0 as SaleQuantity,PurchaseDate as createdDate from Purchase ) a where cast (createdDate as date ) between '2019-10-01' and '2019-11-30' group by ItemID Hope, this will help you! -------------------------------------------------------------------------------- Hi Arifullah Irfann, Good to know that query worked. Now, you want to get ItemName also from Item Table. To Join it with item table "CTE Query" can be used like following: with CTE_Purchase_Sale as ( select a.ItemID, sum (PurchaseQuantity) as TotalPurchaseQuantity, sum (SaleQuantity) as TotalSaleQuantity from ( select ItemID,0 as PurchaseQuantity, SaleQuantity,SaleDate as createdDate from Sale union select ItemID, PurchaseQuantity,0 as SaleQuantity,PurchaseDate as createdDate from Purchase ) a where cast (createdDate as date ) between '2019-10-01' and '2019-11-30' group by a.ItemID) select temp .ItemID, Item. Name as ItemName, temp .TotalPurchaseQuantity, temp .TotalSaleQuantity from CTE_Purchase_Sale as temp inner join Item on Item.ItemID = temp .ItemID Hope, this will help you!

Jignesh Kumar · Answer

Hey , Please use below query which will help to resolve your query, Declare @startDate datetime , @enddate datetime set @startDate = Getdate()-11 set @enddate = Getdate() select p.ItemID, i.ItemName as ItemName, sum (p.PurchaseQuantity) as TotalPurchaseQuantity, sum (s.SaleQuantity) as TotalSaleQuantity from Purchase p left join sale s on p.ItemID = s.ItemID left join Items i on i.ItemID = p.ItemID where p.PurchaseDate between @startDate and @enddate and s.SaleDate between @startDate and @enddate group by p.ItemID, i.ItemName

Rafnas T P · Answer

if you want to get both nomber in between dates try following query select ItemID, sum (PurchaseQuantity) as TotalPurchaseQuantity, sum (SaleQuantity) as TotalSaleQuantity,createdDate from ( select ItemID,0 as PurchaseQuantity, SaleQuantity,SaleDate as createdDate from Sale union all select ItemID, PurchaseQuantity,0 as SaleQuantity,PurchaseDate as createdDate from Purchase ) a where cast (createdDate as date ) between @startDate and @EndDate group by createdDate,ItemID Thank you.

Amit Mohanty · Answer

Hey you have to give Date condtion in both table. Bcoz you need total sale qty and total purchase qty from different table.

Arifullah Irfann · Answer

Thanks alot Pankaj Patel its working As a last question Now the item table has relationship with category table Category table has colums [CategoryID , CategoryName] the relationship is CategoryId in item table Now I want to Display Records Like ItemId, ItemName, CategoryName,TotalsaleQty,TotalpurchaseQty

Suraj Kumar · Answer

As you told the query is working so to get the Itemname you can join the ItemMaster table as shown below. Select a.ItemID, a.ItemName, sum (PurchaseQuantity) as TotalPurchaseQuantity, sum (SaleQuantity) as TotalSaleQuantity,createdDate from ( select ItemMaster.ItemID, ItemMaster.ItemName, 0 as PurchaseQuantity, SaleQuantity,SaleDate as createdDate from Sale inner join ItemMaster on Sale.ItemId = ItemMaster.ItemId union all select ItemMaster.ItemID, ItemMaster.ItemName, PurchaseQuantity,0 as SaleQuantity,PurchaseDate as createdDate from Purchase inner join ItemMaster on Purchase.ItemId = ItemMaster.ItemId ) a where cast (createdDate as date ) between @startDate and @EndDate group by createdDate, ItemName, ItemID

Arifullah Irfann · Answer

Thanks Pankaj Patel your query worked but this both purchase and sale table Have relationship with item table Now i want to get ItemName Also From Item Table How to Join it with item Table select ItemID,ItemName, sum (PurchaseQuantity) as TotalPurchaseQuantity, sum (SaleQuantity) as TotalSaleQuantity from ( select ItemID,0 as PurchaseQuantity, SaleQuantity,SaleDate as createdDate from Sale union select ItemID, PurchaseQuantity,0 as SaleQuantity,PurchaseDate as createdDate from Purchase ) a where cast (createdDate as date ) between '2019-10-01' and '2019-11-30' group by ItemID

Rafnas T P · Answer

Hi, if you want to get sales record like how many sales done in between some dates then you have to give saleDate in condition or if you wanted to get purchase details then you have to give purchase date in condition.

Suraj Kumar · Answer

If you to check the item has been purchased but it has been sale or not and how much then you need to give OR condition for sale date as shown in the below query. Select p.ItemID, s.ItemID, SUM ( isnull (p.PurchaseQuantity, 0)) as TotalPurchaseQuantity, SUM ( isnull (s.SaleQuantity, 0)) as TotalSaleQuantity from Purchase p left join Sale s on p.ItemID = s.ItemID where p.PurchaseDate between @startDate and @endDate OR s.SaleDate between @startDate and @endDate Group By p.ItemID, s.ItemID

Suraj Kumar · Answer

You can try the following query to get the desired result. Create table Purchase (PurchaseID int , ItemID int , PurchaseDate Datetime, PurchaseQuantity int ) insert into Purchase Values (1, 1, '2019-12-01' , 10), (2, 3, '2019-12-02' , 20), (3, 2, '2019-12-03' , 30), (4, 3, '2019-12-04' , 10), (5, 2, '2019-12-04' , 50) Create table Sale (SaleID int , ItemID int , SaleDate Datetime, SaleQuantity int ) insert into Sale Values (1, 2, '2019-12-03' , 5), (1, 2, '2019-12-03' , 3), (1, 3, '2019-12-03' , 10) declare @startDate datetime = '2019-12-01' declare @endDate datetime = '2019-12-04' Select p.ItemID, SUM ( isnull (p.PurchaseQuantity, 0)) as TotalPurchaseQuantity, SUM ( isnull (s.SaleQuantity, 0)) as TotalSaleQuantity from Purchase p left join Sale s on p.ItemID = s.ItemID where p.PurchaseDate between @startDate and @endDate and s.SaleDate between @startDate and @endDate Group By p.ItemID Hope this will help you.

SQL Query Problem

Answers (10)