SQLTeam.com | Weblogs | Forums

Partitioning data


#1

hello, i have this table Products in which i need to update the column "Validation".
For every product ID i have different quote number and for each of them the quota might be expired or valid.
My customers want me to put for every Product ID, "valid" in the column Validation even if for a certain quote number i have expired.
For example the Product ID =105. it has 4 different quote numbers. For 2 of them the Quote is valid and for 2 is expired. Even if this product ID has 2 expired quotes, they want to see valid because the Quote column has at least one quote number valid.
So, if in the Quote column i have at least one valid quote number and the rest are expired, tehy want to see valid in the validation column.
image


#2

Not a lot of details, but something like this should do it:

SELECT [Product ID],
    MAX(CASE WHEN Quote = 'valid' THEN 1 ELSE 0 END) = 1 THEN 'valid' ELSE 'expired' END AS validation
FROM dbo.table_name
GROUP BY [Product ID]
ORDER BY [Product ID]

#3

thank you but it doesnt work. actually i have managed to find a way :slight_smile:


#4

i solved this by creating another 2 columns. in one i have put 1 where the quote is valid and 0 where its expired.
in the other column i have put the sum of the previous column created partitioned by the product id, and in my last column : the Validation column i just made a case in which where the sum is >=1 then is valid else is expired.