Hello,
I am working on a report in which i need department wise order details for current month
report should showed all departments and although ordered qty is null for one or department
for that i am using below query
SELECT distinct T0.[OcrCode],Isnull(Sum(T0.[Quantity]) ,0) AS 'Booking As on date in Current Month', Isnull( Sum( T0.[opencreqty]) ,0) As 'TOTAL PENDING' FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[OcrCode] <>'Store' and T0.[OcrCode] <>'mktg' and T0.[OcrCode] <>'acct' and DateDiff(MM,T0.DocDate,Getdate())=0 GROUP BY T0.[OcrCode]
But it is not working showing only those departments which has ordered quantity
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