Sub _Grand total Logic Missing on Query

[former_member188586]

hello gurus

SELECT Distinct T1.[U_BGNO], T1.[U_TYPE], T1.[U_BNAME], T1.[U_CNAME] "Project", T1.[U_SITE] "Department Name", T1.[U_AMOUNT],
(SELECT max(b.[U_EXTEND]) FROM [dbo].[@I8_BGEXT] a inner join [dbo].[@I8_BGE1] b on a.[DocEntry] = b.[DocEntry] inner join [dbo].[@I8_BGU] c on c.[U_BGNO]= a.[U_BGNO] where a.[U_BGNO]= T1.[U_BGNO] AND T1.[U_TYPE]in ('MOBILIZATION','BALANCE EMD','EMD','FSD','PERFORMANCE','SECURITY DEPOSIT','SUPPLIER SECURITY DEPOSIT') AND T1.[U_CNAME]='ESI Qtrs Andri Mumbai' ) as "Due Upto",T2.[U_VDate] "CliamDate",

(SELECT sum(a1.[U_AMOUNT]) FROM [dbo].[@I8_BGEXT] a1 inner join [dbo].[@I8_BGE1] b1 on a1.[DocEntry] = b1.[DocEntry] inner join [dbo].[@I8_BGU] c1 on c1.[U_BGNO]= a1.[U_BGNO] where a1.[U_BGNO]= T1.[U_BGNO] AND a1.[U_TYPE]=T1.[U_TYPE] AND a1.[U_CNAME]=T1.[U_CNAME] ) as "GTOTAL"

FROM [dbo].[@I8_BGEXT] T1 inner join [dbo].[@I8_BGE1] T2 on T1.[DocEntry] = T2.[DocEntry] inner join [dbo].[@I8_BGU] T3 on T3.[U_BGNO]= T1.[U_BGNO]

where T3.[U_STATUS] in('Open','Extended') AND T1.[U_TYPE]in ('MOBILIZATION','BALANCE EMD','EMD','FSD','PERFORMANCE','SECURITY DEPOSIT','SUPPLIER SECURITY DEPOSIT') AND T1.[U_CNAME]='ESI Qtrs Andri Mumbai'

The above Query is not working for grand total,please help me to correct

Thanks for advance help

[former_member188586]

Thanks jhon for Quick replay

its Showing Wrong Output

[Johan_H]

Is the output empty, too large, too small, or just completely wrong?

[Johan_H]

Hi,

(SELECT sum(a1.[U_AMOUNT]) 
 FROM [dbo].[@I8_BGEXT] a1 
      /* why join these two tables ? */
      inner join [dbo].[@I8_BGE1] b1 on a1.[DocEntry] = b1.[DocEntry] 
      inner join [dbo].[@I8_BGU] c1 on c1.[U_BGNO]= a1.[U_BGNO] 
 WHERE /* all parameters are taken from outside the sub query */
       a1.[U_BGNO] = T1.[U_BGNO] 
   AND a1.[U_TYPE] = T1.[U_TYPE]
   AND a1.[U_CNAME] = T1.[U_CNAME]) as "GTOTAL"

Otherwise, in what way is this not working?

Regards,

Johan