The sample query doesn't even parse and that will be confusing for beginners.

I left out the ID column as it does not appear in the table. To combine both queries I used the except clause.

(SELECT ID,VALUE,PREVREQDATE FROM STATISTICS
WHERE SYSTNODE = 10 and METRICValue = 30 and PREVRQDATE > 20100101 and PREVREQDATE < 20101231
GROUP BY PREVREQDATA)
EXCEPT
(SELECT ID,VALUE,PREVREQDATE FROM STATISTICS
WHERE SYSTNODE = 10 and METRICValue = 40 and PREVRQDATE > 20100101 and PREVREQDATE < 20101231
GROUP BY PREVREQDATA)
ORDER BY VALUE;

I would also like to know the correct queries since I think it's a difference whether the data are grouped by PREVREQDATA (should be PREVREQDATE) and aggregate functions are applied to ID and VALUE columns or if the data will be grouped by ID,VALUE and PREVREQDATE.

It should also be explained in more details what "joining the results in EXCEL to get difference of rows" actually means: it might be used to find the rows in query 1 not in query 2 or vice versa or both. It could also be interpreted to compare the results in EXCEL "row by row" to find the rows not being identical (similar to a comparison based on ROW_NUMBER()).

@Nitron: EXCEPT was introduced in SQL 2005. So it's not a new 2008 feature...

I can't say the code below is the solution, I am rather trying to clarify what business rules should be evaluated (as Lutz Mueller mentioned).

And that code is not a single query. (Changing that code to CTE syntax to make it a single query will not work since MERGE can't be applied to aggregated columns.)

The SQL 2008 new feature that is used here is MERGE INTO (it was in SQL 2005 Beta 2, but was pulled out of RTM). I quess this is not about +=, -= assignment operators (btw - it's there).

So idea was to find difference for given dates between two SUM(values)

DECLARE @SetOne TABLE
(
[ValueTotal] [bigint] NULL,
[PREVREQDATE] [bigint] NULL
);
DECLARE @SetTwo TABLE
(
[ValueTotal] [bigint] NULL,
[PREVREQDATE] [bigint] NULL
)

INSERT INTO @SetOne
SELECT SUM(ISNULL(VALUE,0)) ValueTotal,[PREVREQDATE] FROM  [STATISTICS]
WHERE SYSTNODE = 10 and METRICValue = 30 and [PREVREQDATE] > 20100101 and [PREVREQDATE] < 20101231
GROUP BY PREVREQDATE
ORDER BY ValueTotal

INSERT INTO @SetTwo
SELECT SUM(ISNULL(VALUE,0)) ValueTotal,PREVREQDATE FROM [STATISTICS]
WHERE SYSTNODE = 10 and METRICValue = 40 and [PREVREQDATE] > 20100101 and PREVREQDATE < 20101231
GROUP BY PREVREQDATE
ORDER BY ValueTotal

MERGE INTO @SetOne AS TGT
USING @SetTwo AS SRC
ON TGT.[PREVREQDATE]=SRC.[PREVREQDATE]
WHEN MATCHED THEN
UPDATE SET
TGT.ValueTotal-=SRC.ValueTotal
WHEN NOT MATCHED THEN
INSERT (ValueTotal, PREVREQDATE)
VALUES(SRC.ValueTotal, SRC.PREVREQDATE);


--and check the output
SELECT * FROM @SetOne
ORDER BY ValueTotal

I have no clue what the output should be. What does 'joining the results in EXCEL to get difference of rows" mean? Excel doesn't do JOINs. And this "difference of rows," is that one result per row or what, exactly?

Hello,

Except the laziness of 'typo' errors ( "PREVRQDATE" and where is ID in the "CREATE TABLE"),

Briefly complaining (as Lutz and Erik) about a requirement that is ambiguous and unclear,

I just want to ask 1.5 questions:

• Why your queries use a "GROUP BY" clause when the "SELECT" does not contain ANY aggregate ??? Although you CAN do this, running such a query against a table with "billions of rows" is a good reason for a salary 'increase' ;)

• Why are you using 'ORDER BY VALUE' in the 'intermediary' queries is only 0.5 of a question ... since these queries are to be 'joined' anyway

Regards, E.I.

I do not understand what the requirements are.

The difference in the value of SYSTNODE will be zero as both queries state WHERE SYSTNODE = 10.

Are you asking for the difference in the field "VALUE"?

Are you asking for the difference in SUM(VALUE) between the two queries?

I am still using SQL Server 2000, but I bet I could come up with a single query to provide your answer, if I understood what result you are requesting.

??????????????????? ... what are you talking about...

The problem, as stated, has several glaring errors in it, which make it impossible to do anything but ASSUME that one thing or another is true, but let's go down the road and see where we end up. First of all, the table, as shown in the create statement, does not have a field named "ID", as would be expected from the SELECT statements provided. Since a GROUP BY statement only includes PREVREQDATA (a probable typo of PREVREQDATE), it shouldn't be in either of those selects without an aggregrate. This problem alone makes everything else guesswork, but here goes anyway. If we ignore the ID field entirely, and pretend it just doesn't exist, we run into the VALUE field being selected without an aggregate, and once again, from here foreward, it's all guesswork. It just seems to me that the only possible way forward is to assume that there's only one record for each combination of SYSTNODE, METRICValue of 30 or 40, and PREVREQDATE in the specfied range. As the problems are created primarily by virtue of the existence of the GROUP BY statement, let's do without it on the basis of the previously mentioned assumption, at least for the time being, and get to the core of the desired result. If we assume that at most a single record exists for each possible date value in the range for each of the two METRICValue values (30 and 40), then it would seem reasonable that the desired end result is the numeric difference between the VALUE field for each date, given that a record for METRICValue of 30 as well as one for 40, exists for each date in the range. I've been unable to make any other viable assumption as yet. With that as the basis, I'm not even sure you need any new feature of 2008 to do this.

;WITH ALLDATA AS (

    SELECT PREVREQDATE, METRICValue, MIN(VALUE) AS VALUE
    FROM STATISTICS
    WHERE SYSTNODE = 10
        AND METRICValue IN (30, 40)
        AND PREVREQDATE > 20100101
        AND PREVREQDATE < 20101231
    GROUP BY PREVREQDATE, METRICValue
)
SELECT A.PREVREQDATE, A.VALUE - B.VALUE AS THEDIFFERENCE
FROM ALLDATA AS A
    INNER JOIN ALLDATA AS B
        ON A.PREVREQDATE = B.PREVREQDATE
            AND A.METRICValue = 30
            AND B.METRICValue = 40
ORDER BY A.PREVREQDATE

Of course, this has no new feature of SQL 2008 involved, and thus doesn't quite qualify as what the author is looking for. Problem is, what exactly, is the author looking for? If, rather than looking for the numeric difference in the value field, all that's being looked for is to find records where the VALUE field is different for the two values of METRICValue, all I might need to do is add a final WHERE clause that says WHERE A.VALUE <> B.VALUE. Finally, if it's something else the author is looking for, I'm not seeing it, at least based on what's been provided. As a sidenote, I will also mention that there's an assumption of billions of rows for the table but the CREATE TABLE statement is not followed by any mention of an index , nor even a primary key, both of which would likely be critical to getting any query with a where clause or group by clause to execute in a reasonable timeframe.

I have to believe that as the author is listed as a Microsoft MVP, that he knows exactly what he wants. Unfortunately, as posted, this problem is about as clear as mud - perhaps there was a language translation problem in getting it posted ?

Here's another guessed solution:

Requirement:

Your task is to generate a single TSQL statement to fetch the difference between the SYSTNODE value without having the need to use EXCEL.

You must use one of NEW TSQL FEATURES from SQL Server 2008 version.

Since both queries use WHERE SYSTNODE = 10, the answer to the first question is SELECT 0. In order to meet the 2nd requirement (which is not needed at all based on my interpretation), I simply add a cast to the new DATE format.

So, here's my approach:

SELECT answer 
FROM
(SELECT 0 AS answer, CAST(GETDATE() AS DATE) AS SQL2008_feature) x

As a side note: due to the (still) unclear requirement and the time left to answer the question I herewith recommend to exclude the question from the quiz.