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 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 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.
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?
Hi I need to write something in T-SQL which will return a record set from a table of IT hardware. The record set should comprise all items that are older than 3 years from the current date. Each hardware item has a purchase date against it so it should not be too difficult. I am considering the DATEDIFF function but I am not sure how best to write it.
I need calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2.
Dataset 1:
SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code], factAdmissions.AdmitCCYYMM, ISNULL(sum(AmountPaid),0)As [Amount Paid] FROM factAdmissions
[Code] ....
Dataset2:
Select
(factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product, EffectiveCCYYMM, ISNULL(count(Distinct MemberId),0) As MemberCount From factMembership Where EffectiveCCYYMM >= '200701'
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 need to write an MDX query which will return a flag that indicates if a product has a gap of more than 2 consecutive months in terms of positive sales. So I calculate if there is a gap of 3 months. If yes, then return 1 else null. The calculation below is scoped at product level.
IIF(([month-3 sales]<=0 AND [month-4 sales]<=0 AND [month-5 sales]<=0) OR ([month-4 sales]<=0 AND [month-5 sales]<=0 AND [month-6 sales]<=0) OR ([month-5 sales]<=0 AND [month-6 sales]<=0 AND [month-7 sales]<=0) ............... OR ([month-12 sales]<=0 AND [month-13 sales]<=0 AND [month-14 sales]<=0)),1,NULL)
Unfortunately I cannot do this calculation at relational level as a product may have a gap of more than 2 months at country level but may not have this at a higher level in geography.
optimizing this as the performance is bad and other calculated measures are dependent on this. Based on this calculation, I can write a scope which sums up all products so that I get the count of products with a gap of more than 2 months in sales.
I have also tried CASE statement and NESTED IIFs but the performance is worse than the above.
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?
Hello! Please show me how to calculate how many year of death? I have tb1: fname, lname, date_of_death. Was not sure I can use the below statement. Thanks,
Select DATEDIFF(year, date_of_death, getdate()) AS no_of_days FROM tb1
I have a table with 3 fields: Contract_No, Start_Date, End_Date. I need to calculate how many of the days between the Start and End dates fall within each financial year (FY) beginning 1st April and ending 31st March.
So for example if the Contract Start_Date is 26/01/2012 and the End_Date is 20/05/2012 or is null then the number of days for 2012 FY is 49 (counting from 01/04/2012 to 20/05/2012).
If the End_Date is null for the same contract, then the number of days for 2012 FY would be 365.
Since the contract period from Start_Date to End_Date might span more than one or even several FY I need to be able to show this in columns seperately for each FY. What is the best way of achieving this?
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?
I'm trying to generate a cumulative total with a rolling window of 13 time periods..Previously I was able to do left outer join to the same table 13 times to add the quantity field but it appears with the migration to SQL Server 2014, that many left outer joins is not possible (query that would run in 3 mins is taking well over 15 hours now)..
I am struggling to calculate Full year in my SSAS Cube. Meaning, regardless of what fiscal year hierarchy level I am in; i need a measure aggregating from 01/01/year of current member to 12/31/year of current member.
I want to replicate it using the Year To Date below:
FY-FQ-FM is the fiscal year quarter hieararchy
I am using for built in time intelligence.
Create Member CurrentCube.[DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date] As "NA"; /*Year to Date*/ ( [DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date],
Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.
They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.
Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.
Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?
I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.
I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.
I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.
WHERE (YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month) OR (YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)
Need calculating the count of events on each day. I have an event log table as shown below.
DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime ) INSERT INTO @EventLog SELECT '100','Inplace','01/05/2015 05:00' UNION ALL SELECT '100','Removed','03/05/2015 10:00' UNION ALL SELECT '100','Inplace','05/05/2015 10:41' UNION ALL
[Code] ....
I need an output to look like on each day how many events were in place.
E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103) hence 2 .
event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month.
So I need to count of all each event_id in place for each day in may.
How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.
SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK") FROM ruleResults;
For the second record, ItemsStatus=1 which means the item return to stock, at the time the running stock value calculation should be the previous row Running Stok value (=9 ) +(ItemQuantity*ItemUnitWeight)When the ItemStatus=2, that means the item is definitely out and will be never back to current stock. Is there a way to get that calculation field ?
I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.
I want go get all weekends of the year (year dynamic) with out CTE concept, because I need to implement in 2005 version.I have googled but getting only CTE examples.
I have a query for which in the where clause i use where Year(openDate) = Year(GETDATE()) and Month(OpenDate) = Month(GETDATE())-1 which would give me the data i needed for this year last month. However if i run this query on Jan 2015 or Jan 2016, this query would fail.
how to modify my where clause so that it runs regardless of even if its Jan ?
How I can setup the first of November of the previous year dynamically? So running now it should be 01-11-2014 but if I run in 2th of January 2016 it should be 01-11-2015.
I am trying to create a "Loan Ledger Card" in SSRS which does the calculation up to report extracting time. I was able to create it on Excel (Click to download the Excel file) as we can calculate amounts for each installment based on Row and Column name. You can test it by putting any amount (Here it should be below 62000 as it is the total balance of loan) in "Amount Paid" yellow cell and see the effect.
I have made a selection in SQL (attached query and result in screenshot). It calculates the total paid amount by now on the first row, and all due amounts in other rows. Basically, It should first deduct the interest from first row, then principle, same process for second row and so on until it covers the whole paid amount.
SELECT '0' As RN ,memrepay.lnr ,'Repayment' AS Type ,SYSDATETIME() AS NOW ,SUM(memrepay.mprinc) As PrincPaid ,SUM(memrepay.mint) As IntPaid
[Code] .....
How can I do the similar calculation of Excel file in "SQL query" or "SSRS custom code"?
