Ask Your Question
0

SQL completion problem

asked 2017-11-29 08:23:33 +0200

koolninja gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-11-29 17:46:28 +0200

Ratslinger gravatar image

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")
edit flag offensive delete link more

Comments

1

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.

Jim K gravatar imageJim K ( 2017-11-30 01:41:56 +0200 )edit

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

koolninja gravatar imagekoolninja ( 2017-12-03 17:04:05 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-12-03 19:17:25 +0200 )edit

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

koolninja gravatar imagekoolninja ( 2017-12-04 16:00:23 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-12-04 16:30:05 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-11-29 08:23:33 +0200

Seen: 37 times

Last updated: Nov 29 '17