When I execute a stored procedure that returns a single value using a TableAdapter, I always get error message "Object reference not set to an instance of an object"
I followed all the steps in microsoft help center:
http://msdn2.microsoft.com/en-us/library/37hwc7kt.aspx
but the problem still unsolved.
here are my simple stored procedure:
create PROCEDURE dbo.StoredProcedure1
AS
RETURN 4
and here are the simple code in my asp.net webpage that should display the number "4": protected void Button1_Click(object sender, EventArgs e)
{
DataSet1TableAdapters.QueriesTableAdapter TA
= new DataSet1TableAdapters.QueriesTableAdapter();
int i = (int) TA.StoredProcedure1();
Response.Write(i.ToString());
}
so, please could any one help me solve this problem?
Hi I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query: ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true). ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore. Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
I have a written a dll in 2.0 that calls a webservice. This webservice is used to authenticate users in Active Directory. I created a assembly to that calls this dll because of the diverse languages versions that will use it (from asp,vb6 on up) and all can get values from a stored procedure that calls that assembly. I works great. Until now, I have to add another function to my dll that calls the webservice and returns the Users Full name from Active directory for electronic signitures. Okay I added to the dll then tried to reconstruct my Assembly and stored procedures and recieved the following error. "CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void" I want to keep all these Active directory call all in one place so I can be consistant in all the different applications. I was reading about UDF but that could get messy as I have a config file for the dll that allows the user to dynamically change the url for the webservice. Any suggestions/help will be greatly appreciated
It's a very strange thing! I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.
I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do: select * from invoices where invoiceID = 302247 (and i'll get one result) select * from customer_payments where invoice = 302247 (and i'll get none results)
however, if i do the following: select * from invoices where invoice_id not in (select invoice_id from customer_payments) I get nothing!!!???
It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?
i have a quite strange condition...when i add some value in database with getdate() function it only returns date and minute not the seconds...does somebody have an experience about this
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
FROM (Orders LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID]) INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID, [Orders Subtable].Quantity, Receiving.Quantity, Orders.[Project #], [Orders Subtable].On_Order, [Orders Subtable].[Component #], Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND (([Orders Subtable].On_Order)=True) AND (([Orders Subtable].[Component #])="R02101A") AND ((Receiving.[Component #])="R02101A"));
I was trying to create a simple SP that return a single value as follows:CREATE PROCEDURE IsListingSaved@MemberID INT,@ListingID INTASIF EXISTS (SELECT [Member_ID] FROM [Member_Listing_Link] WHERE [Member_ID] = @MemberID AND [Listing_ID] = @ListingID) Return 1ELSE Return 0GOWhen I try it out in the Tableadapter's preview table, I get the correct result (1, where the entries exist). However, in the BLL, I tried to get the value as:Dim intResult as IntegerintResult = CType(Adapter.IsListingSaved(intMemberID, intListingID), Integer). However, this always returns 0 (when it should be returning 1). P.S. Curiously, breakpoints skipped the VS generated code for the adapter. What could be the problem? Thanks,Wild Thing
I am trying to catch the @retval which is returned finally after executing sp_update_schedule stored procedure (o or 1) but i cannot catch the final resultIf i put Print statement just before the return (@retval), then i am seeing 0 as output but i want to catch that value when i execute the SP with the parameters. How can i do that?? same thing with all other system stored procedures.thanks alot in advance....if you want more info on this Q, plz let me know
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList] as select m.id as Max4SaleId, mt.[Description] as [TypeDescription], CAST(m.[type] as tinyint) as [Type], m.start_time as [StartTime], m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1 UNION SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1 abc-1-200.00 abc-1-500.00
As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I setup a Sql Server Agent job to run daily every 15 minutes from 11:15PM to 10:45PM. I set this up about noon, and the job and schedule were both Enabled.
But the job would not run. I sat and watched it for some time but it just sat there. What do I have to do to get the job to run?
Sorry! I forgot that Recurring schedules do not work until the start-time is reached. If you change the start-time the schedule becomes inactive. So I created a second schedule to cover the hole!
I'm having a tough time understanding my current problem and why I'm having it.
I'm using an ODBC db connection to connect to my sql server and trying to insert data into a table I've created, but I keep getting an error saying that the table doesn't exist. This is the first time I've ever used ODBC and DSN to connect to a db so I don't konw if there are qerks associated with this, but I've tried everything I can think of. I've changed the name of the table, placed it in brackets, placed "dbo" infront of the name, and still I get the same error about the table not existing. My "Intial Catalog" in my connection string is the correct db, I just can't seem to get it going.
I'm pulling data from Oracle 8.05 using Oracle ODBC with DTS to SQL Server 7 sr2. If I choose my columns only, I have no problem. When I try to edit the SQL statement either in the Import Wizard or the DTS Designer, the package won't run. I am selecting on a date column. I corrected the TSQL using the "convert" statement. Does someone know what might be happening?
Hi there, Since I have upgraded to SQL 7 I'm exeriencing a problem with SQL Server Agent. I have a job scheduled to run every hour between 8:30AM and 5:30 PM during weekdays. Every Monday for the last several weeks I check the job status and it says it is enabled, runnable, and scheduled. Last job was run at 5:30PM on Friday, next run time is Monday 8:30AM. The only thing is that it is after 9:00AM. What gives? Then I have to run the job manually for the rest of the day. It usually starts working the next day. Anybody has similar problem? Regards,
I'm pretty new to DTS, so forgive me if this is basic. I created a simple DTS package to run a query and export it to a text file. I can execute the package fine from my workstation through EM, but when I try to execute the job to run the package I get this error: Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied.
I think that maybe SQL Agent doesn't have the right permissions to write to that network drive. What should the permissions be?
This is probably very simple, but I can't get passed this problem.
I have a report in MS Access that uses info generated by a query. One of the text fields in the query contains either the word 'Select' or the name of a course. The report should display a space if the value is 'Select', or the actual value of the field in any other case. The field can never contain a null value.
I've used: =IIf([optVoc1]="Select","",[optVoc1]) in the text box on the report, but this only returns #error regardless of the actual content of the field.
I create and schedule a SQL job to run every minute to update a table base on certain condition but it doesn't work. Job history says successful every time but the table doesn't get updated.
However if I move it to Query Analyzer and run it under dba, it will work. Thinking that it may have to do with the user the job run as, I then change run as user from self to dba. But still SQL job won't update my table.
Anything about user permission or security that I can check? Or it there any other possibility?
When I run the select its fine but I cannot delete..... i have done this many times and it has worked.... I cannot see the error what am i missing
select eqnow.empnumber, eqnow_names.empnumber, eqnow_names.names --delete from eqnow inner join eqnow_names on eqnow.empnumber = eqnow_names.empnumber where eqnow_names.names is null
i get this error Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'inner'.
Hi, all. I tried to get result of sp. Dim rst As Recordset Set rst = New ADODB.Recordset rst.Open sp, cnn
doesn't get result. when i call rst.EOF, it thows error: Can do this since rst is closed...
I found it's problem of sp which is little complex. But, still I think it should work! My question is how can I get the returned value from following sp in VB?
--- return list of tables that needed to update --- list is one string separated by '&' delimeter --- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&... CREATE procedure spGetListOfTableToDownLoad @listOfUpdateTime varchar(500) as SET NOCOUNT ON Declare @listOfTable varchar(300), @item varchar(300) Declare @tbl varchar(50), @uptime datetime Declare @list varchar(500) Declare @sep varchar(1) SET @list = '' SET @sep = '&' DECLARE cur CURSOR FAST_FORWARD FOR SELECT * FROM fnSplit(@listOfUpdateTime, @sep)
OPEN cur
FETCH NEXT FROM cur INTO @item
Declare @re bit, @tp varchar(50) WHILE @@FETCH_STATUS = 0 BEGIN
-- get tablename, update time Declare cur2 CURSOR FAST_FORWARD FOR SELECT * FROM fnSplit(@item, '=') OPEN cur2 FETCH NEXT FROM cur2 INTO @tbl Print @tbl FETCH NEXT FROM cur2 INTO @tp print 'tp:' + @tp SET @uptime = CAST(@tp as datetime) print @uptime -- @re =1: true, 0: false EXEC spIsUpdate @tbl, @uptime, @re output IF @re = 1 SET @list = @list + @tbl + @sep CLOSE cur2 DEALLOCATE cur2
FETCH NEXT FROM cur INTO @item END if LEN(@list) > 0 SET @list = LEFT(@list, Len(@list)-Len(@sep)) CLOSE cur DEALLOCATE cur
SELECT @list as Result
SET NOCOUNT OFF
GO __________________ --- PARAM:: @tbl: table name, --- @uptime : update time (passed from local db) that will be compared on HQ table --- return 1 if Max(UpdateTime) of @table > @uptime --- otherwise return 0 CREATE Procedure spIsUpdate @tbl varchar(50), @uptime datetime, @result bit output as BEGIN Declare @bit bit
DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) declare @uptimeHQ datetime /* Build the SQL string once.*/ SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl SET @ParmDefinition = N'@tp datetime OUTPUT' EXECUTE sp_executesql @SQLString, @ParmDefinition ,@uptimeHQ OUTPUT
If @uptimeHQ > @uptime SET @result = 1 ELSE SET @result = 0 --RETURN @bit END
This problem is on SQL Server development box. SS 2K standard edition SP3 3071 MB 2 processors Windows NT 5.0
SQL job doesn't start nor write on the view history. I have comfirm this by creating a new job with query and run it manually or schedule automatic run.
select * from sysdatabases
It points to database master and using the same account sql server agent uses it to start. This account has sys admi rights. I have check for blocking but thre is not. Anyone has ever has a similar issue like this
Hi can any one see why this will now run when sent to the server. Don't worry about the parameters as these are replaced at runtime in a c# app I have created.
It is not the easiest to read but your help is appreciated.
Code Snippet /* Create new table of dates using the Week Ending date selected by the user This is a list of week ending dates between the start and end dates selected by the user */ DECLARE @Temp AS DATETIME DECLARE @result table(WeekEndDate datetime) SET @Temp = @Start + ((@WeekEnding - (DATEPART(w, @Start) - 1)) % 7) + CASE WHEN (@WeekEnding - (DATEPART(w, @Start) - 1)) < 0 THEN 7 ELSE 0 END
WHILE @Temp <= @End BEGIN INSERT INTO @result values(@Temp) SET @Temp = @Temp + 7 END
/* Aggregate data by Week ending date so that we can see the Metrics for that particular week*/ SELECT dbo.MSP_WEB_PROJECTS.PROJ_NAME AS Project, EV_View.[Week Ending], EV_View.BAC, EV_View.EV, EV_View.PV, EV_View.AC, EV_View.FV, EV_View.FC, EV_View.[Baseline Work], EV_View.[Work], EV_View.[Actual Work], EV_View.[ETC], CASE WHEN EV_View.[Week Ending] BETWEEN dbo.MSP_VIEW_PROJ_PROJECTS_STD.ProjectStartDate AND dbo.MSP_VIEW_PROJ_PROJECTS_STD.ProjectFinishDate THEN 'YES' ELSE 'NO' END AS [1 Project Duration] FROM ( SELECT ProjectID, WeekEndDate AS [Week Ending], SUM(BAC) / @TimeUnits AS BAC, SUM(EV) / @TimeUnits AS EV, SUM(PV) / @TimeUnits AS PV, SUM(AC) / @TimeUnits AS AC, SUM(FV) / @TimeUnits AS FV, SUM(FC) / @TimeUnits AS FC, SUM([Baseline Work]) / @TimeUnits AS [Baseline Work], SUM([Work]) / @TimeUnits AS [Work], SUM([Actual Work]) / @TimeUnits AS [Actual Work], (SUM([Work]) - SUM([Actual Work])) / @TimeUnits AS ETC FROM ( SELECT ProjectID, WeekEnds.WeekEndDate, BAC, CASE WHEN derivedtbl_1.[Baseline Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.PV END AS PV, CASE WHEN derivedtbl_1.[Actual Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.EV END AS EV, CASE WHEN derivedtbl_1.[Actual Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.AC END AS AC, CASE WHEN (derivedtbl_1.Finish BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN derivedtbl_1.[Baseline Work] END AS FV, CASE WHEN (derivedtbl_1.Finish BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN derivedtbl_1.[Estimate at Complete] END AS FC, CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Baseline Work] END AS [Baseline Work], CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Work] END AS [Work], CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Actual Work] END AS [Actual Work] FROM ( SELECT dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart, SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeBaselineWork, 0)) AS [Baseline Work], SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeWork, 0)) AS [Work], SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWork, 0)) AS [Actual Work], SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWorkProtected, 0)) AS [Actual Work Protected] FROM dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY INNER JOIN dbo.MSP_VIEW_PROJ_ASSN_STD ON dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID = dbo.MSP_VIEW_PROJ_ASSN_STD.WPROJ_ID AND dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentUniqueID = dbo.MSP_VIEW_PROJ_ASSN_STD.AssignmentUniqueID WHERE (dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart <= @End) AND (dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart >= @Start) ) AS TP INNER JOIN ( SELECT dbo.MSP_WEB_PROJECTS.WPROJ_ID AS ProjectID, dbo.MSP_WEB_PROJECTS.PROJ_NAME AS Project, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskName AS [Task Name], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineStart)) AS [Baseline Start], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineFinish)) AS [Baseline Finish], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualFinish)) AS [Actual Finish], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS [Baseline Work], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskWork AS [Estimate at Complete], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualWorkProtected AS [Actual Work], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskRemainingWork AS [Estimate to Complete], dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS BAC, CASE WHEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete >= 100 THEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork END AS EV, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS PV, CASE WHEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete >= 100 THEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualWorkProtected ELSE 0 END AS AC, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete AS [% Complete], DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskFinish)) AS Finish FROM dbo.MSP_VIEW_PROJ_TASKS_STD INNER JOIN dbo.MSP_VIEW_PROJ_TASKS_ENT ON dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_VIEW_PROJ_TASKS_ENT.WPROJ_ID AND dbo.MSP_VIEW_PROJ_TASKS_STD.TaskUniqueID = dbo.MSP_VIEW_PROJ_TASKS_ENT.ENT_TaskUniqueID INNER JOIN dbo.MSP_WEB_PROJECTS ON dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN dbo.MSP_VIEW_PROJ_PROJECTS_ENT ON dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_VIEW_PROJ_PROJECTS_ENT.WPROJ_ID WHERE (dbo.MSP_VIEW_PROJ_TASKS_STD.TaskSummary = 0) ) AS derivedtbl_1 ON TP.WPROJ_ID = derivedtbl_1.ProjectID CROSS JOIN @result AS WeekEnds WHERE (DATEADD(day, 0, DATEDIFF(day, 0, [Baseline Finish])) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, [Baseline Finish])) <= @End) OR (DATEADD(day, 0, DATEDIFF(day, 0, [Actual Finish])) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, [Actual Finish])) <= @End) OR (DATEADD(day, 0, DATEDIFF(day, 0, Finish)) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, Finish)) <= @End) ) AS WithWeekEnds WHERE (BAC IS NOT NULL) OR (EV IS NOT NULL) OR (PV IS NOT NULL) OR (AC IS NOT NULL) OR (FV IS NOT NULL) OR (FC IS NOT NULL) GROUP BY WeekEndDate, ProjectID ) AS EV_View INNER JOIN dbo.MSP_WEB_PROJECTS ON EV_View.ProjectID = dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN dbo.MSP_VIEW_PROJ_PROJECTS_STD ON EV_View.ProjectID = dbo.MSP_VIEW_PROJ_PROJECTS_STD.WPROJ_ID INNER JOIN dbo.MSP_VIEW_PROJ_PROJECTS_ENT ON EV_View.ProjectID = dbo.MSP_VIEW_PROJ_PROJECTS_ENT.WPROJ_ID ORDER BY dbo.MSP_WEB_PROJECTS.PROJ_NAME, EV_View.[Week Ending]
