SQL completion problem

[koolninja]

Table name Contractor
Contractor Table have columns “SlNo”(Auto increment), “Type”,“MaterialName”,“LoaNo”, “Quantity”

“Type” defined for two ‘Recieved’ and ‘Released’
“LoaNo”= defined mixed type number such as 23/s/2017-18 for Received or Released of material

SELECT COALESCE(“a”.“TotalRcvd” - “b”.“TotalReleased”, “a”.“TotalRcvd”) AS “Balance” FROM (SELECT “Type”,“MaterialName”,“LoaNo”, SUM(“Quantity”) AS “TotalRcvd”
FROM “Contractor” WHERE “Type” = ‘Recieved’ GROUP BY “MaterialName”, “LoaNo”,“Type”) “a” LEFT JOIN(SELECT"Type", “MaterialName”,“LoaNo”,SUM(“Quantity”) AS “TotalReleased” FROM “Contractor” WHERE “Type” = ‘Released’ GROUP BY “MaterialName”,“LoaNo”, “Type”) “b” ON (“a”.“MaterialName” = “b”.“MaterialName”) WHERE (“a”.“MaterialName” = “Contractor”.“MaterialName”))

I want to design sql query for checking balance in respect of received and released on material basis of “MaterialName”,“LoaNo”

help me solve out this sql

[Ratslinger]

Hello,

In the future please be specific as to what your expected output is desired. I used an educated guess here since your statement only selected “Balance” for output. The following outputs “LoaNo”, “MaterialName”, “RECEIVED”, “RELEASED”, and “BALANCE” for each MaterialName within each LoaNo. The biggest problem with your statement is a starting point. You need to know all Materials for all LoaNo before starting to join totals. I left out any sequencing of the results as you did not specify this either. This SQL is based upon HSQBDB Embedded as you did not identify what you are using:

SELECT "LoaNo", "MaterialName", RECEIVED, RELEASED, RECEIVED - RELEASED BALANCE
FROM (SELECT DISTINCT "LoaNo", "MaterialName",
             CASEWHEN("TotalRcvd" IS NULL,0,"TotalRcvd") RECEIVED,
             CASEWHEN("TotalReleased" IS NULL,0,"TotalReleased") RELEASED
      FROM "Contractor"
        LEFT JOIN (SELECT "Type", "MaterialName", "LoaNo",
                   SUM("Quantity") AS "TotalRcvd"
                   FROM "Contractor"
                   WHERE "Type" = 'Recieved'
                   GROUP BY "MaterialName", "LoaNo", "Type") "a"
               ON ("Contractor"."MaterialName" = "a"."MaterialName")
              AND "Contractor"."LoaNo" = "a"."LoaNo"
        LEFT JOIN (SELECT "Type", "MaterialName", "LoaNo",
                   SUM("Quantity") AS "TotalReleased"
                   FROM "Contractor"
                   WHERE "Type" = 'Released'
                   GROUP BY "MaterialName", "LoaNo", "Type") "b"
               ON ("Contractor"."MaterialName" = "b"."MaterialName")
              AND "Contractor"."LoaNo" = "b"."LoaNo")

[jimk]

On other sites (at least this one), critiques of the question belong as comments under the question, whereas answers should just be answers. I like the idea because it helps answers to be more positive.

[koolninja]

Can above SQL query with search option for specific material and loano. Created same table copy with slno Boolean. (contractor_filter)

[Ratslinger]

Yes. At the very end add a WHERE condition such as Where UPPER("MaterialName") = Upper(:Material) which uses a parameter as input. You can do same for LoaNo.

[koolninja]

Thanks for simple solution. I save search parameters in other table contracor_filter (this table is copy of original with primary key Boolean input). When this search through form then data loaded in control table. For this can u have tips.
Thanks

[Ratslinger]

If you are trying to be selective with this on a form, use a table filter. There are many examples of this posted in this forum. Also this post explains filters. If you have further problems with this, ask as a new question with specific information.