Task: Add a new document header (one insert command); -Number of document = last document number + 1; -Date = current date; -Type = expense, if the documents of the "receipt" type are greater than the documents of the "expense" type. Otherwise, the type = receipt. - Consider that in the DMZ table initially there may not be a single row.

Table DMZ - document. DDM - date, NDM-document number, PR - receipt / expense (1-receipt, 2-consumption) of the goods. The request itself is:

INSERT INTO DMZ (DDM, NDM, PR) SELECT GETDATE() DDM, (isnull((SELECT MAX(NDM) FROM DMZ), 0) + 1) NDM, CASE WHEN isnull((SELECT COUNT(*) FROM DMZ WHERE PR = 1), 0) > isnull((SELECT COUNT(*) FROM DMZ WHERE PR = 2), 0) THEN 2 ELSE 1 END

Db chart

The query works, but you need to optimize it by replacing two queries in the CASE operator with one. How to do this?

I insert the diagram, if something helps.

    1 answer 1

    you need to optimize it by replacing two queries in the CASE statement with one. How to do it?

    You can get COUNT for PR=1 and PR=2 at the same time using PIVOT :

     ;WITH DMZ1 AS (SELECT PR FROM DMZ) SELECT [1], [2] FROM DMZ1 PIVOT (COUNT(PR) FOR PR IN ([1], [2])) P;

    Accordingly, the original request can be converted, for example, to:

     ;WITH DMZ1 AS (SELECT PR FROM DMZ) INSERT INTO DMZ (DDM, NDM, PR) SELECT GETDATE() DDM, (isnull((SELECT MAX(NDM) FROM DMZ), 0) + 1) NDM, CASE WHEN EXISTS( SELECT 1 FROM DMZ1 PIVOT (COUNT(PR) FOR PR IN ([1], [2])) P WHERE [1] > [2] ) THEN 2 ELSE 1 END;

    The usual grouping with CASE can also be used:

     SELECT [1] = COUNT(CASE WHEN PR=1 THEN 1 END), [2] = COUNT(CASE WHEN PR=2 THEN 1 END) FROM DMZ;

    Using PIVOT sometimes gives a more concise record, but CASE more flexible (although PIVOT is syntactic sugar, however, SqlServer's PIVOT syntax is not as well developed as in Oracle, for example).

    The usual grouping with CASE here will be even preferable, since it allows the maximum document number to be obtained in one request:

     SELECT MAXNDM = ISNULL(MAX(NDM), 0), [1] = COUNT(CASE WHEN PR=1 THEN 1 END), [2] = COUNT(CASE WHEN PR=2 THEN 1 END) FROM DMZ;

    Accordingly, the original request can be converted to:

     INSERT INTO DMZ (DDM, NDM, PR) SELECT GETDATE(), ISNULL(MAX(NDM), 0) + 1, CASE WHEN COUNT(CASE WHEN PR=1 THEN 1 END) > COUNT(CASE WHEN PR=2 THEN 1 END) THEN 2 ELSE 1 END FROM DMZ;
    • And you can not do it by applying the approach as in this query: SELECT t.NTOV, SUM (s.KOL * (1- (d.PR-1))) - SUM (s.KOL * (d.PR-1) ) as KOL, SUM (s.CENA * (1- (d.PR-1))) - SUM (s.CENA * (d.PR-1)) as SUMMA FROM DMS s INNER JOIN TOV t on t.KTOV = s.KTOV INNER JOIN DMZ d on d.NDM = s.NDM GROUP BY t.NTOV ORDER BY t.NTOV? Here is determined the amount and amount of the balance (if 1 - receipt , 2 - consumption) - SoftySh
    • In fact, it will be the same, only instead of PIVOT. Use just CASE - SoftySh
    • @SoftySh, "applying the approach as in this request" is meant the usual grouping? Yes you can. In this case, it is even preferable (added to the answer). - i-one
    • Yes, I meant it) Thank you very much, now I figured out two options! - SoftySh