This -

select top(185) EoMonth('1/1/2016',(1-n)) as Eom from tally

- using Bob's tally view covers roughly 2000 to 2016, the entire range of dates in my table of interest.

Lynn Pettis (8/12/2016)

---

Steve Jones - SSC Editor (8/12/2016)

---

Lynn Pettis (8/11/2016)

---

Having rules for answering questions makes sense but I have to ask the question, Why? Has as been noted there...

The Dixie Flatline (8/12/2016)

---

Guys, I'm trying to figure out the disconnect here. I am running against a table of almost 41 million rows using the OPs table...

The Dixie Flatline (8/12/2016)

---

After thinking about times being included, we don't need to waste cycles converting Check_Date from datetime to date. We just need to go a...

Steve Jones - SSC Editor (8/11/2016)

---

...

Part of the process noted that we have a few "how to post code, how to ask a question, how to format code" articles. We...

Thanks.

The UPDATE affects the PK. Is this PK referenced by FK constraints in other tables? If so, are they indexed?

Edit: or it would, if there were any rows affected...

Jeff Moden (8/11/2016)

---

ben.brugman (8/11/2016)

---

Christopher Columbus,... challenges his critics to make an egg stand on its tip. After...

DROP TABLE #temp;

WITH cte as (

select 1 AS ID, 'x' as Item union all

select 2, 'x' union all

select 3, 'x' union all

select 4, 'x' union all

select 5, 'A' union all

select...

mitch 14804 (8/11/2016)

---

@chrism-2@Work

Hi Chris,

Thanks for the feedback. The reasoning behind my long process is so I could put it in a view not a procedure. It now allows...

There's currently no column to set the order of these rows. Without one, SQL Server will not guarantee that the rows will be processed (output) in any specific order. A...

The results I get are much closer than that, John (except for Kevin's).

This might be the simplest option:

SELECT

Adj_CheckDate,

cnt = COUNT(*)

FROM trans t

CROSS APPLY (SELECT ldom = EOMONTH([date]),...

TheSQLGuru (8/11/2016)

---

I quickly slapped this together. Can someone do some scalability testing for me to see how it stacks up with the other code? Sadly I have to grab a...

twin.devil (8/11/2016)

---

Just a minor adjustment to Chris solution, It will not handle if there is date and time are coming in the check_date column.

Here is Chris updated script:

SELECT

t.[Check_date],

cnt =...

SELECT

t.[Date],

cnt = COUNT(*)

FROM Trans t -- 36M rows

CROSS APPLY (SELECT ldom = DATEADD(MONTH,1+DATEDIFF(MONTH,0,t.[Date]),0)-1) x

WHERE DAY([Date]) > 27

AND t.[Date] = x.ldom

GROUP BY t.[Date]

-- (100 row(s) affected) /...

cmullen77 29296 (8/10/2016)

---

Hey Alan,

That was my point.. I had an issue running an update statement, so I started eliminating rows to figure out what was causing my problem. ...