July 9, 2014 at 8:05 pm
mister.magoo (7/9/2014)
One to add to the list:5. Use a single statement WHILE whenever possible (i.e. no BEGIN...END) and you may be surprised at the performance - especially when recoding a poorly performing rCTE.
Excellent point. These can be so incredibly fast in resolving scalar values that they sometimes overcome the performance penalty of scalar functions to a point where they're sometimes faster than iSFs.
--Jeff Moden
July 9, 2014 at 8:06 pm
sqldriver (7/9/2014)
Does anyone else smell spackle? 🙂
🙂
--Jeff Moden
July 9, 2014 at 11:17 pm
More for fun but fitting the discussion, kind of before and after:-D
😎
Before
create function sys.fn_numberOf1InBinaryAfterLoc (@byte binary, @loc int)
returns int
as
begin
declare @index int, @mark-3 int, @counter int
set @counter=0
set @index=0
if @loc not between 1 and 8
return 0
while @index<@loc
begin
set @mark-3=power(2, @index)
if @mark-3&@byte <>0
begin
select @counter = @counter+1
end
set @index=@index+1
end
return @counter
end
After
CREATE FUNCTION sys.fn_EE_numberOf1InBinaryAfterLoc (@byte BINARY, @loc INT)
RETURNS INT
AS
BEGIN
RETURN (SELECT
SUM(SIGN(@byte & POWER(2,N)))
FROM
(
SELECT TOP (31) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) AS X
,(SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) AS Y
) AS NM(N)
WHERE NM.N > @loc)
END
July 10, 2014 at 12:12 am
Eirikur Eiriksson (7/9/2014)
More for fun but fitting the discussion, kind of before and after:-D😎
Before
create function sys.fn_numberOf1InBinaryAfterLoc (@byte binary, @loc int)returns int
as
begin
declare @index int, @mark-3 int, @counter int
set @counter=0
set @index=0
if @loc not between 1 and 8
return 0
while @index<@loc
begin
set @mark-3=power(2, @index)
if @mark-3&@byte <>0
begin
select @counter = @counter+1
end
set @index=@index+1
end
return @counter
end
After
CREATE FUNCTION sys.fn_EE_numberOf1InBinaryAfterLoc (@byte BINARY, @loc INT)RETURNS INT
AS
BEGIN
RETURN (SELECT
SUM(SIGN(@byte & POWER(2,N)))
FROM
(
SELECT TOP (31) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) AS X
,(SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) AS Y
) AS NM(N)
WHERE NM.N > @loc)
END
Performance test code?
--Jeff Moden
July 12, 2014 at 6:25 am
Jeff Moden (7/10/2014)
Performance test code?
Firstly, apologies for the late response, slightly busy period:-D
I am not going to even bother with the code I posted earlier, it doesn't stand a chance; three function calls and Tally table of 32 entries compared to the maximum 8 needed for the byte value. Instead I did two more optimised functions, one scalar and the other iTVF.
This quick test code compares the system function to the two alternatives by the number of iterations, somewhat interesting results.
😎
Test code
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 256000;
DECLARE @INT_BUCKET INT = 0;
DECLARE @BYTE_BUCKET BINARY(1) = 0x00;
/* First alternative function */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'FN_COUNT_1_RIGHT_OF_POS'
AND ROUTINE_SCHEMA = 'dbo')
DROP FUNCTION dbo.FN_COUNT_1_RIGHT_OF_POS;
/* Create function #1 */
DECLARE @SQL_STR_1 NVARCHAR(MAX) = N'
CREATE FUNCTION dbo.FN_COUNT_1_RIGHT_OF_POS
(
@byte BINARY(1)
,@loc INT
)
RETURNS INT
AS
BEGIN
RETURN (
SELECT
SUM(CASE WHEN (@byte & N) <> 0 THEN 1 ELSE 0 END)
FROM ( VALUES (1,1),(2,2),(3,4),(4,8),(5,16),(6,32),(7,64),(8,128)) AS X(P,N)
WHERE X.P <= @loc)
END'
EXEC (@SQL_STR_1);
/*
CODE TO VERIFY THE FUNCTIONALITY
SELECT DISTINCT
sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,8)
,dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,8)
FROM dbo.BYTE_TESTSET BT
*/
/* Second alternative function */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ITVFN_COUNT_1_RIGHT_OF_POS'
AND ROUTINE_SCHEMA = 'dbo')
DROP FUNCTION dbo.ITVFN_COUNT_1_RIGHT_OF_POS;
/* Create function #1 */
DECLARE @SQL_STR_2 NVARCHAR(MAX) = N'
CREATE FUNCTION dbo.ITVFN_COUNT_1_RIGHT_OF_POS
(
@byte BINARY(1)
,@loc INT
)
RETURNS TABLE
AS
RETURN (
SELECT
SUM(CASE WHEN (@byte & N) <> 0 THEN 1 ELSE 0 END) AS ONE_COUNT
FROM ( VALUES (1,1),(2,2),(3,4),(4,8),(5,16),(6,32),(7,64),(8,128)) AS X(P,N)
WHERE X.P <= @loc)
'
EXEC (@SQL_STR_2);
DECLARE @TIMER TABLE
(
TIMER_ID SMALLINT IDENTITY(1,1) NOT NULL
,TIMER_STAMP DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME())
,TIMER_TEXT VARCHAR(128) NOT NULL
);
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES I
WHERE TABLE_NAME = N'BYTE_TESTSET'
AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.BYTE_TESTSET;
CREATE TABLE dbo.BYTE_TESTSET (VAL BINARY(1) NOT NULL);
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Create test set of ' + CAST(@SAMPLE_SIZE AS VARCHAR(12)) + ' entries')
INSERT INTO dbo.BYTE_TESTSET(VAL)
SELECT TOP (@SAMPLE_SIZE)
CONVERT(BINARY(1),(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 256) -1,0)
FROM sys.all_objects X1, sys.all_objects X2
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Create test set of ' + CAST(@SAMPLE_SIZE AS VARCHAR(12)) + ' entries')
/* DRY RUN 1 */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Run full sample select into a bucket, #1')
SELECT
@BYTE_BUCKET = BT.VAL
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Run full sample select into a bucket, #1')
/* DRY RUN 2 */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Run full sample select into a bucket, #2')
SELECT
@BYTE_BUCKET = BT.VAL
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Run full sample select into a bucket, #2')
/* DRY RUN 3 */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Run full sample select into a bucket, #3')
SELECT
@BYTE_BUCKET = BT.VAL
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('Run full sample select into a bucket, #3')
/* 8 BITS */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 8 BITS')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,8)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 8 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 8 BITS')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,8)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 8 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 8 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,8) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 8 BITS')
/* 7 BITS */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 7 BITS')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,7)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 7 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 7 BITS')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,7)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 7 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 7 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,7) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 7 BITS')
/* 6 BITS */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 6 BITS')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,6)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 6 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 6 BITS')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,6)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 6 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 6 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,6) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 6 BITS')
/* 5 BITS */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 5 BITS')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,5)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 5 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 5 BITS')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,5)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 5 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 5 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,5) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 5 BITS')
/* 4 BITS */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 4 BITS')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,4)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 4 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 4 BITS')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,4)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 4 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 4 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,4) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 4 BITS')
/* 3 BITS */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 3 BITS')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,3)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 3 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 3 BITS')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,3)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 3 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 3 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,3) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 3 BITS')
/* 2 BITS */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 2 BITS')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,2)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 2 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 2 BITS')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,2)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 2 BITS')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 2 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,2) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 2 BITS')
/* 1 BIT */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 1 BIT')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,1)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 1 BIT')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 1 BIT')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,1)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 1 BIT')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 1 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,1) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 1 BITS')
/* 0 BIT */
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 0 BIT')
SELECT
@BYTE_BUCKET = sys.fn_numberOf1InBinaryAfterLoc(BT.VAL,0)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #0 0 BIT')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 0 BIT')
SELECT
@BYTE_BUCKET = dbo.FN_COUNT_1_RIGHT_OF_POS(BT.VAL,0)
FROM dbo.BYTE_TESTSET BT
INSERT INTO @TIMER(TIMER_TEXT) VALUES('SCALAR Function #1 0 BIT')
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 0 BITS')
SELECT
@BYTE_BUCKET = X.ONE_COUNT
FROM dbo.BYTE_TESTSET BT
CROSS APPLY dbo.ITVFN_COUNT_1_RIGHT_OF_POS(BT.VAL,0) AS X
INSERT INTO @TIMER(TIMER_TEXT) VALUES('iTVF Function #1 0 BITS')
SELECT
X.TIMER_STAMP
,X.EXEC_TIME
,CAST(X.EXEC_TIME AS FLOAT) / @SAMPLE_SIZE AS T_ROW
,X.TIMER_TEXT
FROM
(
SELECT
T1.TIMER_ID
,ROW_NUMBER() OVER (PARTITION BY T1.TIMER_TEXT ORDER BY T1.TIMER_ID DESC) AS TT_RID
,T1.TIMER_STAMP
,DATEDIFF(MICROSECOND,ISNULL(T2.TIMER_STAMP,T1.TIMER_STAMP),T1.TIMER_STAMP) AS EXEC_TIME
,T1.TIMER_TEXT
FROM @TIMER T1
LEFT OUTER JOIN @TIMER T2
ON T1.TIMER_ID = T2.TIMER_ID + 1
) AS X
WHERE X.TT_RID = 1
ORDER BY X.TIMER_ID;
/* 2012 AND LATER */
/*
SELECT
X.TIMER_STAMP
,X.EXEC_TIME
,CAST(X.EXEC_TIME AS FLOAT) / @SAMPLE_SIZE AS T_ROW
,X.TIMER_TEXT
FROM
(
SELECT
tt.TIMER_ID
,ROW_NUMBER() OVER (PARTITION BY TT.TIMER_TEXT ORDER BY TT.TIMER_ID DESC) AS TT_RID
,TT.TIMER_STAMP
,DATEDIFF(MICROSECOND,LAG(TT.TIMER_STAMP,1,TT.TIMER_STAMP) OVER (ORDER BY TT.TIMER_ID),TT.TIMER_STAMP) AS EXEC_TIME
,TT.TIMER_TEXT
FROM @TIMER TT
) AS X
WHERE X.TT_RID = 1
ORDER BY X.TIMER_ID;'
END
*/
Results
TIMER_STAMP EXEC_TIME T_ROW TIMER_TEXT
--------------------------- ----------- ---------------------- -------------------------------------------
2014-07-12 13:19:56.9834977 432024 1.68759375 Create test set of 256000 entries
2014-07-12 13:19:57.0154996 32002 0.1250078125 Run full sample select into a bucket, #1
2014-07-12 13:19:57.0485015 33002 0.1289140625 Run full sample select into a bucket, #2
2014-07-12 13:19:57.0825034 33002 0.1289140625 Run full sample select into a bucket, #3
2014-07-12 13:19:59.6936528 2611149 10.19980078125 SCALAR Function #0 8 BITS
2014-07-12 13:20:01.0507304 1357078 5.3010859375 SCALAR Function #1 8 BITS
2014-07-12 13:20:01.5697601 519030 2.0274609375 iTVF Function #1 8 BITS
2014-07-12 13:20:03.9358954 2366135 9.24271484375 SCALAR Function #0 7 BITS
2014-07-12 13:20:05.2389699 1303074 5.0901328125 SCALAR Function #1 7 BITS
2014-07-12 13:20:05.6929959 454026 1.7735390625 iTVF Function #1 7 BITS
2014-07-12 13:20:07.8311182 2138123 8.35204296875 SCALAR Function #0 6 BITS
2014-07-12 13:20:09.1061911 1275073 4.98075390625 SCALAR Function #1 6 BITS
2014-07-12 13:20:09.5232150 417024 1.629 iTVF Function #1 6 BITS
2014-07-12 13:20:11.4133231 1890108 7.383234375 SCALAR Function #0 5 BITS
2014-07-12 13:20:12.6553941 1242071 4.85183984375 SCALAR Function #1 5 BITS
2014-07-12 13:20:13.0404162 385022 1.5039921875 iTVF Function #1 5 BITS
2014-07-12 13:20:14.7015112 1661095 6.48865234375 SCALAR Function #0 4 BITS
2014-07-12 13:20:15.9315815 1230070 4.8049609375 SCALAR Function #1 4 BITS
2014-07-12 13:20:16.2746011 343020 1.339921875 iTVF Function #1 4 BITS
2014-07-12 13:20:17.7136834 1439082 5.6214140625 SCALAR Function #0 3 BITS
2014-07-12 13:20:18.8937509 1180067 4.60963671875 SCALAR Function #1 3 BITS
2014-07-12 13:20:19.1897679 296017 1.15631640625 iTVF Function #1 3 BITS
2014-07-12 13:20:20.3848362 1195069 4.66823828125 SCALAR Function #0 2 BITS
2014-07-12 13:20:21.5359021 1151066 4.4963515625 SCALAR Function #1 2 BITS
2014-07-12 13:20:21.7819161 246014 0.9609921875 iTVF Function #1 2 BITS
2014-07-12 13:20:22.7419710 960055 3.75021484375 SCALAR Function #0 1 BIT
2014-07-12 13:20:23.8700356 1128064 4.4065 SCALAR Function #1 1 BIT
2014-07-12 13:20:24.0170440 147009 0.57425390625 iTVF Function #1 1 BITS
2014-07-12 13:20:24.6430798 626035 2.44544921875 SCALAR Function #0 0 BIT
2014-07-12 13:20:25.6831393 1040060 4.062734375 SCALAR Function #1 0 BIT
2014-07-12 13:20:25.7181413 35002 0.1367265625 iTVF Function #1 0 BITS
October 16, 2014 at 5:53 am
I need to create a chart in Excel that totals records by weekending date. The catch is that the data may not naturally create the weekending date. For example, the data could create:
10/4/2014 15
10/11/2014 6
11/1/2014 15
However, I need to fill in the missing weeks so that the data for the chart is
10/4/2014 15
10/11/2014 6
10/18/2014 0
10/25/2014 0
11/1/2014 15
My thought was to use a tally table to create the missing records by populating it with records from 1 to 50,000 and then converting the value to a date. In VBA, CLng(Now()) will return the integer portion of today's date (41928). How can I convert an integer into a Date value? The articles that I've found seem to all reference values such as 20141015 as the 'integer' and then cast it.
In terms of the Tally Table, my thought is to do this...
1. Select records where the value is equal to or greater than today's date
2. Convert the value to a date
3. Extract the day of the week
4. Select only the records that fall on Saturday
Thoughts? Ideas? Sanity Checks?
October 16, 2014 at 6:42 am
david.holley (10/16/2014)
I need to create a chart in Excel that totals records by weekending date. The catch is that the data may not naturally create the weekending date. For example, the data could create:10/4/2014 15
10/11/2014 6
11/1/2014 15
However, I need to fill in the missing weeks so that the data for the chart is
10/4/2014 15
10/11/2014 6
10/18/2014 0
10/25/2014 0
11/1/2014 15
My thought was to use a tally table to create the missing records by populating it with records from 1 to 50,000 and then converting the value to a date. In VBA, CLng(Now()) will return the integer portion of today's date (41928). How can I convert an integer into a Date value? The articles that I've found seem to all reference values such as 20141015 as the 'integer' and then cast it.
In terms of the Tally Table, my thought is to do this...
1. Select records where the value is equal to or greater than today's date
2. Convert the value to a date
3. Extract the day of the week
4. Select only the records that fall on Saturday
Thoughts? Ideas? Sanity Checks?
Here's one approach that uses the Tally Table, so if you don't have one yet, you'll need to create one. I create the table some_table to simulate your table of transactions with a date column.
--first create and populate a table to hold some sample data
create table some_table (
trans_date datetime);
insert into some_table(trans_date)
values('02/01/2014'), ('02/02/2014'), ('02/02/2014'), ('04/01/2014'), ('04/05/2014');
--now run the report
WITH year_base AS (
SELECT CONVERT(datetime, '01/06/2014') week_start
),
year_weeks AS (
SELECT DATEADD(week, t.N - 1, week_start) week_start, DATEADD(day, 6, DATEADD(week, t.N - 1, week_start)) week_end
FROM year_base
CROSS JOIN dbo.Tally t
WHERE t.N < DATEPART(week, GetDate())
)
SELECT week_start, week_end, COUNT(*) trans_count
FROM year_weeks yw
CROSS APPLY some_table st
WHERE st.trans_date BETWEEN yw.week_start AND yw.week_end
GROUP BY week_start, week_end
UNION
SELECT week_start, week_end, 0 trans_count
FROM year_weeks yw
ORDER BY week_start;
The first query pulls the weeks where data exists and the second one all weeks. I returned the week starting and week ending dates for clarity.
In the year_base CTE, the date serves two purposes. It specifies the first date you want to report and also specifies the day of the week you want to use as the start date of each week. This one starts on a Monday, but you can change it it suit your specifications.
In the year_weeks CTE, the WHERE clause limits the weeks being queried to the current week, but you can adapt this to be whatever you need for your particular situation.
HTH
December 7, 2016 at 7:47 am
Excellent article and discussion Jeff
- Damian
December 7, 2016 at 9:14 pm
Thanks, Damian. I appreciate the feedback.
--Jeff Moden
April 10, 2017 at 8:04 pm
I stumbled across this article, and it's definitely at a level above my current abilities. That said, I do get the general idea, and why Tally tables are preferable to loops and cursors (and, I think, also easier to work with) is pretty straightforward. One thing that I didn't get, even at my level, though, was with respect to the code that populated the Tally table:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
Why does the code select the TOP 11000 values from two instances of the SysColumns table? Why not just one instance?
Thanks.
April 10, 2017 at 8:38 pm
jetboy2k - Monday, April 10, 2017 8:04 PMI stumbled across this article, and it's definitely at a level above my current abilities. That said, I do get the general idea, and why Tally tables are preferable to loops and cursors (and, I think, also easier to work with) is pretty straightforward. One thing that I didn't get, even at my level, though, was with respect to the code that populated the Tally table:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2Why does the code select the TOP 11000 values from two instances of the SysColumns table? Why not just one instance?
Thanks.
Because there usually aren't enough rows in the single table to get to 11,000 but there are in a CROSS JOIN of that table. For example and updating it to 2005+ technology, there are more than 4,000 rows in sys.all_columns (new thing that replaced sys.columns) but not much more in 2005. However, if you create a CROSS JOIN (Cartesian Product), you end up with roughly 4,000 X 4,000 or the ability to count to at least 16 million. TOP simply limits the number of rows used.
--Jeff Moden
April 11, 2017 at 5:24 am
jetboy2k - Monday, April 10, 2017 8:04 PMI stumbled across this article, and it's definitely at a level above my current abilities. That said, I do get the general idea, and why Tally tables are preferable to loops and cursors (and, I think, also easier to work with) is pretty straightforward. One thing that I didn't get, even at my level, though, was with respect to the code that populated the Tally table:
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2Why does the code select the TOP 11000 values from two instances of the SysColumns table? Why not just one instance?
Thanks.
Oops. Duplicate post.
April 11, 2017 at 7:58 am
Ed, sorry, how was this a duplicate post? Had someone else already asked this question?
April 11, 2017 at 9:12 am
jetboy2k - Tuesday, April 11, 2017 7:58 AMJeff, thanks for the explanation regarding why the query to generate the Tally table used a CROSS JOIN on two instances of the Syscolumns table. In my SQL instance, the COUNT(*) for the Master.dbo.Syscolumns table was 15645, so I couldn't figure out why there was a need for the CROSS JOIN. Now it makes sense.Ed, sorry, how was this a duplicate post? Had someone else already asked this question?
Ed's a good man and he knows a whole lot. He was probably in a bit of a hurry, didn't read my post, and posted a nearly identical post to mine and then realized all that and deleted his.
--Jeff Moden
April 11, 2017 at 9:24 am
jetboy2k - Tuesday, April 11, 2017 7:58 AMJeff, thanks for the explanation regarding why the query to generate the Tally table used a CROSS JOIN on two instances of the Syscolumns table. In my SQL instance, the COUNT(*) for the Master.dbo.Syscolumns table was 15645, so I couldn't figure out why there was a need for the CROSS JOIN. Now it makes sense.Ed, sorry, how was this a duplicate post? Had someone else already asked this question?
Ed's a good man and he knows a whole lot. He was probably in a bit of a hurry, didn't read my post, and posted a nearly identical post to mine and then realized all that and deleted his.
Thanks, Jeff. No, I didn't see your post before I created mine, even though you posted it last night. :blush:
JetBoy2K, I described the technique of using the CROSS JOIN and how it used the presence of rows to create a list of numbers. I also explained what Jeff explained, but didn't include the math like he did. After posting it, I saw Jeff's and deleted mine.
Sorry for the confusion. I wasn't awake enough yet.
Viewing 15 posts - 421 through 435 (of 497 total)
You must be logged in to reply to this topic. Login to reply