Out of curiosity, why do you say this?

I could see it it you were talking about DATETIME data types only, simply...

It depends... CAST is typically the preferred syntax being that it's an ansi standard... But, sometimes it's necessary, when working with different date/time data types to use the CONVERT function...

The reason it doesn't work in either of your original versions is because they are both attempting to concatenate a date datatype to a varchar data type... The two data...

If keeping those rows is important... This doesn't seem to beat it up too bad...

IF OBJECT_ID('tempdb..#address') IS NOT NULL

DROP TABLE #address;

CREATE TABLE #address (

address_id INT,

addr1 CHAR(30),

addr2 CHAR(30),

...

CAST(@EndDate AS VARCHAR(10))

If the PO Numbers are all the same length, the "Shift + Alt" block typing trick works well too...

WOW!!! I really am a big dumb...

I don't recall recording any specific time measurements. I was more interested in verifying that the execution plan was changing based on the existence of index (and that the index...

That test was done using SQL Server 2014 Dev Edition. (Local instantaneous running on a laptop)

Phil Parkin (5/13/2015)

---

tom.w.brannon (5/13/2015)

---

Yes, intersect is not normal to me either. I actually changed it where exists and then used except. Still non-obvious logic for me so I...

Luis beat me too it but I took the time to write it so I'm posting it... 😉

SELECT

a.Id,

a.Category,

MAX(a.CountID) AS OverallTotal,

SUM(a.CountCat) OVER (PARTITION BY a.Id, a.Category ORDER BY a.Date) AS...

Thanks for the info Tom. Sounds like Phil's solution has the edge. I've never used the INTERSECT operator in anything before, so it looks like I've got some homework of...

A tally table is simply a simple table that contains a single column of indexed integers.

Jeff Moden has a great article on the topic... The "Numbers" or "Tally" Table: What...

I can also make Jason's answer work, again by adding A to the OVER (ORDER BY ) clauses. One disadvantage of Jason's method for me is that I believe I...

Here's a slightly different approach...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp

GO

CREATE TABLE #temp (

A CHAR(1) NOT NULL,

B CHAR(1) NULL,

DTE DATE

)

INSERT #temp (A,B,DTE) VALUES

('1','1','2015-01-01'),

('1','1','2015-01-02'),

('1','1','2015-01-03'),

('2','1','2015-01-04'),

('1','1','2015-01-05'),

('1',NULL,'2015-01-06'),

('1',NULL,'2015-01-07'),

('2','1','2015-01-08'),

('2','1','2015-01-09'),

('2','2','2015-01-10')

;WITH BinVals AS (

SELECT

t.A,

t.B,

t.DTE,

CAST(t.A AS...