How To Add The Total Balances Of Previous Year Into The Next Year?
Apr 13, 2006
I'm working with a table called SALFLDGAMS where it has two columns that I need to work with. One of the columns is Period which has years from 2000 to 2005 and the other column is called amount which has the balance for that year. Let me explain in a little more detail. There are account codes associated with the years as well so there will be many places where for example the year 2000 will show up with a given value. What I'm trying to do is to create a view which has a BeginBalance column which adds lets say all the values for 2000 and sticks them to 2001. So what I'm saying is all the values from the previous year I want them in the current year. All in one column. Thanks for the help guys.
HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan
I have one matrix that shows the CrashCount (measure) by month. Looks like this:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec
25 90 100 55 52 55 22 55 22 35 65
The user selects a Year as a parameter. I want to put another matrix in that displays the previous year, just as the first year is displayed. How do i edit the second matrix? Do i put the parameter as
=(Parameters!CrashStatisticalYear.Value)-1 or is there some other way this can be done. Without having the user put 2 years. I just want them to pick one. And the previous year shows up in the matrix below this one. Can anyone help me with this...what should i do?
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
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 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 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 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?
I have two queries that give me the total sales amount for the current year, and the last year.
SELECT SUM([Sales (LCY)]) FROM [$Cust_ Ledger Entry] cle LEFT OUTER JOIN dw.dim.FiscalDate fd ON fd.CalendarDate = cle.[Posting Date] WHERE [Customer No_] = '10135' AND fd.CalendarYear = '2013'
[Code] ....
I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.
This is my query without the columns I need:
SELECT c.CustomerNumber ,c.Name ,c.ChainName ,c.PaymentTermsCode ,cle.CreditLimit AS 'CreditLimit' ,SUM(cle.Amount) AS 'Amount'
I need a report that shows calcul of a field for current year as well as previous year respecting this rule expression(Last-Last Previous)/Last Previous*100 (I work with MDX Query and i work with SSRS 2008).
Also the report runs on a Year Parameter. Below is an example for the result for example i selected the years 2010 2011 2012 i can select another years because i have the report runs on a Year Parameter
year Data 2010 2011 2012 hp 14 25 30 Dell 17 18 20
and the result i want
year Data 2010 2011 2012 2011/2012 hp 14 25 30 0.002 (Last -Last Previous)/(last Previous*100) =(30-25)/(25*100) Dell 17 18 20 0.0040
I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.
The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.
I've tried evaluating the parts of the episode date seperately like:
where (epimonth>=10 and epiyear=2003) or (epimonth<=2 and epiyear=2004)
and that works, but what happens when someone wants to see from 10/2002 to 2/2004?
We have customers who are new to this year (2014) and there same customers in last year (2013). also there are customers we have not received business this year but only last year. so there are 4 conditions.
1) New customer (2014) - Customer(B) 2) Old Regular customer (2013 and 2014) - Customer(A) 3) Last Year (Lost) customer (2013) - Customer(C), no business received in year(2014)
For example we have a transaction table:
TransactionId, ReceivedDate, Customer 1, 2-Dec-2013, A 2, 3-Jan-2014, A 3, 2-Mar-2014, B 4, 25-Nov-2013, C
I want results like
Customer, Business (this year activity/last year activity) A, 1/1 B, 1/0 C, 0/1
How can i show this for each year? I used to separate it month wise as below but it does not return applying year with each customer anyhow...
select t.customerId, YEAR(Receiveddate), sum(case month(ReceivedDate) when 1 then 1 else 0 end )as Jan, sum(case month(ReceivedDate) when 2 then 1 else 0 end )as Feb, sum(case month(ReceivedDate) when 3 then 1 else 0 end )as Mar, sum(case month(ReceivedDate) when 4 then 1 else 0 end )as Apr,
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
What I am trying to do is create a DAX formula to rank each event in the order that it happened for the year. So I want to end up with something like this. This way I can compare the events year over year as the events don't happen on any regular time schedule.
I have tried to do this by creating a formula that will give me the day number of the year:
Day of Year =(YEARFRAC(CONCATENATE("Jan 1 ", YEAR([Event Date])),[Event Date])*360)+1
Then using rankX on this table, but I cant seem to get the proper result. Perhaps I am not understanding the use of rankX or going about this the right way.
=RANKX(FILTER(Event,EARLIER(Event[Event Year])=Event[Event Year]),Event[Day of Year])
or
=RANKX(All(Event[Event Year]),[Day of Year],,1,Dense)
I'm trying to creating the following matrix in SSRS 2008R2 (with more product categories than shown here)The matrix only shows the last two year per product.
The matrix in the report builder looks like this:
For me it's unclear what the expression should be. (or perhaps I need to make a calculation in my TSQL?)
As Row Groups in the matrix I've got:
Product; Year
As Column Groups i've got Month
I can't get it to work. Previous doesn't seem to work in this case (only when I got a total of each year (e.g. one column instead of 12))
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
I want to get a running total. But its a bit tricky. I have data depending on ID, YEAR, Month, Value like the following.
ID, Year, Month, Value 36002084 2014 Jan 8391 36002084 2014 Jan 0 36002084 2014 Jan 3440 36002084 2014 Jan 0 36002084 2014 Jan 548 36002084 2014 Jan 109 36002084 2014 Jan 125
I wrote the Query below
select [UEP ID],[Year],[Month],sum([Total VtM EUR]) 'Running Total' from RegData where [UEP ID]=36002084 and [Year]=2014 group by [UEP ID],[Year],[Month]
I got this output
36002084 2014 Apr 887620 36002084 2014 Aug 789440 36002084 2014 Dec 528453 36002084 2014 Feb 606627 36002084 2014 Jan 331613 36002084 2014 Jul 681314 36002084 2014 Jun 330384 36002084 2014 Mar 1044301 36002084 2014 May 671818 36002084 2014 Nov 465576 36002084 2014 Oct 590147 36002084 2014 Sep 527861
Now I need to get a output like this,
36002084 2014 Jan 331613 36002084 2014 Feb 938240
A running total depending on the ID, Year and Month. Although I showed this for two steps I need to get it from one query.
Question is - To get a running total based on ID, Year, Month. If I do the normal group by and sum query I could get it for all the months for each id. But need to then add January value to February and updated February value.
I have a question regarding ROLLUP and how to use the operator.
I've been asked to retrieve a list showing the total repair cost per year displaying Transport Type, Manufacturer and Transport. The query I used is:
SELECT ty.Description AS TransportDescription, t.SerialNumber AS Transport, ty.Manufacturer, SUM (r.RepairCost) AS RepairCost, YEAR(r.EndWorkDate) AS [Year]
[Code] .....
How could I use the rollup function to display subtotals per Transport Type, Manufacturer and Transport?
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
Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.
I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.
Hi every one, I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview. How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
I want to write a query which returns count values sold of each product for last three months... which I can write...the hard bit is, I also want to return what the count value was last year for this month
i.e. Feb Jan Dec 2007 2006 2007 2006 2007 2006 Soap 2 9 1 2 9 9 Kitchen Towel 1 34 3 34 23 45 Washing Up Liquid 33 3 5 6 7 33
How can I write a query which will show count totals for both years?????? Thanks
12/1/07 What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4). If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales) FROM Sales a CROSS JOIN Sales b WHERE (b.DayCount <= a.DayCount) AS RunningTotal GROUP BY a.DayCount,a.Sales ORDER BY a.DayCount,a.Sales Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07 Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
i have some classes that I want to group by month/year (note:i dont need the day of the month) how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so.. 11/2006 12/2006 1/2007 3/2007 i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value. dbo.classgiven.classdate date of the class.thank you all SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
