What type of database are you connecting to and what type of connection are you using?
For most databases, the "Sum()" function will never return null - it will default to 0. Also, when you use Sums, you'll never get duplicate values, so you don't need to use "Distinct"
If you're using SQL Server, I might change the query to something like this:
SELECT
OcrCode,
Sum(Quantity) AS 'Booking As on date in Current Month',
Sum(OpenCreQty) As 'TOTAL PENDING'
from (
SELECT distinct
T0.[OcrCode],
IsNull(T0.[Quantity] ,0) as Quantity,
Isnull(T0.[opencreqty] ,0) as OpenCreQty
FROM RDR1 T0
INNER JOIN ORDR T1
ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[OcrCode] not in ('Store', 'mktg', 'acct')
and DateDiff(MM,T0.DocDate,Getdate())=0 ) as data
GROUP BY OcrCode
Will the RDR1 table always have a record for the department even if there are no orders? What about the ORDR table? If not, then that's what's causing the departments to not appear. In that case, you would need to have a join from whatever table contains the list of valid departments and their descriptions to RDR1 and use the fields from that table instead of RDR1 to show the department.
-Dell
Add comment