I select the data from the table as follows

SELECT DISTINCT clientid,productid,[DATE], (SELECT quantity FROM csv_export c1 WHERE c.clientid=c1.clientid AND c.productid=c1.productid AND c.[DATE]=c1.[DATE]) AS quantity , (SELECT amount FROM csv_export c1 WHERE c.clientid=c1.clientid AND c.productid=c1.productid AND c.[DATE]=c1.[DATE]) AS amount FROM dbo.CSV_Export c WHERE quantity>0 AND amount>=0

And how do I get those records that do not pass the specified conditions, that is, are not unique in the three fields and not quantity> 0 and amount> = 0

update

 select distinct clientid,productid,[date], (select quantity from csv_export c1 where c.clientid=c1.clientid and c.productid=c1.productid and c.[date]=c1.[date]) as quantity , (select amount from csv_export c1 where c.clientid=c1.clientid and c.productid=c1.productid and c.[date]=c1.[date]) as amount, (select id from csv_export c1 where c.clientid=c1.clientid and c.productid=c1.productid and c.[date]=c1.[date]) as id from dbo.CSV_Export c where quantity>0 and amount>=0

The question now is how to pull out only the entries with the id field from here.

update1:

enter image description here

These are the original data.

With a minimum amount of code from this table, it is necessary to transfer to table A — data by condition — the record must be unique in the set of client-product-date fields; - number> 0 and sum> = 0 in table B - data where this condition is not met

  • And "are not unique" - are those that repeat 2 or more times? And they themselves simply change the conditions to the opposite and AND changes to OR, because you’ll get to choose records where quantity <= 0 OR amount <0 - Mike
  • @Mike can't understand yet how to express this opposite, in the case of Distinct, in the code - Antykus
  • You say in words what you want to get at the output, only the lines that are repeated or all the same all the lines. and if only those that repeat, then how many times, as much as there is or enough one record, perhaps with the number of repetitions (hint at group by и проверку having count(1) > 1 ) - Mike
  • In general, the problem is this: I have a table with data. I need to execute a query, which is A - when the conditions are met (3 fields must be unique and the number> 0) will transfer data from the table to the second table B - if any of the conditions are not met, it will transfer the data to the third table. Now I made it so that in the original table I just added the id field, made a selection based on the conditions, and those that did not get into this selection by id and fall into the third table. But somehow, this option smacks of a crutch by @Mike - Antykus
  • On account of the uniqueness, it’s still not clear. Yes, you transfer unique values, but you do not check whether they were initially unique or they became such after distinct, which means that those records that met once and those that are many will fall into that table. but be transported of course in one copy. And if so, then the uniqueness of the lines is not related to the question at all, just change the conditions in where and that's it. And distinct or set or not, depending on what is required in the third table - Mike

1 answer 1

Insert the entries into table A as follows:

 insert into A(....) select clientid,productid,[DATE],quantity,amount from ( select *, count(1) over(partition by clientid,productid,date) cnt from CSV_Export ) X where quantity>0 AND amount>=0 AND cnt=1

In table B as follows:

 insert into A(....) select clientid,productid,[DATE],quantity,amount from ( select *, count(1) over(partition by clientid,productid,date) cnt from CSV_Export ) X where quantity<=0 OR amount<0 OR cnt>1

In this case, we use the window function count () to count the records within the required grouping, regardless of the receipt of specific rows. Next we apply the required conditions to this sample.