How To Write SQL Query And Not Cursor ?
Please help me to get the required result:
For each IDS in table1
- change the ids to numbers (eg. for '1,2,3' get the numbers (IntValue) 1, 2 & 3)
- in table2, find the maxVal for each number
- disply the table1..ids, number, table2..maxVal & table1..idsDesc,
order by table1..ids, table2..maxVal & IntValue
I have 2 tables, over milin records each. The Simplified versions of the tables looks like that:
create table table1 (ids varchar(100), idsDesc varchar(100))
go
insert table1 select '1,2,3', 'Description 1'
union all select '2,3,4', 'Description 2'
union all select '1,7', 'Description 3'
union all select '16,3,8', 'Description 4'
union all select '2,5,6,1', 'Description 5'
go
create table table2 (ids int, maxVal int)
go
insert table2 select 1, 10
union all select 2, 6
union all select 3, 12
union all select 4, 11
union all select 5, 66
union all select 6, 4
union all select 7, 3
-- union all select 8, 5 -- no value for 8
union all select 9, 6
union all select 16, 12
go
I have also function that returns table variable of numbers delivered from given string:
create function dbo.fn_StrToIntValues ( @str varchar(1000) )
returns @numsTbl table (IntValue int not null)
The command
select * from dbo.fn_StrToIntValues('1,2,33')
Returns --> intValue
1
2
33
Can I use SQL query and not cursor to get the following result ?
Required Output :
ids IntValue maxVal idsDesc
--------- -------- ------- ---------------
'2,5,6,1' 5 66 'Description 5'
'16,3,8' 16 12 'Description 4'
'2,3,4,9' 3 12 'Description 2'
'1,2,3' 3 12 'Description 1'
'1,7' 1 10 'Description 3'
View Complete Forum Thread with Replies
Related Forum Messages:
How To Write Set-based SQL Instead Of Cursor
Guys Here's the scenario create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT) insert data1 (dealid , datex , Tn ) values ('12345' , '31-12-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '30-11-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('12345' , '31-10-2005' , 9999) insert data1 (dealid , datex , Tn ) values ('98765' , '31-12-2005' , 2) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 1) insert data1 (dealid , datex , Tn ) values ('98765' , '30-11-2005' , 0) select * from data1 I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way Any ideas
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
Does Cursor Convert Table To Read/write?
Hello, Any help here much appreciated. I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write. i suspect this is when i run a cursor through the table. Is anyone able to confirm for me whether running a cursor changes a table's properties? Many thanks. Tim Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
View Replies !
Please Help Me To Write A Sql Query
I have two table named tbl_Scale and tbl_NGTrDAMaster tbl_Scale(ScaleID,ScaleName,ScaleLB,ScaleUB,ScaleSI1,ScaleSI2,ScaleSI3) here scale id is prim key tbl_NGTrDAMaster(TrDaId,ScaleID,CityTypeID,DAAmount) no prim key and we get CityTypeID from xml databinder....... In my form thr is two drop down list one for scale name and another for city type id this is the data form tbl_NGTrDAMaster 17 1 1 555 18 3 1 777 19 3 1 999 8 1 1 777 5 5 1 34634 20 1 1 52352 27 1 1 6666 23 5 1 12412 12 2 1 235235 13 3 1 456456 14 5 1 1000000 15 4 1 60000 16 5 1 90 24 5 1 25123 25 5 1 13124 26 5 1 12412 but i am expecting only one combination of set..... like 1-1,1-2,1-3,1-4.......but if reenter 1-1 thn we have to restrict that.... please help me.... i am in big trouble......Thanx in advance If my qes is not clear for everyone... plz tell me.... i try my lebel best for understand my prob to u.....
View Replies !
How To Write Query For This Using C#?
Hi,I have included here my webform here.i need some assistance here with code.my webform contains two parts.the 1st part is office info and the 2nd part is client info.i also have two table named office_info and client_info.1st part is populated from the table office_info as soon as the office name is chosen from the dropdownlist.in my scenario,when user selects officename from dropdownlist,then textboxes correspondingto address and email gets populated by the related data from table office_info.2nd part is client info.here there are 3 textboxes(for name,age,address) to collect the data from the client using the form.these data gets posted to new row in table client_info as soon as user clicks on the save button.Now my actual question starts here.when user selects the option from the dropdonwlist the office info displays,now when he fills the client info part and clicks the save button,i want all the data to go to the table client_info in such a way that all the data fromthe client info part plus the id of the office also go along with it.eg: when user clicks the save button.i want data to get submitted in table client_info in this way.(id,name,age,address,off_row_id) (1,jack,25,US,1) here off_row_id is the id from the below table.my table office_info is like this (id,off_name,address,email) eg(1,xyz,ny,xyz@xyz.com) well can anyone tell me how to write query to do insert,edit,update,delete query in this case using c# and sql?here is the scenario <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"></script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> Office Info:<br /> <hr /> <br /> Office name: <asp:DropDownList ID="DropDownList1" runat="server" Width="63px"> <asp:ListItem>ABC</asp:ListItem> <asp:ListItem>XYZ</asp:ListItem> </asp:DropDownList><br /> <br /> Address: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> email: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> <hr /> </div> Client info:<br /> <br /> name: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <br /> age: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <br /> address:<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox><br /> <br /> <br /> <hr /> <asp:Button ID="Button1" runat="server" Text="save" /> <asp:Button ID="Button2" runat="server" Text="cancel" /> </form></body></html> thanks.jack.
View Replies !
How To Write Sql Query With Asp.net(C#)?
hello everyone. i want to know how asp.net works with sql database. can i have a link to the article where i can perform from basic to advance sql query using asp.net(C#)? (in context of vwd 2005 and sql express ) thanks. jack.
View Replies !
How Do I Write This Query?
I have a Properties table like thisPropertyID PropertyValue 1 Address 2 City 3 Stateetc.and a UserProfile table like thisUserID PropertyID PropertyValue1 1 123 Main Street1 2 Denveretc.How do I write a query that can populate a registration page with Address,City, State as labels and 123 Main Street, Denver, as TextBox text?
View Replies !
Need Help To Write The Query
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
View Replies !
How To Write This Query
Hello, I have a table with fields; T1: Dept, Name, Desc, ModificationDate How can I group by T1.Name, T1.Desc and bring T1.Dept which has the latest T1.ModificationDate Can anyone write me this query?
View Replies !
How Can I Write This SQL Query ??
Hi I have 2 tables and I want to Get information from that tables by SQL Query but How Can I writ this SQL Query ? .. My target as Follow Class Table ------------------------------------------------- ClassID ClassName 1 AA 2 BB Student Table ------------------------------------------------- StudentID StudentName ClassID 1 Student 1 1 2 Student 2 1 3 Student 3 2 4 Student 4 1 5 Student 5 2 6 Student 6 1 How Can I Writ SQL Query to get result like the following .. -------------------------------------------------- ClassID ClassName StudentCount 1 AA 4 2 BB 2 My SQL Query must get all Class table column plus column content the count of student in each class And thanks with my regarding Fraas
View Replies !
How To Write This Sql Query?
i have a table tab col1 col2 num A a 30 A b 20 B a 10 B b 40 C a 50 C b 40 now i want get col1 by distinct col1 ,and order by num, as the result: col1 C B A so can someone help me to write this "select..."
View Replies !
How Would I Write This Query?
It comes from one table called Inventory... there is a [Part Num] field. There is a field called [Cycle Count] which could have a valus from 1 to 3. And there is a field called [Cycle Quantity]. The Keys r Part Num and Cycle Count. 3 counts can be down on one part. I have to pull from lets say Cycle Count 2 all Part Nums with different values of Cycle Quantity than fry Cycle Count 1. How do I do this from a query that doesn't used Delcared variable. I need to be able to run this query in VB basically.
View Replies !
How To Write This SQL Query?
CUSTOMER Name City IndustryType Abernathy Construction Willow B Amalgamated HousingMernphisB Manchester LumberManchesterF Tri-City BuildersMemphis B ORDERS NumberCustNameSalespersonNameAmount 100Abernathy ConstructionZenith560 200Abernathy ConstructionJones1800 300Manchester LumberAbel480 400Abernathy ConstructionAbel2500 500Abernathy ConstructionMurphy6000 600Tri-City BuildersAbel700 700Manchester LumberJones150 800 Abernathy Construction Abel 75000 SALESPERSON NamePercentOfQuotaSalary Abel63132000 Baker3846200 Jones2649500 Kobad2739600 Murphy4255000 Zenith59129800 I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
View Replies !
How To Write This SQL Query?
CUSTOMER Name City IndustryType Abernathy Construction Willow B Amalgamated HousingMernphisB Manchester LumberManchesterF Tri-City BuildersMemphis B ORDERS NumberCustNameSalespersonNameAmount 100Abernathy ConstructionZenith560 200Abernathy ConstructionJones1800 300Manchester LumberAbel480 400Abernathy ConstructionAbel2500 500Abernathy ConstructionMurphy6000 600Tri-City BuildersAbel700 700Manchester LumberJones150 800 Abernathy Construction Abel 75000 SALESPERSON NamePercentOfQuotaSalary Abel63132000 Baker3846200 Jones2649500 Kobad2739600 Murphy4255000 Zenith59129800 I have got the three tables above. Would you help me to write a SQL query to show the names and PercentOfQuota of sales people who have an order with all cuatomers. Thank you very much!
View Replies !
I Appreciate If Anyone Help Me To Write This Query
User Page Name Permission vijay customer.aspx 1 vijay customer.aspx 2 vijay customer.aspx 3 vijay user.aspx 2 Rajashekar customer.aspx 1 Rajashekar customer.aspx 2 Where Permission 1 = SAVE 2 = UPDATE 3 = DELLETE Where I query on User and PageName I want the output as User Page Name Permission vijay customer.aspx 1,2,3 vijay user.aspx 2 Rajashekar customer.aspx 1,2
View Replies !
Better Way To Write This Query?
i am assuming there is a better way to write this query (since im not too proficient in SQL) sql Code: Original - sql Code select client_id from clients where client_id not in (select schedule_det.client_id from schedule_det, schedule_mstr where schedule_det.schedule_id=schedule_mstr.schedule_id and schedule_mstr.status_code!='COMPLETE') SELECT client_id FROM clients WHERE client_id NOT IN (SELECT schedule_det.client_id FROM schedule_det, schedule_mstr WHERE schedule_det.schedule_id=schedule_mstr.schedule_id AND schedule_mstr.status_code!='COMPLETE')
View Replies !
How Do I Write This Query
ok i have table1, table 2 and table 3. these table have some common feild names. table1,2 and 3 all have a,b,c and d as field names. each table has other field names too but the ones they all have in common are a,b,c and d. so i would like to write a query that returns all rows from all 3 tables where column d is greater than 5 and less than 10. so basically i want it to treat the records from all 3 tables ad one big dataset. how would i write a query to do this. i know i could say: SELECT a,b,c,d FROM table1,table2,table3 but what gets me is the WHERE clause do i have to say WHERE table1.d >5 AND table1.d <10 OR table2.d>5 AND table2.d <10 OR table3.d>5 AND table3.d <10 ?? any guidance please?
View Replies !
How To Write This Query?
hi guys, I run into this question again. Here is it. I have two tables like this: StoreVisit(StoreVisitID(PK), UserID, StoreID, LoginID, LogoutID) ProductOrders(ProductID, UserID, StoreID, OrderQuantity, StoreVisitID) What I want to do is, I need to find out the lastest store visit record and put the StoreVisitID into ProductOrders table for the same userID, StoreID. So what I tried is: update ProductOrders set StoreVisitID=sv.StoreVisitID from ProductOrders po inner join (select top 1 * from StoreVisit where UserID=po.UserID and StoreID=po.StoreID order by StoreVisitID desc) sv on po.StoreID=sv.StoreID and po.UserID=sv.UserID Where po.UserID=@UID and po.StoreVisitID is null When I run it, it shows the error: The column prefix po, does not match with a table name or alias name used in the query. Looks like it does not recognize the po in select top 1... . I hope you guys already understand what I am trying to do. I am wondering what is the best way to do this? Thanks.
View Replies !
How To Write A Query
Please help me to get this result Bellow is my requirement I have employee table which stored employee information And I have reference table which holds information for that employee reference I need to count from the reference table like how many reference made for male Employee and female depending on their age range I have a selection list: Gender: Male Female All When user select : Male from the list Male Female Age range: 20-29 Age range: 30-45 120 0 100 20 When user select : Female from the list Male Female Age range: 20-29 Age range: 30-45 0 25 15 10 When user select : All from the list Male Female Age range: 20-29 Age range: 30-45 120 25 115 30 Please help me to sort out this problem regards Sujithcf
View Replies !
How To Write This Query
I have a Table "T1" with col "id","name". Table "T2" with "TID","ID" etc..d [Here, id of T1 is mapped to id of T2] Now, I wanted to write a query such that the query should output T1.id,T1.name and a third column which will show me the count of records in T2 corresponding to an ID in T1. Any help on this is highly appreciated. Thanks!
View Replies !
How Best To Write This Query?
I need help on writing a query which will return my results as I need them. I have three tbls. TimeType, TimePlan and TimeSpent TimeType simply lists the Individual Jobs (types) a user can be doing. The TimePlan and Time Spent each list individual records with start and end dates and what TimeType was being performed. Each TimeType can have multiple starting and ending dates for the TimePlan and Timespent. I need my results to show individual timetypes, with the starting and end dates for the Timeplans and also the TimeSpent EG: TypeType | TimeplanStart | TimeplanEnd | TimeSpentStart | TimeSpentEnd Writing | 01/02/2007 | 02/02/2007 | | Writing | | 03/02/2007 | So there will be multiple Types, and for each one I need to show the start and end dates. I would normally write this as three seperate queries. The first would get the TimeTypes, The second the TimePlan starting and ending dates for that type, The third , the Spent starting and ending dates for that type However that doesn't come across as very efficient so I wonder if there was any other alternative
View Replies !
Help To Write Query...
Hi ! There is one table tCustomers. It has following columns: ID, Name, Code... By the mistake in this table has appeared incorrect records (duplicates). How can I write the query to find them ? I tried: Select c.ID ID1,s.ID ID2, c.NAME NAME1,s.NAME NAME2, c.Code C1, s.Code C2, From tCustomers c, tCustomers s where c.Code=s.Code and c.ID <> s.ID But the result is not that I expected
View Replies !
Help Me Write This SQL Query....
I have a table with many records in it. There is one field called "Nature". How would I select the value that appears the most often in the "Nature" field? The nature field contains text. For example, this code selects those with more than 10 records... I just want the top record. SELECT count(*) FROM WEBASGN_FULL GROUP BY NATURE HAVING count(*) > 10 Basically select nature from webasgn_full that occurs the most often in the table.... Thanks
View Replies !
How To Write QUERY
Hi i want to audit a tables For That i created audit tables In that table i want to store data as all field related to old data table and from which system user had changed the data For this system id i used host_id() but the iam not getting the id Malathi Rao
View Replies !
Help Need To Write A Query
Hello all, I need a help to write a query. Here is the table Declare @Test Table ( EName Varchar(15) ) Insert into Ename Select 'a' Union all Select 'a' Union all Select 'a' Union all Select 'b' Union all Select 'c' Union all Select 'b' Union all Select 'd' Union all Select 'g' Union all Select 'g'. Now i need a result like a0 a1 a2 b0 b1 c0 d0 g0 g1 Could any one can help me to wite this query..? Thanks Lakshmi.S
View Replies !
Bit Of A Complicated SQL Query To Try And Write...
I'll start by giving the basic idea of the data structure I'm looking at in the database (pardon my MS Paint skills!): And this all starts with a root plan. So (taking projects out of the picture for the minute), it could be a structure going any number of levels down like so: plan -> goal -> strategy -> plan -> goal -> strategy -> plan -> goal -> strategy, etc.And not forgetting, that a strategy can also have any number of projects, and all these relationships are one to many, so it ends up being like a large tree structure. What I am trying to accomplish is user security related, in that I need to enforce rules set to say whether a single user is allowed to view a project. You can define the rules at any level in the structure above. So if for instance, a user is given a rule that they can access anything from a goal, they can see any projects that exist in any strategies underneath that goal in the tree structure. I hope this is making sense so far... The rules can also just be defined at a specific project level. These rules (at the moment, I'm more than happy to change if this is a silly way of doing it) are set in a table that has the columns:user_id intplan_id intgoal_id intstrategy_id intproject_id intallow bitEvery row will have a user_id value, and one of either a plan_id, goal_id, strategy_id or project id (which I thought would be a simple way to pull out what kind of rule if needed), and the allow column just defines if it's an allow or deny rule (a specific deny rule on any object lower in the tree structure will override any cascaded allow rule). The way my application needs to grab this information, is to simply have some way of returning a list project_id values for which the current user is allowed to access. I'm basically stumped. The closest I have gotten (which still didn't quite work) was going to end up using quite a large number of cursors... Which is bad. It can be inefficient to an extent, as once these values are gathered once, I can store them in a Session variable, so it's not grabbing them every time the user tries to load a project. Thanks for any help!
View Replies !
Urgent - How To Write Query In Asp.net
i am working on asp.net i am passing two values through querystring to the next page, that values are comming to next page but i want to display results on these two crieria hats. i have written following query which working fine in sql server 2000 SQL 2000==> select caterername, ccity from caterer where ccity='matunga' and cateringfor like '%event%' but when i am trying to do this in asp.net its not working. following code accepting only one value . i want to compair two value. Please guide me how to write above query in asp.net using 'and' and 'like' operatorasp.net ==>select * from addahall where HCity='" + city + "' ==> city is variable please let me know as early as possible i have very less time.
View Replies !
How To Write Sql Query For This Case?
In one SQL server table table1 it contains following data ID Date CA01-001 01/01/2000 CA01-002 02/01/2000 CA01-003 01/15/2000 CA01-004 02/11/2000 CB02-001 01/01/2001 CB02-002 02/01/2001 ID is grouped by first four characters CA01 or CB02, then each record increased by 1 to be 001, 002 .. untill 999 in sequence How do I write SQL query just to get each group largerest record such as for our case: CA01-004 02/11/2000 CB02-002 02/01/2001 Thanks in advance!
View Replies !
Help! I Can't Write This SQL Server Query ... Can You?
Take the following tableID Shipment ETA Updated01 123 3/1/04 2/12/0402 123 3/2/04 2/13/0403 123 3/1/04 2/14/0404 154 3/2/04 2/12/0405 456 3/1/04 2/17/0406 456 3/1/04 2/16/0407 456 3/1/04 2/15/04I need a query that will return the 2 most recently updated rows foreach shipment. So the results would look like the following:ID Shipment ETA Updated02 123 3/2/04 2/13/0403 123 3/1/04 2/14/0404 154 3/2/04 2/12/0406 456 3/1/04 2/16/0405 456 3/1/04 2/17/04Thanks,Teknari
View Replies !
How Do I Write A Query To Look In Any Table For The Value 'foo'?
I've got a rather large database, approx 560 tables, that is about 10G. We're running into trouble when an application moves data from my database into GreatPlains accounting software. The problem seems related to the value 'foo' not coming across correctly. I've looked at all the tables that I think would be involved, but couldn't find the value. So, I'd like to write a script to sequence through the tables and check each column for the value. Anybody know how to write such a script. Thanks, alex8675
View Replies !
Trying To Write A Replace Query
I can't figure out how to replace a single digit entry in my database with a new digit. i want to replace in the alarms.severity all values of 4 with a new value of 2. For some reason the script I wirte won't get past the digit 4 in the statement. Anyone have a script they can let me try?
View Replies !
How To Write A Recursive Query?
I am wondering if there is some type of recursive query to return the values I want from the following database. Here is the setup: The client builds reptile cages. Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage. The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table. PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY. Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb Here is a quick schema: Table: PRODUCT -------------------------- PRODUCTIDPK PRODUCTNAMEnVarChar(30) Table: ASSEMBLY -------------------------- PRODUCTIDPK(FK to PRODUCT.PRODUCTID) COMPONENTIDPK(FK to PRODUCT.PRODUCTID) QTYINT I can write a query that takes the PRODUCTID, and returns all PRODUCT ======= PRODUCTIDPRODUCTNAME -------------------- 1Cage Assembly - Solid Sides 2Cage Assembly - Split Back 3Cage Assembly - Split Sides 4Cage Assembly - Split Top/Bottom 5Cage Assembly - Split Back and Sides 6Cage Assembly - Split Back and Top/Bottom 7Cage Assembly - Split Back and Sides and Top/Bottom 833S - Aluminum Divider 933C - Aluminum Frame 10T3C - Door Frame 11Connector Kit 12Connector Socket 13Connector Screws ASSEMBLY ========= PRODUCTIDCOMPONENTQTY --------------------- 198 1104 1111 211 281 311 381 411 481 511 582 611 682 711 783 11128 11138 I need a query that will give me all parts for each PRODUCT. Example: I want all parts for the PRODUCT "Cage Assembly - Split Back" The results would be: PRODUCTIDPRODUCTNAME -------------------- 2Cage Assembly - Split Back 1Cage Assemble - Solid Back 933C - Aluminum Frame 10T3C - Door Frame 11Connector Kit 833S - Aluminum Divider 12Connector Socket 13Connector Screws Is it possible to write such a query or stored procedure?
View Replies !
How To Write Query For This Requirement
Hi friends this is the Orders to rise the publisheres (Titles or magzines). I have two tables 1) orders Tables the columns are like orderno int , titleId int ,ItContaincomb bit, combinationID int. Values(1,2,null,null) values(1,3,null,null) values(2,4,null,null) Values(2,3,null,null) values(2,2,null,null) values(3,4,null,null) 2) CombinationTable containg columns like CombId int , Titleid int. values(101,2) values(101,3) values(102,4) values(102,5) valus(103,2) values(103,4) my requirement is in orders table the last twocolumns should be filled by checking the combination table 1) take 101 combination containg 2,3 titles check these all titles are containg or not . if Compulsory all titles containg means in orders table we put ItContaincomb true, combinationID 101 for all containg titles. if not containg single title in orders table for combination 101 means we put that titleid rows should containg column of ItContaincomb is false and combid is 0. i want below out put for above two tables how to write a query or how to use all function. the output is (in orders table) Values(1,2,true,101) values(1,3,true,101) values(2,4,false,0) Values(2,3,false,0) values(2,2,true,103) values(3,4,true,103 please help me . thanks regards venkat
View Replies !
How To Write Logical SQL Query ?
Hi Friends, I have three tabels A,B,C having relation of primery & forign key, and i want to fetch data from all three. In case if only A have the values and B & C dont have values, then also i want values. But my simple sql statement dose not show any data, if B & C dont have values. Any one tell me that how to fetch data from this type of Situation??????? pls Thanks, gs
View Replies !
Is It Possible Write In 1 Query Statement?
Hi guys I'll appreciate any help with the following problem: Table 1: TBSALESHEADER PK: SALESNO Table 2: TBSALESENTRY FK: SALESNO This query is return 2 rows. SELECT PAYMTDID AS TYPE, CONVERT(CHAR(8),SALESDATE,112) AS DATE, SUM(SALESAMT) AS AMOUNT FROM TBSALESHEADER A, TBSALESENTRY B WHERE A.SALESNO = B.SALESNO AND CONVERT(CHAR(8),SALESDATE,112) = '20070701' GROUP BY PAYMTDID,CONVERT(CHAR(8),SALESDATE,112) TYPE DATE AMOUNT ---------------------------------------------------------- 0 20070701 3041.29 1 20070701 1442.20 Issue: Instead of retrieving 2 rows showing 2 type of records, I want to display the 2 type in 2 columns (need to return in 1 row). Can I get this desired result as following in 1 query statement: DATE AMOUNT (TYPE 1) AMOUNT (TYPE 2) --------------------------------------------------------------------------------- 20070701 3041.29 1442.20 Please help. Thanks.
View Replies !
Query Without Using Cursor
hi friends, i want to get that row's startdatetime where sum of duration becomes equal to or greater than 1000 without using cursor. create table test ( duration int, startdatetime bigint primary key, userid int ) go insert into practise select 400, 500, 1 union all select 500, 600, 1 union all select 100, 650, 1 union all select 100, 700, 1 go thnks in adv., chakri
View Replies !
A Challenge: Need To Write A Difficult Query
GO CREATE TABLE [dbo].[Product] ( [ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY, [Name] [varchar](52) NOT NULL, [Type] [smallint] NOT NULL, ) For this table I have to write the querywhich willget the TOP 1 Row of each Type. I know the alternate way of doing this by union. But this is not professional. Can anyone resolve this issue?
View Replies !
Beginner's Question: How To Write This Query
Hi - I feel stupied b/c I can't figure this out but I hope it's just a few seconds for some of the more senior posters of this forum: I have a table that looks like this: Table:RequestID: bigint... (a bunch of table specific columns)User_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)Provider_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several) and then there is a second table that looks like this: Table: StatusID: bigintRequestID: bigint (the request ID, so I can see all status messages for the request)Time: TimestampCode: byte (there are only a few status messages) The status table holds both the status for users and providers, as they use exactly the same status code. What I'd like to get is a table/view that looks like this: RequestID:bigint (the request that this status belongs to)User_Code: byte (the code of th most recent user status)Provider_Code: byte (the code of the most recent provider status) Somehow, I am blanking how to create a query that returns both user and provider codes in one row. Any help greatly appreciated!!!! Oliver
View Replies !
How To Write A Nested GROUP BY Query, Please Help.
Use Pubs SELECT pub_id, type, SUM(price) as Total_price FROM titles GROUP BY pub_id, type The above query returns the following resultset: 0736business 2.9900 1389business 51.9300 0877mod_cook 22.9800 1389popular_comp42.9500 0736psychology 45.9300 0877psychology 21.5900 0877trad_cook 47.8900 0877UNDECIDED NULL Now I want to add another "Group By" on type, so I tried: Select type, sum(Total_Price) from (SELECT pub_id, type, SUM(price) as Total_Price FROM titles GROUP BY pub_id, type) Group By type But I got error: Incorrect syntax near the keyword 'Group'. How can I write such a nested group by query. Thanks in advance for any help.
View Replies !
How To Write Query From Dynamic Table Name
Hi, Please advice me how to write query from table which is given as input(It is not hard coded) Declare @varTableName varchar(30) Select @varTableName = 'table_employee' -- table name Select * from @varTableName This gives me an error : Must declare the variable '@varTableName'.
View Replies !
Help Me Write A Query In Sql Server 2000
[Initially posted in expert group under separate heading but nobody answered.So trying my luck in this section ;) ] I have following table .. name profession ---- ---------- Johnauthor Gregauthor Bravoclerk Toryteacher Macclerk Brianteacher Adrianauthor Tinateacher ..... ... .. There can be any infinite profession types.. a column with total profession of specific type is needed in each record. In above table number of person with author profession is 3 with clerk is 2 and with teacher is 3. From above table i need following output name profession totalProfessionals ---- ---------- ------------------ Johnauthor3 Gregauthor3 Bravoclerk2 Toryteacher3 Macclerk2 Brianteacher3 Adrianauthor3 Tinateacher3 ......... ..... .... Can anyone suggest me any query or stored proc to generate the tbl like above? I tried using cursors too but I am not that good enough in it.. Thanking you in advance.
View Replies !
|
TRACKER
