Skip to Content
0
Apr 18, 2017 at 05:21 AM

Sub _Grand total Logic Missing on Query

76 Views Last edit Aug 22, 2017 at 01:21 PM 2 rev

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