Just a straight forward question: In the SQL SVR 2000 Northwind database, how do I display the top 5 salesman? I would like to display the salesmen and the total amount of their sales to date. I have this as a starting point:
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between '19970101' And '20051231'
It displays all the sales between the dates, I just need to work something that will total up the sales for each employee then list the top 5.
Above says it. I'm trying to select a random 5% of sales for each salesman for the last day serviced. I've got it all down, but I can't get it down to salesman. I'm using a basic select:
name, address, order, order_id, etc where order_id in (select top 5 percent order_id from MYSERVER group by salesman, order_ID order by newid())
The actual statement's much longer and has more where statements, but the pertinent info is above. All I can manage to do is create very long-winded ways to pick a random 5% of all sales.
i have a column name "Principle" and the data in this column is a dollar value. How do i write a query where when the dollar amount is <0 it displays as - and when its >0, it displays as +
I created a Time Dimension and ended up with the following (as shown in the attached image). The problem is that when I go to the Day level, I see numbers e.g. 1, 2, ..., 31. Instead I want to display the Weekday Name e.g. Monday, Tueday and so on (i.e. what we get with the datename(dw,<date>) function. Right?)
However I am not sure what to change and where in order to get the desired results. Can someone kindly guide me.
My table "Branch" contain a Field name as MassMail it contains mail IDs a@yahoo.com;b@yahoo.com;c@yahoo.com;d@yahoo.com;e@yahoo.com
Now plz write me what is the SQL Statement for Retiveing MassMail except one or Two Mail ID Example : Retrive a@yahoo.com;b@yahoo.com;d@yahoo.com;e@yahoo.com
I want to display the number of records for each state in a database. I'm using a strongly types dataset. In my method, I have the followingSELECT COUNT(ID) AS iTotal, STATEFROM membersWHERE (Suspend = 0)GROUP BY STATEORDER BY STATEIn the code behind of my page I have Dim mateAdapter As New WAPTableAdapters.membersTableAdapter Dim mates As WAP.membersDataTable Dim mate As WAP.membersRow mates = mateAdapter.GetDataState For Each mate In mates Select Case mate.STATE Case "AK" LabelAK.Text = mate.ID. End Select Next What should LabelAK.Text = mate.ID. be for me to be able to display the number of records that have 'AK' in the state field?Diane
Hai, My condition of the program is to check whether the username is available or not. If it is available, I should insert that username into the db, if not I should display "Username already exists". I've written SP for the condition, but I dunno how to display the error message in asp.net. I've my codings below: DB - SP:alter procedure insert_user@username varchar(20)asif exists(select username from test where username=@username)print 'Username already exists'elseinsert into test(username) values(@username) Default.aspx.cs: protected void Button1_Click(object sender, EventArgs e) { SqlConnection insertconn = new SqlConnection("Server=achuthakrishnan;Initial Catalog=classifieds;Integrated Security=SSPI"); SqlCommand insertcmd = new SqlCommand("insert_user", insertconn); insertconn.Open(); insertcmd.CommandType = CommandType.StoredProcedure; insertcmd.Parameters.AddWithValue("username",TextBox1.Text); SqlDataReader dr; dr=cmd.ExecuteReader(); insertconn.close(); } What is the condition I should check after SqlDataReader dr? Or else, I believe I should use @@error to display error messages in asp.net. I googled a lot and couldn't find the exact solution. Can any one help me?
I am JOINing a Table with a View, and the View can have multiple rows relating to the join condition(i.e. My view as 4 columns, and can look like the following: A B C D A E F G B H I J I want this to look like - A B C D E F G B H I J I want one row, but not one column, I need to maintain each value in its own column, withinin a single row. Doesn't matter to me if that transition happens in my View or as the outcome of my Join. I've been looking at the PIVOT command, but that appears to only work with an aggrgrate function. I don't want to calculate/aggreagte anything, just combine my multiple rows in one. This is my VIEW stmt - SELECT [PKG ID] AS PKG_ID, [APPR GRP NAME], [USRID OF APPR], [APPR STATUS]FROM dbo.pkgapproversWHERE ([APPR STATUS] = 'approved') AND ([APPR GRP TYPE] = 'E') This is my Join stmt - set @sql = N'SELECT DISTINCT [PKG ID] AS PKG_ID, [ELM ACT] AS Action, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment, [USRID OF APPR] FROM [PkgAction] FULL JOIN vw_PkgApprovals ON PkgAction.[PKG ID] = vw_PkgApprovals.[PKG_ID] WHERE 1=1'
I'm trying to follow along with lesson07 Web Development Express series and when I drag the SqlDataSource tool onto my form I do not see the object on the screen. I therefore can not configure it until I have placed GridView object onto my form. I hope you can help me in how to view the SqlDataSource or inform me where I can get further information
Hi, When I execute a script by using isql, it displays the number of statement executed. can anybody tell me how to turn this off (no display)? Thanks,
I want to know how to display only each of it where in my database for example the ProductItem got a lot of value of '1000' in it. i only want it to display once.
ProductItem | Name 1000 | ABC 1000 | DEF 1000 | HIJ 2000 | KLM 3000 | NOP
I want in my dropdownlistbox only display 1000, 2000, 3000. I using the 'Select ProductItem from Product' for sure it will display 1000,1000,1000,2000,3000. So to filter it.
Hello. I've been searching for a couple of hours online and can't quite seem to find what I'm looking for. I've found plenty of database administration programs, but that's not what I need.
I'm looking for a program (or online service or whatever) that has the ability to basically take any database I choose and display the information within on a web page in which others can access. Such as, if my company wants to take on a few other company's databases and have them log in to our server through the web to display information from their (and only their) database dynamically on the web, then this program allows me to set all of that up. Information could be formatted in tables, charts, etc., whatever the program is capable of. Is there such a thing and where can I find more information?
How do I display an item in a day? This is a featured product of the day. This will change everyday. I can do this in classic ASP but not in stored procedure.
Table: This is just to give you an idea of the table ID | ProductName | DatePosted ---------------------------------------- 1 | item1 | 5/1/2008 2 | item2 | 1/2/2008 3 | item3 | 6/2/2007
INSERT INTO @Months SELECT 'Jan' UNION ALL SELECT 'Feb' UNION ALL SELECT 'Mar' UNION ALL SELECT 'Apr' UNION ALL SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL SELECT 'Jul' UNION ALL SELECT 'Aug' UNION ALL SELECT 'Sep' UNION ALL SELECT 'Oct' UNION ALL SELECT 'Nov' UNION ALL SELECT 'Dec'
SELECT m.MonthName,ISNULL(CallOutCharge,0) As 'CallOutCharge' FROM @Months m LEFT JOIN (SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'Month', month(CC.CompletedDate) As 'intMonth', Year(CC.CompletedDate) As 'Year' FROM HSSPMS_Tbl_Callcentre_Compliants AS CC INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FT ON FT.ContractorCode = CC.ContractorCode And CC.FaultCleared='1' WHERE FT.CreatedBy=@OwnerId AND FT.IsDelete='0' And Year(CC.CompletedDate)=@Year Group BY CC.CompletedDate)t ON t.month=m.MonthName
This my Result
MonthName CallOutCharge intMonth -------------------- ------------- ----------- Jan 0 NULL Feb 0 NULL Mar 0 NULL Apr 0 NULL May 767 5 Jun 0 NULL Jul 0 NULL Aug 0 NULL Sep 0 NULL Oct 0 NULL Nov 0 NULL Dec 0 NULL
But i need to display
MonthName CallOutCharge intMonth -------------------- ------------- ----------- Jan 0 1 Feb 0 2 Mar 0 3 Apr 0 4 May 767 5 Jun 0 6 Jul 0 7 Aug 0 8 Sep 0 9 Oct 0 10 Nov 0 11 Dec 0 12
I have the following code <html> <head> <title>Eye Contact - Century to Century</title> <link rel="stylesheet" href="eye01.css" type="text/css" /> </head> <body> <form name ="" method = "post" action=""> <table width="100%" border="1" height ="85%"> <tr height = "15%"> <th>Date</th> <th>Year Left</th> <th>City Origin</th> <th>Country Origin</th> <th>Year Arrived</th> <th>City Arrived</th> <th>State Arrived</th> <th>Filename</th></tr>
<?php $db = mysql_connect("localhost","root",""); if (!$db) { print "error - Could not connect to MySQL"; exit; }
$er = mysql_select_db("touch_art1"); if(!$er) { print "error - Could not select the database"; exit; }
extract($_POST);
$query = "SELECT date, yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename FROM story WHERE country_origin = '$search_country' AND pamapproved = 'Y' ORDER BY yr_left"; $result = mysql_query($query);
How would I display just the date in the dataset if I have the data with date and time like this 2007-05-02 07:14:48.000 I want to truncate/or something to the time and just display the date. How would I change my query.
SELECT usr_end_date,date_exp,usr_last_name, usr_first_name, usr_login,employeeid, displayname FROM [OIM_FIX].[dbo].[OIM_USR] a, CORP_EMP_IDs b WHERE USR_login = CAST(b.employeeid AS varchar)and usr_end_date <> date_exp
currently it is returning the full date and time like this 2007-05-02 07:14:48.000 I want just the date like this 2007-05-02
I have a table in SQL Server 2000 with have date/time columns anddisplays the dates as : 1900-01-01 00:00:00.000 which is fine, but whenI select the column through dreamweaver for an asp page to date isshortened to 1900-01-01 but I need to see the whole date / time fieldfor conversion purposes.Can anyone help ?Thanks in advance.
Hello,I would like to query the top 5 best companies' sales (total sales),then total the rest, what is the quickest and effective SQL to queryit?Thanks in advance
HiI need to produce an excel output from SQL that showsemail - company name - producttype1, producttype2 (where the - denotesa change in column)basically i have a table the first line is the fields and the secondand third are the entriesemail - companyname - producttypeJoin Bytes! - xx - Product1Join Bytes! - xx - Product2
Hi, I have a table that I insert a member's country into every timesomeone signs up. What I'd like to do is pull information from the DBsuch that I can see each country and the number of users from each.For example:Argentina 10Brazil 5Canada 3I'm having trouble writing the SQL for this...any suggestions?Thanks,Erik
