"Nathan Carroll" wrote:

> I run three processes to return data that I want.  This seems like a long
> and maybe not very good way to get data from the table in question.  I was
> hoping to use #temp and put all the actions into one batch procedure that
> returns the queried data or maybe create view with the variable parameters.
> New to stored procedures.  Have not found much in the means of guidance
> documentation for stored procedures etc with sql server.  Any reference
> knowledge would be appreciated.
>
> first I make two tables with data queried:
>
> ALTER PROCEDURE dbo.QueryData
>
>     @user int,
>     @sDate DATETIME,
>     @fDate DATETIME
>
> AS
>
> CREATE TABLE temptable (client nvarchar(50), WO nvarchar(255), UserHours
> float(8))
> CREATE TABLE temptable2 (client nvarchar(50), UserHours float(8))
>
> INSERT INTO temptable (client, WO, UserHours)
> (SELECT dbo.BillableTimesheet.FullClient as client,
> dbo.BillableTimesheet.WO as WO,
> SUM(dbo.BillableTimesheet.BillableHours) AS UserHours
> FROM dbo.BillableTimesheet
> WHERE (dbo.BillableTimesheet.Userid = @user) AND
> (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate, 102)
> AND CONVERT(DATETIME, @fDate, 102))
> GROUP BY dbo.BillableTimesheet.FullClient, dbo.BillableTimesheet.WO)
>
> INSERT INTO temptable2 (client, UserHours)
> (SELECT temptable.client,SUM(BillableTimesheet.BillableHours) as UserHours
> FROM temptable INNER JOIN BillableTimesheet ON BillableTimesheet.FullClient
> = temptable.client
> WHERE (BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate, 102)
> AND CONVERT(DATETIME, @fDate, 102))
> GROUP BY BillableTimesheet.FullClient, temptable.client,
> temptable.UserHours, temptable.WO)
>
>
> second I retrieve the results:
>
> ALTER FUNCTION dbo.QueryResults
> ()
> RETURNS TABLE
> AS
>
> RETURN (SELECT temptable.client, temptable.UserHours, temptable.WO,
> temptable2.UserHours as AllHours
> FROM temptable INNER JOIN temptable2 ON temptable2.client =
> temptable.client)
>
>
> Third I delete the temp tables:
>
> ALTER PROCEDURE dbo.QueryEnd
> AS
>
>     DROP Table temptable
>     DROP Table temptable2
>
>
>
>

"Chandra" <Chan***@discussions.microsoft.com> wrote in message
news:50DBF9C1-C539-42B7-9BAF-0356EB786BEC@microsoft.com...
> Hi
> I am not sure what your requirement is, but you can do it this way:
>
>
>
> ALTER PROCEDURE dbo.QueryData
>
>     @user int,
>     @sDate DATETIME,
>     @fDate DATETIME
>
> AS
>
> CREATE TABLE #temptable (client nvarchar(50), WO nvarchar(255), UserHours
> float(8))
> CREATE TABLE #temptable2 (client nvarchar(50), UserHours float(8))
>
> INSERT INTO #temptable (client, WO, UserHours)
> (SELECT dbo.BillableTimesheet.FullClient as client,
> dbo.BillableTimesheet.WO as WO,
> SUM(dbo.BillableTimesheet.BillableHours) AS UserHours
> FROM dbo.BillableTimesheet
> WHERE (dbo.BillableTimesheet.Userid = @user) AND
> (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate, 102)
> AND CONVERT(DATETIME, @fDate, 102))
> GROUP BY dbo.BillableTimesheet.FullClient, dbo.BillableTimesheet.WO)
>
> INSERT INTO #temptable2 (client, UserHours)
> (SELECT #temptable.client,SUM(BillableTimesheet.BillableHours) as
UserHours
> FROM #temptable INNER JOIN BillableTimesheet ON
BillableTimesheet.FullClient
> = #temptable.client
> WHERE (BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate,
102)
> AND CONVERT(DATETIME, @fDate, 102))
> GROUP BY BillableTimesheet.FullClient, #temptable.client,
> #temptable.UserHours, #temptable.WO)
>
> SELECT #temptable.client, #temptable.UserHours, #temptable.WO,
> #temptable2.UserHours as AllHours
> FROM #temptable INNER JOIN #temptable2 ON #temptable2.client =
> #temptable.client
>
> DROP Table #temptable
> DROP Table #temptable2
>
> GO
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---------------------------------------
>
>
>
> "Nathan Carroll" wrote:
>
> > I run three processes to return data that I want.  This seems like a
long
> > and maybe not very good way to get data from the table in question.  I
was
> > hoping to use #temp and put all the actions into one batch procedure
that
> > returns the queried data or maybe create view with the variable
parameters.
> > New to stored procedures.  Have not found much in the means of guidance
> > documentation for stored procedures etc with sql server.  Any reference
> > knowledge would be appreciated.
> >
> > first I make two tables with data queried:
> >
> > ALTER PROCEDURE dbo.QueryData
> >
> >     @user int,
> >     @sDate DATETIME,
> >     @fDate DATETIME
> >
> > AS
> >
> > CREATE TABLE temptable (client nvarchar(50), WO nvarchar(255), UserHours
> > float(8))
> > CREATE TABLE temptable2 (client nvarchar(50), UserHours float(8))
> >
> > INSERT INTO temptable (client, WO, UserHours)
> > (SELECT dbo.BillableTimesheet.FullClient as client,
> > dbo.BillableTimesheet.WO as WO,
> > SUM(dbo.BillableTimesheet.BillableHours) AS UserHours
> > FROM dbo.BillableTimesheet
> > WHERE (dbo.BillableTimesheet.Userid = @user) AND
> > (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate,
102)
> > AND CONVERT(DATETIME, @fDate, 102))
> > GROUP BY dbo.BillableTimesheet.FullClient, dbo.BillableTimesheet.WO)
> >
> > INSERT INTO temptable2 (client, UserHours)
> > (SELECT temptable.client,SUM(BillableTimesheet.BillableHours) as
UserHours
> > FROM temptable INNER JOIN BillableTimesheet ON
BillableTimesheet.FullClient
> > = temptable.client
> > WHERE (BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate,
102)
> > AND CONVERT(DATETIME, @fDate, 102))
> > GROUP BY BillableTimesheet.FullClient, temptable.client,
> > temptable.UserHours, temptable.WO)
> >
> >
> > second I retrieve the results:
> >
> > ALTER FUNCTION dbo.QueryResults
> > ()
> > RETURNS TABLE
> > AS
> >
> > RETURN (SELECT temptable.client, temptable.UserHours, temptable.WO,
> > temptable2.UserHours as AllHours
> > FROM temptable INNER JOIN temptable2 ON temptable2.client =
> > temptable.client)
> >
> >
> > Third I delete the temp tables:
> >
> > ALTER PROCEDURE dbo.QueryEnd
> > AS
> >
> >     DROP Table temptable
> >     DROP Table temptable2
> >
> >
> >
> >