Hi

I need help in writing a SQL query that gives the difference in values for two consecutive dates.

My table structure is as follows :

Collapse | Copy Code
Symbol Name Dates Outstanding values
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0.7995
VAN Equity 12/5/2011 0.7000
VAN Equity 12/8/2011 0.7000

I want the output in the following form :

Collapse | Copy Code
Symbol Name Dates Difference
VAN Equity 12/3/2011 0.7995
VAN Equity 12/4/2011 0
VAN Equity 12/5/2011 -0.0995
VAN Equity 12/8/2011 0
I came up with the below query.

Collapse | Copy Code
WITH LHP AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Dates ) AS rn
FROM test_table as LI
)
SELECT mc.[Outstanding Values] - mp.[Outstanding Values],
mc.[Outstanding Values] , mp.[Outstanding Values]
FROM LHP mc
inner JOIN LHP mp
ON mc.rn = mp.rn - 1
The above query works fine if I have just one set of Symbol Names.
However, if I have my data in the below format :

Collapse | Copy Code
Symbol Name Dates Outstanding Values
VAN Equity 2011-12-03 00:00:00.000 0.7995
VAN Equity 2011-12-04 00:00:00.000 0.7995
VAN Equity 2011-12-05 00:00:00.000 0.7
VAN Equity 2011-12-08 00:00:00.000 0.7
VAN Equity 2011-12-09 00:00:00.000 0.6
VIN Equity 2011-12-03 00:00:00.000 0.1
VIN Equity 2011-12-04 00:00:00.000 0.2
VIN Equity 2011-12-05 00:00:00.000 0.7
VIN Equity 2011-12-08 00:00:00.000 0.7
VIN Equity 2011-12-09 00:00:00.000 0.6
VAT Equity 2011-12-03 00:00:00.000 0.1
VAT Equity 2011-12-04 00:00:00.000 0.2
VAT Equity 2011-12-05 00:00:00.000 0.7
VAT Equity 2011-12-08 00:00:00.000 0.7
VAT Equity 2011-12-09 00:00:00.000 0.6
i.e. multiple sets of Symbol Names distributed across the same set of dates my query gives me the results as shown below : which is not as expected.

Collapse | Copy Code
No Outstanding Outstanding
Name Values Values
0.6995 0.7995 0.1
-0.1 0.1 0.2
-0.5995 0.2 0.7995
0.0995 0.7995 0.7
0 0.7 0.7
0.1 0.7 0.6
Any help would be greatly appreciated.

Hi, I am trying to get the contents of a pdf file as text in my C# .net program. I am using PDFBox 3.0 for reading the pdf as text. However, the problem is that the pdf file is not physically located on the same machine and I am accessing it through a URL. The PDFBox documentation shows that there is a static function which is of the form : PDDocument load(URL url) This will load a document from a url. However, C# does not have a URL class but rather it has a URI class. Can anybody suggest a workaround. Or is there any other free tool that would help me achieve reading the pdf file as text from a URL? Any help would be appreciated. Thanks, Aakar.

Hi! In my application the web pages are consisting of various different sections. I am using AJAX update panel in one of the sections of my web page to do a partial postback and I am setting the values of the controls in this section. Now when I re-direct to another page if the user clicks the back button then the state of the controls in the section of the Page that was earlier set using AJAX is lost. Is there some way I could overcome this. Any help would be appreciated.

Hi, In my application I am using a function that is currently written in the app_code directory. This function called GetRole() gets the role of the logged-in user from the database table. The function is called from almost all of the pages and user controls in my application both on Page Load as well as on the PostBack events such as the click of a button etc. Now is it possible that I get the Users Role only once when the user launches the application and store it across all pages in my application. We are not allowed to use sessions. Any ideas would be appreciated. Thanks in advance, Aakar

Hi, I need to access the members (controls,properties,methods) in the Parent Page from the User Control which is contained in it. For the controls I tried looping through the controls in the Parent Page but I am not able to find the textboxes as they are rendered as a literal control. Also how do I access the properties and methods of the Parent Page from the User Control. Please help me out. Thanks in advance, Aakar.

Hi, I am new to ASP .Net. I was going through the Page LifeCycle in ASP .Net 2.0 and what I found was a lot of events like TestDeviceFilter() AddParsedSubObject() OnInitComplete() OnPreRender() OnRenderComplete() etc. that can be overridden. What I want to know is where would one override these events, I mean in what scenarios. Any help would be appreciated. Aakar.

Hi, I am automating MS Word using VB .Net . What I want is that I want some text to be inserted on the word document that I have opened. Once I have done this when my mouse moves above this line of text, the cursor should change to hand cursor and the text should behave like a hyperlink i.e. when that text is clicked, it should take the user to some other location. How can I achieve this. Thanks in advance, Aryan.

Hi, I need to write a windows service that will take a backup of the database tables and their data to a file when the server goes down and at the end of day everyday. Can anybody tell me how do I go about it? For the back-up do I need to use SQL-DMO or is there some other way out? Thanks in advance, Aakar.

Hi, I am writing a query that returns me the various figures all the salesperons: My query goes like this : Select Usr.user_name as SalesPerson, isnull(ReqNo,0) ReqNo,isnull(ResSubToSalesPerNo,0) ResSubToSalesPerNo, isnull(ResSubToClientNo,0) ResSubToClientNo,isnull(InterviewNo,0) InterviewNo, isnull(ConfirmNo,0) ConfirmNo,isnull(RejectionNo,0) RejectionNo from xr_user Usr left Join (select sales_person_id,count(requisition_date) ReqNo from xr_sales_reqs where requisition_date between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by sales_person_id) Reg on (Usr.User_Id = Reg.sales_person_id) left join (select A.sales_person_id,count(date_sub_sales_per) ResSubToSalesPerNo from xr_sales_reqs A left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_sub_sales_per between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl1 on (Usr.User_ID = cl1.sales_person_id) left join (select A.sales_person_id,count(date_sub_client) ResSubToClientNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_sub_client between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl2 on (Usr.User_ID = cl2.sales_person_id) left join (select A.sales_person_id,count(date_interview) InterviewNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_interview between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl3 on (Usr.User_ID = cl3.sales_person_id) left join (select A.sales_person_id,count(date_of_start_entry) ConfirmNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_of_start_entry between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl4 on (Usr.User_ID = cl4.sales_person_id) left join (select A.sales_person_id,count(date_of_rejection) RejectionNo from xr_sales_reqs A Left join xr_candidate_links B on A.sales_req_id=B.sales_req_id where B.date_of_rejection between convert(varchar,'5/1/2006',101) and convert(varchar,'5/29/2006',101) group by A.sales_person_id) cl5 on (Usr.User_ID = cl5.sales_person_id) Where isnull(ReqNo,0) > 0 or isnull(ResSubToSalesPerNo,0) > 0 or isnull(ResSubToClientNo,0) > 0 or isnull(InterviewNo,0) > 0 or isnull(ConfirmNo,0) >

Hi I am writing a windows application using C# for performing a task as scheduled. Now I am calling a method which does the needful inside the form1 constructor which is inside the main method like this: static void Main() { System.Windows.Forms.Application.Run(new Form1()); System.Windows.Forms.Application.Exit(); } now what is happenning is that the control does not reach this statement System.Windows.Forms.Application.Exit(); and I need to manually close the form which is not what I want. I want the application (and the form) to close by itself. How can i do this. Thanks in advance, Aryan.

Hi, I am automating excel from my windows application developed using C# and saving this file having the reports obtained through a query. Then I am mailing the excel file as an attachment through the application using the system.web.mail class provided by .net. Now the mail feature works fine but when the person receives the mail, and clicks on the excel attachment to open it, he gets the following error: "abc.xls" cannot be accessed. the file may-be read only or you may be trying to access a read-only location or the server on which the document is stored may not be responding. Please help me out. Thanks in advance, Aryan.