CODEWITHSUNDEEP
sql
CixrTyx
CixrTyx

Reputation: 1

SQL Select same fields with different dates

I have a query that I need to run for some data with MS SQL Server 2008. Basically I have 3 unique fields, and 3 more that need to be repeated for time ranges. So that I will have three sub queries within the primary select statement. I've been only experimenting with just the one sub query get that working and then copy n pasting the other two and changing the date range.

Here is the SQL that I've come up with but it gives me an error. I'm trying to use sub queries since I don't have perms to create procedures.

SELECT
mon.description as ScriptName,
up0.pageseqnum as PageSeq,
usd.DisplayName as PageName,

--This subquery is the one week old data for the Response Time, Page Weight, and Number of Objects.
(SELECT
ROUND(AVG(CAST(up1.ResponseTime AS FLOAT)* 0.001),3) AS ResponseTime,
ROUND(AVG(CAST(up1.numbytes AS FLOAT)* 0.001), 3) AS NumberofKilobytes,
CONVERT(INT,AVG(up1.numobj),0) AS NumberOfObjects
FROM
table0 AS up1
WHERE
up1.Monitor_Id = up0.Monitor_id
AND up1.TestTime BETWEEN DATEADD(HOUR,4,'2012-05-14 00:00:00') AND DATEADD(HOUR,4,'2012-05-21 00:00:00')
AND ISNULL(up1.ContentMatchStatus,'0')= 0
AND up1.UserScriptStatus = 0
AND up1.TimeoutStatus = 0
AND up1.ResponseTime > 0
) AS CurrentWeek

FROM
table0 (nolock) AS up0
JOIN table1 usd ON up0.Monitor_Id=usd.monitor_id
and up0.PageSeqNum=usd.PageSeqNum
JOIN table3 mon on up0.Monitor_Id = mon.Monitor_id

WHERE
up0.Monitor_Id in (1, 2, 3, 4, 5, 6, 7, 8 ,9 ,10)

GROUP BY up0.Monitor_Id, mon.Description, up0.PageSeqNum, usd.DisplayName
ORDER BY mon.Description ASC;

I'm hoping the result set should have these columns, and that I can add two more previous weeks of data after the current week! PLEASE SCROLL to see what I'm talking about as a result.

                                |           CurrentWeek (Sub Query)                  |           OneWeekOld (Sub Query)                   |
ScriptName | PageSeq | PageName | ResponseTime | NumberofKilobytes | NumberOfObjects | ResponseTime | NumberofKilobytes | NumberOfObjects |
Test1      |   0     |   Home   |      1.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test1      |   1     |   Sale   |      2.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test1      |   2     |   Bake   |      3.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test1      |   3     |   Cake   |      4.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test2      |   0     |   Home   |      1.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test2      |   1     |   Sale   |      2.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |
Test2      |   2     |   Bake   |      3.2     |      50.23        |        56       |      1.2     |      50.23        |        56       |

Any help would be appreciated.

Upvotes: 0

Views: 373

Answers (1)

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6584

Try something like this:

SELECT  mon.description as ScriptName,
        up0.pageseqnum as PageSeq,
        usd.DisplayName as PageName,
        wk.ResponseTime,
        wk.NumberofKilobytes,
        wk.NumberOfObjects

FROM
table0 (nolock) AS up0
JOIN table1 usd ON up0.Monitor_Id=usd.monitor_id
and up0.PageSeqNum=usd.PageSeqNum
JOIN table3 mon on up0.Monitor_Id = mon.Monitor_id
CROSS APPLY (   SELECT  up1.Monitor_Id,
                        ROUND(AVG(CAST(up1.ResponseTime AS FLOAT)* 0.001),3) AS ResponseTime,
                        ROUND(AVG(CAST(up1.numbytes AS FLOAT)* 0.001), 3) AS NumberofKilobytes,
                        CONVERT(INT,AVG(up1.numobj),0) AS NumberOfObjects
                FROM table0 AS up1
                WHERE up1.TestTime BETWEEN DATEADD(HOUR,4,'2012-05-14 00:00:00') AND DATEADD(HOUR,4,'2012-05-21 00:00:00')
                AND ISNULL(up1.ContentMatchStatus,'0')= 0
                AND up1.UserScriptStatus = 0
                AND up1.TimeoutStatus = 0
                AND up1.ResponseTime > 0 
                GROUP BY up1.Monitor_Id) wk

WHERE up0.Monitor_Id in (1, 2, 3, 4, 5, 6, 7, 8 ,9 ,10)
AND up0.Monitor_Id = up1.Monitor_Id
ORDER BY mon.Description ASC;

It would be easier if you posted your schema up on sqlfiddle.com then I can write a query against it to return what you need.

Also Im assuming youre using SQL Server hence the use of APPLY. Please state which database system you are working with.

Upvotes: 1

Related Questions