Need Help With Query -- Sales Data By Customer.GroupCode In Monthly Columns

Jan 22, 2008

I'm trying to develop a query that provides sales data by Customer.GroupCode in monthly columns as depicted below:

GrpCd JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOT
Film 5,000 15,000 20,000
Aero
Elct 3,000 950 3,950
Desg
Edu 150 150

Here€™s a simplified version of the DDL:
CREATE TABLE invchead (
invoicenum int NULL ,
invoicedate datetime NULL ,
invoiceamt decimal(16, 2) NULL ,
custnum int NULL )

CREATE TABLE customer (
custnum int NULL ,
groupcode varchar (4) NULL )

The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.

SELECT distinct
c.Name,
c.GroupCode,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB,
......
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT

FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum)
WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'

Grateful for any advice that will get me closer to accomplishing this.

Thanks

View 4 Replies


ADVERTISEMENT

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

SQL Server 2008 :: MDX Query Filtering - Return Sales For Each Customer For Past 6 Weeks

Feb 4, 2015

I'm having problems filtering in my MDX query.

My requirements are to return the sales for each customer for each store, for the past 6 weeks.

The catch is that I only want those customers which have had sales over 10,000 within the last week.

This is my query:

WITH SET [CustomerList] AS
FILTER(
([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]),
[Measures].[Sales] >= 10000
AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]

[Code] ....

The dates are hard-coded for testing purposes.

View 0 Replies View Related

Stored Procedure For Monthly Sales With Discount

Mar 6, 2014

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.

View 6 Replies View Related

Monthly Sales - Three Discounts In Stored Procedure

Jun 25, 2014

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

View 1 Replies View Related

Getting Daily Average Of Sales From Monthly Report?

Oct 9, 2014

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"

View 1 Replies View Related

How To Write Such A Stored Procedure To Return Monthly Sales?

Mar 8, 2005

I have a Orders and OrderDetails table having the columns listed below:

Orders (OrderID, OrderDate, CustomerID, ...)
OrderDetails (OrderID, ProductID, UnitPrice, Quantity, ...)

Now, I want to obtain monthly sales from the data in the two tables by passing in a Year parameter. How to develop such a stored procedure? I have no idea where to get started. I was thinking to call a SELECT statement on each month. But, things trouble me are:

1. how to loop through each month to make a SELECT query for each month for a given Year? Use a cursor or what?
2. how to determine month boundary for a given year and construct the where clause on OrderDate using the month boundary for the SELECT query ? What happens if it is a leap year?
3. how to stop processing for the rest of the year when last order month is done?

Any suggestion would be appreciated. Thanks.

View 1 Replies View Related

SQL Server 2008 :: Find Locations With MAX Sales Amount Per Customer

Feb 13, 2015

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.

CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250

I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.

CustLoc
A35
B15

If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.

View 2 Replies View Related

Analysis :: Sales Fact Table - Customer Who Didn't Buy Top 10 Products

Nov 3, 2015

We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.

We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?

View 4 Replies View Related

Power Pivot :: First Sales Date By Customer For Specific Product Only

Dec 21, 2012

I have a sales order table that has customer, sales, product and sales date.  I am looking to return the first sales date by customer for a specific product.

SalesTable:

Customer
Product
Sales Date
Sales
First Bike Date

Jon
Bike
12/4/2011
 $10.00

[Code] ....

I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer.  Can I use function to complete?  I have a datedim table as well related to the sales date.

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

DTS Fails At Customer Site With Too Many Columns, Works Locally

Nov 16, 2004

I am having the most baffling problem with DTS.... :confused:

I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0

Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000

The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.

The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.

If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.

View 6 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

Query To Get Customer With Same Set Of Loans

May 16, 2014

I have this table below with customers having loans. Nou I want to write a query which retrieves all customers with exactly the same set of Loans.

Rules:

If I give C1 then I should get C2 (same set of loans)
If I give C2 then I should get C1 (same set of loans)
If I give C3 then I should get C4 (same set of loans)
If I give C4 then I should get C3 (same set of loans)

If I give C5 then I should NOT get any row because there is no other Customer with the same set of loans.
If I give C6 then I should NOT get any row because there is no other Customer with the same set of loans.
If I give C7 then I should NOT get any row because there is no other Customer with the same set of loans.

Table LoanCustomer:

[KEY] [LOAN] [Customer]
1L1C5
2L2C1
3L2C2
4L3C1
5L3C2
6L4C3
7L4C4
8L5C6
9L5C7
10L6C7

I tried this query below but it doesn't give me always the same right results.

SELECT t1.LOAN, t1.CUSTOMER
FROM LoanCustomer t1
WHERE EXISTS (
SELECT t2.LOAN, t2.CUSTOMER
FROM LoanCustomer t2
WHERE CUSTOMER= 'C1'
and t1.CUSTOMER != t2.CUSTOMER and t1.LOAN = t2.LOAN
)

View 3 Replies View Related

SQl Query: How Can I Get Only One Record For Each Customer?

Oct 30, 2006



i have a database with a list of customers and goods that they have ordered. I want to send one email to each customer regardless of the number of products he has ordered. eg.

Userid, product id, createddate

1034 2000788 2006-09-01 14:50:19.880
1034 383002 7 2005-09-07 20:50:19.880

1034 4493903 2006-09-01 20:00:19.880

I am therefore making a query for getting the data.How can i get only one record for each customer?

Sincerely

wan.



View 3 Replies View Related

Diffcult Is Issue, How To Add A Row Of Data For A Customer That Has No Data For A Month

May 23, 2008

Ok i dont know how to ask this question but here it goes.
I am pulling monthly data for suppliers showing ontime, early and late deliveries
let say month of Jan. they had
supplier month stat qty

A Jan early 5
A Jan late 1

A Jan ontime 20



A feb late 50
A feb ontime 5


I need to retrieve the data but as you can see since feb did not have any early I am missing the data

how can I create a line
A feb early 0

but remember that i have multiple customers and multiple months.

All I can think of is saveing the base data then run a cursor to inser the lines as needed

View 4 Replies View Related

Exporting Data In A Monthly Manner By DTS

May 27, 2002

I am looking for a script whichs exports data (by DTS?) into a flat file and store the files (according their date stamp in the transactions) with a name like 05_2002.txt, 06_2002.txt etc. The data in the table Transactions will be deleted after some time to prevent fast growing of this particular table.

Any idea?

Thanks

Mike

View 1 Replies View Related

How To Calculate Data Entry By Monthly

Mar 3, 2008

Hi, I'm using SQL 2005, and I want get the total of data for February as an example. My table table is like this

submitdt
-----------
01/02/2008
25/02/2008
29/02/2008
01/03/2008
02/03/2008


so total submit date for february is 3. Your idea is highly appreciated.thanks you so much

View 7 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

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

Query For Total Sum By Monthly / Year Wise By Reference Code

Feb 20, 2015

In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year

Reference_Master

RefCode Date Count
100012/18/20074
100012/19/20078
100012/20/20074
100022/18/20073
100022/19/20072
100022/20/20076

[Code] ...

I need the result as below

Result

RefCodeMonth/YearTotalCount
10001FEB-200716
10002FEB-200711
10003Mar-20077
10004Mar-20073
10005Mar-20072
10001FEB-200816
10002FEB-200811
10003Mar-20087
10004Mar-20083
10005Mar-20082

View 8 Replies View Related

Transfer Data In Monthly Or Adhoc Basis

May 7, 2012

I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.

View 2 Replies View Related

Cube With Monthly Data Chunks Not Working

Apr 3, 2008

I have a table based around requisitions, and each requisition has a number of positions. That number can change over time through updates to pertinent rows rather than through transaction-like records that record an entire history, and I'm only able to get a monthly snapshot of the table. What I decided to do is still use one table for OLAP (fact_requisitions) but add a column called period_key that refers to the month the data comes from. So if I have two months of data then the table has each requisition twice, possibly with differing position counts, and new requisitions from the second month are only present once. Then I tried to filter the MDX query like so:

SELECT {
([Dim TimeRequestClosed].[Year - MonthNumber].[Year_Text].&[2008].&[1],[Dim Requisitions].[Period].[Period Key].&[200801])
}
ON COLUMNS,
NON EMPTY
{
([Dim Location].[Region Name].MEMBERS, [Dim Location].[Period Key].&[200801])
}
ON ROWS
FROM
[Requisitions]
WHERE
[Measures].[Request Closed Date Count]


This query doesn't work even though the data is there, it just returns nulls. Am I going about this all wrong? If not, what might I be doing wrong, and how would I get the query to return more than one period (e.g. tell Dim Requisition to match up with Dim Location on the period key)?

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

SQL Search :: Query For Finding Two Columns Data In Third Column And Getting Data From It?

Jul 6, 2015

getting result as given below.

Input:

id Name Data

101 AA PQ102BBRAJAKIRANBUBLU

102 BB RS101AAEROJUCHALABAGUNDI

103 CC TU104GGANDICANKILLANYONE

OUTPUT:

id Name Data

101 AA 101AAEROJUCHALABAGUNDI

102 BB PQ102BBRAJAKIRANBUBLU

103 CC

View 4 Replies View Related

How To Debug The Customer Data Provider?

Apr 10, 2007

Hello,

I am doing a customer data provider dataextension such as FsiDataExtension.

But I want to know how to debug it?



Thank you.



Jeffers

View 1 Replies View Related

Tough 2 Year Comparison Sales Query

Dec 17, 2007

I'm developing a 2 year comparison rolling 12 month Sales report but am having problems with the query I'm using. Because I need to include every month (whether no sales or not) I have a calendar table named metaDates and each table gets outer joined as a result. (Forgive the long query) Please see below.

SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) )AS GM,
'Current 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)

GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid

UNION
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) ) AS GM,
'Previous 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))

GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
ORDER BY MetaDates.[Year], MetaDates.[Month]


That said the second outer join (Customer.country IN (@country)) never gets executed. It's really a simple concept. "Give me last 2 year sales by month (every previous and last month even if no sales) but only for the country the user is in"

Can someone help me out with this sql statement. Any help would be much appreciated.

View 11 Replies View Related

Analysis :: MDX Query For Sales Unique Rows

Sep 21, 2015

I am trying to calculate the maximum sales per store where multiple stores exist in the same region (i.e there must be doubles of that row) in order to determine the most profitable store per that region.

E.g.
Store Sales
A 500
B 200
B 100
C 400
C 800
D 100
D 200
D 700

Looking for result set like:
B 200
C 800
D 700

View 3 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

Sales Report - Previous Year Stats In Same Query?

Aug 6, 2004

Hello,

I am creating a sales report that needs to display Sales statistics for a selected month grouped by Material. Also, it needs to display the same stats for the selected month of the previous year. Finally, it needs to display Year To Date Statistics for the current year and previous year up to and including the selected month.

Currently, I am using 3 queries to do this. The first one gets the statistics the current month grouped by material. The others sprocs get their corresponding information by me passing in the material number and the month and doing the appropriate sum. So, essentially, for a single line of the report, 3 queries are being done. The problem arises in that sometimes there may be as many as 300 materils displayed in a given month, which amounts to alot of db activity and a long delay loading the report.

Is there anyway to get this information in one swoop using some fancy aggregation?

Thanks,
Jake

View 14 Replies View Related

Query: Two Columns With Same Data

May 13, 2008

Hi,
I have 3 tables:
Table SLA: SLA_code, SLA_name
Table industry: Industry_code, Industry_name
Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq

I would like to query to get in one table:
SLA_name for DESTINATION, Industry_name, Freq

Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.

How can I query to get the Freq and Industry for those destination SLAs?

I have make a relationship between:
Table SLA.SLA_code with Table14.SLA_code_destination and
Table_industry.Industry_code with Table14.Industry_code

But I am not sure if just a
SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq
FROM SLA, Industry, Table14
would select the SLA_destination and not the SLA_origin.

Thanks very much in advance,

Maria

View 1 Replies View Related

SQL Server 2012 :: Query Design - Find Most Recent Datetime Record Each Day For A Customer

Apr 2, 2015

So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:

SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)

[Code] ....

View 4 Replies View Related







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