select contractnbr,affcontractnbr,tdd,affrtramount,affturn,
pt=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate),ratio=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate)/afftrun
from #targetfinishedaff
datediff(d,afffirstfundingdate,affRTRBalanceZeroDate) appears two times in this code, Is there a way to make it appear only once?
Thx
I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?
View 2 Replies View RelatedHi guys, I have a car_race table which has these fields
car_id int
race_id int
b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 2
b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
car_id 1
race_id 4
b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?
I want to make query for average sales price, the records are like this
item, Sales_qty, UnitPrice
A, 10, 1000
A, 20, 1100
A, 5, 1500
B, 1, 1000
B, 2, 1200
and display like this
A, 35, 1128 (35 FROM total qty, 1128 from average sales price)
B, 3, 1133
Thanks in advance
I have two SQL query that I would like to make in to one, if possibleHere is the first one: (Query4)SELECT rooms.DESCRIPTIO, rooms.ID, bookings1.DATEFROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms.IDWHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));And the second one:SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMIDFROM rooms LEFT JOIN Query4 ON rooms.ID = Query4.ROOMIDWHERE (((Query4.ROOMID) Is Null));Is it possible to make one query of this two?With the same result.
View 3 Replies View RelatedI havea textbox for searching by patient name but now and though I am searching with LIKE% i can only search for exact string, for example if user typed JASSIM it will search for field full_name LIKE '%JASSIM%" ad if user typed JASSIM RAHMA it will search for full_name LIKE '%JASSIM RAHMA%' but i want to search if the field CONTAINS '%JASSIM% AND %RAHMA% when the user types JASSIM RAHAM in the textbox
View 4 Replies View Related
Hi all. I have tried making this query for a while and i didn't manage to.
I know it is something related to JOIN, but i didn't manage to make it work :
I have two tables.
Table1- db_stockprices
Table2- db_stockSymbols
db_stockprices is getting updated every day with stock daily prices.
db_stockprices looks like:
ID, SymbolID, Price,Date
1 1 33.21 1/1/2007
2 1 33.14 1/2/2007
3 1 34.21 1/3/2007
4 2 11.42 1/1/2007
5 2 11.73 1/2/2007
6 3 18.32 1/1/2007
7 3 19.11 1/2/2007
8 3 18.41 1/3/2007
9 4 52.44 1/1/2007
and so on for all the stocks.
ID is Primary Key
SymbolID is related to SymbolID in db_stockSymbols which contains all the stock information.
What i am trying to do is an SQL QUERY that will delete all same SymbolID Rows if a specific INPUT date do not exist within that SymbolID.
Example:
If the INPUT date for the query is 1/3/2007 all rows with SymbolID "2" abd "4" will be deleted since 1/3/2007 does not exist in SymbolID "2" and "4". Moreover, the row in db_stockSymbols with SymbolID "2" and "4" will also be deleted.
Is this possible within 1 SQL Query?
I would really appreciate a good query example for this example..
Dear member
Hi
I have a problem,
I have a table for my company name and another for cities.
I have a table Company_City to indicate that each company has office in each city
For example
company X in city A company Y in city B
company X in city B company Y in city H
company X in city C
I want make a query to retrieve companies that have office in city A and B. so company Y that has office in B only should not retrieved in my query.
If i use "IN" operator(by subquery from city table) in my WHERE Clouse the result will be A,B.
Can you help me?
Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:
Select * from Table1
It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Thanks a lot!
And Merry X'max!!!
Hi,
I have a query thatI need to make into one query for the sake of an application reading only one cursor.
Here's the syntax:
select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
The error message i'm recieving is the following:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas? any suggestions would be great.
Thanks
I have table 'OrderTab' in Sql Server database which have Company Number,ContactId,Quantity fields and data in tables are in following format.
Company ContactId QuantityNumber100001 101 1100001 102 2100001 103 9100001 104 8100001 105 4100001 106 3100001 107 2100001 108 1100001 109 3
I need to create the query and want the result in following format.
CompanyNumber |MaxQuantityContactId |MaxQuantity| MinQuantityContactId |MinQuantity100001 103 9 101 1
Where MaxQuatityContactId = ContactId which has order max quantityMaxQuantity = max quantity order by single company(100001)
MinQuatityContactId = ContactId which has order min quantityMinQuantity = min quantity order by single company (100001)
Any idea how to do that.
Thanks in AdvanceArvind
Hello,
I'm a bit new to MS-SQL so i thought maybe you could help me.
This is my prob. I have an Access application that i have upsized to a MS-SQL server. I have a query based on 3 tables: Customers, Companys and Payreminders. When i run this query, i can't add new records or make any changes.
If i only run the query with the tables: companys and payreminders i can add new records and edit them. If i run a query that's based on companys and customers, i can edit and add new records, but when i run the query based on companys and customers i can't add or edit records.
companys, payreminders: Editable
payreminders, companys: Editable
Companys, customers: Non editable
Companys, payreminders, customers: Non editable
Something must go wrong when i use the companys and customers table. Can anybody help or give a suggestion what to do.
Thanks.
I need help makeing the following query run more efficently.
Code:
SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
FROM Landlord_tbl t1
left outer join Mail_tbl t2
ON t2.potentialSitesID = t1.potentialSitesID
WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null
GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
ORDER BY t1.firstName, t1.lastName, t1.city, t1.state
I have tbWarehouseStock, contain
WHCode Item Stock
------ ------ -----
WH001 Pencil 10
WH001 Pen 10
WH002 Pencil 5
WH003 ruler 100
How to make pivot query like this, I am thinking of dynamic SQL but dont know how to do this
Item WH001 WH002 WH003 WH004 WH005 ....
------ ----- ----- ----- ----- -----
Pencil 10 5 0
Pen 10 0 0
Ruler 0 0 100
thanks
Please help me to get the result I want.
I have this table.
Date Value
=== ====
1-Jan 100
1-Jan 80
1-Jan 20
2-Jan 50
2-Jan 30
2-Jan 5
2-Jan 15
3-Jan 120
3-Jan 80
4-Jan 45
4-Jan 55
5-Jan 120
How do I write the query to get the following result from above table.
Date Opening Value Closing
1-Jan 0 200 200
2-Jan 200 100 300
3-Jan 300 200 500
4-Jan 500 100 600
5-Jan 600 120 720
Where, Opening column is 0 when there is no previous closing and Value column would be the daily total. The closing would be the Opening value + Daily Total and this value would Opening value for next line. Please help me how to write the SQL statement to get this result from above table.
Thanks in Advanced,
AV
Hi
I have encountered some problems creating MDX query.
There are two input parameters on report, both dropdown list and from query.
The first parameter will check "State", and the second parameter "Store Name" depends on previous' result.
If there's no "State" found, I manage to show a "No Data" in the "State" droplist.
But with "No Data" in first parameter, the second parameter simply is not enabled.
I want the second parameter shows " No Data" as well but not succeed.
Here's code
...
SELECT {[Measures].[A1], [Measures].[B1], [Measures].[C1]} ON COLUMNS ,
[Store].[Store Name].ALLMEMBERS ON ROWS
FROM ( SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube])
...
The query need to look at dataset that contains [store name] on rows, and some measures on columns.
It also restricted by parameter @State.
Store Name that satisfies @State will be displayed.
But if nothing from SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube], there's nothing in result.
I'd rather like show something rather than nothing.
I try codes like
...
MEMBER [Store].[Store].[NA] AS '"N/A"'
IIF(<Parameter empty>,
(SELECT ... ON COLUMNS, [Store].[Store].[NA] ON ROWS FROM ...),
(SELECT ...<Original statement >)
)
...
It shows "Subselect support only Column axis".
Any one has an idea?
Please help me out
Many thanks!
Mr. L
.
Hi,
How can I make relations between two tables using query? Tables are already there with data.
Regards
If we use: select * from ..... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?
View 1 Replies View RelatedAny help would be really appreciated....
My stored procedure...
CREATE PROCEDURE business3rd7
@Fromdate DATETIME,
@ToDate DATETIME
AS
select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,
-------
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) end,
"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end end,
"Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate)
AND DateAdd(year, -1, @ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)
FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid
GO
Query plan...
----------------------Query Plan
|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))
The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...
I am currently stuck on how to make this 2 separate query work together, both work as i want them to individually, please note the syntax is according to a application i use that uses mysql to manipulate columns in an imported csv file.
CONCAT('at-',
REPLACE([CSV_COL(18)],'http://www.homebuy.co.uk/product.php/','')
)
removes last character i.e. /
SUBSTRING([CSV_COL(18)], 1, CHAR_LENGTH([CSV_COL(18)]) - 1)
basically i need these 2 to work together to give me an output like this
at-09fd8903
from the this. URL...url above minus the "" as i said both work on there own, but not together.
select no_dossier from dbo.membre where date_MAJ = GETDATE()select no_dossier from dbo.membre where date_MAJ = '2005-07-21'Both should give me the same result, yes or no?thanks
View 1 Replies View Relatedselect no_dossier from dbo.membre where date_MAJ = GETDATE()select no_dossier from dbo.membre where date_MAJ = '2005-07-21'Both should give me the same result, yes or no?thanks
View 11 Replies View RelatedI want to make a calculation to have the same return as the following SQL Query:
SELECT MAX(COLLECT_TOTAL)
FROM [dbo].[FACT_CAPACITY_COLLECT]
GROUP BY CI_NAME, FILESYSTEM_ID, SUBTYPE_ID, Month_DAY
How Can I do it?
I am going to make a distributed query, but in the query design view I cannot add the tables from another server, why?
View 3 Replies View Relatedi will try to explain what i want with an example
if have a query that returns
user code amount
user1 A 10
user1 B 100
user1 C 10
user2 B 50
user2 D 10
user3 A 10
user3 C 20
what i want in my report is the following as result
Sum of code A and C Sum of code B and D
user1 20 100
user2 60
user3 30
so can you make variables on the scope user that do a selection on the 'code'
in words like a variable that give me the sum of the amount where code in A and C in the scope user
HI, i work on Sql Developer Edition when i select for any Company name in arabic it does not get any data ,why? although my data are inserted by arabic language,please help me asap,thank youand my code like this use todayselect CatID from dbo.tbl_Categories where CatName like '%اتصالات%'
View 11 Replies View Relatedcreate table t1(a varchar(50) , b varchar(50))
create index i1 on t1(a)
create index i2 on t1(b)
create view v1
as
select * from t1 where isnull(a,b) = 'test'
select * from v1
The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ????
TIA
- ForXLDB
I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST">
<select name="ManagerID" id="manager" style="width:400px" class="form_textbox">
<?
$db_connect= mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $db_connect) || die(mysql_error());
$sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
[code]....
I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)
name language
-------------
ron english
ron french
ron spanish
andy english
andy hindi
kate english
Desired output
name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron english french spanish
andy english hindi
Kate english
Dear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help
thanks
I have 2 tables, one of those table is master formula which has some records like this :
Table1: MasterFormula
NoDescriptionFormula
1Occupancy
2Leased
3Rate Consumed
[Code] ....
Table2: TransactionFormula
Only has 2 fields, No & Amount
the formula fields means, we sum the Amount from TransactionFormula table for each No., for example if the formula is [3]+[8] means, we sum the Amount from No=3 added with the Amount from No=8 and we added records on TransactionFormula for row 9
Here's the example result for TransactionFormula records
NoAmount
1100
2100
310
43
510
610
720
810
920[3]+[8]
1020[6]+[8]
1140[7]+[9]
121.2[4]*[11]/100
134.58([11]+{12])/9
1445.78[11]+[12]+[13]
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
Thanks in for you help in advance
I want to use a Make Table Query to generate a new read-only Table. This I can do but my problem is that I want to be able to name the new Table containing only the records from a calendar year; e.y. Rents2001 and the next year another new table would be created and called Rents2002 and next year Rents2003 ...............
I require the Table to be generated yearly. I know I could do this in other ways but I really require the Table as once I have it I will be doing other things with it to give the final report.
Any suggestions how I can generate the Table with the YEAR being in the Table Name as part of running the Make Table Query? Thanks