Subtraction of two queries in MS SQL

Discussion in 'Databases' started by Master Sensei, Mar 5, 2013.

  1. #1
    Hello! Please help to solve the issue.
    I can't create the proper working request, which could calculate the difference between two queries.
    In order to get the quantity of goods in the store, we need todeduct the amount of goods sold from the sum of all products from all suppliers.

    There are two separate queries.
    1. First calculates the sum of all products from all suppliers.

    SELECT CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) AS Dat,
    SUM(nb.TEreport.goods) AS Goods
    FROM nb.TEreport INNER JOIN nb.TEprovider ON nb.TEreport.id_provider = nb.TEprovider.id
    WHERE (LEFT(nb.TEprovider.name, 10) != 'FROM STORE')
    AND (CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) <= CONVERT(VARCHAR, GETDATE(), 102))
    AND CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) >= '2013.01.01'
    GROUP BY CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102)
    ORDER BY Dat DESC

    2. Second calculates the amount of goods sold.

    SELECT CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) AS Dat,
    SUM(nb.TEreport.goods) AS Goods
    FROM nb.TEreport INNER JOIN nb.TEway ON nb.TEreport.id_way = nb.TEway.id
    WHERE (SUBSTRING(nb.TEway.name, 5, 8) != 'TO STORE')
    AND (CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) <= CONVERT(VARCHAR, GETDATE(), 102))
    AND CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) >= '2013.01.01'
    GROUP BY CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102)
    ORDER BY Dat DESC

    3. I tried to do with subqueries, but it is not what is actually needed
    For example,

    SELECT CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) AS Dat,
    (SELECT SUM(nb.TEreport.weight)
    FROM nb.TEreport INNER JOIN nb.TEprovider ON nb.TEreport.id_provider = nb.TEprovider.id
    WHERE LEFT(nb.TEprovider.name, 10) != 'FROM STORE')-
    (SELECT SUM(nb.TEreport.weight)
    FROM nb.TEreport INNER JOIN nb.TEway ON nb.TEreport.id_way = nb.TEway.id
    WHERE SUBSTRING(nb.TEway.name, 5, 8) != 'TO STORE')
    FROM nb.TEreport
    WHERE CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) <= CONVERT(VARCHAR, GETDATE(), 102)
    AND CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102) >= '2013.01.01'
    GROUP BY CONVERT(VARCHAR, DATEADD(s, nb.TEreport.dat, 25568), 102)
    ORDER BY Dat ASC


    But in this case, the query gives no specific amount for a specific day, it just gives the sum of all numbers for the period.
    How can I solve the problem?

    P.S. Date is stored in seconds, that is why I used date converter .
     
    Last edited: Mar 5, 2013
    Master Sensei, Mar 5, 2013 IP