Is there any way that I could achieve displaying the revenue grouped by a month?
I've got the following fields in order table:
DateClosed,
StartDate,
EndDate,
OrderValue
The fiscal year starts in march and ends in feb.
I know how to calculate the year's revenue but I have got difficulties in spreading it over the months.
I am trying to create a query that will show how much revenue that we have recieved from a customer After the first invoice and I'm having a difficult time creating a query to do it.. I have a customer table and a sales table joined by custno. SELECT Customer.LastName, Sales.InvDate, Sales.AmtChargeFROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo The output I'd like is CustNo, LastName, FirstInvoiceAmount, LifeCycleAmount Getting the first inv date seems straight forward SELECT Customer.CustNo, MIN(Sales.InvDate) AS FirstInv FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo GROUP BY Customer.CustNo However getting the amount of that first inv and then getting the sum of all invoices not including the first invoice has me scratching my head. Can anyone point me in the right direction?
I'm trying to find out less than 10% in revenue accounts from a table. Below is a snapshot. Basically, I want to add Revenue mix column in the table using procedure.
I have a requirement (motivated by a SOX thing) that is just giving mefits. I know it should be easy and I'm probably overthinking it, but Ijust can seem to find the best way to get where I need to go.I have some payment projection data derived from a huge procedure thatI'm dumping into a temp table that looks like looks this:Key Pd Start End AnnualAmt MonthAmt DailyAmt6789 1 2005-06-01 2010-05-31 49,500.00 4,125.00 135.6164386789 2 2010-06-01 2015-05-31 54,450.00 4,537.50 149.1780826789 3 2015-06-01 2020-05-31 59,895.00 4,991.25 164.0958906789 4 2020-06-01 2024-05-31 65,884.50 5,490.38 180.505479(there are actually 6 levels of keys, but you get the idea)I need it to get into a reporting table looking like this:Key Rev Year ProjectedAmt6789 2005 29,021.926789 2006 49,500.006789 2007 49,500.006789 2008 49,500.006789 2009 49,500.006789 2010 20,478.086789 2010 31,924.116789 2011 54,450.006789 2012 54,450.006789 2013 54,450.006789 2014 54,450.006789 2015 22,525.886789 2015 35,117.406789 2016 59,895.006789 2017 59,895.006789 2018 59,895.006789 2019 59,895.006789 2020 24.779.10etc...I'm having a problem wrapping my head around how to get the rows in themiddle of each period.The other, probably minor and statistically insignificant, issue isproration on a leap year. If a proration occurs on a leap year and Ihave to calculate the proration based on a DATEDIFF and an Annual orMonthly Amount, I'm going to be a day over.Anybody have any tricks or ideas???Thanks so much for your help!Jody
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.
I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are
Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.
We can easily calculate the revenue per month with "Total Contract value divide by duration".
However what I would really like to do is be able to know how much revenue we will be getting each month.
To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.
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
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]
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
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
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September: @CurrentDate = 9/10/2007 @EndDate = last working day 9/30/2007
Total# of working days = 15
October: @CurrentDate = 10/1/2007 @EndDate = last working day 10/31/2007
Total# of working days = 23
November: @CurrentDate = 11/1/2007 @EndDate = last working day 11/30/2007
Total# of working days = 22 etc. Any ideas of how i can approch this?
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?
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
In my database . Some to the dates has been stored wrongly . for example 03/04/2008 to be stored in database . But in my database it was stored as 04/03/2008 . like this i have more that 100 records . How can i change this to correct format using query . Guide me urgent .
The SQL statement below works to provide the number of help desk requests submitted per month for help_company_id = 1. The problem is that the months are return as numbers (1 for January). I tried using the DATENAME function but all the months were return as January. This is how I tried to use it: SELECT DATENAME(month, MONTH(help_date_submitted)) AS Month, COUNT(help_request_id) AS Requests) How do I convert the month numbers to month names? SELECT MONTH(help_date_submitted) AS Month, COUNT(help_request_id) AS RequestsFROM help_requestsWHERE (help_company_id = 1) GROUP BY MONTH(help_date_submitted)ORDER BY Month
dear friends i want to get last day of month anybody make any stored procedure for checking lip year or how to get last day of month Thanks Nilesh Shah
I have a sales table date total 2004-1-3 123,000 2004-2-2 232,000 2004-4-1 142,000 ... I need a report has sum of total sale on every month even there is on sale record on that month(show as zero) e.g. date total 2004-1 123,000 2004-2 232,000 2004-3 0 2004-4 142,000 .... is this possible??? thanks
What is the best way to get the month name from an int value within a mssql statement ? (there is no need to consider the language settings)
e.g. 1 -> January 11 -> Novembre
My first approach seems to work but is very ugly: CASE WHEN my_month_int<10 THEN DATENAME(month,"20000"+cast(my_month_int as varchar(2))+"15 00:00:00") ELSE DATENAME(month,"2000"+cast(my_month_int as varchar(2))+"15 00:00:00") END AS my_month_name
I have the following data in a Table. Note that this table holds Sales Qty till Today and also Forecast Qty for the next n Months (where n could be between 1 - 15 months)
YYYYMM Stock_Code Whse_code Sales_Qty ----------- ---------------- --------- ------------ 200710 009040 B 505 200711 009040 B 370 200712 009040 B 385 200801 009040 B 335 200802 009040 B 305 200803 009040 B 250 200804 009040 B 250 200805 009040 B 250 200806 009040 B 250 200807 009040 B 250 200808 009040 B 250 200809 009040 B 250 200810 009040 B 250 200710 009040 M 549 200711 009040 M 565 200712 009040 M 535 200801 009040 M 685 200802 009040 M 305 200803 009040 M 240 200804 009040 M 240 200805 009040 M 240 200806 009040 M 240 200807 009040 M 240 200808 009040 M 240 200809 009040 M 240 200810 009040 M 240 200811 009040 M 240 200812 009040 M 240 200901 009040 M 240 200902 009040 M 240 200903 009040 M 240 200904 009040 M 240 200905 009040 M 240 200906 009040 M 240
How can I create a Column (6MthQtyToDate) that is the Sum of the Qty of the Last6Mths. I need this Column for each Row of data.
I am having Joined Two Tables and i get sum of "calloutCharges" and get the result in the below procedure
Result -------- Jan 100 Feb 200 March 300 May 200 Aug 100
But I need result is in the following Format
Jan 100 Feb 200 March 300 Apr 0 May 200 jun 0 jul 0 Aug 100 Sep 0 Oct 0 nov 0 Dec 0
This my Query
SELECT Sum(FT.CallOutCharge) AS 'CallOutCharge', Convert(CHAR(3), CC.CompletedDate,109) As 'Month', month(CC.CompletedDate) As 'intMonth', Year(CC.CompletedDate) As 'Year' FROM HSSPMS_Tbl_Callcentre_Compliants AS CC INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FT ON FT.ContractorCode = CC.ContractorCode And CC.FaultCleared='1' WHERE FT.CreatedBy=@OwnerId AND FT.IsDelete='0' And Year(CC.CompletedDate)=@Year Group BY CC.CompletedDate
I am very new to VBA, and my boss gave me a very simple assignment to do. Basically, right now the code is pulling data for forecasts for the following 10 days, and the code is the following:
WHERE (((dbo_ACTUAL_HDD_DAILY.DATE)>=Now()-1 And (dbo_ACTUAL_HDD_DAILY.DATE)<Now()+9)
All he wants modified is to pull data for the entire current Month (Ex. if it is in the middle of July, he would want the data from July 1-July 31, or if February from Feb 1-Feb 28) It would be nice to do this without having to change the VBA every month.
Thanks for the help, I know its really simple but I hope I will get the hang of this eventually.