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.
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 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.
table A-NewParts PartNumber Description LaunchDate a123 product a 4/1/2009
[code].....
I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.
PartNumber Description LaunchDate Yr1 Sales Total Yr1 Sales Yr2 Sales Total Yr2 Sales a123
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.
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?
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.
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
And the resultant string is: dd/mm/yyyy, for example 30/08/2004. But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel
Greetings All, I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated. TIA, ~ck
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
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 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?
Hello All, i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively..... i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..? Any help.. Thanks.. Anne
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.
I have a table in sql server, i need to import this table to another database in same sql server using DTS, In the table, we have a field called 'qualDate', I need to import the record that the qualDate is in the date of today and back off four years, for example, today is 8/10/2004, back off four year should be 8/10/2000, so i need only the record that qualDate is between 8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.
I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.
SO I need to do:
IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)
But it has to be comparing the year,month and day (excluding the time part of the datetime type).
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date ---------------------- 2004101-Jan-2004 2004201-Feb-2004 2004301-Mar-2004 and so on ....
Many thanks in advance. I'll appreciate your help P.S. Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
Hi Guys, Can anyone please let me know How to find the first day of the week, if I know the weekno and the year. For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week for the above query the date will be eg:26/06/2003
I really appreciate your Help
Thanks:confused: Find the Date if I know the week and year
Hey all, how do you take the current date and subtract a year from it in a SP?
What I want to do is...
Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date ---------------------- 2004101-Jan-2004 2004201-Feb-2004 2004301-Mar-2004 and so on ....
Many thanks in advance. I'll appreciate your help P.S. Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
I wrote the query like this "select datepart(year, Msg_Time) from C20_Messages" This query result is:- 2006 2006 2006 2005 2005 2005 2005 Here I want to get only the year part without repeating. I mean 2005 2006
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
I am collecting a year parameter from my user and want to use that year as part of a complete date condition.
So I collect the year say '2003' from my user.
A condition must be applied so that the date must be after 01-APR-...and here is where i want to utilize the parameter @Year. So I can apply the same date condition depending on what year we are looking at.
I am currently using the following logic:
(DATEPART(yy, CANCELLATION_DATE) = @Year) AND (DATEPART(mm, CANCELLATION_DATE) >= 04) AND (DATEPART(dd, CANCELLATION_DATE) >= 02)
This causes problems as the date can be only Jan, Feb, Mar, Apr 01 or 02. What I need is to gather all dates prior to Apr 02, with the year paramter to follow.
We originally developed this querty using Oracle so idealy there would exist a pl-sql to t-sql conversion tool out there! Anybody know of one?
Here is how the pl-sql is structured: CANCELLATION_DATE >= TO_DATE('02-APR-'||:"Year",'DD-MON-YYYY') ) ) )
