Let's say that I have three tables:
Buyer
------
ID
Name
Adress
Session
-------
ID
Date
Pageviews
Buyer
Orders
-------
ID
DatePaid
Session
Now, I've been racking my brain on how to list the Buyers and the number of related rows in the Orders table. Add to this that I only want to count the Orders where DatePaid IS NOT NULL.
Any help would be enourmously appriceated.
I have a table as follows:
Fixtures(ID, HomeTeam, AwayTeam, WeekNumber)
Each team plays alternately at home then away throughout the course of a season.
I want perform 2 seperate queries on this table.
Query 1:
I want to select a particular teams opposition for the entire season.
Query 2:
I want to select a particular teams opposition for a particular weekNumber.
Thanks
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is:
The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value
(and if they don't I don't want to find any post at all, of course)
something like this:
SELECT * FROM table1
WHERE ???
ex of table1:
Key | Item
----------
1 | 1
1 | 2
2 | 2
3 | 1
please help...
I have a table that keeps track of account access errors. When there are three access errors in one day, the account is locked out. How can i construct a query to select any accounts that have three access errors on the same date. The pertinent fields would be ACCOUNTNUMBER AND ERRORDATE.
View 1 Replies View RelatedI have a feeling it is very easy to do what I want. But I don't know how.
I have 2 queries that return 2 results sets. I'd like to have just 1 query
that returns 1 result set that contains all the data of the 2 results sets.
Example.
Query 1 returns
Item Expected
--------------------
Lion 2
Tiger 2
Bear 2
Query 2 returns
Item Actual
-------------------
Lion 1
Bear 1
What I want is 1 query that will combine the results
Item Expected Actual
-------------------------------
Lion 2 1
Tiger 2 0
Bear 2 1
I tried using a unions between the 2 queries but that doesn't work.
I am pulling my hair out. I have been struggling with this for several
days now. Any help would be greatly appreciated.
Thanks
Josh
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is:
The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value
(and if they don't I don't want to find any post at all, of course)
something like this:
SELECT * FROM table1
WHERE ???
ex of table1:
Key | Item
----------
1 | 1
1 | 2
2 | 2
3 | 1
please help...
Hi,
I need to return a number of records at specifik days, i do it with this query;
SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS Days, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10))
AS Numbers_total, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_In
FROM tb_SecurityLog
WHERE (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(SL_PolicyName LIKE N'%')
GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
ORDER BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
i also need to have a criteria at that second COUNT and if the criteria is not met that row should not be counted, is this possible at all?
//Mr
I have a data table like this:
MachineIDProductSales
-------------------------------
1Magazine$20.00
1Drink$30.00
2Drink$30.00
3Magazine$30.00
3Drink$40.00
4Magazine$30.00
5Food$40.00
5Drink$30.00
6Drink$40.00
One of the reports the user needs to see looks like this:
ProductNumber of MachinesTotal Sales
Magazine/Drink2$120.00
Drink2$70.00
Magazine1$30.00
Food/Drink1$70.00
To clarify:
There are two magazine/drink machines (ID 1 and 3)
There are two drink only machines (ID 2 and 6)
There is one magazine only machine (ID 4)
There is one food and drink machine (ID 5)
How do I do this query?
Ideally, I wouldn't limit the number of products in a given machine, but I can do so if necessary.
I'm using SQL Server 2000 so I can't use the newer PIVOT/UNPIVOT functions in SQL Server 2005.
Here is some setup T-SQL code:
CREATE TABLE SalesData
(
MachineIDINTEGER,
ProductNameVARCHAR(50),
SalesMONEY
)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Magazine', 20)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (2, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Drink', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (4, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Food', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (6, 'Drink', 40)
Of course, this is a much simplified version of the real business problem I'm facing. Any help is greatly appreciated. Thanks!
Hello
I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).
Example:
(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <--- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <--- Would pass
(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <--- Would not pass
How can I accomplish that?
Thanks a lot.
I have 2 tables joined together by the IDs, People and the pets theyownPEOPLEID NAME1 JohnSMith2 JaneDoePETSID PET1 Dog2 Cat2 Hamster2 Hamster2 FishI have create another where the PETS are in one column separated bysemi-colons and removing the dupsNEW TABLEID NAME ALLPETS1 JohnSmith Dog2 JaneDoe Cat;Hamster;FishWhat is the best way to do it? The only way I can think of is to runan update where it checks to see if the value already existsTHanks!
View 4 Replies View RelatedI'm self-taught at SQL, so this may be an easy one for others, but I can't even figure out how to search for an answer.
I need to put together a query as a datasource for a chart showing the firm's top ten clients by revenue AND the top ten clients by hours worked. It's easy to do either query separately, but the problem comes in when the two are combined. Then top ten by revenue doesn't always include all the top ten by hours clients, and vice versa [at the moment, I'm running a top twenty for each, then hand-compiling the top ten in Excel--oy!].
How can I write a query that will guarantee to include the top ten of both revenue and hours lists?
Thanks,
elinde
Hi All,
We've a table which has about 1.5 mil records.
The table has info like AccountNum FName LName, Flag, Address etc.
There are duplicate Account Numbers.
What we're trying to accomplish is:
If I query the table as in the following,
SELECT AccountNum, Flag, COUNT(*) AS CountStar FROM Table1
GROUP BY AccountNum, Flag
HAVING COUNT(*) > 1
I'll get something like this:
AccountNum Flag CountStar
1234567 Y 2
9876543 Y 4
9184382 Y 3
7439831 Y 5
6958373 Y 4
....... . .
....... . . etc..
First, I want to display the result as in the following:
AccountNum Flag
1234567 Y (along with other columns)
1234567 Y
9876543 Y
9876543 Y
9876543 Y
9876543 Y
9184382 Y
9184382 Y
9184382 Y
....... .
....... . etc...
Is it possible?
Once I've the result in the above format, the next step in plan is to update the flag with 'N' leaving the first occurrence flag as 'Y' but all others as 'N' for a particular AccountNum.
Once I do this, the result should look like the following:
AccountNum Flag
1234567 Y (along with other columns)
1234567 N
9876543 Y
9876543 N
9876543 N
9876543 N
9184382 Y
9184382 N
9184382 N
....... .
....... . etc...
Can anybody suggest any ideas how to accomplish this?
Thanks much,
Siva.
I'm having much difficulty figuring out how to write the followingquery. Please help!I have this table:EventEventId int Primary KeyPatientId intSeverityLevel intWhat I want returned in my query is a list of all (distinct)PatientIds appearing in Event, with the *most severe* EventId returnedfor each Patient. The higher the value of SeverityLevel, the moresevere that Event is considered to be.The problem I am having is that I can't figure out how to (a) group byPatientId, AND (b) return the EventId of the highest-severity Eventfor *each* PatientId (Order By SeverityLevel Desc).So if my table contained:EventId PatientId SeverityLevel------- --------- -------------1 1 02 1 13 1 54 2 55 2 2I would want my result set to be:PatientId EventId--------- -------1 32 4since events 3 and 4 are the most severe events for patients 1 and 2,respectively.Any help would be greatly appreciated. This seems to be something thatcould be handled easily with a FIRST() aggregate operator (as in MSAccess) but this is apparently lacking in SQL Server. Also note theremay be multiple Events with a given PatientId and SeverityLevel, inthat case I'd want only one of the EventIds (the Max() one).Many thanks,Joel ThorntonDeveloper, Total Living Choices<joelt@tlchoices.com>(206) 709-2801 x24
View 7 Replies View RelatedHello, I'm trying to find the most optimal way to perform a trickyquery. I'm hoping this is some sort of standard problem that has beensolved before, but I'm not finding anything too useful so far. I havea solution that works (using subqueries), but is pretty slow.Assume I have two tables:[Item]ItemID int (Primary Key)ItemSourceID intItemUniversalKey uniqueidentifierPrice int[Source]ItemSourceIDPriorityI'm looking for a set of ItemIDs that match a query to the Price(something like Price < 30), with a unique ItemUniversalKey, taking thefirst item with each key according to Source.Priority.So, given Item rows like this:1 2 [key_one] 152 2 [key_two] 253 1 [key_one] 15and Source rows like this:1 12 2I want results like this:2 2 [key_two] 253 1 [key_one] 15Row 1 in Item would be eliminated because it shares an ItemUniversalKeywith row 3, and row 3's Source.Priority is lower than row 1.Help!?
View 5 Replies View RelatedI have a SQL7 database that was installed as case-insensitive./* Sort Order = 52, Case-insensitive dictionary sort order. */This database contains a table that has a varchar column which containsdata such as:'JUDICIARY; EDUCATION; Subcommittee on Justice and Judiciary''Subcommittee on Justice and Judiciary; TRANSPORTATION''Subcommittee on Cities; JUDICIARY; TRANSPORTATION'I want to write a SELECT statement that gives me only those rows (1stand 3rd) that have JUDICIARY (not Judiciary) in the varchar column.This is SQL7 so I can't use COLLATE.I triedSELECT mycolFROM mytableWHERE mycol LIKE '%JUDICIARY%'AND CAST(SUBSTRING(mycol ,PATINDEX('%JUDICIARY%',mycol),LEN('JUDICIARY')) AS VARBINARY) = CAST('JUDICIARY' AS VARBINARY)But this leaves out the row with JUDICIARY and Judiciary in it (onlyreturns 3rd row).Any suggestions?
View 3 Replies View RelatedI need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Many thanks,
Leonard
Hi,
My tables:
Product
- productID
- name
- price
Inventory
- productID
- stockCount
- timestamp
So each day the Inventory table has a new row for each productID with its stock count.
How can I create a report for the total products sold from one day to another? Or from what a dateStart from a dateEnd (i.e. a range)
Example:
ProductID StockCount TimeStamp
1 10 2008/03/07
1 7 2008/03/08
So you can see that 3 products were sold in the last day.
Having problem completing this query. I have a list of items. Some items need to be grouped by a list, some by a range. I was thinking of useing two tables, one for the items, and one for the groups. The groups would have something like groupid, title, listnumbers, rangelow, and rangehigh. The tables are in sql database. For example:
The list of items numbers are 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20. In the group table, it would be listed like this:
G1, Group1, 1,,
G1, Group1, 6,,
G2, Group2, ,2,5
G3, Group3,6,18,20
G3, Group3,7,15,17
G3, Group3,8,14,16
G3, Group3,9,11,13
In this example, Group1 is a list, group2 is a range, and group3 is a list of ranges. I can make a query that pulls all the items just in the groups:
SELECT ECC_ITEMS.NBR, Group.Group_Name, Group.Title, ECC_ITEMS.DESCR, ECC_ITEMS.REG_PRC
FROM Group, ECC_ITEMS
WHERE ECC_ITEMS.NBR Between Group.RangeLow And Group.RangeHigh Or ECC_ITEMS.NBR=Group.GroupItems
Now, I am not sure how to put the rest of the items (the ones that aren't in a group) in that query. I was thinking on making a union and the second query being a unmatched query. Not sure how to make it were that query is "unmatched" with a table in the same query. And ideas on how to make the second part of the union query?
in mysql, we can have
select * from test where date like '%-02-01'
all result that ends with -02-01 will be displayed.
however, if i want to do it in ms sql, may i know what's the syntax for this select....like ?
Thanks.
Code:
Select id from tbl_account in Game_User DB
id is binary data
USE Billing
INSERT INTO tblUser (userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('test','1','1','9','6') where userId = id from tbl_account
and
INSERT INTO tblUserInfo (userNumber,userId,cpId,userTypeId,userStatusId,gameServiceId) VALUES ('123','test','1','1','9','6') where userNumber = userNumber and userId = userId from tblUser
Im new when it comes to making SQL queries, so i need help badly.
Basically what i need the query to do is call from a DB "User" id where the id is in Binary data. from that i need it to insert into 2 other tables in the Billing DB.
the UserId needs to be the same in all areas, and when it inserts into tblUserInfo it has to pull from userNumber from tblUser after the rows are entered so that the userNumber in tblUser and tblUserInfo are the same.
Hopefully you can help. if any further information is needed please just ask and ill try to give as much as i know.
Thanks
David
i am familiar with php mySql combo, but from now on the boss wants me on MS SQL. i have a little query sender that used mySql and i tried to switch it over to use mssql_functions like:
php Code:
Original
- php Code
mssql_connect($host,$user,$password);
mssql_select_db($_POST['database']);
mssql_query($cxn,$_POST['query']);
mssql_num_rows($result) == 0;
mssql_connect($host,$user,$password);mssql_select_db($_POST['database']);mssql_query($cxn,$_POST['query']);mssql_num_rows($result) == 0;
When i run my script i get a blank screen. I am already bent out of shape trying to switch from mySql to this. any help would be appreciated. here is my code...
php Code:
Original
- php Code
<?php
/*Program: mssql_send.php
*Desc: PHP program that sends an SQL query to the
* MS SQL server and displays the results.
*/
echo "<html>
<head><title>MSSQL Query Sender</title></head>
<body>";
if(ini_get("magic_quotes_gpc") == "1")
{
$_POST['query'] = stripslashes($_POST['query']);
}
$host="yourhost";
$user="you";
$password="example";
/* Section that executes query and displays the results */
if(!empty($_POST['form']))
{
$cxn = mssql_connect($host,$user,$password);
mssql_select_db($_POST['database']);
$result = mssql_query($cxn,$_POST['query']);
echo "Database Selected: <b>{$_POST['database']}</b><br>
Query: <b>{$_POST['query']}</b>
<h3>Results</h3><hr>";
if($result == false)
{
echo "<h4>Error!</h4>";
}
elseif(mssql_num_rows($result) == 0)
{
echo "<h4>Query completed.
No results returned.</h4>";
}
else
{
/* Display results */
echo "<table border='1'><thead><tr>";
$finfo = mssql_fetch_field($result);
foreach($finfo as $field)
{
echo "<th>".$field->name."</th>";
}
echo "</tr></thead>
<tbody>";
for ($i=0;$i < mssql_num_rows($result);$i++)
{
echo "<tr>";
$row = mssql_fetch_row($result);
foreach($row as $value)
{
echo "<td>".$value."</td>";
}
echo "</tr>";
}
echo "</tbody></table>";
}
/* Display form with only buttons after results */
$query = str_replace("'","%&%",$_POST['query']);
echo "<hr><br>
<form action='{$_SERVER['PHP_SELF']}' method='POST'>
<input type='hidden' name='query' value='$query'>
<input type='hidden' name='database'
value={$_POST['database']}>
<input type='submit' name='queryButton'
value='New Query'>
<input type='submit' name='queryButton'
value='Edit Query'>
</form>";
exit();
}
/* Displays form for query input */
if (@$_POST['queryButton'] != "Edit Query")
{
$query = " ";
}
else
{
$query = str_replace("%&%","'",$_POST['query']);
}
?>
<form action="<?php echo $_SERVER['PHP_SELF'] ?>"
method="POST">
<table>
<tr><td style='text-align: right; font-weight: bold'>
Type in database name</td>
<td><input type="text" name="database"
value=<?php echo @$_POST['database'] ?> ></td>
</tr>
<tr><td style='text-align: right; font-weight: bold'
valign="top">Type in SQL query</td>
<td><textarea name="query" cols="60"
rows="10"><?php echo $query ?></textarea></td>
</tr>
<tr><td colspan="2" style='text-align: center'>
<input type="submit" value="Submit Query"></td>
</tr>
</table>
<input type="hidden" name="form" value="yes">
</form>
</body></html>
<?php/*Program: mssql_send.php *Desc: PHP program that sends an SQL query to the * MS SQL server and displays the results. */echo "<html> <head><title>MSSQL Query Sender</title></head> <body>";if(ini_get("magic_quotes_gpc") == "1"){ $_POST['query'] = stripslashes($_POST['query']);}$host="yourhost";$user="you";$password="example"; /* Section that executes query and displays the results */if(!empty($_POST['form'])){ $cxn = mssql_connect($host,$user,$password); mssql_select_db($_POST['database']); $result = mssql_query($cxn,$_POST['query']); echo "Database Selected: <b>{$_POST['database']}</b><br> Query: <b>{$_POST['query']}</b> <h3>Results</h3><hr>"; if($result == false) { echo "<h4>Error!</h4>"; } elseif(mssql_num_rows($result) == 0) { echo "<h4>Query completed. No results returned.</h4>"; } else { /* Display results */ echo "<table border='1'><thead><tr>"; $finfo = mssql_fetch_field($result); foreach($finfo as $field) { echo "<th>".$field->name."</th>"; } echo "</tr></thead> <tbody>"; for ($i=0;$i < mssql_num_rows($result);$i++) { echo "<tr>"; $row = mssql_fetch_row($result); foreach($row as $value) { echo "<td>".$value."</td>"; } echo "</tr>"; } echo "</tbody></table>"; } /* Display form with only buttons after results */ $query = str_replace("'","%&%",$_POST['query']); echo "<hr><br> <form action='{$_SERVER['PHP_SELF']}' method='POST'> <input type='hidden' name='query' value='$query'> <input type='hidden' name='database' value={$_POST['database']}> <input type='submit' name='queryButton' value='New Query'> <input type='submit' name='queryButton' value='Edit Query'> </form>"; exit();} /* Displays form for query input */if (@$_POST['queryButton'] != "Edit Query"){ $query = " ";}else{ $query = str_replace("%&%","'",$_POST['query']);}?><form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="POST"><table> <tr><td style='text-align: right; font-weight: bold'> Type in database name</td> <td><input type="text" name="database" value=<?php echo @$_POST['database'] ?> ></td> </tr> <tr><td style='text-align: right; font-weight: bold' valign="top">Type in SQL query</td> <td><textarea name="query" cols="60" rows="10"><?php echo $query ?></textarea></td> </tr> <tr><td colspan="2" style='text-align: center'> <input type="submit" value="Submit Query"></td> </tr></table><input type="hidden" name="form" value="yes"></form> </body></html>
What would the MySQL equivalent for "SHOW TABLES" and "SHOW FIELDS" be in MSSQL?
View 8 Replies View RelatedI have this database running (ignore that that was done in Access, this is being made in Microsoft SQL Server 2005).
What I need to do is if you look at the tbl_events table and the tbl_timekeeperDetails table I need to make a query that:
Lists the names of all timekeepers (whether they are booked for a meeting or not), and the meetings at which they are timekeeping.
The tricky part of this is getting the query to show the timekeepers who aren't assigned to an event.
I have two seperate querys so far, but I'm presuming there must be a way of merging them or something.
I have this code so far:
select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast,eventID
from timekeeperDetails,events
where timekeeperDetails.timekeeperID = events.timekeeperID
select timekeeperTitle,timekeeperNameFirst,timekeeperNameLast
from timekeeperDetails
If anyone has any ideas, please do post a reply or email me at paul [at] abscond [dot] org
It would be very much appreciated.
im managing most queries without any problems (im converting from access to mssql) but this one is causing me grief - how do i put this into mssql ?
SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead, dbo_Lead.DateOfLead, dbo_Mortgage.MortgageAppSubmitted, dbo_Mortgage.MortgageOfferedAccepted, dbo_Mortgage.MortgageDrawndown, dbo_Mortgage.MortgageApplicationClosed,
[dbo_Mortgage.MortgageCommissionAnticipated]+[dbo_Life.LifeCommissionAnticipated]+[dbo_BuildingsAndContents.BandCCommissionAnticipated]+[dbo_OtherBusiness.OtherBusinessCommissionAnticipated] AS Expr1,
[dbo_commissions.MortgageCommissionReceived]+[dbo_commissions.LifeCommissionReceived]+[dbo_commissions.BandCCommissionReceived]+[dbo_commissions.OtherBusinessCommissionReceived] AS Expr2
, IIf([Expr1]<1000,[Expr1]*0.3,IIf([Expr1]<2000,[Expr1]*0.4,[Expr1]*0.5)) AS Expr3
, IIf([Expr2]<1000,[Expr2]*0.3,IIf([Expr2]<2000,[Expr1]*0.4,[Expr2]*0.5)) AS Expr4
FROM (((((dbo_Personal INNER JOIN dbo_Lead ON dbo_Personal.ID = dbo_Lead.ID) LEFT JOIN dbo_Mortgage ON dbo_Personal.ID = dbo_Mortgage.ID) LEFT JOIN dbo_OtherBusiness ON dbo_Personal.ID = dbo_OtherBusiness.ID) LEFT JOIN dbo_BuildingsAndContents ON dbo_Personal.ID = dbo_BuildingsAndContents.ID) LEFT JOIN dbo_Commissions ON dbo_Personal.ID = dbo_Commissions.ID) LEFT JOIN dbo_Life ON dbo_Personal.ID = dbo_Life.ID
WHERE (((dbo_Lead.SourceOfLead) Like "Solutions*"));
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
Hi
I have a mysql query in my php script like
UNIX_TIMESTAMP() - UNIX_TIMESTAMP(sessioncreated) as sessionspan .
What is the equivalent of above query in mssql. I need the same query in mssql. Is there any function that does the same action in mssql2000.
Thanks in Advance.
stranger
I have 3 tables, that appear as follows (insignificant fields are not mentioned for brevity):
RETAIL(code, CurrentLocation) ~ 2.6 million records
LOCAUDIT(code, Date, Time, Location) ~ 3.6 million records
STAFF(ID, NAME) ~ 40K records
Each record in the RETAIL table represents a document. The LOCAUDIT table maintains history information for documents: locations they've been to. A location can be represented by a staff (from STAFF table), or an unlimited range of different names - not enumerated in a table.
The query we run tries to find the currentlocation for each document in the RETAIL table (if any). Since a document may have been to many location, I'm interested in the last location which has the max Date,Time.
To perform the query, I created two views:
HISTORY
=======
CREATE VIEW HISTORY
AS
SELECT CODE, "DATE", TIME, CAST("DATE" + ' ' + TIME AS datetime) AS UpdateDateTime, LOCATION
FROM LOCAUDIT
LASTHISTORY
==========
CREATE VIEW LASTHISTORY
AS
SELECT CODE, Max(UpdateDateTime) AS LastUpdated
FROM HISTORY
GROUP BY CODE
UPDATE RETAIL
SET CURRENTLOCATION = (CASE WHEN t3.NAME IS NULL THEN t2.LOCATION ELSE t3.NAME END)
FROM RETAIL AS t4
LEFT JOIN LASTHISTORY AS t1 ON (t4.CODE = t1.CODE)
LEFT JOIN HISTORY AS t2 ON (t1.ITEM = t2.ITEM AND t1.LastUpdated = t2.UpdateDateTime)
LEFT JOIN STAFF AS t3 ON (t2.LOCATION = t3.ID)
What the query does is update the current location of each document. If the current location is a staff, we find the name of the staff member (hence the case).
In addition to clustered indexes on the primary keys, I've also created an index on (Code, Date, Time) on LOCAUDIT.
However, the query still seems to take up to 3 hours sometimes to run on a server with 4 CPU's and a whole bunch of memory. Can anyone suggest some way to improve this, add more effective indexes, or rewrite the queries all together. Any help is appreciated..
I need to use mssql to create a ranking of some kind. This is the situation:
I need to assign position to a list of students based on thier scores. e.g
Student Score Position
StudentA 56 4
StudentB 78 1
StudentC 66 2
StudentD 56 4
I need to create the positions based on the scores of the ctudents.
I will appreciate any assistance.
Thank you.
Heya, I'm stuck on this query, we are currently moving from mysql to mssql, and this one has stumped me.
I used to use CONCAT, and i thought i'd found the correct syntax for MSSQL but it fails when used.
Code:
$sql = " SELECT *
FROM ol_user_2_group ug
INNER JOIN ol_users_details ud
ON ug.user_id = ud.user_id
INNER JOIN ol_user_rank ur
ON ug.user_id = ur.user_id
WHERE ug.group_id= ".$group_id."
AND ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
ud.firstname + ' ' + ud.lastname AS fullname LIKE '%".$search."%'";
is the part that fails with
Code:
'Incorrect syntax near the keyword 'AS'.
can anyone tell me what im doing wrong?
thanks in advance
Hi,
I got a big problem. I try to run a query on ms query analyzer or my .net-app, but in both cause, it wouldn't work. when i run the query in ms query analyzer it returns me this error-message:
Der aktuelle Zeilenwert der [verkauf_online]..[hs].[std_ftext].text-Spalte konnte nicht vom OLE DB-Provider 'MSDASQL' gelesen werden.
[OLE/DB provider returned message: Die angeforderte Konvertierung wird nicht unterstützt.]
OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
sorry, its in german, i know but the most important fact is, that a conversion failed. i tried to read a text-field from a sybase asa-8-database which is linked with a ms sql-server 2000. I tried to find out more about the error-messe an the error-code (x80040e1d) but i couldn't find anything helpful.
Now, I just hope some of you can maybe help me.
Is there a way to do a SELECT TOP # using a variable for the #?
In other words I'm doing a SELECT TOP 50* FROM DATATABLE
If I pass an @value for the number
SELECT TOP @value* FROM DATATABLE doesn't work
I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.
Thanks in advance.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
View 5 Replies View RelatedHi,
How can you get the Query Statement of the last executed SQL command.
I am not quite sure but I do remember coming across such a command ( maybe an undocumented one).
Thanks for your help and pointers.
My Best wishes for the new year to all the folks in the forum. Wishing you greater days ahead.
Warm Regards,
Ranjit S Hans.
---------------------------------------------------------------------
Everywhere is a walking distance if you have the time - Steven Wright