# How am I doing?

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

• Nice simple one thanks Steve.

...

• Cheers Steve, Nice and easy one today 🙂

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

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

• Nice one, thanks Steve

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

• 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.

• 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;

• 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
• 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.

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