http://forums.devshed.com/ms-sql-development-95/writing-a-simple-replace-query-335014.html
So, what if I wanted to do a search and replace for a single quotation? I can't use that in my search. How to get around that?
example:
Code:
update NamTable
set namstr=replace(namstr,''','')
I am trying to do following thing:i got a table which contains some words....all i need to do is whenever i see a word starting with 'R', i need to remove the starting 'R' and update it in the same table... could anyone help me out here.....thanks a lot in advance.........
View 2 Replies View RelatedHi,
I have two little identical tables.
Pdates - (here pid is foreign key field linked to diff table proposal)
pdate
pid
pkid-primary key
Cdates - (here cid is foreign key field linked to diff table confirm)
cdate
cid
tid
strttime
endtime
pkid-primary key
I wish to just copy the records from pdates to cdates selected based on pid value with diff cid. The other fields tid,strttime and endtime will be updated later.
what kind of query should I use?
as I am a newbie to SQL server can anybody help
thanks, regards.
Hi, I need to write a simple function to format the contents of the fields in my table. I bascially want to say that if the value in a field is below '0' then format the text in colour red and if the value in the field is 0 or above then format it in black. Obviously this can be done by writing an expression in each field but i would prefer to write a function - any ideas.....
View 1 Replies View RelatedExperts: Please assist with coding a trigger for a SQL Server 2005 .NET application.
Here's the scenario:
Suppose there are tables MEMBERS, ACTIVITY, and HEADCOUNT that look like this:
MEMBER
member_id (int)
member_name (varchar(50))
...etc
ACTIVITY
activity_id (int)
activity_name (varchar(50))
...etc
HEADCOUNT
headcount_id (int)
member_id (int)
activity_id (int)
...etc
Suppose also that the ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3.
OBJECTIVE: When a new member record is added to MEMBER, say member_id 10, insert one record in the HEADCOUNT table for EACH activity in ACTIVITY for that member. Thus, if member #10 is added to MEMBER, then the trigger (or some other mechanism) would add the following records to HEADCOUNT (which, say, already has 30 records):
headcount_id member_id activity_id
31 10 1
32 10 2
33 10 3
I've been advised that a trigger should do the trick for this, but as I'm totally new to SQL, I'll need some help. I'm guessing some iterating SQL command language might be required, but as I'm new to SQL, I don't know how to proceed.
Note that I'm building an ASP.NET application based on VB, and so records will be added to MEMBER through a tableadapter INSERT command. (Though I suspect this has no bearing on trigger behavior.)
Much obliged for your assistance.
-Kurt Euler
San Jose, CA
Ok I have 2 batch files and I have rum them one after one another. I am using
call batch1.bat
call batch2.bat
It is running the 1st batch fil successfully but it is not running the second one. I used a pause to see th e error it says some internal and external batch error.
Ok batch1 is at the desktop. and batch2 is in one of the folders in the desktop.
Nature of batch1 is that it runs successfully.
Nature of batch2 is that it gives and error if I individually execute it. But when I am running together then its not showing the error.l
Please if u did not understand this situation atleast show me how to run two batch files using command lines.
Thanks
I've got an nvarchar(max) column that I need to transform with some simple text processing: insert some markup at the very beginning, and insert some markup just before a particular regular expression is matched (or at the end, if no match is found).
Since the SSIS expression language doesn't support anything like this, is a Script Component the only way to go? Does Visual Basic .NET provide regular expression matching?
Thanks!
Hello, I have two tables: Customer Training Requests has say two columns.Customer - Class Requested a 1b 1c 1a 2b 2c 3a 3 I have another table called packages, which has packages of class.Package No. Class1 11 2 I want to write a query which will return only those customers who have requested exactly the classes in a particular package.so in above example if the package is 1 the query should return only customer b as only customer b has requested class 1 and 2. Any suggestions? Thanks,Paresh.
View 8 Replies View RelatedHello, how can do the following using sql:
if exists(select * from siteindex where site_url like '%msn.com')ThenUpdate siteindex Set dmodified = GetDate(), isFullPage = 0, fp_flname = 'filename', fp_iWidth = 800, fp_iheight = 600
Where site_url = '%msn.com'
else
insert into siteindex (site_url, dcreated, dmodified, isFullPage, fp_flname, fp_iWidth, fp_iHeight)
Values ('msn.com', GetDate(), GetDate(), 0, 'filename', 800, 600)
I cant get the syntax right. Thanks!
Hi all,
I have a problem with retrieving data from lookup tables. Ok here is the issue I have a Tables ...
Tables | Schools StuAccount
Fields | Sch_Id (pk) Student_ID (pk)
School_Name AccountableSch_Id
DistrictName TestedSch_Id
ParticipatedSch_Id
Ok, so i have two tables Schools and StuAccount, each has one primary key and and the StuAccount has three foreign keys that associates to the Schools TABLE which has a the definition of school names wich i want to retrieve for all three foreign key in the stuaccount table. Here is what i want ....
Student_ID
AccountableSch_Id
Shool_Name( this the name of the AccountableSch_Id )
TestedSch_Id
Shool_Name( this the name of the TestedSch_Id)
ParticipatedSch_Id
Shool_Name( this the name of the ParticipatedSch_Id)
I just want to look up the id and put the school name in the final resulting join and i have no idea how to lookup the table School three time and put school name for all three foreign keys.??
Any help will be much appreciated.
Thanks
I have table Products
I have coloumn like ProductName:
ProductName Having two values like Colgate and pepsi
I want to change these coloumn values in the result set
Please write a query
Hi here is clear question?
Table Name is Products
in that One coloumn name is
ProductsName:
and the values for productsName are
colgate
pepsi
the result set should be like following way
ProductsName:
Mike
Johnson
so here I am replacing value Mike instead of colgate
and Johnson instead of pepsi
Please help with writing query
I have 2 tables:
Table1:
IDDescription
1name1
2name2
3name3...
Table2:
SiDID
1231
4562
789myname
852yourname
if description exisits in table1 get description from table1 in
all other cases get ID from table2
please help me write a query:
I have a user function dbo.udf_Valid which is return true or false
(@SID ,@PType,@Group,@Date)
Table 1 with this info:
SID PType Group
12 12 123
45 1 456
Table2 with this info:
PType PType2
12 13
12 45
12 8
1 8
1 9
when I pass the data from table1 to the function I am Okay.
The complexity starts when table2 is involved.
I need to pass PType2 to the function if Ptype is exists in table1.
If function returns true for one of the PType2 return true in all other cases false.
I need assistance on writing a particular query:
I need to be able to filter out duplicates that have different values in a particular field.
For example, I have a table that contains the following:
Code:
WBS1 WBS2 WBS3 Amount Section
123 13 00 475 F
123 13 00 0 L
123 21 01 125 C
123 24 03 50 L
I need to filter out the first two lines because they have the same values for the first three columns but the last column under 'Section' has different values.
Can someone help me?
Thanks
Hello I'm new on sql server and I wonder if there's an specific order for writing a query's clauses (joins, and other conditions in where clause or even the order of the tables in the from clause), I mean, I have a query that takes 1 minute executing, but if I execute the same query after moving the tables in the from clause (changing the order) It takes 5 seconds, could you please tell me Why ??? and what's the best order from writing a query ?
This is the query I was talking about:
SELECT TREP_VALOR_PRECIOS.CDPRECIO,
TREP_VALOR_PRECIOS.NMANO,
TREP_VALOR_PRECIOS.NMSEMANA,
ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
TREP_PRECIOS.CDTIPO_PRECIO,
TREP_VALOR_PRECIOS.SNACTIVO,
'N' SNACTIVAR
FROM
UN_TASASCAMBI, -- I just moved this one to the end of the from clause
T2_CALENDARIO, -- I just moved this one to the end of the from clause
UN_MONEDEXTRA,
TREP_PRECIOS TREP_PRECIOS_BASE,
TREP_VALOR_PRECIOS,
TREP_PRECIOS
WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
AND T2_CALENDARIO.CALTIPOFRUTA = '01'
AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'
Hi Friends,
I need help on writing an optimized query for the following problem..
Consider a table with columns "Date Emp1 Emp2"
Date Emp1 Emp2
1-feb 1 . . 2
2-feb 3 . . 4
3-feb 6 . . 1
4-feb 1 . . 2
5-feb 1 . . 5
6-Feb 5 . . 7
Now, I want search for records with employee id 1 in a way that if column emp1 contains 1 then I want the data in column emp2, and if column emp2 contains 1, then I want data in column emp1.
The output has to be a single column with no duplicate values. In the above example, for employee id 1, the output would be, 2,5 and 6 only.
This table has lakhs of records. I have to scan both columns for a given employee id. What will be the most optimized way to retrieve the data faster. Also, do I need to restructure this table for faster data retrieval?
I have indexes on emp1 and emp2 columns. Do we need union here and if yes, what is the best optimized query for the same?
Hi,
Mine Below Query is taking too much execution time. I am using this query in one of my sp.
Is there any other way to write this query?
Declare @p_Location_Code nvarchar(10)
Declare @p_ShowNearFarRate int
DECLARE @p_Data_Show_Flag int
Set @p_ShowNearFarRate = 1
Set @p_Data_Show_Flag = 1
select
X.Main_Party_Role ,
X.SET_ID,
X.TradeVisionId,
X.EntityId,
DBO.GET_ENTITY_TICKER (X.EntityId) Ticker,
X.Done,
X.MatchDate,
X.Main_Party_Id,
X.BUYER_MNEMONIC,
X.SELLER_MNEMONIC,
X.Other_Party_Id,
DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Near_Dt) Main_Near_Dt,
DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Far_Dt) Main_Far_Dt,
X.Amounts,
'Amount' = Case When ((ISNULL(X.Amount,0) >20) ) then Case When @p_Location_Code = 'NY44' then '20+' Else convert(nvarchar,x.Amount) End Else convert(nvarchar,x.Amount) End,
X.Price,
X.Duration,
X.Other_Party_Bro,
X.Main_Party_Bro,
X.Main_Party_State,
X.Other_Party_State,
'Main_Party_Near_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Near_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Near_Parse End End,
'Main_Party_Far_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Far_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Far_Parse End End,
X.Main_Party_Spread BPS,
'Main_Party_Amt' = Case When @p_Location_Code <> 'NY44' then X.Main_Party_Amt Else Case When (ISNULL(X.DONE,0) = 1 or X.Main_Party_State = 184 or X.Main_Party_State = 106) then X.Main_Party_Amt Else Case When ISNULL(X.Main_Party_Amt,0) <= 20 then X.Main_Party_Amt Else 20 End End End,
X.Other_Party_Near_Parse,
X.Other_Party_Far_Parse,
X.Other_Party_Spread,
X.Other_Party_Amt,
X.LOCATION_CODE,
X.Color_Value Org_Color_Value,
'Color_Value' = Case When @p_Location_Code = 'NY44' then
Case When X.Color_Value=500 then 500 + X.CallMe_Btn_Value
Else Case When X.Color_Value>= 284 and Isnull(x.done,0) <> 1 then 284 + X.CallMe_Btn_Value
Else Case When X.Color_Value=201 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=200 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=184 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=106 then 16 + X.CallMe_Btn_Value
Else Case When X.Color_Value=101 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=100 then 0 + X.CallMe_Btn_Value
Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value
Else 0 + X.CallMe_Btn_Value
End
End
End
End
End
End
End
End
End
Else
Case When X.Color_Value>=300 then 300 + X.CallMe_Btn_Value
Else Case When X.Color_Value=284 then 284 + X.CallMe_Btn_Value
Else Case When X.Color_Value=201 then 201 + X.CallMe_Btn_Value
Else Case When X.Color_Value=200 then 200 + X.CallMe_Btn_Value
Else Case When X.Color_Value=184 then 184 + X.CallMe_Btn_Value
Else Case When X.Color_Value=106 then 106 + X.CallMe_Btn_Value
Else Case When X.Color_Value=101 then 101 + X.CallMe_Btn_Value
Else Case When X.Color_Value=100 then 100 + X.CallMe_Btn_Value
Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value
Else 0 + X.CallMe_Btn_Value
End
End
End
End
End
End
End
End
End
End,
X.Main_Party_Strike_State,
X.Other_Party_Strike_State,
'BACK_COLOR' = (DBO.Fn_Get_TradeVisionRR_Back_Color_Value (@p_Location_Code, X.Color_Value)) ,
'FORE_COLOR' = (DBO.Fn_Get_TradeVisionRR_Fore_Color_Value (@p_Location_Code, X.Color_Value))
FROM
(
SELECT
Y.TradeVisionFileIdSET_ID,
'BUYER'Main_Party_Role,
Y.TradeVisionId,
Y.EntityId,
Y.Done,
Y.MatchDate,
Y.BuyerIdMain_Party_Id,
DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERId) BUYER_MNEMONIC,
'SELLER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID)
Else Case When ( @p_Location_Code <> 'NY44' AND Y.BuyerState = 184AND Y.BuyerState = 184AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched= 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpreadAND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID)
Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERId)
Else '*'
End
End
End,
Y.SellerIdOther_Party_Id,
Y.NearDateMain_Near_Dt,
Y.FarDateMain_Far_Dt,
Y.AmountsAmounts,
Y.AmountAmount,
Y.PricePrice,
Y.DurationDuration,
Y.SellerBroOther_Party_Bro,
Y.BuyerBroMain_Party_Bro,
Y.BuyerStateMain_Party_State,
Y.SellerStateOther_Party_State,
Y.BuyerNearPraceMain_Party_Near_Parse,
Y.BuyerFarPraceMain_Party_Far_Parse,
Y.BuyerSpreadMain_Party_Spread,
Y.BuyerAmtMain_Party_Amt,
Y.SellerNearPraceOther_Party_Near_Parse,
Y.SellerFarPraceOther_Party_Far_Parse,
Y.SellerSpreadOther_Party_Spread,
Y.SellerAmtOther_Party_Amt,
Y.LOCATION_CODE,
Y.Buyer_Strike_StateMain_Party_Strike_State,
Y.Seller_Strike_StateOther_Party_Strike_State,
(DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 18, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'BUYER' )) Color_Value ,
'CallMe_Btn_Value' = Case When Y.Buyer_Strike_State = 206 then 1000 Else 0 End
from DBO.TradeVisionRR Y
WHERE
Y.LOCATION_CODE = @p_Location_Code
AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId ))
AND Y.TRADEVISIONFILEID = 169
------------------------------------------------------------
UNION ALL
------------------------------------------------------------
select
Y.TradeVisionFileIdSET_ID,
'SELLER'Main_Party_Role,
Y.TradeVisionId,
Y.EntityId,
Y.Done,
Y.MatchDate,
Y.SellerIdMain_Party_Id,
'BUYER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else Case When (@p_Location_Code <> 'NY44' AND Y.BuyerState = 184AND Y.BuyerState = 184AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched= 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpreadAND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID)
Else '*'
End
End
End,
DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID) SELLER_MNEMONIC,
Y.BuyerIdOther_Party_Id,
Y.NearDateMain_Near_Dt,
Y.FarDateMain_Far_Dt,
Y.AmountsAmounts,
Y.AmountAmount,
Y.PricePrice,
Y.DurationDuration,
Y.BuyerBroOther_Party_Bro,
Y.SellerBroMain_Party_Bro,
Y.SellerStateMain_Party_State,
Y.BuyerStateOther_Party_State,
Y.SellerNearPraceMain_Party_Near_Parse,
Y.SellerFarPraceMain_Party_Far_Parse,
Y.SellerSpreadMain_Party_Spread,
Y.SellerAmtMain_Party_Amt,
Y.BuyerNearPraceOther_Party_Near_Parse,
Y.BuyerFarPraceOther_Party_Far_Parse,
Y.BuyerSpreadOther_Party_Spread,
Y.BuyerAmtOther_Party_Amt,
Y.LOCATION_CODE,
Y.Seller_Strike_StateMain_Party_Strike_State,
Y.Buyer_Strike_StateOther_Party_Strike_State,
(DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 169, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'SELLER' )) Color_Value ,
'CallMe_Btn_Value' = Case When Y.Seller_Strike_State = 206 then 1000 Else 0 End
from DBO.TradeVisionRR Y
WHERE
Y.LOCATION_CODE = @p_Location_Code
AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId ))
AND Y.TRADEVISIONFILEID = 169
) X
where
X.EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List('NY44' , 169 , '02/05/2008' ,200000 ))
and X.Main_Party_Id = 101901
Order By X.TradeVisionId
-- Regards
Prashant Hirani
Hi,
I have a table with the below data:
Billnumber
11111
11111
11111
33333
33333
44444
44444
44444
44444
I want to create an additional field lineItem like below
BillNumber LineItem
11111 1
11111 2
11111 3
33333 1
33333 2
44444 1
44444 2
44444 3
44444 4
Could somebody help me to write this query? Thanks
Help! I need to write a query that looks at one table with two columns. One column has 20K records and the second column has 25K records. I need to compare the two columns and pull out the difference between the two columns. The majority of the two columns data is the same but there are some that do not have a match between the two. The data is not in any current order. HELP! THANKS!
View 8 Replies View RelatedHi, I need some help writing a query, and would rather not go into detail inside here. But if there is someone more advanced in SQL that could help me, could you please write me at g35gurl@yahoo.com.
Thanks!
Hi all,
I am not an expert in writing sql queries. Please help me writing this sql query.
I have a table with two columns like this.
teacher_id --student_id
1 ----------- 10
1 ----------- 11
2 ----------- 12
2 ----------- 13
2 ----------- 14
3 ----------- 15
4 ----------- 16
Now i need only teacher_id or teacher_id's for which minimum number of students assigned.
Thanks in advance
Hello,
I have a database containing 2 tables:
the first one called "weeks" has 4 columns: id, week number (ex:
2007/01), start date(ex:01/01/2007), end date(ex: 07/01/2007)
the second one is called "tests" and has 5 columns: index, date, name,
test name, duration
my goal: I want to make a query to get rows containing 3 columns:
week number,
name,
dates = date1, date2, etc.,
tests=test1, test2, etc.,
total duration of test for the given week and given person
To be clear, I am trying to get per week a summary of names with the
tests they did and how long total it lasted. The first tables is just
here to get the week number, given the test date.
My current query returns a list of all tests (table 2) with an added
column of the week number and looks like this:
SELECT date, initials, test, [hours outside 8-19], [Week number] FROM
tests, weeks WHERE date BETWEEN [start date] AND [end date]
What I need now is to have one line per person and per week with their total of hours for the week and the list of tests for the same week. I tried lots of things but does not know to get to my goal. Could
someone please help me?
Any help will be appreciated. Thank you in advance
Pierrot
Guys,
is there any better way of writing the below query. i don't want to use select again and again.. and as my table has millions of records.. can u pls.suggest me the best possible method.
Select comm_Name,comm_CreationDate,
Total_Users = (Select count(*) from User_Community where UC_COMMID = comm_ID),
New_Members = (Select count(*) from User_Community where convert(char(10),uc_timestamp,101) > CONVERT (char (10) ,'01/01/2008' ,101) and UC_CommID = COMM_ID)
from Community
thanks
Database consists of the following 4 tables with respectiveattributes:CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#]ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#]BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE]PREFER(I#, C#), the key is [I#, C#]I'm trying to construct the following query (in SQL)List of customers that bought all the items that John prefers.I can get the list of all the items that John prefers, but I'm notsure how to check that list against customers who bought ALL thoseitems. I'm assuming it's either a division or some sort of subtractionbut I'm not sure how to formulate the SQL query.Any and all help is appreciated, thanks!
View 21 Replies View RelatedHello,I am trying to write a query and getting a little confused.My problem:I send enquiries to partners from 12:00am to 11:59pm each day. I have tenpartners, and each partner has a different daily limit. In my partner table(Table1), I have a columnm for the Daily Enquiry Limit called "DailyCap". Ialso have a second table (Table2) which counts how many enquiries a partnerhas had and assigns a date stamp.So, my query needs to:Select PARTNERwhere Table2.PartnerCount is less than Table1.DailyCapAND where Table2.TimeStamp between 12:00am TODAY and 11:59pm TODAY.This should hopefully then select any partner who hasent yet reached thedaily cap between midnight start and 24 hours later.Any odeas how to write this query using proper SQL? I have tried butfailed.Regards,Gary.
View 3 Replies View RelatedHello I'm new on sql server and I wonder if there's an specific order for writing a query's clauses (joins, and other conditions in where clause or even the order of the tables in the from clause), I mean, I have a query that takes 1 minute executing, but if I execute the same query after moving the tables in the from clause (changing the order) It takes 5 seconds, could you please tell me Why ??? and what's the best order from writing a query ?
This is my specific query:
/**************/
This is the first one:
***************/
SELECT TREP_VALOR_PRECIOS.CDPRECIO,
TREP_VALOR_PRECIOS.NMANO,
TREP_VALOR_PRECIOS.NMSEMANA,
ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
TREP_PRECIOS.CDTIPO_PRECIO,
TREP_VALOR_PRECIOS.SNACTIVO,
'N' SNACTIVAR
FROM
UN_TASASCAMBI,
T2_CALENDARIO,
UN_MONEDEXTRA,
TREP_PRECIOS TREP_PRECIOS_BASE,
TREP_VALOR_PRECIOS,
TREP_PRECIOS
WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
AND T2_CALENDARIO.CALTIPOFRUTA = '01'
AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'
/*********************************************/
This is the second one: This is much faster than the first one
/*********************************************/
SELECT TREP_VALOR_PRECIOS.CDPRECIO,
TREP_VALOR_PRECIOS.NMANO,
TREP_VALOR_PRECIOS.NMSEMANA,
ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO,
UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO,
TREP_PRECIOS.CDTIPO_PRECIO,
TREP_VALOR_PRECIOS.SNACTIVO,
'N' SNACTIVAR
FROM
UN_MONEDEXTRA,
TREP_PRECIOS TREP_PRECIOS_BASE,
TREP_VALOR_PRECIOS,
TREP_PRECIOS
UN_TASASCAMBI, -- I just moved this one to the end of the from clause
T2_CALENDARIO, -- I just moved this one to the end of the from clause
WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST
AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA
AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO
AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA
AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO
AND T2_CALENDARIO.CALTIPOFRUTA = '01'
AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA
AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA
AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC'
AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO
AND TREP_VALOR_PRECIOS.SNACTIVO = 'S'
AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO)
AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'
I have a database that contains projects and information about the projects.
So, for example:
proj Table
Columns:
project_id
project_name
project_code
Sample Data:
project_id: 1
project_name: Test1
project_code: Alpha6
emp Table
Columns:
employee_id
employee_name
Sample Data:
employee_id: 1
employee_name: david
employee_id:2
employee_name: mike
employee_id:3
employee_name:jason
assign_emp Table
Columns:
project_id = project id from proj table
assign_lab = employee id from emp table
assign_tech = employee id from emp table
assign_tester = employee id from emp table
Sample Data:
project_id: 1
Assign_lab: 1
Assign_tech: 2
Assign_tester: 3
So, I have a table called "proj" that contains the "project_id", "project_name", "project_code" and other various information (not related to my current task). I then have a master employee table called "emp". This contains information about the employees, including the "employee_id" and "employee_name". I then have a 3rd table that links the employee to the project. I've called it: "assign_emp". This table contains the "project_id", "assign_lab", "assign_tech", "assign_tester".
I want to create a query to be able to list the following in a single row, For Example:
Project ID | Project Name | Project Code | Lab Employee | Tech Employee | Tester Employee
1 | Test1 | Alpha6 | david | mike | jason
My problem is figuring out how to select multiple employees from a single table based on whether they are assigned to the project or not. As far as I got was:
Select proj.project_id, proj.project_name, proj.project_code, emp.employee_name as 'Lab Employee' from proj inner join assign_emp on proj.project_id = assign_emp.project_id inner join emp on assign_emp.assign_lab = emp.employee_id where proj.project_id = 1
Any help figuring out the query would be great!
Could someone please help me out? I need to write a sql stored proc to query the following table.My SQL experience is very week. If someone can help me with this, I will be happy to pay you $40 foryour help.
I need the proc to do the following:1.) For every Superintendent in a region, country state and county; return the state name, superintendent name, the county name and and a string which is a comma delimited list of schools they supervise. See the sample output italicised and bold.
So the big challenge here is to also return a string that is a concatenation of school names for a particularSuperintendent in a given state and county. For example: East,Kennedy,Apolo,Morrison.
So basically the stored proc should accept input parameters of the Region, Country, State, and County
Here is the data table:
REGION COUNTRY STATE SUPER_INTENDENT PHONE_NO SCHOOL County
NA USA Texas Mike Andrews 789-3614 East LakeNA USA Texas Mike Andrews 789-3614 Kennedy LakeNA USA Texas Mike Andrews 789-3614 Apolo LakeNA USA Texas Mike Andrews 789-3614 Morrison LakeNA USA Texas Amy Markson 789-2134 Anderson MaylorNA USA Texas Amy Markson 789-2134 Molina MaylorNA USA Texas Amy Markson 789-2134 Polima MaylorNA USA Ohio Terry Ellis 966-8314 Kingston KeelNA USA Ohio Terry Ellis 966-8314 Martin KeelNA USA Ohio Terry Ellis 966-8314 Eastmore KeelNA USA Ohio Terry Ellis 966-8314 Canondale Keel
Here is the sample output the way it will appear on a web form:
State:Texas County:Lake
Mike Andrews East,Kennedy,Apolo,Morrison 789-3614
County:Maylor
Amy Markson789-2134 Anderson,Molina,Polima
State:Ohio County:Keel
Terry Ellis Kingston,Martin,Eastomore,Keel
Hi All,
For one of our projects, we are supposed to write a query builder to display reports in tabular format. For the filter criteria ( i.e where clause ) user can select from a list of predefined columns and assign conditions to it. For example, user can select 'CustomerName' as the column and specify condition where name begines with 'A'. Similarly he can also select ouput columns
Problem
The columns that user selects can come from different tables. We are planning to store mapping between tables and columns and also their primary and foreign keys in an xml file. But I would want to understand how to build the join clause.
For example, say there are 4 tables customer, customer address, order, and items. Say user wants the number of items purchased by each customer who stays in 'Delhi'.
1. In output column he selects customer.name and count(items.id)
2. In filter criteria he selects address.city = 'delhi'
How would the query builder know that to join customer and items, order also has to be joined. Any pointers would be helpful.
I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
This is what I need to achieve:
for instance I create a form with 4 text fields
- First Name
- Last Name
- Employee ID
- Date
I am interested in writing a stored procedure that would run a select query based on the input in the text fields
e.g.
- if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
select * from Employee where FirstName like @FirstName and LastName like @LastName
- or if the user enters only the Employee ID stored procedure should run a query similar to
select * from Employee where EmployeeID like @EmployeeID
Hi
I have two product tables in two different databases, both contain thousands of records. I have to write a query that suggests matches on similar codes, and have come up with:
SELECT TB1.product, TB2.product
FROM TB1
JOIN (select distinct product
from db2.dbo.TB2) as TB2 --this table has PK of product and warehouse
ON TB2.product LIKE '%' + TB1.product+'%'
which DOES work, but because the table have many rows,takes time to do it... is there a way of rewritting this query, so it gives a faster result?
Thanks in advance...
I am using a Table Many Times in Left Outer Joins and Inner Joins for various Conditions,
is there anyway of writing a query using minimal Table usage, instead of Recurring all the time.
**********************************
SELECT
blog.blogid,
BM.TITLE,
U.USER_FIRSTNAME+ ' ' + U.USER_LASTNAME AS AUTHORNAME,
Blog_Entries = (cASE WHEN Blog_Entries is NULL or Blog_Entries = ' ' then 0 else Blog_Entries END),
Blog_NewEntries = (cASE WHEN Blog_NewEntries is NULL or Blog_NewEntries = ' ' then 0 else Blog_NewEntries END),
Blog_comments = (cASE WHEN Blog_comments is NULL or Blog_comments = ' ' then 0 else Blog_comments END),
dbo.DateFloor(VCOM.objCreationDate) AS CreationDate,
dbo.DateFloor(BLE.entryDate) AS Date_LastEntry
FROM vportal4VSEARCHCOMM.dbo.blog_metaData BM
INNER JOIN vportal4VSEARCHCOMM.dbo.blog BLOG
ON BM.BLOGID = BLOG.BLOGID
INNER JOIN vportal4VSEARCH.dbo.[USER] U
ON U.USER_ID = BLOG.OWNERID
INNER JOIN vportal4VSEARCHCOMM.dbo.vComm_obj VCOM
ON BLOG.vCommObjID = VCOM.vCommObjId
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry BLE
ON BLOG.BLOGID = BLE.BLOGID
LEFT OUTER JOIN
(SELECT BlogID, Blog_Entries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
GROUP BY BlogID )B on B.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BlogID, Blog_NewEntries = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.Blog_Entry
WHERE ENTRYDATE > '01/01/2008'
GROUP BY BlogID )C on C.BLOGID = BM.BLOGID
LEFT OUTER JOIN
(
SELECT BEN.BLOGID, Blog_comments = COUNT(*)
FROM vportal4VSEARCHCOMM.dbo.blog_comment BC
INNER JOIN vportal4VSEARCHCOMM.dbo.blog_entry BEN
ON BEN.blog_entryId = BC.blogEntryId
GROUP BY BEN.BLOGID )D on D.BLOGID = BM.BLOGID
WHERE VCOM.objName like '%blog%'
thanks
How to find out 2nd highest salary using sql?
thanx in advance