Best Salesperson Of Each Shop
Nov 14, 2006
Hi!
How do I return best salesperson of every shop? Not working.. return all the best not one per shop. Thank you so much in advance!
SELECT SUM(Cd.Price) AS Sales, Shop.Name, Personnel.Name
FROM Purchase INNER JOIN
Personnel ON Purchase.Salesperson_id = Personnel.Personnel_id RIGHT OUTER JOIN
Shop ON Personnel.Work_id = Shop.Shop_id FULL OUTER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY Butik.Namn, Personnel.Name
ORDER BY Sales DESC
View 6 Replies
Nov 13, 2014
I have a salesman file and a sales history file.
I want to list all Salesman in the Salesman file along with there sales. If there is no record in the sales file I still want to see the salesman listed.
I've tried this but it doesn't seem to work. The code below is only listing salesman if they have sales in period 10.
select s.humres_id , sum(q.Sales) as Sales, sum(q.Cost)as cost
from arslmfil_SQL s left outer join QIVSalesMTDYTDCustSalesPerson Q on s.humres_id = q.slspsn_no
where Year=2014 and Period = 10
group by s.humres_id
View 7 Replies
View Related
Mar 13, 2005
i have an database create using webmatrix called online-shop, but when i want to use osql command to back up this database with the
backup database online-shop TO DISK = "c:onlineshop.bak"
then i got the error message said an incorrect syntax near "-"
but i can access this database with comand: osql -E -d online-shop
do i have to add some special string before "-"????? like in linux when you want to access and name with a space in it you have to add an "" before the space
View 4 Replies
View Related
Dec 11, 2007
We are relatively new to the setup, configuration and use of MS SQL. We are currently using MS SQL2000, Our sql application performance as become very slow. I am trying to find some basic "beginner" steps that I can take to improve performance and quicker lookups. Some of the things I have heard but I am not sure what they mean or how to do them are:
1) Locate SQL Temp files onto another drive, will this help and which/what temp files
2) should i put the mdf ldf files onto a separate drive?
3) Put Sql server program on a seperate drive?
4) our ldf file size is about 2GB is that OK any suggestions
Any other basic suggestions to improve performance and throughput is appreciated
www.accellus.com
View 4 Replies
View Related
Sep 1, 2014
I desire output by each hour of sales by different shop total with sales_datetime format is 20140831 22:30:xxx.
for example now is 23:05 then desire result is 23:00-23:05 of sale.
-----------------------------------------------------------------
select shopid,qty,amount from sales where sales_datetime>='hh:mm' and sales_datetime<='hh:mm' group by shop
View 4 Replies
View Related
Oct 28, 2014
We have 9 shop installed POS system and how to get the 10 shop of daily sales transaction by use of different shop IP Address? I don't want to repeat typing 9 time for change IP address as well.
select * from xsoheader inner join
xsodetail.memonum = xsoheader.memonum where xshopcode='%00*'
View 8 Replies
View Related
Jun 6, 2006
I downloaded MS Pet Shop 4.0 recently for best-practice training purposes. The installation went smoothly with a SQL Server 2005 backend. At first I had a problem authenticating the mspetshop4 user in the database, but that was solved by fixing some settings with the password policy. Now the mspetshop4 user is authenticated properly, but I came across this error instead:Server Error in '/Web' Application.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
Source Error:
Line 216:
Line 217: if (conn.State != ConnectionState.Open)
Line 218: conn.Open();
Line 219:
Line 220: cmd.Connection = conn;
Source File: C:Program FilesMicrosoft.NET Pet Shop 4.0DBUtilitySQLHelper.cs Line: 218
Stack Trace:
[SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +117
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +619
System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +224
System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +113
System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +59
System.Data.SqlClient.TdsParserStateObject.ReadByte() +36
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +181
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +56
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +1083
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +272
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +688
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +82
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +558
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +126
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +651
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +160
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +122
System.Data.SqlClient.SqlConnection.Open() +229
PetShop.DBUtility.SqlHelper.PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, String cmdText, SqlParameter[] cmdParms) in C:Program FilesMicrosoft.NET Pet Shop 4.0DBUtilitySQLHelper.cs:218
PetShop.DBUtility.SqlHelper.ExecuteReader(String connectionString, CommandType cmdType, String cmdText, SqlParameter[] commandParameters) in C:Program FilesMicrosoft.NET Pet Shop 4.0DBUtilitySQLHelper.cs:127
PetShop.SQLServerDAL.Category.GetCategories() in C:Program FilesMicrosoft.NET Pet Shop 4.0SQLServerCategory.cs:27
PetShop.BLL.Category.GetCategories() in C:Program FilesMicrosoft.NET Pet Shop 4.0BLLCategory.cs:20
PetShop.Web.NavigationControl.BindCategories() in c:Program FilesMicrosoft.NET Pet Shop 4.0WebControlsNavigationControl.ascx.cs:53
PetShop.Web.NavigationControl.Page_Load(Object sender, EventArgs e) in c:Program FilesMicrosoft.NET Pet Shop 4.0WebControlsNavigationControl.ascx.cs:27
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +31
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +68
System.Web.UI.Control.OnLoad(EventArgs e) +88
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Control.LoadRecursive() +158
System.Web.UI.BasePartialCachingControl.LoadRecursive() +61
System.Web.UI.Control.LoadRecursive() +158
System.Web.UI.Control.LoadRecursive() +158
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3035
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42Now I'm clueless. What does No process at the other end of the pipe mean?
View 2 Replies
View Related
Aug 28, 2014
How to edit this query to show the total by shop on time range ?
current result.(Time range from 9:00am-23:00pm)
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
BBB 11:59 $100 $50 +150 (+)
BBB 12:59 $100 $50 +50 (+)
------------------------------------------------------------------
Desired Result .
Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales Direction
Abc 10:59 $100 $50 +50 (+)
Abc 11:59 $100 $50 +150 (+)
Abc 12:59 $100 $50 +50 (+)
Total $300 $150 +$200 (+)
BBB 11:59 $10 $50 -40 (-)
BBB 12:59 $10 $50 -40 (-)
Total $20 $100 -80 (-)
-----------------------------------------------------------
select shop
,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span
,sum(case
when datediff(dd,yourdatetimefield,getdate())=0
[Code] .....
View 1 Replies
View Related
Mar 19, 2015
I am trying to create a table that would represent a workload for each shop. In order to do that I need to have WorkLoad table and ShopWorkLoad table which is actually just aggregation of WorkLoad.
WorkLoad contains a list of following items:
current orders that are in the process (one select statement)
scheduled orders (another select statement)
expected orders (third select statement) that come through a third-party system
All of this needs to be live. So, for example, as soon as order is added to Order table it should be included in WorkLoad if certain conditions are met. Same goes for scheduled orders (which come from another table). Expected orders will be loaded on a daily bases (based on historical data).
ShopWorkLoad table is aggregation of WorkLoad table.
Currently I did it this way:
Added after insert/update trigger on Order table: when order is created/updated, if it meets certain conditions, it should be inserted in WorkLoad, otherwise remove it from workload if it's in there and doesn't meet conditions
Added after insert/update trigger on Schedule table: when order is scheduled, if it meets certain conditions, it should be inserted in WorkLoad, otherwise remove it from workload if it's in there and doesn't meet conditions
Running daily job that populates WorkLoad table with expected orders based on historical values
Final step is to create an indexed view vShopWorkLoad
My biggest concern is usage of triggers which call pretty complex logic to determine whether item should be added to workload or not.
One other option was to create vWorkLoad view and somehow make it an indexed view but currently I don't see a way of doing that because the query consists of 4 union select statements, below is pseudo example. But even if doing it that way, how to build aggregated indexed view on top of vWorkLoad indexed view?
Third option is to use sql agent job which would run every x seconds (maybe 20) and it would execute all of these queries to populate WorkLoad table with delay of 10-20 seconds, but I am still not sure if this is acceptable to the client.
Fourth option is to create 3 or 4 indexed view where sum of them makes a workload. Then, ShopWorkLoad view would be built on top of these 3 or 4 indexed views, but in this case I don't know how this would affect performance since ShopWorkLoad query would be often queried.
Example of workload pseudo query:
select
WorkLoadType = 'Order in process',
OrderId,
ShopId,
...
from
Order
[Code] ....
View 1 Replies
View Related
