Skip to Content

Sub _Grand total Logic Missing on Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Apr 18, 2017 at 05:58 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 18, 2017 at 06:38 AM

    Thanks jhon for Quick replay

    its Showing Wrong Output

    Add comment
    10|10000 characters needed characters exceeded