jeudi 29 janvier 2015

How to determine is a value exists in a column within a partition


I have a set of data where I need to look at partitioned data and determine if a value exists within that partition.


Example data:



AccountNumber Code Status Credited
--------------------------------------------------------
500001 AEE Cancelled 0
500001 AEE Error 0
500001 AEE Active 1

500001 BEC Error 0

500002 CES Error 0
500002 CES Cancelled 0

500003 BEC Rescinded 1
500003 BEC Cancelled 1
500003 BEC Active 0
500003 BEC Error 0

500004 DFE Cancelled 0
500004 DFE Active 0


So, how can I tell which partitions contains Credited = 1 (bit type), and if Credited = 0, if the partition contains Status = Active (varchar type)?


This can be done in one statement or separate, either way is okay.


If, let's say, Credited was a numeric type instead, this is how I would do it, but it's very hacky, and I need to be able to do it with other data types. To note, the AccountNumber and Code create a unique key for each account. This is running in MS SQL Server 2008 R2.



WITH cte AS(
SELECT AccountNumber, Code, MAX(Credited) OVER (PARTITION BY AccountNumber, Code) AS Credited
FROM myTable
GROUP BY AccountNumber, Code, Credited
)
SELECT m.*, cte.Credited
FROM myTable m
JOIN cte
ON m.AccountNumber = cte.AccountNumber
AND m.Code = cte.Code;




Aucun commentaire:

Enregistrer un commentaire