I am hoping there are folks who are both responsible for sta...

[robert fuschetto]

I am hoping there are folks who are both responsible for staging data using SQL Server as well as those who use WF. I am aware how WF treats case sensitivity.

Question:

I have a sql table: tblHistory. We append data each month.

There is a column called: PayorCategory. One possible value for this column has always been: Self-pay. (lowercase p in pay)

Recently, in there infinite wisdom they changed the value for self pay to: Self-Payie with an UPPERCASE: P.

We started appending this new value THIS month. So in summary reporting, say for the year where we GROUP BY only: PayorCategory, I expected to see TWO lines for self pay: Self-pay and Self-Pay. To check I wrote a simple SQL query:

SELECT Payor Category

FROM tblHistory

GROUP BY PayorCategory

I saw only ONE row: Self-pay!!!

Figuring I was mis-informaed about the aforementioned change from: Self-pay to: Self-Pay I wrote another query to double check:

SELECT Period, Payor Category

FROM tblHistory

GROUP BY Period, PayorCategory

Sure enough, in history (older records) I now see: Self-pay but in newer records I see: Self-Pay.

What the heck is SQL doing!

[David Beagan]

It looks like they discuss the issue on the stackoverlow site:

How to do a case sensitive GROUP BY

[robert fuschetto]

thanks for the quick responseIll try to absorb thatseems a little confusing.

I can under stand if GROUP BY PERIDOD,CATEGORY I see Self-Pay appear this month since we started using it.

However it seems SQL just opts to show: Self-pay alone if PERIOD is removedalmost like it says 'hey 95% of the data is Self-payehhh Self-Pay is the same thing, lets just throw it under Self-pay if PERIOD is not involved!