INPUT VALUE depending on the table rows

Question

I want to input a new row in a table with the following design

CREATE TABLE DMZ
(
DDM date NOT NULL,
NDM int NOT NULL,
PR int NOT NULL
CONSTRAINT PK_DMZ PRIMARY KEY(NDM)
);

PR can only be 1, or 2, which I defined as a constraint.(1 if this document is for income, and 2 if this document is a consumption. DM is a document number (actually Id in my case).

ALTER TABLE DMZ
ADD CONSTRAINT PR CHECK (PR IN (1,2));

I filled it with some handwritten data

INSERT INTO DMZ VALUES('2014.01.04', 20, 1);
INSERT INTO DMZ VALUES('2014.01.04', 21, 1);
INSERT INTO DMZ VALUES('2014.01.04', 22, 2);

There are two rows, where PR = 1, and only one where PR = 2. I want to write a script to INSERT a new row like this

INSERT INTO DMZ(DDM, PR) VALUES(GETDATE(), X)

Where X, I want to have something like "count rows where PR = 1 and rows where PR = 2, and if there more rows where PR = 1, use PR = 2 in newly inserted row, and if there are more rows where PR = 2, use PR = 1. P.S.: That is a recreation of my deleted answer, hope now it's clear. To those who asked, why am I doing such a nonsence - it is a part of a list of tasks I HAVE to perform. I tried to do it, but I don't know how to perform this part with PR. EDIT: I managed to write what I needed, but I am getting the following error ""Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

INSERT INTO DMZ(ddm, pr)
SELECT COUNT(CASE WHEN (COUNT(CASE WHEN PR = 1 THEN 1 ELSE 0 END)> COUNT(CASE WHEN PR = 2 THEN 1 ELSE 0 END)) THEN 1 ELSE 2 END) AS pr, GETDATE() as ddm
FROM DMZ

griv · Accepted Answer · 2022-10-19T17:37:15.097

Try doing a INSERT SELECT statement with a CASE statement to check your PR counts using SUM and CASE in a subquery:

INSERT INTO DMZ (a.DDM, a.NDM, a.PR)
SELECT GETDATE() AS DOM,
       a.NDM AS NDM, 
       CASE WHEN a.PR_1_Count > a.PR_2_Count 
        THEN 2
        ELSE 1
       END AS PR
FROM (SELECT
        MAX(NDM) + 1 AS NDM,
        SUM(CASE WHEN PR = 1 THEN 1 ELSE 0 END) AS PR_1_Count, 
        SUM(CASE WHEN PR = 2 THEN 1 ELSE 0 END) AS PR_2_Count
       FROM DMZ) a

Fiddle here.

Note: If you want an actual count to be inserted, remove your CONSTRAINT for the PR check and change the CASE statement from THEN 2 to THEN PR_2_Count and THEN 1 to THEN PR_1_Count.

Also, I've hardcoded a NDM column value in my demo because you're column is set to NOT NULL, I assume you'll handle that.

Update: Per your comment below, I've updated the syntax to include MAX(NDM) + 1. I would, however, suggest adding a new NDM IDENTITY column to replace your current NDM column so that it will generate your PK for you vs. generating the value yourself (see the attached Fiddle for an example of this). Read more about IDENTITY columns here and how to do it here.

Identity columns can be used for generating key values. The identity property on a column guarantees the following:

Each new value is generated based on the current seed & increment.

Each new value for a particular transaction is different from other concurrent transactions on the table.

The identity property on a column does not guarantee the following:

Uniqueness of the value - Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

I have one more question, I tried to add an extra select after your code to fill the NDM with the highest + 1 instead of 27(I removed 27 as NDM). My select is (SELECT (MAX(NDM) + 1) as NDM FROM DMZ) But it keep saying, that "statement contains fewer items than the insert list". Could you, please, help me with this one so I won't create an addition question topic. — Palamar66, Oct 19 '22 at 16:32
@Palamar66 I've updated my answer for you to include this. Please consider using an `IDENTITY` column going forward (see my updated note and fiddle). — griv, Oct 19 '22 at 17:37

INPUT VALUE depending on the table rows

1 Answers1