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.
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.
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 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.
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?
Hi. Im 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. its sales, that works fine (because theres a column labeled sale thats 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? Ive tried converting the nulls to a NONE category, but that doesnt 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?
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),
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
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.
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
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?
Looking for sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun. Thus start day is last sun.
now the time is 22:00 & group by shop plus underline +24 hour format
Sun Mon Tue Wed Thur Fri Sat Sun Total Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx ------------------------------------------------------------------- Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx Total $30 $30 $10 $4 $20 $0 $no sales $xxx
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
We are trying to forcast product sales for next three months based on their sales for previous 12 months. In this case, Microsoft Time Series algorithm requires the sales data to be present for each product for past 12 months (???). However, our products have typical life span of 6 months and obviously the new products will not have sales before they were added. Any help will be very much appreciated.
If I stay at the Year level (say 2004), I get the Year To Date Sales for the selected Product(s)... However when I "zoom into" the Time dimension and select, say, Quarter 2, I get the Sales only for Quarter 2 of the selected Product(s). This is fine but at the same time I also want to see the Year To Date Sales of the selected Products. I hope my post is clear? Let me add a little example to clarify this further and will really appreciate your help:
Suppose I have selected ALL products and have selected Quarter 2 of 2004. I want the following results (Note it's really the YTD column that I am after):
Total Sales YTD Sales ----------- ---------- $ 50,000 $ 125,000
Again, I'll appreciate your help in writing the MDX for this.
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.
I know this is an easy one, but for some reason i keep gettin the wrong results. This displays something like this : Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams
But i want it to show all the SR_Name's ..like this: Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams NULL null Tom Jones NULL null Kim Jones NULL null Jon Travis
Any help will be grately appreciated! thanks!
Code Block ALTER PROCEDURE [dbo].[PROC_RPT_SR_DAILY_ACTIVITIES] (@Region_Key int=null, @Daily_activity_statistics_datetime datetime ) AS BEGIN
I have few more clarification regarding time series. Firstly In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year. Eg: 2006-01-01, 2006-02-01. etc. all in (yy-mm-dd) format But when I make prediction for next five months, though it makes monthly predictions the date part for the months are random whereas I expect the date part to be 1st of every month. What is the reason for this and how can I overcome it. Secondly Predicted sales values for some time period are negative though I do not have any negative value in the training data. What is the reason for this and how can I rectify it?
Thirdly In one of your earlier posts you had said that the time series algorithm does not have any built in time intelligence but uses the key time column as a time sequence stamp. So If have to make predictions for a particular time period where the time slice for each time period is 25 days or 50 days etc, then I understand that the input data used to train the model should be in the same time sequence. Or Can I specify the span of the time period according to which the prediction needs to be made? Basically how can I use the same time series model to make monthly, yearly, quarterly, daily or predictions or for custom time period like I have mentioned above.
I'm trying to make a parameter in SSRS that defaults to most recent date with sales. Below gives me all dates with sales. How can I get the results to give me just the most recent? I've tried MAX and LastChild in a couple different ways but no luck.
WITH MEMBER [Measures].[ParameterCaption] AS [Time].[Date].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Time].[Date].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Time].[Date].CURRENTMEMBER.LEVEL.ORDINAL SELECT
Having tough time putting together an resultset that lists all potential items sold, wether or not they were actually sold, for each customer class, and store. I have two tables. The first table has the actual item sales data. The second table a list of the commodities that could be sold.
Aim – Take the “One Month Figures”, look at the revenue start date, and calculate the sales by the “Contract Length”, broken down by the year in the “Revenue_State_Date”.
For Example
As the Revenue_State_Date starts on 01/01/2015, you have 12months of full trading in 2015, so you need to calculate 12 months of sales,
This needs to be spread out over the contract length
For eg YearSales Months 2015£2,914,033.3312 2016£2,914,033.3312 2017£2,914,033.3312 2018£2,914,033.3312 2018£1,457,016.676 Total£13,113,150.0054
My query is
SELECT [Total_Contract_Value__c] as TCV, [Contract_Term__c] As Contract_Length, [Revenue_Start_Date__c] as Revenue_State_Date, [Total_Contract_Value__c]/[Contract_Term__c] as One_Month_Sales FROM [FDMS].[Dan].[Raz_Reporting_LCS] where ID = '006a0000015qcGsAAI'
Which produces following results
TCV £13,113,150.00 Contract_Length 54 Revenue_State_Date01/01/2015 One Month Figures £242,836.11
How to create a stored procedure, or many stored procedures for generating a monthly sales report in our company.
We have two tables:
ITEM_SALES which consists of: Item_ID Name Store Sales_Date Sales_Price Quantity
And then
ITEM_DISCOUNT which consists of: Item_ID Name Store Sales_Price Date_From Date_To
Explanation: After each month, our different stores will send us a report on which discounts they had. These discounts can vary from one, to many days and therefor we have the Date_From, Date_to.
To make this sales report, i need a procedure that first fetches all sales in ITEM_SALES, then checks all the discounts in ITEM_DISCOUNT and overwrites the ones that have similar Item_ID, Name, and Store for a selected period.
Example: So if a item originally had a sales_price on 99,- and then had a discount sales_price to 79,- for 2014-01-02 to 2014-01-10 it has to be overwritten for that period so the report shows the right numbers.
I am making a stored procedure for monthly sales. In the stored procedure we have a Discount. This discount can be fetched from three different tables. If the discount is not in id.rabatt, it should fetch from dp.rabatt, if its not there, it should fetch from ds.rabatt. So the first two ones can be empty, while the last one always has a discount..
Im having big trouble designing the WHEN part of the procedure.
CASE ( when Isnull(id.rabatt, Isnull(u.rabatt, id.rabatt)) then.. when Isnull(dp.rabatt, Isnull(x.rabatt, id.rabatt)) then.. when Isnull(ds.rabatt, Isnull(y.rabatt, id.rabatt)) then.. end) AS 'Discount',
The reason i have to use Isnull is that inside each Discount table, i also have two different discounts, one that lasts forever(2999) and one that have a selected period. Like i show here:
LEFT OUTER JOIN discount AS id ON id.identifiers = isa.identifiers AND id.store = BV.name AND id.from_date <= isa.sales_date AND id.to_date >= isa.sales_date AND id.to_date < '2999-01-01' LEFT OUTER JOIN discount AS u ON u.identifiers = isa.identifiers AND u.to_date = '2999-01-01'
The two others tables are designed in similar ways
