Help Writing A SELECT Query, Please. (Should Be Interesting For You!!!)
May 28, 2008
All- I would greatly appreciate some assistance in creating a SELECT statement for table headcount in the following problem:
First, find the schema here: http://home.pacbell.net/eulimi/schema_headcount.GIF
The problem:
Given a person_id (a record in the person table), list all headcount records WHERE:
1) headcount.person_id = person.person_id AND
2) the headcount.act_session_id belongs to a activity (from the activity_session table) that the person is allowed to attend by virture of the person's membership in a subexpedition.
Note that:
- A person can be a member of one or more circles (via the circle_person table)
- Each circle belongs to exactly one subexpedition.
- A persons who is a member of a subexpedition (via one or more circles) is entitled to sign up for one or more activities (activity_sessions) associated with the subexpedition.
So, put yet another way, the SELECT command should show, for a given person_id, the list of records in headcount that includes the activities (act_session_id) that the person is entitled to join by virtue of the persons membership in the subexpedition via his circle. (Whew!)
I hope this is enough information. I think the problem would be fun if I knew what I was doing. (SQL newbie here.) I suspect I need several INNER JOINs and a compound WHERE clause.
Hi AllWondered if you could help me with the below query.I have 1 simple table called STOCKCATS that consists of 2 fields.These fields are called CATID and LEVEL.The contents of this table are as follows:CATID LEVELcat01 <nothing>cat02 <nothing>cat03 cat01cat04 <nothing>cat05 cat01cat06 cat02cat07 cat04etc.. etc...The way this table works is that I have an ASP page that allows the user tocreate a stock category at 2 levels, category level and sub-category level.When I file the entered data into the table, if the user has chosen tocreate a category level stock category then the LEVEL field is left blankand if they chose to create a sub-category level category then I post therelevant category level stock category code in the LEVEL field. Forexample, in the above list cat01 is a category level stock category andcat05 is a sub-category as it is a sub-category of cat01.My query is that I want to populate a simple HTML <SELECT> menu (using ASP),but instead of it being a straightforward 'select catid from stockcats orderby catid', I want to group this list into some kind of order, eg:instead of:cat01 <nothing> << I need to bring back this 2nd column so that I cando a simple IF THEN in asp to indent sub-catscat02 <nothing>cat03 cat01cat04 <nothing>cat05 cat01cat06 cat02cat07 cat04I would likecat01 <nothing> << dittocat03 cat01cat05 cat01cat02 <nothing>cat06 cat02cat04 <nothing>cat07 cat04Do you know if this is possible in pure SQL (I must confess that I'm usingMySQL, but I would have thought the SQL syntax would be the same if it ispossible) or a combo of ASP & SQL?ThanksRobbie
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €ścustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
The value in the table of one DB is 17869 sq. ft. Now to insert this value in a new table of other database the reporting basis is 1000 i.e I need to do 17869/1000 = 17.8 so I have to take the value as 18. Another thing to be kept in mind is the value in the new table should have leading Zeroes. If the value is 18 it should be displayed as 0000018 ( data type in new table is Varchar(7) and in old table char (9) ). What can be the best way to implement this??
I'm writing my first .NET app, and have a simple page where I set up a connection to my db, query it, and write out a value. But I get this error, that doesn't give me a very good idea of what I should fix... Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 10: Dim comm As SqlCommand Line 11: Dim reader As SqlDataReader Line 12: Dim connectionString As String = ConfigurationManager.ConnectionStrings("IntegratedData").ConnectionString Line 13: Line 14: conn = New SqlConnection(connectionString) Here's the vb.net code from my page. It seems soo simple, but I'm missing something. <%@ Page Language="VB" %> <%@ Import Namespace="System.Data.SqlClient" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim conn As SqlConnectionDim comm As SqlCommand Dim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("IntegratedData").ConnectionString conn = New SqlConnection(connectionString) comm = New SqlCommand("Select tkinit, tklast as FullName from tEliteTimeKeep", conn) conn.Open()
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%'
