--- In firebird-support@yahoogroups.com, Vishal Tiwari <vishualsoft@...> wrote:

>
> Dear Set (A Real Master-Trouble Shooter),
>  
> Let me ask you one question, if I know the name of those books for which I need to print those respective columns then is it easy to write sql ?
>  
> I am ready to get books name at application level (i.e. coding level) and if required I would make the required sql using loop in my application and the this looped sql I would merge (concatinate) with original "Select ..." SQL.
>  
> Is it possible, or if you have any suggestion by which I could get data using only sql, or any this I need to do at application level i.e. any thing required to do at application level and finaly I make the sql, would I get such logic ?
>  

Sure, if you know the name of the books, things are simpler and you can even name the columns as you want (I've only named three fields, but this part is a no-brainer if you can get the name from an application before creating the SQL statement). I assume you're only interested in branches actually having sold the books below:

WITH Branches(BranchCode) as
(SELECT DISTINCT sBrachCode
FROM Book_Details
WHERE (sYearMonth = :YearMonthA or sYearMonth = :YearMonthB)
AND BookName in(:MyFirstBookName, :MySecondBookName)
)

SELECT B.BranchCode, BD1A.rBook_Price as Book1PriceYearMonthA, BD1B.rBook_Price as Book1PriceYearMonthB,
coalesce(BD1A.iCopy_Sold, 0) - coalesce(BD1B.iCopy_Sold, 0) as Book1CopySoldDifference,
coalesce(BD1A.Tot_Amount, 0) - coalesce(BD1B.Tot_Amount, 0),
BD2A.rBook_Price, BD2B.rBook_Price, coalesce(BD2A.iCopy_Sold, 0) -
coalesce(BD2B.iCopy_Sold, 0),
coalesce(BD2A.Tot_Amount, 0) - coalesce(BD2B.Tot_Amount, 0)
FROM Branches B
LEFT JOIN Book_Details BD1A ON B.BranchCode = BD1A.BranchCode
AND BD1A.BookName = :MyFirstBookName
AND BD1A.sYearMonth = :YearMonthA
LEFT JOIN Book_Details BD1B ON B.BranchCode = BD1B.BranchCode
AND BD1B.BookName = :MyFirstBookName
AND BD1B.sYearMonth = :YearMonthB
LEFT JOIN Book_Details BD2A ON B.BranchCode = BD2A.BranchCode
AND BD2A.BookName = :MySecondBookName
AND BD2A.sYearMonth = :YearMonthA
LEFT JOIN Book_Details BD2B ON B.BranchCode = BD2B.BranchCode
AND BD2B.BookName = :MySecondBookName
AND BD2B.sYearMonth = :YearMonthB

HTH,
Set