I have the following data (very simplified version)TransactionId Agent_Code------------- ----------191462 95328C205427 000024C205427 75547CAgent Code 75547C is a corporate agent. The others are not. I have alist of corporate codes so I can query against it, BUT what I want todo is...Return a unique TransactionId and max of the AgentCode, but if theAgent is a corporate agent, I need to return max of the corporate agentcodes. We can have multiple agents against the transaction andsometimes have a mix of corporate and none corporate agents. What weneed to do is see the corporate adviser if there is one. I only want 1record per TransactionId.We derive more data (sales hierarchy) from this, so are not interestedin anything other than the maximum, but need to know if it wascorporate which therefore gives me a different hierarchy later.Ideally I want to do this in a view and not use an SP. I can then usethis in my main view. If I have to resort to an SP, then so be it, butI would appreciate any helpful comments (or even better, the answer)ThanksRyan
I have a login name being displayed when a user logs in, like so: "Welcome: johndoe23"This is displayed through the use of the <asp:LoginName> tag.I want to do a query on the database that only selects data relevant to that userName and wish to assign that logged in username to a variable so we can use an SQL command similar to: "SELECT something WHERE userID=" + loggedInUserName;
Hi, i have a listbox with multiple selection enabled, the end user uses this listbox to select what data they want to view eg. they select "green" to view all the green cars, "red" to select all the red cars etc. i have the listbox as the control that is connected to the datasource (the sql used for it is select * from cars_table where color =@colorthis works fine when one item in the listbox is selected, but when multiples are selected it does not work what format does the =@color have to be when multiples are selected? i've tried "green, red" "green + red" etc. but cannot seem to get it workingdoes anybody have any working examples that i can take a look at? it seems to be a common action, yet i cannot seem to find any documentation on how to get it to workthanks in advance!
i am making a query which select the data again a particuler date.
I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.
I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.
Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?
How to identify different fields with in a group of records?
Example: create table #test (ID int, Text varchar(10)) insert into #test select 1, 'ab' union all select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
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
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. Skills Table
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();