SQL Query to Union All

Hello there,

I have this query which is working as I expect and it has 3 conditions which I need to show in the result in different columns. I can acheive this by running the query 3 times with different conditions. I can achevie this using the UNION ALL but I want to know is there a better way out.Can somebody please help me. I will appreciate it!!

The conditions are these

         AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
      --AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'

and

       AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
      AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'

and

       AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
      --AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      AND dbo.Batch.expiryDate < '2015-07-23'

SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    SUM(a.AvailQty) as AvailableQty
    --,a.Expirydate
    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
    --,batch.expirydate as Expirydate
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)
      --AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       --,a.Expirydate

Open in new window

zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
seems that the selection criterion are same for all of 3 queries, hence it could be simplified into a simple query IF the SELECT SQL statement of your 3 queries are identical.

But this also depends on how you want to populate the records accordingly. Are all these records populating into a same grid, etc?

if the SELECT SQL statement of your 3 queries are different, you probably need 3 queries or 1 query with UNION/ UNION ALL clause.
0
zolfAuthor Commented:
Ryan Chong:

Thanks for your suggestion. Yes all the 3 queries SELECT are same only the condition are different. I thought to us UNION ALL but I keep getting error. CAn by any chance you try to combine these 3 queries in one using UNION ALL with the different conditions.i will appreciate your help!!
0
zolfAuthor Commented:
Currently to reduce the complexity I am trying to only UNION the first two condions but I get this error

[SELECT - 0 row(s), 0.004 secs]  [Error Code: 207, SQL State: 42S22]  Invalid column name 'AvailableQty1'.


SELECT 
    code,
    productName,
    BranchCode,
    Branch,
    SUM(AvailableQty) as AvailableQty,
    SUM(AvailableQty1) as AvailableQty11
    FROM 
 (

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.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)
      --AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       
       UNION ALL
       
       SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    SUM(a.AvailQty1) as AvailableQty1
    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 AvailQty1
    --,batch.expirydate as Expirydate
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)
      AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       --,a.Expirydate
       )sq

GROUP BY code
       , productname
      , Branchcode
       , branch

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
I guess you may want to derive a "flag" field, so that we can determine the source of records in case similar records are being returned from 2 union-ed queries.

so try:

SELECT 
    flag,
    code,
    productName,
    BranchCode,
    Branch,
    SUM(AvailableQty) as AvailableQty
    FROM 
 (


SELECT 
    '1' as flag,
    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.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)
      --AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       

UNION ALL
       
    
    SELECT 
    '2' as flag,
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    SUM(a.AvailQty1) as AvailableQty1
    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 AvailQty1
    --,batch.expirydate as Expirydate
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)
      AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       --,a.Expirydate

) sq

GROUP BY flag
       , code
       , productname
       , Branchcode
       , branch

Open in new window


will this fulfill your requirement?

OR you need AvailableQty for all your 3 queries individually in a single record?
0
PortletPaulfreelancerCommented:
If I understand your question correctly I believe it requires use of parentheses to form clusters of conditions, then separate those clusters with OR

SELECT ... FROM ...

WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
OR (
        dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
    AND dbo.Batch.expiryDate < DATEADD(MONTH, 3, '2015-07-23')
    )
OR (
        dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
    AND dbo.Batch.expiryDate < '2015-07-23'
    )

Open in new window

But that looks like it can be simplified to:
SELECT ... FROM ...

WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
AND (
        dbo.Batch.expiryDate < DATEADD(MONTH, 3, '2015-07-23')
     OR dbo.Batch.expiryDate < '2015-07-23'
    )
;

Open in new window

But then any date thet is less than 2015-07-23 is also less than DATEADD(MONTH, 3, '2015-07-23') so, again it looks like it can be simplified to:
SELECT ... FROM ...

WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
AND dbo.Batch.expiryDate < DATEADD(MONTH, 3, '2015-07-23')
;

Open in new window

0
zolfAuthor Commented:
Guys thanks for your inputs. I managed to get the result of those 3 conditions into one query using UNION ALL but just for my information is there a better way to this issue. For e.g. using SSIS or some other options.

SELECT 
    code,
    productName,
    BranchCode,
    Branch,
    SUM(AvailableQty) as AvailableQty,
    SUM(AvailableQty1) as AvailableQtyLess3,
     SUM(AvailableQtyExpired) as AvailableQtyExpired
    FROM 
 (

SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    SUM(a.AvailQty) as AvailableQty,
     CONVERT(INT,null) AvailableQty1,
     CONVERT(INT,null) AvailableQtyExpired
    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.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)
      --AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       
       UNION ALL
       
       SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    CONVERT(INT,null) AvailableQty,
    SUM(a.AvailQty1) as AvailableQty1,
    CONVERT(INT,null) AvailableQtyExpired
    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 AvailQty1
    --,batch.expirydate as Expirydate
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)
      AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      --AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       
       
       UNION ALL
       
       SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    CONVERT(INT,null) AvailableQty,
    CONVERT(INT,null) AvailableQty1,
    SUM(a.AvailQty1) as AvailableQtyExpired
    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 AvailQty1
    --,batch.expirydate as Expirydate
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)
      AND dbo.Batch.expiryDate < dateadd(month,3,'2015-07-23')
      AND dbo.Batch.expiryDate < '2015-07-23'
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       
       
       --,a.Expirydate
       )sq

GROUP BY code
       , productname
      , Branchcode
       , branch

Open in new window

0
PortletPaulfreelancerCommented:
>>"is there a better way to this issue"

YES, a much better way I think! I don't believe you need any unions.

&: Not sure how to put this, but there are conditions in your HAVING clause that should be in the WHERE clause.

The having clause is for calculated information  that is only available AFTER the group by, it should NOT be used as an alternative to the where clause.

Use the having clause for calculations that depend on the group by
e.g.
HAVING  SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0

excellent!, that SUM() is only available after the group by

Please note that you can SUM() a "case expression"
e.g. SUM( case when column = 'a' then qty else 0 end )

In your query those 3 conditions, currently in 3 different queries, are just 3 different conditions to produce 3 columns.
one column is produced from all rows: WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1)
the other 2 columns depend on dates.

Try this:
SELECT
      RIGHT('000' + CAST(kol.code AS varchar), 3) 
    + RIGHT('000' + 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 AvailableQty

    , SUM(CASE WHEN dbo.Batch.expiryDate < DATEADD(MONTH, 3, '2015-07-23')
                THEN dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity ELSE 0 END) AS AvailableQtyLess3

    , SUM(CASE WHEN dbo.Batch.expiryDate < '2015-07-23' 
                THEN dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity ELSE 0 END) AS AvailableQtyExpired

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

WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1)

GROUP BY
      RIGHT('000' + CAST(kol.code AS varchar), 3) 
    + RIGHT('000' + CONVERT(varchar, dbo.Mohin.code), 3) 
    + '' 
    + RIGHT('0000' + CAST(tafsil.code AS varchar), 4) 
    , dbo.Tafsil.description
    , dbo.Branch.code
    , dbo.Branch.name
HAVING SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
;

Open in new window

{+ please note there have been edits}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
cheers!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.