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.........
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.
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.....
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):
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.)
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.
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?
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.
Hello, 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
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 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.
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'
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?
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
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!
Hi, 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!
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
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
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!
Hello,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.
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 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'
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
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
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
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?
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%'