SQL Server 2012 :: Find Trend How Employee Makes Sales Every Month?
Jun 7, 2015
How do I find sales trend of an employee via comparing current month and previous month sales?
I got so far query upto following,
;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,
[Code]....
I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Let us assume that there are 100 employee in a company. And sum of salary of all employee is 10000. Find list of highest paid employees whose sum of salary is 8000. Remaining employee will fall in 20% bracket.
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year], T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) , (SELECT SUM(Stores) FROM #ABC WHERE [Year] = T.[Year] AND [Month] = T.[Month]) AS [Sum_Stores], (SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.
I have a requirement in a payroll system to show the status of an employee on the first and last day of each pay period. The pay periods are pretty simple, 24 per year starting on the 1st and 16th of each month. The status effective date, however, can occur on any day, not just the first or last day of the pay period. Here's my DDL.
SET NOCOUNT ON; declare @EmployeeStatus TABLE ( [EmployeeID] [int] NOT NULL, [EmployeeStatus] [varchar](24) NOT NULL, [EffectiveDate] [date] NOT NULL
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),
Recently I've been stumbling with a way to properly produce a result that provides me a count of sales orders that fall in a range of sales order totals.
I want to take the Sales Order Total and then count how many times it falls into a specific bucket.
I've included the script where I've left with sample data so you can run in into a tempdb.
I hope to have the end result look like the below summary if you use my sample data.
Count Less Than 5K: 5 Count Greater Than 5K Less Than 10K: 0 Count Greater Than 10K Less Than 25K: 3 Count Greater Than 25K: 2
Here is the script to set up the tempdb
use tempdb go /* drop table #salesinvoice; drop table #salesinvoicedetail;
[Code] ....
Below is my query, I change it to perform a count on the summarized totals.
select si.OrderID as OrderNum ,sum(case when sid.LineAmount < 5000 then 1 else 0 end) as LT5K ,sum(case when sid.LineAmount >=5000 and sid.LineAmount < 10000 then 1 else 0 end) as GT5kLT10k ,sum(case when sid.LineAmount >=10000 and sid.LineAmount < 25000 then 1 else 0 end) as GT10kLT25k
Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins
Note:- also need records who doesn't have manager (null)
I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
--find day,month,year --for day select datediff(d,'01 may 2008',getdate()) -- --for month select datediff(m,'01 jun 2006',getdate()) -- --for year select datediff(year,'01 jun 2006',getdate()) above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively. but I want completely so I can use this in case of expired user in my project. User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize. thanks
I have sales from 2010 to today in my cube. my question is i want to see sales amount from 2010 to 2014 if i select 2014. or if I select 201405 ( may 2014), i want to see sales from 20100101 to 20140531. same with date. basically i want to to al the sales til the date/month /year selected. how can i achieve this?
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no, isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr', isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May', isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun', isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June .... 100 2012 100 $23 $30 $400 100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
I am trying to add month to a date. Here is my code
declare @CollectionDate date='10-28-2014' select @CollectionDate ;WITH CTemp AS ( SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1 UNION all
[Code] ....
It is working fine. But when I am giving date '10-30-2014' it shows me the error
Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string.
I can understand the problem that it is for the month of February. But How do I overcome the situation?
I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217' SELECT CONVERT(DATE, CAST(DATEPART(YEAR, IIF( --If we're at the start of a year --we'll need to go back a year DATEPART(MONTH, @Date) IN (1,2,3), DATEADD(YEAR, - 1, @Date), @Date )) AS VARCHAR(4)) + '0401')
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number , concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year , cast(fh.transaction_date as date) as transaction_date , sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
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?
