View with a subselect - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

View with a subselect

Author

Topic

aznova
Starting Member

2 Posts

Posted - 2010-10-12 : 15:34:52

I have a large database (approx 1mil records created per week). I am trying to analyze the data into "buckets". I am counting the number of times (Occurrences) that an Event is Replayed.

Ultimately, this is what I am look for:


Event   Status          Replay  Occurrences
ADD	COMPLETE	1	167
ADD	COMPLETE	2	33
ADD	COMPLETE	3	5
ADD	COMPLETE	4	6
ADD	COMPLETE	5	2
ADD	COMPLETE	6	1
ADD	COMPLETE	8	1
ADD	COMPLETE	12	1

The source data has a record per event (and status) with additional data including an event ID and datetime stamps. For the above example data, there would be 308 ADD/COMPLETE source records ((1*167) + (2*33) + (3*5) + ...).

I am trying to create a view such that I could accomplish something similar to:


SELECT Event, Status, ReplayCount, Occurrences
  FROM vw_WeeklyData
 WHERE StartDate >= '2010-09-29' AND StartDate <= '2010-10-06'
   AND Event = 'ADD'
   AND Status = 'COMPLETE'

I have a query now that does what I want but I need the flexibility of a view:


SELECT t1.Event, t1.Status, t1.ReplayCount, COUNT(*) AS Occurrences
  FROM 
  (
    SELECT dw.Event, dw.EventID, dw.Status, COUNT(*) AS ReplayCount
      FROM DataWeekly AS dw 
     WHERE StartDate >= '2010-09-29' AND StartDate <= '2010-10-06'
       AND Event = 'ADD'
       AND Status = 'COMPLETE'
     GROUP BY dw.Event, dw.EventID, dw.Status 
  ) AS t1
 GROUP BY t1.Event, t1.Status, t1.ReplayCount
 ORDER BY Event;

Essentially, the subselect is grouping by EventID to get the number of times the specific EventID has an Event/Status record. The outer select then counts the number of occurrences that events were replayed x number of times.

So, how can I get from this query to a more flexibly view which would allow me to query by a range of dates for StartDate, Event and Status?

Any help would be greatly appreciated!

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 15:42:40

Just put CREATE VIEW ... AS Your Query Here GO and you've got a view now.

But you don't need a view. Your query already allows you to do what you want, just add a WHERE clause to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

aznova
Starting Member

2 Posts

Posted - 2010-10-12 : 16:23:31

The issue is that the StartDate is only visible in the inner select, which is where I am rolling up the replay counts via the GROUP BY dw.Event, dw.EventID, dw.Status.

The StartDate cannot be visible to the outer select because of the roll-up.

With my existing query, I have to edit the inner select WHERE clause to get my data out.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 16:44:01

Can you switch this to a stored procedure?:

create proc usp_SomeProc as (@start datetime, @end datetime)
as
SELECT t1.Event, t1.Status, t1.ReplayCount, COUNT(*) AS Occurrences
FROM
(
SELECT dw.Event, dw.EventID, dw.Status, COUNT(*) AS ReplayCount
FROM DataWeekly AS dw
WHERE StartDate >= @start AND StartDate <= @end
AND Event = 'ADD'
AND Status = 'COMPLETE'
GROUP BY dw.Event, dw.EventID, dw.Status
) AS t1
GROUP BY t1.Event, t1.Status, t1.ReplayCount
ORDER BY Event;
GO

exec usp_SomeProc '2010-09-29', '2010-10-06'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-12 : 16:52:20

or a in-line table valued user defined function:


select <column_List> 
from   dbo.fn_SomeFunction('2010-09-29', '2010-10-06')
--join other tables if you want

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources