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*'
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
I have this small project, I have this report that have the total of order along with the date of the order
SELECT sf.ORDER_QNT, dd.ACTUAL_DATE, dd.MONTH_NUMBER
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;
ORDER_QNT ACTUAL_DATE MONTH_NUMBER
1100 05/01/13 1
100 05/01/13 1
140 06/01/13 1
110 07/01/13 1
200 08/01/13 1
500 08/01/13 1
230 08/01/13 1
500 08/01/13 1
200 08/01/13 1
53 15/01/13 1
53 22/01/13 1
Now, I want to get the average for that month (average per day).
SELECT sum(sf.ORDER_QNT)/31 as AVGPERDAY
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;
AVGPERDAY MONTH_NUMBER
---------- ------------
113.785714 1
but instead putting 31, I'd like to pull the totaldays from the actual_date using the Extract function so I try this
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(to_date('05/01/13','dd/mm/rr'))) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;
AVGPERDAY MONTH_NUMBER
---------- ------------
113.785714 1
The result is nice, but now when I change the date with the dd.actual_date it gives error
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;
Error at Command Line : 1 Column : 53
Error report -
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
Can't remember which is the best way....
Does transaction table have a PK?!
table details:
order_no - fk (from orders table)
company - fk (from orders table)
product fk (from stock table)
whse fk (from stock table)
qty
price
should i have a unique_no column so that there is a pk?!
Thanks!
I have a database that the transaction log grows about 1 GB per day. I would like this size was decreased daily. Does anyone have any suggestions?
Some friends told me that after the Full backup that is done daily, I should perform a backup of transanction log with option to truncate and after, make a shrink in the database. That is exactly what should be done?
Thank you,
Best Regards,
Ralph Haddad
Say I have one factSales table with the following columns
[Sales company]
[Item]
[Date]
[Price]
And another table with valid prices per sales company
[Sales company]
[Valid from]
[Valid to]
[Price] (calculated column)
I also have a calendar table. Is it possible to get the valid price from the prices table to the calculated column in the sales table?
Hello -
I have a database server with over 300 databases. I want that MS-SQL Server should daily report me the sizes of SQL databases along with Transaction log files by sending me an email on my address.
How can I do that. Does someone have any script which can help me to do that.
Any help will be appreciated.
Kind Regards,
Rubal Jain
For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.
She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:
- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.
- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.
- It looks unprofessional.
These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.
Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go
Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO
My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children:Â Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
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
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
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
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?
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] .....
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] ....
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. 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.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
Hi.
I’m having a conceptual problem with tracking sales vs. call center calls.
Each record in the fact table represents a call to the call center.
In this record are various facts and foreign keys that map to marketing campaigns, etc.
The product sold ID is NULL on no sale, and filled in for a sale.
When creating MDX to retrieve data by campaign, for example, to track the number of calls for a campaign vs. it’s sales, that works fine (because there’s a column labeled sale that’s either 0 or 1 for the sale and I just sum it). This way I get the conversion percentage of calls to sales.
But when creating MDX to track the product sold, as soon as I do a crossjoin on campaign and products sold, for example, I lose the total calls- the number of calls is the same as the sales (similar results as a SQL join). Am I doing something wrong or is it conceptually impossible to track this type of metric down to the product when there are NULLs in the fact table? I’ve tried converting the nulls to a NONE category, but that doesn’t stop the crossjoin from not giving me the desired results, plus I then have to filter out NONE as a product.
Is that why a lot of sample warehouses have a separate sales cube?
Thank you,
Richard
I need to get the sum of sales for the last date of each month group by custom and by month(fecha)
for example, for custom ='q' in month=8 I have 3 items in the last day of the month ='2014-08-15' totalling 13 and so on
DECLARE @sales TABLE
(custom VARCHAR(10) NOT NULL,
fecha DATE NOT NULL,
sales NUMERIC(10, 2) NOT NULL);
INSERT INTO @sales(custom, fecha, sales)
VALUES ('q', '20140708', 51),
[Code] .....
I have a table tblSales ( DollarAmount,DateSold, Barcode --- ) in SQL MSDE 2000.
What I want is the Top 5 sales for EACH month:
Month TotalSales Barcode
2006-05 Top01 Barcode01
2006-05 Top02 Barcode02
2006-05 Top03 Barcode03
2006-05 Top04 Barcode04
2006-05 Top05 Barcode05
2006-04 Top11 Barcode11
2006-04 Top12 Barcode12
2006-04 Top13 Barcode13
2006-04 Top14 Barcode14
2006-04 Top15 Barcode15
2006-03 Top21 Barcode21
2006-03 Top22 Barcode22
---- --- ----
--- ---- ---
TopNN is SUM(DollarAmount).
I created a table AAAA and use a while loop to insert the data into AAAA.
What I did is:
declare @StartDate and @EndDate, set @StartDate and @EndDate
Delete AAAA
Begin
While (@EndDate<GetDate())
Begin
Insert into AAAA(sales, Month, Barcode)
Select TOP 5 SUM(DollarAmount) AS sales,
LEFT(CONVERT(CHAR(8),DateSold,112),4)+'-'+RIGHT(LEFT(CONVERT(CHAR(8),DateSold,112),6),2) AS Month,
Barcode
from tblSales
where something
group by something
order by sales1 DESC
increase @StartDate and @EndDate by a month
End
Select * from AAAA
It works fine. My question is: Can I get rid of table AAAA?
How can I use T-SQL only to get the correct sets?
Thanks in advance.
Long
I am quite newbie, really grateful for some help how to create a sql sentence in Reporting Services that would return the best sales person from each shop.. The following returns all the sales persons from each shop
So how to select the top sales person from each shop?
SELECT TOP (10) shop.name, SUM(Cd.Price) AS Sales, Personal.Name AS Salesperson
FROM Purchase INNER JOIN
Personal
ON Purchase.Salesperson_id = Personal.Personal_id RIGHT OUTER JOIN shop
ON Personal.work_id = shop.shop_id FULL OUTER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY Shop.Name, Personal.Name
ORDER BY Sales DESC
Or something like this? But how in Rep.Services???
...LEFT OUTER JOIN (
SELECT P.work_id, P.Name, SUM(C.Price) AS TotalSale
FROM @Personal P
INNER JOIN @Purchase B
ON B.Salesperson_id = P.Personal_id
INNER JOIN @Cd C
ON C.Cd_id = B.Cd_id
GROUP BY P.Work_id, P.Name
) D
ON D.Work_id = S.Shop_id
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.
Hi,
I currently have a table whose DDL is as follows:
CREATE TABLE [tblSales] (
[OrderID] [int]
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[QtySold] [float] ,
[UnitPrice] [float] ,
[Discount] [float] ,
[GrossSaleAmount] NULL ,
[NetSaleAmount] [float]
)
The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION:
What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available
date is grouped/summed together.
CREATE TABLE [tbl_Product_Grouped_Sales] (
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[TotalQtySold] [float] ,
[NetSaleAmt] [float]
)
Thanks in advance for your help.
Let say I have this table in MS SQL server
table transaction(date,sales)
how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.
note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day
thanks
hello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group
Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...
Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...
Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: 1,2,3,4,5,6,7,8,...
Thank you
I ran the following but only received the top 3 sales.
with cte as (
select SALESMAN_ID,
ORDER_NO,
ORDER_TOTAL,
rank() over (partition by order_no
[Code] ....
results:
salesman_idorder_noorder_total
BF 9389037333.86
BF 8630094288.59
BF 8630813491.90
How can I get top 3 sales per salesmanid:
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.
SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table
My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.
This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:
1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.
I realize this task is difficult, but am I at least starting in the right direction?
Experts Only Please. (jk)