I have a table that contains a column called hardness. It is a varchar 50 and actually contains numbers. I did not create the database so not sure why they choose varchar vs. number. I need to write a query that will return all results with a space prior to the number in this column. I am having difficulty and would greatly appreciate some help. Thanks!
View 6 RepliesI have written several scripts to pull in nested info to the analyzer window. How do I get this data to write to the new table I have created in the database? here is the current script:
select Hierarchy_List.Hierarchy_Label as Hierarchy_Name,
Hierarchy_List.hierarchy_ID as Hierarchy_ID,
Hierarchy_List.Parent_ID as Parent_ID,
frequency_item.manufacturer as Motor_Make,
frequency_item.model as Frame
from hierarchy_list full outer join Frequency_item
ON HIERARCHY_LIST.HIERARCHY_ID = frequency_item.HIERARCHY_ID
where parent_id in (select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id in
(select hierarchy_id from hierarchy_list where parent_id=0)
and parent_id<>0) and parent_id<>0) and parent_id<>0)
and parent_id<>0 and frequency_item.description = 'motor'
I need to move this data to the VAER.Al_Machines and the column names are the same. I can move data via DTS, but it won't work on this because the nested info. Is there a script addendum I can add to this to execute both the search and the transfer in one job so I can automate it? Thanks for any help.
Mick Flanigan
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
I have a table (represented by #Events) that holds modifications made to another table. I do have some control over the table structure and indexing. I want to pull all of the change records that were made between two dates.
The tricky part is to include the previous version of each record, which will usually be found prior to the start date in question.
The code that I have provided below works. So you can use it to easily see what should be returned. But it's very slow in production.
Any better method to pull this data together?
-- Production version of this table has 4.5 million rows (roughly 1,000 rows per day)
-- Primary key is on L4Ident (clustered)
-- nonclustered index on ProcessDate, LinkRL4
DROP TABLE dbo.#Events;
DROP TABLE dbo.#Results;
CREATE TABLE dbo.#Events (
L4Ident int IDENTITY(1,1) NOT NULL,
[Code] ....
What is the best way to check for leading spaces in your table, using ltrim?
Such as TableA(name, city)
the data in TableA Smith Dallas
John New York
Greg Richmond
David Chicago
Return only David.
so i wrote this script and its pretty sweet (tara helped me before on this one) but now, for some STRANGE reason and its been reported by users the following:
the script for example dumps "first name, last name"
when we save the results as a CSV it looks good in notepad...
"first name, last name"
but we kept getting some kind of stange issues when we tried to dump that CSV data into another program to use... so i opened the CSV file in command prompt to see what it really looks like and it is actually "f i r s t n a m e , l a s t n a m e" with all these unnecessary spaces! why is it saving the CSV with spaces in it when my script is nothing of the sort? it was working fine last week, no changes were made to the script but now the CSV's are rendering those weird spaces.
Hi,
I realize that I need to learn how to write expressions for conditional splits, etc. Frankly, I don't even know what "language" expressions are written in. They look a lot like excel functions to me.
I tried searching the internet for some documentation, but it is sparse.
Can anyone point me to some links, etc that can teach me how to write my own expressions?
Thanks much
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
Just Like these:
-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)
/*
can I do something here to free res used by prior statement?
*/
select * from someOtherTable
--batch end
The Sqls above are written in a procedure to automating test for some select querys.
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedHello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Hello guys,
Iam showing the query results in the asp.net grid. Iam getting a long string with more than 1 space between some words.
How i can trim the spaces of that column result.
I tried RTRIM and LTRIM but these functions need two individual strings with spaces either left or right.
Help me.
I want to add 3 spaces at the end of each row for the result of a query.
The initial query being output to a text file (USING DTS) is:
select colA, colB =
CONVERT( CHAR(15), TableX.ColB )
from TableX
------------------------------------------------------------------------
BUT WHEN I EXPORT THE FOLLOWING (USING DTS), I GET THE SAME RESULTS
select colA, colB =
CONVERT( CHAR(18), TableX.ColB )+char(32)+char(32)+char(32) -- ' '
from TableX
Replacing the char(32) with ' ' (3 spaces in quotes) doesn't help.
Any ideas of how to append the 3 spaces after each record when exporting to
a text file using DTS??
Thanks
I'm using the following command:
osql -E -n -d testdb -i testquery.qry -o "c:Scriptsoutput.txt" -h-1 -w 500 -s ","
With the following query (testquery.qry):
SET NOCOUNT ON
SELECT table1.column5, table2.column9
FROM table1, table2
WHERE table1.column4 = table2.column4
AND table1.column1 != "NULL"
ORDER BY table1.column5
All of the columns are cast as char up to 50 characters.
Even if only a field has a few characters, I get a lot of extra white space in my output. I want to get rid of those trailing spaces. I've tried SET ANSI_PADDING OFF, RTRIM(), and CAST(x AS VARCHAR(y)). I still get the same output. What am I doing wrong, what am I missing?
In SQL Reporting I want the user to pass a parameter to the report; the parameter is a employee number. Easy right, you write a where statement like where table_name.employee_number =@employee_number. When the user runs the report they€™re asked to type in the number.
My problem is the dB stores the employee number with leading spaces padding the number out to 9, so if the user types in 1 they get jack, but if they type in €˜ 1€™ they get the right employee. I can€™t roll it out that way
I€™d do something in the where clause so the user could just type in 1 & it would auto pad out to 9 spaces. I€™ve tried using the €˜LIKE€™ clause & I€™ve tried using concatenate but so far neither works well.
I'd appreciate any advice
Thanks -- Vince
Is there some way to save a selection set in SQLso that another SQL query can further refine theprior query?
View 2 Replies View RelatedHello, 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 Related