How am I doing?

Question

Post reply

How am I doing?

Steve Jones - SSC Editor

SSC Guru

Points: 736430
More actions
August 13, 2018 at 11:19 pm

#77766

Comments posted to this topic are about the item How am I doing?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

HappyGeek SSCoach Points: 18729 More actions · Answer 1

HappyGeek

SSCoach

Points: 18729

August 13, 2018 at 11:20 pm

#2001580

Nice simple one thanks Steve.

...

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 2

Cheers Steve, Nice and easy one today 🙂

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

t.ovod-everett Default port Points: 1487 More actions · Answer 3

Won't you get 100 rows? If you only want 9, you'll need a "WHERE m.n <= 9" or some such.

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 4

Nice one, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 5

t.ovod-everett - Tuesday, August 14, 2018 3:49 PM
Won't you get 100 rows? If you only want 9, you'll need a "WHERE m.n <= 9" or some such.

Yes, I said all rows, past and future. I want this to let me know the sales for each week. Right now, week 9 (if that's all the data) would show 0.

t.ovod-everett Default port Points: 1487 More actions · Answer 6

Steve Jones - SSC Editor - Wednesday, August 15, 2018 9:08 AM
t.ovod-everett - Tuesday, August 14, 2018 3:49 PM
Won't you get 100 rows? If you only want 9, you'll need a "WHERE m.n <= 9" or some such.
Yes, I said all rows, past and future. I want this to let me know the sales for each week. Right now, week 9 (if that's all the data) would show 0.

Technically, wouldn't "all rows, past and future," require some sort of lazy enumerator that supports infinite sets? It would need to return the rows as 0, 1, -1, 2, -2, 3, -3, etc. in order for it to be able to start (otherwise it would have to start at negative infinity, and T-SQL tends to have a problem with that). Realistically, I suspect the query really needs "all rows for a specified week range." But now you've got me thinking - I wonder if there is a way to create a query in SQL Server that effectively behaves like a lazy infinite iterator. Obviously, it would blow up eventually (bigint has a limit), but it would start returning 0, 1, -1, 2, -2, and pretty much just keep running until the query is killed.

Couldn't resist:

WITH T AS ( SELECT V.N FROM ( VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) AS V(N) ), T2 AS ( SELECT A.N FROM T AS A CROSS JOIN T AS B ), T4 AS ( SELECT A.N FROM T2 AS A CROSS JOIN T2 AS B ), T8 AS ( SELECT A.N FROM T4 AS A CROSS JOIN T4 AS B ), T16 AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T8 AS A CROSS JOIN T8 AS B ) SELECT (S.N / 2) * (S.N % 2 * 2 - 1) AS N FROM T16 AS S ORDER BY S.N;

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

t.ovod-everett - Wednesday, August 15, 2018 10:14 AM
Steve Jones - SSC Editor - Wednesday, August 15, 2018 9:08 AM
t.ovod-everett - Tuesday, August 14, 2018 3:49 PM
Won't you get 100 rows? If you only want 9, you'll need a "WHERE m.n <= 9" or some such.
Yes, I said all rows, past and future. I want this to let me know the sales for each week. Right now, week 9 (if that's all the data) would show 0.
Technically, wouldn't "all rows, past and future," require some sort of lazy enumerator that supports infinite sets? It would need to return the rows as 0, 1, -1, 2, -2, 3, -3, etc. in order for it to be able to start (otherwise it would have to start at negative infinity, and T-SQL tends to have a problem with that). Realistically, I suspect the query really needs "all rows for a specified week range." But now you've got me thinking - I wonder if there is a way to create a query in SQL Server that effectively behaves like a lazy infinite iterator. Obviously, it would blow up eventually (bigint has a limit), but it would start returning 0, 1, -1, 2, -2, and pretty much just keep running until the query is killed.

Technically, there wouldn't be any negatives for Sales Weeks. That would mean that the sales happened before the business opened. You don't need an infinite set of weeks, because SQL Server can only handle 521,722 weeks. That said, I don't believe that any business can survive that long.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

t.ovod-everett Default port Points: 1487 More actions · Answer 8

Luis Cazares - Wednesday, August 15, 2018 11:26 AM
Technically, there wouldn't be any negatives for Sales Weeks. That would mean that the sales happened before the business opened. You don't need an infinite set of weeks, because SQL Server can only handle 521,722 weeks. That said, I don't believe that any business can survive that long.

While there might not be any sales before the business opened, it would be accurate to say that there were 0 sales for each of the weeks prior to the business opening, so one could argue that those records should be displayed in the output if one truly wants all records past and future.

My iterator will happily generate (assuming you don't run out of memory or disk space) 10^16 weeks. It's interesting to run it with "Include Live Query Statistics" turned on. SQL Server generates an accurate estimate for the number of rows returned, and so can accurately estimate progress.

I do recommend against doing a SELECT ... INTO #temp with this query - that might cause problematic growth in tempdb.