SQL Server :: Query Solution One Column Convert Into Two?
Mar 2, 2011
i have a salary table that have data like this ID EmpNo mYear Net Salary 29 3 January 2010 29217 1210 3 February 2010 29217 2052 3 March 2010 29217 3102 3 April 2010 29447 4153 3 May 2010 29447 6376 3 July 2010 28803 7231 3 August 2010 28281 7893 3 September 2010 29303 5207 3 June 2010 29197 8874 3 October 2010 29303 10076 3 November 2010 31276 11318 3 December 2010 31216 12503 3 January 2011 31266 13880 3 February 2011 31266 13414 1613 January 2011 19445 12043 1613 December 2010 19445 10563 1613 November 2010 19445 9364 1613 October 2010 18150 6163 1613 June 2010 10075 8433 1613 September 2010 18150 5154 1613 May 2010 18806 But i want to create a view like this ID EmpNo ThisMonth Previous Month mYear ThisNetSalary PreviousNetSalary Net Salary 29 3 January 2010 December 2009 January 2010 29217 0 29217 1210 3 February 2010 January 2010 February 2010 29217 29217 29217 2052 3 March 2010 February 2010 March 2010 29217 29217 29217 3102 3 April 2010 March 2010 April 2010 29447 29217 29447 4153 3 May 2010 April 2010 May 2010 29447 29447 29447 5207 3 June 2010 May 2010 June 2010 29197 29447 29197 6376 3 July 2010 June 2010 July 2010 28803 29197 28803 7231 3 August 2010 July 2010 August 2010 28281 28803 28281 7893 3 September 2010 August 2010 September 2010 29303 28281 29303 8874 3 October 2010 September 2010 October 2010 29303 29303 29303 10076 3 November 2010 October 2010 November 2010 31276 29303 31276 11318 3 December 2010 November 2010 December 2010 31216 31276 31216 12503 3 January 2011 December 2010 January 2011 31266 31216 31266 13880 3 February 2011 January 2011 February 2011 31266 31266 31266 13881 3 March 2011 February 2011 March 2011 0 31266 0 5154 1613 May 2010 April 2010 May 2010 18806 0 18806 6163 1613 June 2010 May 2010 June 2010 10075 18806 10075 8433 1613 September 2010 August 2010 September 2010 18150 10075 18150 9364 1613 October 2010 September 2010 October 2010 18150 18150 18150 10563 1613 November 2010 October 2010 November 2010 19445 18150 19445 12043 1613 December 2010 November 2010 December 2010 19445 19445 19445 13414 1613 January 2011 December 2010 January 2011 19445 19445 19445 13415 1613 February 2011 January 2011 February 2011 0 19445 0.
View 4 Replies
Similar Messages:
Sep 3, 2010
I am a biggner in SQL DB . but i started a complicated and painfull work in SQL SERVER 2008. the problem convert Oracle hierarchical query to SQL query. the query
SELECT DISTINCT
LEVEL LVL,
SCH.NSCHEDULE_SL,
SCH.NSCHEDULE_SL_FM,
SCH.CSHED_CNAME
FROM FA_SCHEDULES SCH
WHERE LEVEL = 1
AND NSCHEDULE_SL_FM IS NULL
AND NBRANCH_SL = 2
CONNECT BY PRIOR SCH.NSCHEDULE_SL_FM = SCH.NSCHEDULE_SL
AND NBRANCH_SL = 2
View 1 Replies
Mar 30, 2011
convert the query in pic #3.
pic #1 is where the user will put the 3 fields.
pic #2 is the involved tables and data that we already tried, it works, but only through sql, how can we make the formula where datagrid will show the results.
1. http://i1188.photobucket.com/albums/z403/hell91131/8udh1nd12e2.png
2. http://i1188.photobucket.com/albums/z403/hell91131/wqrfefcwerfewf.png
3. http://i1188.photobucket.com/albums/z403/hell91131/qewrfewfewrgferge.png
View 2 Replies
Nov 30, 2010
Heres the situation: I need update a column from my table (T1) from two other columns in a different table (T2).
My constraints are:
That I have to match the first 4 letters of a column in T2 to one column in T1 I have to identify that the first letter in a column in T1 corresponds to one letter in the middle of a string in a column in T2 For instance:
My Table (T1):
Order Type Combined
Place
0090 0001 YYXX 1YY
0091 1001 YYXX YYY
0092 1002 XXXX 2XX
Table 2 (T2):
Order Value
00900001YY XX
00911001YY XX
00921002XX XX
The Combined column in T1 is what i'm trying to complete. The T1.Place column contains the first character that I want to check for.
If it's a "1" then I want to make sure the 5th letter in T2.Order is a "0" If its a "Y" then I want to look for a "1001" If its a "2" then I want to make sure the 8th letter is a "2" in T2.Order
If all that matches then I was the last two letters in T2.Order + T2.Value to be combined and put into the appropiate spot in T1.Combined
Here's what I have:
[Code]....
I know it's a little complex, but i'm really stuck on it and any help would be greatly appreciated.
View 12 Replies
Oct 20, 2010
In my database when anyone registers - By default the registration time is saved as UTC universal time zone.
But i want to display everything on the website in (GMT-05:00) Eastern Time (US & Canada)
I am using this SQL QUERY to display registration date:
SELECT TOP (5) Username, CustomerID, RegistrationDate = CONVERT(VARCHAR(12),RegistrationDate,107) FROM Customer ORDER BY RegistrationDate DESC
And, i am displaying it like this:
<asp:Label ID="nameLabeldate" runat="server" Text='<%# Eval("RegistrationDate") %>' />
The problem is the output is getting displayed as : 10/20/2010 1:36:01 PM
I know RegistrationDate is datetime stamp that's why i am getting output in this format.
But i want to display RegistrationDate as: Oct 20, 2010 at 1:36pm -> according to (GMT-05:00) Eastern Time (US & Canada) ORDER BY RegistrationDate in Desc (top most will be last registered user)
View 11 Replies
Oct 19, 2010
note MONTH AND DATES stored in single or double digits as they occur. Unfortunately DATE TIME is stored in NVARCHAR Data type column as 2/9/2010, 22/10/2010 etc. Following UK Date here. It is required to pull the data to COUNT No. of Expired. Obviously below one fails.
[Code]....
View 7 Replies
Sep 25, 2010
i want to add add coulmn name in sql query at run time and if any field is blank or null than it should not goes with sql query for search ....
column name:
firstName.Text
lastName.Text
address.Text
SqlCommand cmd = new SqlCommand("select * from hotelreg where '" + firstName.Text + "' and '" + lastName.Text + "', and '" + address.Text + "' like '%"+textbox1.Text+"%'", con);
View 1 Replies
Dec 21, 2010
This is my Table structure:-Table1
Field1 Field2 Age
Dean Chen 20
Janel Wast 25
ann Miller 20
I used this select statement
select Field1+' - '+Filed2 from Table1
and i binded this in dropdownlist
now on selecting dropdownlist i need to show age in label. so how would be my select statement... to show age base on Filed1 and Filed2
View 6 Replies
Jan 12, 2011
I am using below query to count a particular column data based on single condition.
[code].....
But I have to calculate for 3 more conditions. Is it possible to count based on 3 conditions(i.e. 2001 - 3000,3001 - 4000, 4001 - 5000,>5000) and I want result set for all the conditions?
View 8 Replies
Sep 13, 2010
I have three folders , which original is build by vistual 2003 , recent ly I install vistuall 2005 on Win 7 and succesful covert one folder from vistual 2003 t0 2005 ( I forget how , I am new on win 7) , when I deat with my second folder I always get Test2( unviable) under the Solution Exporer ( vistual 2005) But I can access this folder by brows( when I try http://localhost/test2/) I get all the files under this folder , why I can see them on my vistual studio 2005???
View 6 Replies
Aug 28, 2010
i have two solutions, 1st one is webapplication, 2nd one is website.
the 1st soltions (webapplication) includes aspx pages, user controls .... etc.
the 2nd solution (website) also includes pages, user controls ... etc.
now i need to combine between those, so i copy the webapplication inside a folder in the website, and i put the DLL's for the webapplication inside the main Bin folder, and the theme's like so.
but after that, i make some changes in the source code of webapplication, i build the solution, but the new source code updates doesnot appear in the run time !!!! its still running the code before i make combination !!
does that because i make the 1st solution as a webapplication ?
how can i convert the 1st solution to a webapplicatoin? or in other words how can i make combination between webapplication and website to run as one solution?
View 2 Replies
Mar 10, 2010
select Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 Replies
Apr 1, 2010
I am trying to rewrite an Access Database in to a asp.net front end and i have hit an issue that i can not find a good way round.
in the access version of the software there is a screen the displays all cusotmers and invoice information about these customers, there are currently 15 columns on the screen and we currently have around 25K customers and this will only go up. This is done using a multi column listbox i need to replicate this screen in asp.net but i spent all day on this on as yet not found a good way of do this.
the user will need the ability to select multiple customers at a time and because of the amount of data a scroll bar is a must, this is why the listbox is an ideal tool, apart from the fact the standard one in asp.net does not allow multi columns. grid lines would also be useful. I have look at a few third party multi column multi column list boxes but the only one that i have found that seems to work OBOUT's takes far to long to render 25K rows.
View 5 Replies
Mar 20, 2010
I'm trying to create a .NET solution and add class libraries to it. First - in VS2008 I go to File > New > Project > Other Project Types > Visual Studio Solutions > Blank Solution. Then - after VS2008 creates the blank solution I right click on the solution and select Add > New Project > Visual C# > Class Library. However, when I add the Class Library to the solution, the solution icon disappears from Solution Explorer (although the solution name is still in the VS2008 title bar). Why does the solution icon disappear
from the Solution Explorer when I attempt to do this?
View 2 Replies
Sep 10, 2010
I am stumped by a serious issue in Vs 2008.
I created a Blank solution called HelloWorld.Then I added a new Class Library project by the name of HelloWorldService.After creation of the
Class Library project the Solution name disappeared from Solution Explorer.
As a result of which I can now only add new items into the Class Library project but cannot add new item to the solution.
This appears to be a pretty old problem as I found it in another link on the internet.
[URL]
It seems to be a bug.Has MS released a patch or a fix for this.
View 6 Replies
Oct 2, 2010
how convert this sql query in linq,
SELECT COUNT(AdvisorID) AS AdvisorRegisterLast2Days FROM ob_Advisor WHERE CONVERT(varchar,CreationDate,101) BETWEEN CONVERT(varchar,DATEADD(day,-4,DATEADD(day,2,GETDATE())),101) AND CONVERT(varchar,GETDATE(),101)
View 1 Replies
Feb 23, 2011
how to convert a linq query to a datatable
this is my query
[Code]....
View 4 Replies
Feb 1, 2011
I have a datatable which contains rows for empid this is how the data looks empid year status category
1 2010 y 1
1 2010 y 2
2 2011 n 1
3 2010 y 2
i would like to make the data in the following format
1 2010 y 1 2
2 2011 n 1 0
3 2010 y 0 2
View 6 Replies
Feb 13, 2010
How can I convert this sql query to LINQ ?
[code]...
View 1 Replies
Feb 14, 2011
List<string> IDs = new List<string>();
XDocument doc = XDocument.Parse(xmlFile);
var query = from c in doc.Root.Elements("a").Elements("b")
select new { ID = c.Element("val").Value};
How can I convert query to List without loop foreach ?
View 2 Replies
Dec 29, 2010
I have a quite advanced application, where I need to add some client/server functionality. Some examples of functionalities is:
Chat
One vs. one fight in a browser game
Draw people on a map
I am creating this application in ASP.NET and AJAX. I do not use Silverlight/Java Applet/Flash or anything else. If possible, I do not want to change this for other reasons.
I thought about creating some kind of game server, and then communicating either web services or TCP protocol: But I have NO idea if this is a good idea. Also, I don't know how to do this.
So my question is:
What's the best way to implement this? And is it even possible? And if it is, could you give a reference or two?
View 2 Replies
Aug 12, 2010
I have sql database db1..I am using this db1 for my web application...I had taken a back up for this file db1.bak......I want to restore this .bak file to a machine where no sql server is installed..Visual studio is working with solution explorer,where we can create tables,sp's etc...can I able to restore the .bak file to solution explorer?
View 1 Replies
May 17, 2010
I am using DATAPART(..) function in SQL to query table. Now I have Day of the year which I want to convert to actual date and then return Here is the query
[Code]....
For the moment I am returning day of the year, but I want to return date (current year can be assumed). Therefore I have two pieces of information1) day of the year2) year it selfExample day of the year = 125 and year = 2010 How to convert this to date in SQL
View 4 Replies
Feb 18, 2010
I want to move some class libraries (server controls in a .NET 4.0 solution) from one folder in windows explorer to another.
How can I do this and not break anything in the actual .NET solution?
View 1 Replies
May 17, 2010
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
View 10 Replies
