ALL IN ONE SQL STATEMENT?

I am using one SQL (View) to get my sums on various fields. I then use a
second SQL (View) to reference the first View to do my calculations. Is ther
e
a way to combine this all in one SQL View? I want to use the second View and
be able to pass it date range variables from a Web page form.
I am new at this and appreciate your help in advance...
View 1.
SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty) A
S
SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
GROUP BY Site, [Date]
ORDER BY Site
View 2.
SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
FROM dbo.View1
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1You can use a derived table for this purpose:
SELECT ...
FROM (SELECT ... FROM ...) AS D
Also, if you need to parameterize the query, instead of using a view, you
can use an inline table-valued function:
CREATE FUNCTION dbo.f1
(
@.from_dt AS DATETIME,
@.to_dt AS DATETIME
)
RETURNS TABLE
AS
RETURN
SELECT ... FROM ... WHERE dt >= @.from_dt AND dt < @.to_dt
GO
SELECT ... FROM dbo.f1('20040101', '20050101') AS F;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615ef24bad050@.uwe...
>I am using one SQL (View) to get my sums on various fields. I then use a
> second SQL (View) to reference the first View to do my calculations. Is
> there
> a way to combine this all in one SQL View? I want to use the second View
> and
> be able to pass it date range variables from a Web page form.
> I am new at this and appreciate your help in advance...
> View 1.
> SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty)
> AS
> SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
> AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
> SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
> SumOfOutOfAdherenceSecsQty,
> SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
> (TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
> SumOfTalkHoldAvailable,
> [Date]
> FROM dbo.[National Call Stats]
> WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
> GROUP BY Site, [Date]
> ORDER BY Site
> View 2.
> SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
> SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
> FROM dbo.View1
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||I appreciate your response, I still need a little more clarification, so
thank you in advance for your patience. In your example are you using
referring to SELECT the code in view 2 FROM(SELECT view 1)? I haven't gotten
into creating functions yet, but thanks though.
Itzik Ben-Gan wrote:
>You can use a derived table for this purpose:
>SELECT ...
>FROM (SELECT ... FROM ...) AS D
>Also, if you need to parameterize the query, instead of using a view, you
>can use an inline table-valued function:
>CREATE FUNCTION dbo.f1
>(
> @.from_dt AS DATETIME,
> @.to_dt AS DATETIME
> )
>RETURNS TABLE
>AS
>RETURN
> SELECT ... FROM ... WHERE dt >= @.from_dt AND dt < @.to_dt
>GO
>SELECT ... FROM dbo.f1('20040101', '20050101') AS F;
>
>[quoted text clipped - 25 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Yes. Something like this:
CREATE VIEW dbo.MyView
AS
SELECT Site, SumOfInAdherenceSecsQty / (SumOfInAdherenceSecsQty +
SumOfOutOfAdherenceSecsQty) AS Adherence, [Date]
FROM
(
SELECT TOP 1000 Site, SUM(NchQty) AS SumOfNchQty, SUM(SchdOpenSecsQty)
AS
SumOfSchdOpenSecsQty, SUM(LogOnSecsQty)
AS SumOfLogOnSecsQty, SUM(InAdherenceSecsQty) AS
SumOfInAdherenceSecsQty, SUM(OutOfAdherenceSecsQty) AS
SumOfOutOfAdherenceSecsQty,
SUM(HoldSecsQty) AS SumOfHoldSecsQty, SUM
(TotalHandleTime) AS SumOfTotalHandleTime, SUM(TalkHoldAvailable) AS
SumOfTalkHoldAvailable,
[Date]
FROM dbo.[National Call Stats]
WHERE ([Date] >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
GROUP BY Site, [Date]
) AS D
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615f8daa4774b@.uwe...
>I appreciate your response, I still need a little more clarification, so
> thank you in advance for your patience. In your example are you using
> referring to SELECT the code in view 2 FROM(SELECT view 1)? I haven't
> gotten
> into creating functions yet, but thanks though.
> Itzik Ben-Gan wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1