Sum Of Sales - Last Day Of The Month

Nov 15, 2014

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] .....

View 2 Replies


ADVERTISEMENT

Transact SQL :: Displaying Sales Data In A Month By Month Grid

Aug 11, 2015

Most of the data is in one table. 

Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264

I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.

 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January', 

View 2 Replies View Related

Top 5 Sales EACH Month

Jun 7, 2006

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

View 4 Replies View Related

Power Pivot :: Measure Returning Blank (Month Sales)

Jun 25, 2015

The following measure returns Month Sales: (blank)

Month Sales:=TotalMTD(Sum([Sales Amount]),'Date'[Full Date])

I have a relationship between ResellerSales-OrderDateKey and Date-DateKey

The relationship works fine when I use it in Access 

View 3 Replies View Related

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.

View 3 Replies View Related

Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

Nov 28, 2012

I have a sales tables which looks as below.

DEPARTMENT
Barnd_Name
Item_Group
     S_DATE
          S_AMOUNT
Administration
IBM

[code]....

Now i need Month Wise Running Totals.but i should check the following group as show below i that order

1) DEPARTMENT
1) Brand
3) Item Group
4) Month

View 12 Replies View Related

Variables And Grouping - Calculate Net Sales And Margin Percentage For Each Month Of Current Year

Mar 11, 2014

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.

SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------

DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)

SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL

[Code] .....

View 2 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

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.

View 2 Replies View Related

Transact SQL :: Establishing Unique Sales ID To Future Sales IDs From Original Sale

May 19, 2015

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?

View 4 Replies View Related

Report That Shows Customer And Total Sales Who Had Max Sales By Year?

Dec 9, 2014

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

View 1 Replies View Related

Power Pivot :: Calculate Sum Of Actual Sales Until Date And Forecast Sales After A Date?

Sep 30, 2015

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.

View 2 Replies View Related

Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row

Apr 5, 2008

Hello what I'd like to display the following in a matrix report:

Parameter selected: 3 (March), 2008 (Year)

Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...

So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.

View 3 Replies View Related

Total Sales By Customer Vs Total Sales

Nov 26, 2007



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.

View 3 Replies View Related

T-SQL (SS2K8) :: Get Last Record In A Month When No Guarantee Month Exists Of Unique Dates?

Apr 22, 2015

following table global_usage

ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01

[Code] .....

Now the problem,per month I need the most recent value so I'm expecting

ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01

I've tried a few thing like group,having even row_number() but I keep getting wrong results

View 6 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies View Related

Get Last Day Of Month For Agiven Month And Year

Aug 2, 2002

Does anyone know how I can get last day of month
if I pass a function a given month and and given year.
@Month = 2
@Year = 2004
The result I would need is 29 because there are 29 in
the month of February in the 2004.
Any help on this is greatly appreciated.
Kellie

View 1 Replies View Related

Calls Vs. Sales

Apr 25, 2005

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?

Thank you,
Richard

View 2 Replies View Related

The Top Sales Person?

Dec 26, 2006

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

View 4 Replies View Related

Top 5% Of Sales For Each Salesman

Aug 28, 2006

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.

View 7 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

Need Help With SQL Statement - How To Group Sales?

Apr 12, 2004

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.

View 1 Replies View Related

Help: Query To Get Total Last 7 Day Sales For Each Day

Aug 5, 2004

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

View 2 Replies View Related

Query Based On The Top MTD Sales

May 29, 2006

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

View 10 Replies View Related

T-SQL (SS2K8) :: Top 3 Sales Per SalesmanID

Dec 2, 2014

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:

View 7 Replies View Related

Tracking Sales Changes Over Time

May 15, 2008

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)

View 17 Replies View Related

7 Day Sales Analysis Query

Aug 31, 2014

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

View 12 Replies View Related

Show Salesperson If No Sales

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

All Sales Reported After Quarter End

Apr 19, 2007

It's been a long time since I've built tough queries and need some help.

I need to report all sales that happened within a quarter but were reported after the quarter end.

Quarters

Q1 - 1/1 thru 3/31
Q2 - 4/1 thru 6/30
Q3 - 7/1 thru 9/30
Q4 - 10/1 thru 12/31

Sample Data

ticketid salespersonid saledate sale_entereddate
1234 bsmith 1/1/2006 2/1/2006
1235 jgarcia 3/31/2006 4/1/2006
1236 bsmith 1/1/2006 2/3/2006
1237 jdoe 6/23/2006 7/1/2006
1238 bsmith 8/5/2006 8/6/2006
1239 bsmith 10/1/2006 1/1/2007

View 4 Replies View Related

Product Sales Forecasting.

Jul 13, 2006

Hi,

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.

Thanks

Riju

 

 

 

View 6 Replies View Related

MDX Question About Getting Year To Date Sales

Jun 8, 2004

Hi,

I have 2 dimensions Time and Product....

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.

View 3 Replies View Related

Sales Transaction Table Design

Dec 13, 2006

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!

View 2 Replies View Related

Need Help Qith SQL Query - How To Group Sales?

Apr 13, 2004

Hi,

I currently have a table whose structure 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.

[blue]CREATE TABLE [tbl_Product_Grouped_Sales] (
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[TotalQtySold] [float] ,
[NetSaleAmt] [float]
)



Thanks in advance for your help.

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved