SQL Query Problem

Arifullah Irfann

6y1.5k

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)

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankajkumar Patel6y ago
Accepted 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!
+2
Arifullah Irfann6y ago
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
0
Suraj Kumar6y ago
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
0
Jignesh Kumar6y ago
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
0
Arifullah Irfann6y ago
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
0
Rafnas T P6y ago
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.
0
Rafnas T P6y ago
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.
0
Suraj Kumar6y ago
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
0
Suraj Kumar6y ago
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.
0
Amit Mohanty6y ago
Hey you have to give Date condtion in both table. Bcoz you need total sale qty and total purchase qty from different table.
0