0

Below is query ,i want to convert it into sub query

    declare @FirstDOM datetime, @LastDOM datetime
set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())+30),getdate())) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0))) 

--First Query 

SELECT SUM(QTY) AS QTY, SUM(IWeight) AS Weight, EntryDate
FROM     ConIssuance
where EntryDate between @FirstDOM and @LastDOM 
GROUP BY EntryDate
order by EntryDate asc

--Second Query
SELECT SUM(s.prdqty) AS qty, SUM(i.weight) AS Expr1, s.EntryDate
FROM     Probale AS s INNER JOIN
                  ItemMasterFile AS i ON i.CodeItem = s.Codeitem
                  where S.EntryDate between @FirstDOM and @LastDOM 
GROUP BY s.EntryDate

--Third Query

SELECT SUM(Bweight) AS weight, SUM(Bpqty) AS qty, EntryDate
FROM     Bigbalprd
 where EntryDate between @FirstDOM and @LastDOM 
GROUP BY EntryDate

Output Horizontally.

enter image description here

2

you can do it this way:

;WITH FirstQuery  AS (
    SELECT SUM(QTY) AS QTY, SUM(IWeight) AS Weight, EntryDate
    FROM     ConIssuance
    where EntryDate between @FirstDOM and @LastDOM 
    GROUP BY EntryDate
    order by EntryDate asc
)
,SecondQuery AS (
    SELECT SUM(s.prdqty) AS qty, SUM(i.weight) AS Expr1, s.EntryDate
    FROM     Probale AS s INNER JOIN
                      ItemMasterFile AS i ON i.CodeItem = s.Codeitem
                      where S.EntryDate between @FirstDOM and @LastDOM 
    GROUP BY s.EntryDate
)
,ThirdQuery AS (
    SELECT SUM(Bweight) AS weight, SUM(Bpqty) AS qty, EntryDate
    FROM     Bigbalprd
     where EntryDate between @FirstDOM and @LastDOM 
    GROUP BY EntryDate
)
SELECT *
FROM FirstQuery
JOIN SecondQuery ON SecondQuery.EntryDate = FirstQuery.EntryDate
JOIN ThirdQuery ON ThirdQuery.EntryDate = FirstQuery.EntryDate;

If you have gaps (no records for some dates in some of three tables) you may need to use FULL JOIN with a bit more complex join conditions or even generate a list of dates (in case if you need a row for each date even when data for this date is not presented in any of your tables)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.