SELECT
a.code,
a.productName,
a. BranchCode,
a.Branch,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity
) AS AvailQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
SELECT
code,
productname,
Branch.code,
Branch.name AS Branch,
SUM(AvailQty) AS SalesQty,
SUM(AvailableQty) as AvailableQty
FROM (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
cast(null as int) as AvailableQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
UNION ALL
SELECT
a.code,
a.productName,
a. BranchCode,
a.Branch,
null,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
null,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
) sq
GROUP BY productcode
, productname
, branch
SELECT
code,
productname,
Branch.code as BranchCode,
Branch.name AS Branch,
SUM(AvailQty) AS SalesQty,
SUM(AvailableQty) as AvailableQty
FROM (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
cast(null as int) as AvailableQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
UNION ALL
SELECT
a.code,
a.productName,
a.BranchCode,
a.Branch,
CONVERT(INT,null) AS SalesQty,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
null,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
) sq
GROUP BY productcode
, productname
, branch
SELECT
code,
productname,
Branchcode,
Branch.name AS Branch,
SUM(AvailQty) AS SalesQty,
SUM(AvailableQty) as AvailableQty
FROM (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code AS Branchcode,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
cast(null as int) as AvailableQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
UNION ALL
SELECT
a.code,
a.productName,
a. BranchCode,
a.Branch,
CONVERT(INT,null) AvailQty,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
null,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
) sq
GROUP BY code
, productname
, branch
No column name was specified for column 5 of 'a'. 2) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
Column 'sq.Branchcode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
To apply union to two recordset
you have to have same data type to each column in both query
select col1 from tabl1
union
select col2 from tabl2
Number of columns and datatype should be same