Calculating The 3rd Value By The First 2's Selection
May 30, 2008
Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
in one invoice has many po# number i already get the po number individually using explode... the ouput like this 001 002 003 and so on it is ok for that
but my problem is
i want to the po# should be selected i mean like a menu ..like the user select the which po# should be updated....
but i dont know how..
is there anyone can help me?
to elaborate more...
i can display the po# already without commas like in invoice# 1111 i can already display the po# number like this 001 002 003
after that i want it to be like a menu that user will only select the which po# should be updated..using arrow keys down and up....i mean just like selecting..
SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name , b.station_nbr, b.beg_eff_date, b.end_eff_date, Round( e.C02_pct,3)As C02_pct, MAX( e.sample_date)As Highest_Sample_Date, d.station_name from [HOU-TIES01].TIES_Gathering.dbo.group_detail a Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_group b on a.group_code = b.group_code Inner Join [HOU-TIES01].TIES_Gathering.dbo.station_type c on b.station_nbr = c.station_nbr Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.station d ON c.station_nbr = d.station_nbr Inner JOIN [HOU-TIES01].TIES_Gathering.dbo.gas_analysis e ON d.station_nbr = e.station_nbr Where (b.station_nbr=e.station_nbr)and (b.group_code = a.group_code) and (a.group_type = 'NPN')and( b.end_eff_date >=DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0)) and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))and (e.sample_date Between DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0) and DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) and e.seq_nbr = (select max(x.seq_nbr) From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x Where x.station_nbr = b.station_nbr and x.sample_date= e.sample_date ) and e.C02_pct = (Select max(x.C02_pct) From [HOU-TIES01].TIES_Gathering.dbo.gas_analysis x Where x.station_nbr = b.station_nbr and x.sample_date= e.sample_date ) Group by a.group_code, a.group_name, a.group_note, b.station_nbr, b.beg_eff_date, b.end_eff_date, e.c02_pct, d.station_name order by a.group_code
ok. Here is how the data looks. Group code station_nbr beg_eff_date end_eff_date C02 sample_date 0004 D01G000819 2007-09-01 3000-12-31 0.40 2007-10-02 0004 D01G000819 2007-09-01 3000-12-31 2.49 2007-10-09
What I am trying to do is to select the most current sample date. In this case "2007/10/09" when station_nbr and group_code are the same for both dates.
Thank You for all your help!!!! From the Jr.in SQL
This is a new query and I am facing a new problem. Here I am requesting all the records for a Contract Number 'GTH00001' for the specific date of 2/2/2007. I want the SUM(c.rcpt_nom_vol)AS Receipt and the b]SUM(c.rcpt_fuel)As Allocated_Fuel[/b] But Instead I am getting 147 records for Contract Number'GTH00001'for the date of 2/2/2007.
Declare @Req_Contract_nbr char (8), @Req_Day int, @Req_month int, @Req_year int
Set @Req_Contract_nbr = 'GTH00001' Set @Req_Day = '2' set @Req_month = '2' Set @Req_year ='2007'
SELECT Distinct a.contract_nbr, c.beg_eff_date, c.rcpt_dlvry_ind, SUM(c.rcpt_nom_vol)AS Receipt, SUM(c.rcpt_fuel)As Allocated_Fuel from dbo.contract a Inner Join dbo.NOm b on a.contract_nbr = b.contract_nbr Inner Join dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH') and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) and (c.rcpt_dlvry_ind ='R') and a.contract_nbr= @Req_Contract_nbr and Day(c.Beg_eff_date) = @Req_Day and (month(c.Beg_eff_date)= @Req_month ) Group by a.contract_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel
We are looking to purchase a new SQL2000 server.... Tada.... Right then, for around about the same money, we could have either 2 x PIII 1Ghz Xeon with 256Kb cache, or 2 x PIII 700Mhz Xeon with 1Mb cache.... Which choice is best for SQL Server 2000???
I hope I can explain this somewhat coherantly. Here goes..
I have a products database. I want to select product details for items having distinct style codes. IE if there are 20 products that different from one another in some way, and 3 different style numbers throughout them, I want to end up with 3 products selected. I don't care which ones, as long as i have a sample product for each style. I've attemped to use GROUP BY to do this, but it's not giving me what I want. I managed to get right number of products, but the query seems to have mixed and matched the fields together in an undesireable way. I guess this kinda makes sense, since providing aggregate functions for each of the extra fields is necessary. SO THE QUESTION IS: How do I select (number of distinct styles) products, and still keep the records in tact as they should be?
Here is a sample...
Code:
SELECT DISTINCT Min(team) as team, min(pict) as pict, min(colors) as colors, min(league) as league, min(product_description) as product_description, min(category) as category, min(subcategory) as subcategory, min(style) as style FROM prods WHERE (style='style1' or style='style2' or style='style3') AND league='leaguex' AND soldout='' GROUP by style ORDER by style
Thanks very much for any help!! I'm sure I'm going about this all wrong.
Select DOJ AS 'JoiningDate' from emp where DOJ like '%2008/09/04%'
there are 8 records on this time.But it displays no result . The column is datetime datatype year month date or we need to mention any thing other than this.
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.
Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:
NetworkId Full Name Sec Class Desc Sec Type Desc SecValue tte Test Scenario Accounting Budget Center 142- ?
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
------------------
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
i need some help i have a database which has single column containing 10,000 records. i enter a number say 1000. so it divides the 10,000 records into 10 different column containing 1000 record each.how do i do it?
While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause: e.g. select * from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1
Is logically the same as: select * from Table1 a inner join Table2 b ON a.key = b.key where a.a = 1
Being new to this forum and to SQL, I would to apologise upfront for my clumsy code and explanations as I try and get to grips with this subject.
I have created this query which does work:
SELECT T1.U_Serial, case when (U_RDate < GETDATE() AND U_ASDate Is NULL) then 'NA' when (U_RDate Is NULL) then 'No Request' when (U_ASDate Is Not NULL And U_AEDate Is NULL) then 'NA' when (U_ASDate Is Not NULL And U_AEDate Is Not NULL) then 'A' when (U_RDate > GETDATE() AND U_ASDate Is NULL) then 'NA' else 'DN' end As Available, case when (U_RDate < GETDATE() AND U_ASDate Is NULL) then 'Late' when (U_RDate Is NULL) then 'No Request' when (U_ASDate Is Not NULL And U_AEDate Is NULL) then 'Busy' when (U_ASDate Is Not NULL And U_AEDate Is Not NULL) then 'Done' when (U_RDate > GETDATE() AND U_ASDate Is NULL) then 'Waiting' else 'DN' end As Progress,
FROM [dbo].[@IDH_JOBENTR] T0 , [dbo].[@IDH_JOBSHD] T1
WHERE T0.Code = T1.U_JobNr AND T1.U_Serial not like ''
ORDER BY T1.U_Serial, T1.U_AEDate DESC, T1.U_JobNr DESC ,T1.Code DESC,Today, T1.U_RDate, Available, Progress, T1.U_Status,T1.U_JobTp,T0.U_CardNM, T0.U_ZpCd, T1.U_ASDate, T1.U_ItemCd, T1.U_ItemDsc
The tables I’m using are IDH_JOBENTR which is the header records for my jobs and IDH_JOBSHD, which holds the row details for the jobs.
I’m trying to extract from the IDH_JOBSHD table the last (row) detail record for the (header) job.
The way the query works now, the first row for each unique serial number is the record I need, but this is all the info I would like to display I don’t need the other lines, and I don't know how to strip out the rest of the data
i am having problems selecting by date. I have made sure that the datetime field only contains dates with the time set to 00:00:00
In this instance both it should return one record dated 24/11/2006 00:00:00 but it returns a record with the date 25/11/2006 00:00:00 can someone expain why thi is.
Thanxs
WHERE(Tbl_01020_Tour_Types.TourTypeId = @TourTypeId) AND (Tbl_01030_Tour_Schedule.Sch_TourStartDate <= CONVERT(DATETIME, CONVERT(INT,GETDATE()))) AND (Tbl_01030_Tour_Schedule.Sch_TourEndDate >= CONVERT(DATETIME, CONVERT(INT, GETDATE())))
Select field1, field2 from table1 left table2 where chdate = AAAAMMGG (a variable I type whenever I run the query).
Now I want to change the command in red as follows: where chdate = AAAAMMGG or (if AAAAMMGG is null) the last date before AAAAMMGG that is not null. Is there a command called 'MAX' or something like that?
we have a data base that have so many tables that tables have a uniqe data but we use another autonumber column as key because this unique data is entered by a human and some mistake could be happened and because of so many relations to this table many changes should be happen during these tables use this primery key(Uniqe data). Because this primary key doesn't exist in the real world, we should search over the table by the unique data(Which used in the real world) to find the primary key. for example: we have a database named "SIS" and a table named "Users". National ID is a column which is unique but it should be entered by human and they may have some mistakes on entering this data so we add ID which is autonumber as a primary key. The problem here is that users for example want to join in a class we must save it then search over the table to find his id and after that use this key to insert him in a class.
SELECT * FROM ServidorPrincipal.BDPrincipal.dbo.tblCADENA;
Because it sends this message:
Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'ID_CADENA' (compile-time ordinal 1) of object '"BDPrincipal"."dbo"."tblCADENA"' was reported to have changed. The exact nature of the change is unknown].
I have over 50 rows in this table and my bizIDNum is always getting set to 4. Please take a look at the code and tell me whats wrong. If I do the select statement in the database the return number is correct. here is the code
// db connection SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["work4tipsConnectionString"].ConnectionString); sqlConnection.Open(); string sqlSelect = "SELECT MAX(bizID) FROM business"; SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection); //sqlCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = username; string bizIdNum = (string)sqlCommand.ExecuteScalar(); sqlConnection.Close(); sqlConnection.Dispose();
hello members i want to no that weathere there is any procedure to select come random records from the database for eg i want to select 10 students out of 100 randomly is there any query for this in SQL server 200
Hi... I have a problem getting a select statement to return a boolean value from sql server... the aim is to populate a checkbox list with permissions a group of users has. The statement is an outer join as follows: select SS.PermissionName, BB.GroupId,case when BB.GroupId Is Null then cast(0 as bit) else cast(1 as bit)end IsAddedfrom clPermissions SS left join ( select PermissionName, GroupId from clvGroupPermissions where GroupId = (Select groupId from clGroups where GroupName = 'Admin')) BB on SS.PermissionName = BB.PermissionName The returned values are 0 and 1... but .NET does not seem to recognize these values as true and false...Any one had an issue like this before?
hi im creating 2 random numbers, matching one of them (i) to the QuestionsNo column in my database, and then making it display the whole record. the code below generate 2 random numbers but i cannot get the rest of it to work. i need it to match the random number to the QuestionNo field in my database and display the whole record. can anyone help me and tell me where im going wrong please????
Dim i,j as integer Randomize i = CInt(Int((10 * Rnd()) + 1)) j = CInt(Int((10 * Rnd()) + 1)) example.InnerHtml = "Random Number : " & i example1.InnerHtml = "Random Number : " & j
strSQL = "SELECT * FROM Table1 WHERE QuestionNo = " & i &""
Hello, I wonder if someone has come across a similar problem I am having. I am migrating a database from Access to MS SQL 2000. The tables imported fine, but the queries had to be rewritten. I placed them under "Views". At present I am able to run a query directly on the SQL server, and it populates all fields nicely. When I do "SELECT * FROM EQCustomerReport" where EQCustomerReport is a name of a View on SQL, it only returns part of the fields. The other fields are blanked out for some reason. Please tell me if I am making a common mistake or something. Should I be using Stored Procedures instead of Views. Or would I be better of passing a full text of a query from my .NET application? Thanks very much in advance. Martyn
I need to use the list of items from a multiselect listbox in a parameter of a query's IN clause.
Example:
I assemble the list from the listbox and set the paramter value so @CityList = 'London','Paris','Rome' .... etc.
A sample SQL would be:
SELECT * FROM Customers WHERE City IN (@CityList)
However, the SQL will not work. It seems the whole string is put in another set of single quotes by the compiler so it's treated as one string literal instead of a list.
Is there any way by which I can determine whether the tempdb size for the SQL Server is enough or not? (I ean are there any symptoms like excessive paging etc. that can identify this bottleneck?).
I need to pull a report that gives me the desired results as show below. I first show the data that is in the table, then below that is the desired result.
What I am trying to do is pull every employee's most recent 3 appraisal Effective Dates within the last 4 years where Rating is different than X.
Sorry I don't reall know how to explain this using code because I seem to be having a block right now. Not sure where to start really.
Any help would be greatly appreciated!! Thanks again!
Records in tblAPPRAISALS Table:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 A | 1/10/2003 | 5 A | 1/10/2002 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2004 | X B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5
My current SQL:
SELECT DISTINCT TOP 3 Employee, Appr_Eff as [Appr Effective], Rating_Of_Rcd as [Rating] FROM tblAPPRAISALS WHERE Appr_Eff >= DateAdd("YYYY", -4, Date()) AND Rating_Of_Rcd <> "X" Order by 1, 2 DESC
Results from this Query:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5
Desired Results:
Employee | Appraisal Effective | Rating A | 1/10/2006 | 5 A | 1/10/2005 | 4 A | 1/10/2004 | 5 B | 1/10/2006 | 5 B | 1/10/2005 | 5 B | 1/10/2003 | 5 C | 1/10/2006 | 4 C | 1/10/2005 | 5 C | 1/10/2004 | 5