Hi there,

I have a function that splits data into intervals based on their
timestamp as follows:

ALTER  FUNCTION intervals(@begin DATETIME, @end DATETIME,
@lengthInSeconds INT)
RETURNS @intervals TABLE(interval INT PRIMARY KEY, interval_begin
DATETIME, interval_end DATETIME)
AS
BEGIN
    DECLARE @i INT
    DECLARE @intervalBegin DATETIME
    DECLARE @intervalEnd DATETIME

    SET @intervalBegin = @begin
    SET @i = 0

    WHILE (@intervalBegin < @end)
    BEGIN
        SET @intervalEnd = DATEADD(s,@lengthInSeconds,@intervalBegin)
        INSERT INTO @intervals(interval,interval_begin,interval_end)
            VALUES(@i,@intervalBegin,@intervalEnd)
        SET @i = @i + 1
        SET @intervalBegin = @intervalEnd
    END
    RETURN
END

and I am using it to sort out data from the following query:

DECLARE @DataTable VARCHAR(25)
DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @Length INT
DECLARE @TLT VARCHAR(25)
SET @DataTable = 'sn_mom_historic_ldn'
SET @SD = '02/01/2006'
SET @ED = '03/01/2006'
SET @Length = 604800
SET @TLT = 'xxxxx'
Execute datecompare2 @DataTable, @SD, @ED, @TLT
SELECT tlt AS TLT, COUNT(DISTINCT compname) AS Systems,
COUNT(localtime) AS Total_time,
    (SELECT COUNT (CPU)
        FROM ##sn_tmp2_mom
        WHERE CPU <= 2 AND a.tlt=tlt) AS Idle_Time,
    (SELECT AVG (CPU)
        FROM ##sn_tmp2_mom
        WHERE CPU > 2 AND a.tlt=tlt) AS Average_Utilisation
FROM ##sn_tmp2_mom a
JOIN intervals(@SD, @ED, @Length)
    ON localtime >= interval_begin
    AND localtime < interval_end
GROUP BY interval, tlt
ORDER BY interval


The problem is the data is spliting correctly for the query, but not
the subqueries contained in it. I can see the problem, I just don't
have the knowledge on how to fix it. Could someone give me a point in
the right direction please?

Thanks in advance,

Steve

Steve (steve.ni***@gmail.com) writes:
Show quoteHide quoteGood that you see the problem, because I don't. What do you mean "not
the subqueries contained it". I can't see that the function relates to
the subqueries at all.

I think the best is if you post:

o   The CREATE TABLE statement for ##sn_tmp2_mom
o   INSERT statments with sample data.
o   The desired result given the sample.
o   A short narrative of what the query is supposed to achieve.

Erland Sommarskog wrote:
No problems, I've been working on this for so long that I'd forgotten
that people won't have the same knowledge about the issue I have.

In a nutshell, I am trying to take some data from a number of tables
that get selected on within a procedure to create the temporary table
##sn_tmp2_mom. That temporary table is created and holds 4 pieces of
information: TLT (or System owner), computer name, timestamp and CPU
utilization. For completeness, the procedure is as follows:

ALTER   PROCEDURE DateCompare2 @DataTable VARCHAR(25), @StartDate
DATETIME, @EndDate DATETIME, @TLT VARCHAR(25)
AS
DECLARE @sql_statement NVARCHAR (2000)
IF EXISTS (SELECT 1 FROM TEMPDB.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='##sn_tmp2_mom')
    DROP TABLE ##sn_tmp2_mom
CREATE TABLE ##sn_tmp2_mom (tlt VARCHAR (50), compname VARCHAR (50),
localtime DATETIME, CPU INT)
SET @sql_statement = N'
INSERT INTO ##sn_tmp2_mom (tlt, compname, localtime, cpu)
SELECT dbo.sn_tlt_lookup.Revised_TLT, dbo.' + @DataTable +
'.ComputerName, dbo.' + @DataTable + '.localtime,
       dbo.' + @DataTable + '.CPU_Used
FROM dbo.VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE INNER
JOIN
     dbo.sn_tlt_lookup ON
dbo.VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE.TLT =
dbo.sn_tlt_lookup.Orig_TLT INNER JOIN
     dbo.' + @DataTable + ' ON
     dbo.VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE.HOST_NAME
= dbo.' + @DataTable + '.ComputerName
WHERE localtime > @StartDate AND localtime < @EndDate AND
dbo.sn_tlt_lookup.Revised_TLT = @TLT
ORDER BY localtime'
BEGIN
EXECUTE sp_executesql @sql_statement, N'@StartDate DATETIME, @EndDate
DATETIME, @TLT VARCHAR(25)', @StartDate, @EndDate, @TLT
END

Once the temporary table is created I want to select the TLT, a
distinct count of computer names, a count of all the timestamps, and
then the tricky bit: a count of rows where the CPU field is <= 2, and
an average of the CPU where CPU > 2. This allows me to calculate the
percentage of time the owners boxes are idle, and shows and average
load on the systems when they aren't.

The problem arises because I want to be able to do trending on the
data. I have written the function in my first post to split the start
and end timestamps into chunks defined by the interval given. My select
statement is the joined with the results of the query, and gives me a
breakdown on a week by week basis (or whatever the interval happens to
be). Unfortunately, the subqueries in my select statement that get the
count and average of CPU need different WHERE clauses, so they are run
as sub-SELECT statements within the main SELECT statement. Obviously,
the function only applies itself to the main SELECT, so I get a number
of rows of correct information in 3 columns, and the same information
in every row for the last 2 columns.

Hopefully that makes sense.

The procedure is above.


To recreate the database as it stands, the following should give some
sample data:

CREATE DATABASE Inventory
GO
USE Inventory
GO
CREATE TABLE sn_mom_historic_ldn
(ComputerName VARCHAR (50), localtime DATETIME, CPU_Used INT)
CREATE TABLE sn_tlt_lookup
(Orig_TLT VARCHAR(50), Revised_TLT VARCHAR(50))
CREATE TABLE VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE
(TLT VARCHAR(50), HOST_NAME VARCHAR(50))
INSERT INTO sn_mom_historic_ldn VALUES
('system1', '01/02/2006 00:15:00', 0),
('system1', '08/02/2006 00:15:00', 2),
('system1', '21/02/2006 00:15:00', 5),
('system2', '01/02/2006 00:15:00', 4),
('system2', '08/02/2006 00:15:00', 3),
('system2', '21/02/2006 00:15:00', 2),
('system3', '01/02/2006 00:15:00', 1),
('system3', '08/02/2006 00:15:00', 0),
('system4', '01/02/2006 00:15:00', 1),
('system4', '08/02/2006 00:15:00', 2),
('system4', '21/02/2006 00:15:00', 3)

INSERT INTO sn_tlt_lookup VALUES
('aaaaa', 'xxxxx'),
('bbbbb', 'yyyyy')

INSERT INTO VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE VALUES
('aaaaa', 'system1'),
('aaaaa', 'system2'),
('bbbbb', 'system3'),
('aaaaa', 'system4')


Given all that (and the original query) the ouput of the query would
hopefully look like:

TLT       Systems        Total Time     Idle_Time  Average_Utilisation
xxxxx    3                   3                  2                  4
xxxxx    3                   3                  2                  3
xxxxx    3                   3                  1                  4

Not exactly short I know, but hopefully all that makes things clearer.
Just for clarity, and to keep everything in one place, the original
post had the function:

ALTER  FUNCTION intervals(@begin DATETIME, @end DATETIME,
@lengthInSeconds INT)
RETURNS @intervals TABLE(interval INT PRIMARY KEY, interval_begin
DATETIME, interval_end DATETIME)
AS
BEGIN
        DECLARE @i INT
        DECLARE @intervalBegin DATETIME
        DECLARE @intervalEnd DATETIME

        SET @intervalBegin = @begin
        SET @i = 0

        WHILE (@intervalBegin < @end)
        BEGIN
                SET @intervalEnd =
DATEADD(s,@lengthInSeconds,@intervalBegin)
                INSERT INTO
@intervals(interval,interval_begin,interval_end)
                        VALUES(@i,@intervalBegin,@intervalEnd)
                SET @i = @i + 1
                SET @intervalBegin = @intervalEnd
        END
        RETURN
END

and the query:

DECLARE @DataTable VARCHAR(25)
DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @Length INT
DECLARE @TLT VARCHAR(25)
SET @DataTable = 'sn_mom_historic_ldn'
SET @SD = '02/01/2006'
SET @ED = '03/01/2006'
SET @Length = 604800
SET @TLT = 'xxxxx'
Execute datecompare2 @DataTable, @SD, @ED, @TLT
SELECT tlt AS TLT, COUNT(DISTINCT compname) AS Systems,
COUNT(localtime) AS Total_time,
        (SELECT COUNT (CPU)
                FROM ##sn_tmp2_mom
                WHERE CPU <= 2 AND a.tlt=tlt) AS Idle_Time,
        (SELECT AVG (CPU)
                FROM ##sn_tmp2_mom
                WHERE CPU > 2 AND a.tlt=tlt) AS Average_Utilisation
FROM ##sn_tmp2_mom a
JOIN intervals(@SD, @ED, @Length)
        ON localtime >= interval_begin
        AND localtime < interval_end
GROUP BY interval, tlt
ORDER BY interval

Thanks,

Steve

Steve (steve.ni***@gmail.com) writes:
Thsnks for the script. However, when I assembled the pieces and ran it,
I didn't get any data in the global temp table. Since your INSERT statements
had an illegal syntax, I am skeptic to that you actually tested your
script before you posted it.

Please note that there is no need to drag your procedure into this. If the
problem is the final query with the function, I only need INSERT statements
to populate the temp table with test data (and the desired output).

This is the script that I assembled from your pieces:

CREATE DATABASE Inventory
GO
USE Inventory
GO
SET DATEFORMAT dmy
go
CREATE TABLE sn_mom_historic_ldn
(ComputerName VARCHAR (50), localtime DATETIME, CPU_Used INT)
CREATE TABLE sn_tlt_lookup
(Orig_TLT VARCHAR(50), Revised_TLT VARCHAR(50))
CREATE TABLE VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE
(TLT VARCHAR(50), HOST_NAME VARCHAR(50))
INSERT INTO sn_mom_historic_ldn VALUES
('system1', '01/02/2006 00:15:00', 0)
INSERT INTO sn_mom_historic_ldn VALUES
('system1', '08/02/2006 00:15:00', 2)
INSERT INTO sn_mom_historic_ldn VALUES
('system1', '21/02/2006 00:15:00', 5)
INSERT INTO sn_mom_historic_ldn VALUES
('system2', '01/02/2006 00:15:00', 4)
INSERT INTO sn_mom_historic_ldn VALUES
('system2', '08/02/2006 00:15:00', 3)
INSERT INTO sn_mom_historic_ldn VALUES
('system2', '21/02/2006 00:15:00', 2)
INSERT INTO sn_mom_historic_ldn VALUES
('system3', '01/02/2006 00:15:00', 1)
INSERT INTO sn_mom_historic_ldn VALUES
('system3', '08/02/2006 00:15:00', 0)
INSERT INTO sn_mom_historic_ldn VALUES
('system4', '01/02/2006 00:15:00', 1)
INSERT INTO sn_mom_historic_ldn VALUES
('system4', '08/02/2006 00:15:00', 2)
INSERT INTO sn_mom_historic_ldn VALUES
('system4', '21/02/2006 00:15:00', 3)

INSERT INTO sn_tlt_lookup VALUES
('aaaaa', 'xxxxx')
INSERT INTO sn_tlt_lookup VALUES
('bbbbb', 'yyyyy')

INSERT INTO VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE VALUES
('aaaaa', 'system1')
INSERT INTO VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE VALUES
('aaaaa', 'system2')
INSERT INTO VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE VALUES
('bbbbb', 'system3')
INSERT INTO VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE VALUES
('aaaaa', 'system4')
go
CREATE   PROCEDURE DateCompare2 @DataTable VARCHAR(25), @StartDate
DATETIME, @EndDate DATETIME, @TLT VARCHAR(25)
AS
DECLARE @sql_statement NVARCHAR (2000)
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='##sn_tmp2_mom')
    DROP TABLE ##sn_tmp2_mom
CREATE TABLE ##sn_tmp2_mom (tlt VARCHAR (50), compname VARCHAR (50),
localtime DATETIME, CPU INT)
SET @sql_statement = N'
INSERT INTO ##sn_tmp2_mom (tlt, compname, localtime, CPU)
SELECT dbo.sn_tlt_lookup.Revised_TLT, dbo.' + @DataTable +
'.ComputerName, dbo.' + @DataTable + '.localtime,
       dbo.' + @DataTable + '.CPU_Used
FROM dbo.VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE INNER
JOIN
     dbo.sn_tlt_lookup ON
dbo.VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE.TLT =
dbo.sn_tlt_lookup.Orig_TLT INNER JOIN
     dbo.' + @DataTable + ' ON
     dbo.VW_COST_TRANSPARENCY_SERVERS_BY_LOCATION_AND_SERVICE.HOST_NAME
= dbo.' + @DataTable + '.ComputerName
WHERE localtime > @StartDate AND localtime < @EndDate AND
dbo.sn_tlt_lookup.Revised_TLT = @TLT
ORDER BY localtime'
BEGIN
EXECUTE sp_executesql @sql_statement, N'@StartDate DATETIME, @EndDate
DATETIME, @TLT VARCHAR(25)', @StartDate, @EndDate, @TLT
END
go
CREATE  FUNCTION intervals(@begin DATETIME, @end DATETIME,
@lengthInSeconds INT)
RETURNS @intervals TABLE(interval INT PRIMARY KEY, interval_begin
DATETIME, interval_end DATETIME)
AS
BEGIN
        DECLARE @i INT
        DECLARE @intervalBegin DATETIME
        DECLARE @intervalEnd DATETIME

        SET @intervalBegin = @begin
        SET @i = 0

        WHILE (@intervalBegin < @end)
        BEGIN
                SET @intervalEnd =
DATEADD(s,@lengthInSeconds,@intervalBegin)
                INSERT INTO
@intervals(interval,interval_begin,interval_end)
                        VALUES(@i,@intervalBegin,@intervalEnd)
                SET @i = @i + 1
                SET @intervalBegin = @intervalEnd
        END
        RETURN
END
go
DECLARE @DataTable VARCHAR(25)
DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @Length INT
DECLARE @TLT VARCHAR(25)
SET @DataTable = 'sn_mom_historic_ldn'
SET @SD = '02/01/2006'
SET @ED = '03/01/2006'
SET @Length = 604800
SET @TLT = 'xxxxx'

Execute DateCompare2 @DataTable, @SD, @ED, @TLT

SELECT * FROM ##sn_tmp2_mom

SELECT tlt AS TLT, COUNT(DISTINCT compname) AS Systems,
COUNT(localtime) AS Total_time,
        (SELECT COUNT (CPU)
                FROM ##sn_tmp2_mom
                WHERE CPU <= 2 AND a.tlt=tlt) AS Idle_Time,
        (SELECT AVG (CPU)
                FROM ##sn_tmp2_mom
                WHERE CPU > 2 AND a.tlt=tlt) AS Average_Utilisation
FROM ##sn_tmp2_mom a
JOIN intervals(@SD, @ED, @Length)
        ON localtime >= interval_begin
        AND localtime < interval_end
GROUP BY interval, tlt
ORDER BY interval
go
use master
go
DROP DATABASE Inventory
DROP TABLE ##sn_tmp2_mom

Joining 2 select statement with like fields
Views and CTEs performance problem
Adding a space in a field
Can't use Shared Memory protocol to connect
merge two date ranges with overlap from two tables