bitbucket: You've haven't quite got the same data as the original post; the original post has dates and times in the TransDate column (and it makes a world of...

... and ordering by the full TransDate is asking for trouble too!

As long as you insist on grouping by TransDate, you will have a new row for each distinct value of TransDate, a datetime that includes time.

You should be using:

group by...

Of course it only works if all your data is in the same year. There are many ways to deal with converting datetime values to dates. Datepart() can...

That's not new; I already covered that in my first response. Your use of datepart is a working solution to that problem.

Then that's what you group on. Every field other than your group fields must be a function like max(), min(), sum() or count(); this can include your case statement...

To me, the format of your desired output makes it unclear whether you want one row per date or one row for each date/payment method combination.

Either way your 'group by'...

No. Your query would give orders received in the past and either filled on the same date or not at all. In general, orders can be filled on...

The project I've been working on far many, many years lies somewhere between off-the-shelf and 100% custom software. That is to say that while we as the developers try...