Multiple selection of item to display Pre Bal, Received Bal,Issue Bal, Post Bal, Present Bal when compared with Date

English
#1

I tried sql qurey for this as below

SELECT “store”.“Product” AS “Material Name”, ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) AND ( ( ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” < “store_filter1”.“Date” ) ) ) ) GROUP BY “store”.“Product” ) - ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) AND ( ( ( “store_filter1”.“Date” IS NULL ) ) OR ( ( “store”.“Date” < “store_filter1”.“Date” ) ) ) ) GROUP BY “store”.“Product” ) AS “Pre Bal.”, ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) AND ( ( ( “store_filter1”.“Date” IS NULL ) AND ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” >= “store_filter1”.“Date” ) AND ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) ) GROUP BY “store”.“Product” ) AS “Recieved Bal.”, ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) AND ( ( ( “store_filter1”.“Date” IS NULL ) AND ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” >= “store_filter1”.“Date” ) AND ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) ) GROUP BY “store”.“Product” ) AS “Issue Bal.”, ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) AND ( ( ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) ) GROUP BY “store”.“Product” ) - ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) AND ( ( ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) ) GROUP BY “store”.“Product” ) AS “Post Bal.”, ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) ) GROUP BY “store”.“Product” ) - ( SELECT SUM( “store”.“Quantity” ) AS “Quantity” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ AND ( ( ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) ) ) GROUP BY “store”.“Product” ) AS “Present Bal.” FROM “store”, “store_filter1” WHERE ( UPPER ( “store”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL ) GROUP BY “store”.“Product”

its works fine for on single item selection through combo box but when for all item then error cardinality violation. suggestion for improvemet to get this result multiitem

material Name Pre Bal Recived Bal Issue Bal Post Bal Present Bal

    A           10          20        5      25         10

Suppose material is Jan to April, selected period Feb so Pre Bal is before Feb … Recived Bal and Issue Bal of Feb… Post Bal after Feb and Present Bal after April

may my sql qurey is drafted complex.
some suggestion can be converted to results as desired for multi selection of item at time

#2

Hello,

It would be nice if you were to learn by some of the previous answers you have been given. This statement should be basically the same as the one presented in this answer. Your statement does NOT work on a single item as you claim. You have not done enough testing. If ISSUE is greater than RECEIVED then you have a problem. You are over complicating things by not normalizing information. You are also duplicating selections unnecessarily. You also have some logic problems. Your statement is nothing more than a bunch of small select statements put together. Get each of the individual items separately and join them into single records. Then work with that result to finalize the information you want. Here is a PARTIAL solution.

First start with a record containing all the Products:

Select DISTINCT "Product" From "store"

Now work on the Pre issue/received (you misspelled this again) items only selecting based upon date:

Received Items:

SELECT "Product", SUM("store"."Quantity") AS "Pre_Rcvd"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Recieved'
        AND   (("store_filter1"."Date2" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product"

Issued Items:

SELECT "Product", SUM("store"."Quantity") AS "Pre_Issued"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Issue'
        AND   (("store_filter1"."Date" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product"

Take a close look at these two statements. This is YOUR code. In one statement you check Date for NULL & the other you check Date2 for NULL. My example here will not change this. You must make the necessary corrections since you know what you want & expect.

Now you can JOIN these last two statements to the first. Then, as shown in linked answer make sure values are in each field by using a CASEWHEN. Finally you select the results wanted with proper calculations and selection of Products based upon the filter:

Select A."Product", "PriorRcvd" - "PriorIssued" "Pre-Balance" From "store_filter1",
(Select PRODUCTS."Product", CASEWHEN("Pre_Rcvd" IS NULL,0,"Pre_Rcvd") "PriorRcvd", CASEWHEN("Pre_Issued" IS NULL,0,"Pre_Issued") "PriorIssued" From
    (Select DISTINCT "Product" From "store")PRODUCTS
    LEFT JOIN (SELECT "Product", SUM("store"."Quantity") AS "Pre_Rcvd"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Recieved'
        AND   (("store_filter1"."Date2" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product")PRERCVD on PRERCVD."Product" = PRODUCTS."Product"
    LEFT JOIN (SELECT "Product", SUM("store"."Quantity") AS "Pre_Issued"
        FROM "store", "store_filter1"
        WHERE "store"."Type" = 'Issue'
        AND   (("store_filter1"."Date" IS NULL) OR ("store"."Date" < "store_filter1"."Date"))
        GROUP BY "store"."Product")PREISSUED on PREISSUED."Product" = PRODUCTS."Product")A
WHERE (UPPER(A."Product") LIKE '%' || UPPER("store_filter1"."Product") || '%') OR ("store_filter1"."Product" IS NULL)

This now gives you a basis for the rest of the statement to be completed. This forum is not a free consulting service and you should not treat it as such. You have been given many SQL answers in the past yet you continue to present much the same questions over & over along with many simple errors.

New questions are always welcome, but don’t expect free workers here. A little research goes a long way especially when you’ve already been given the answers recently.

#3

sorry for this, above part i get done with yours previous help but i only stuck when when multiple results… next time i not trouble for this type question …

#4

I found my full answer by using left join properly. here is my working SQL

SELECT “A”.“Product”, “PriorRcvd” - “PriorIssued” “Pre-Balance”, “RriorRcvd” “Recived-Bal”, “RriorIssued” “Issued-Bal”, “IriorRcvd” - “IriorIssued” “Post-Bal”, “PIriorRcvd” - “PIriorIssued” “Present-Bal” FROM “store_filter1”, ( SELECT DISTINCT “PRODUCTS”.“Product”, CASEWHEN( “Pre_Rcvd” IS NULL, 0, “Pre_Rcvd” ) “PriorRcvd”, CASEWHEN( “Pre_Issued” IS NULL, 0, “Pre_Issued” ) “PriorIssued”, CASEWHEN( “Rre_Rcvd” IS NULL, 0, “Rre_Rcvd” ) “RriorRcvd”, CASEWHEN( “Rre_Issued” IS NULL, 0, “Rre_Issued” ) “RriorIssued”, CASEWHEN( “Ire_Rcvd” IS NULL, 0, “Ire_Rcvd” ) “IriorRcvd”, CASEWHEN( “Ire_Issued” IS NULL, 0, “Ire_Issued” ) “IriorIssued”, CASEWHEN( “PIre_Rcvd” IS NULL, 0, “PIre_Rcvd” ) “PIriorRcvd”, CASEWHEN( “PIre_Issued” IS NULL, 0, “PIre_Issued” ) “PIriorIssued” FROM ( SELECT “Product” FROM “store” ) “PRODUCTS” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “Pre_Rcvd” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ AND ( ( “store_filter1”.“Date2” IS NULL ) OR ( “store”.“Date” < “store_filter1”.“Date” ) ) GROUP BY “store”.“Product” ) “PRERCVD” ON “PRERCVD”.“Product” = “PRODUCTS”.“Product” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “Pre_Issued” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ AND ( ( “store_filter1”.“Date” IS NULL ) OR ( “store”.“Date” < “store_filter1”.“Date” ) ) GROUP BY “store”.“Product” ) “PREISSUED” ON “PREISSUED”.“Product” = “PRODUCTS”.“Product” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “Rre_Rcvd” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ AND ( ( ( “store_filter1”.“Date” IS NULL ) AND ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” >= “store_filter1”.“Date” ) AND ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) GROUP BY “store”.“Product” ) “RRERCVD” ON “RRERCVD”.“Product” = “PRODUCTS”.“Product” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “Rre_Issued” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ AND ( ( ( “store_filter1”.“Date” IS NULL ) AND ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” >= “store_filter1”.“Date” ) AND ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) GROUP BY “store”.“Product” ) “RREISSUED” ON “RREISSUED”.“Product” = “PRODUCTS”.“Product” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “Ire_Rcvd” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ AND ( ( ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) GROUP BY “store”.“Product” ) “IRERCVD” ON “IRERCVD”.“Product” = “PRODUCTS”.“Product” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “Ire_Issued” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ AND ( ( ( “store_filter1”.“Date2” IS NULL ) ) OR ( ( “store”.“Date” <= “store_filter1”.“Date2” ) ) ) GROUP BY “store”.“Product” ) “IREISSUED” ON “IREISSUED”.“Product” = “PRODUCTS”.“Product” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “PIre_Rcvd” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Recieved’ GROUP BY “store”.“Product” ) “PIRERCVD” ON “PIRERCVD”.“Product” = “PRODUCTS”.“Product” LEFT JOIN ( SELECT “Product”, SUM( “store”.“Quantity” ) AS “PIre_Issued” FROM “store”, “store_filter1” WHERE “store”.“Type” = ‘Issue’ GROUP BY “store”.“Product” ) “PIREISSUED” ON “PIREISSUED”.“Product” = “PRODUCTS”.“Product” ) “A” WHERE ( UPPER ( “A”.“Product” ) LIKE ‘%’ || UPPER ( “store_filter1”.“Product” ) || ‘%’ ) OR ( “store_filter1”.“Product” IS NULL )

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.