Hi All Experts,
I need to show a result of a revenues total for Monthly,Quarterly, and YTD of a given sales person by a given period.
e.g
Period : 7-1-2006 to 8-1-2006
Sales Person Monthy Quarter YTD
SalesPerson1 $1999 $10000 $100000
SalesPerson2 $1999 $10000 $100000
How can i do that?
Thanks in advance.
I have a file that will be produced both Monthly and Quarterly. The file name will be the same except for the Month/Quarter in the name:
Monthly file - Inforce Download - APR 2015.txt, Inforce Download - MAY 2015.txt, etc...
Quarterly file - Inforce Download - Q1 2015.txt, Inforce Download - Q2 2015.txt, etc...
The SSIS package will need to accomplish the following:
1. The package will somehow need to know what the file name should be based on the last file processed. So for example if we just loaded the Jan 2015 file, the next monthly file to drop should be the Feb 2015 file. For the quarterly files, it should be Q1 2015, then Q2 2015, etc...
2. Based on the file (Monthly or Quarterly), the package needs to somehow split and process one way for Monthly and another way for Quarterly.
Hi
I am very new to analysis services and using MDX.
I want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
----
Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The case statement will take paramter values when finished
----------------end of code portion
Is this possible or is it suppose to 'split' the columns when moving to SSRS.
Thans in advance
Dev environment - SQL 2008 Feb CTP, VS 2008
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?
Hi everybody!
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.
Any thoughts?
Thanks in advance.
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.
ACCOUTSREVENUEREVENUEMIX
ACCOUNT1 100 2%
ACCOUNT2 200 4%
ACCOUNT3 500 9%
ACCOUNT4 1000 19%
ACCOUNT5 1500 28%
ACCOUNT6 2000 38%
TOTAL 5300 100%
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
View 3 Replies View RelatedI'm trying to build a where clause that looks at two columns to determine if QUARTERLY and ANNUAL payments are due.
rec_day could be any day.
I got the monthly one pretty easily:
WHERE rec_start <= GETDATE() AND rec_frequency = 'Monthly' AND rec_day = DAY(GETDATE())
So if the rec_day was 9 and the rec_frequency was 'Quarterly' then I would be looking to see if todays date is 1/9 or 4/9 or 7/9 or 10/9 (not sure how I do this part)
I'm pretty much stuck on where to go with the quarterly where clause:
WHERE rec_start <= GETDATE() AND rec_frequency = 'Quarterly' AND rec_day = ????
I tried the following, but it did not work:
(CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) = GETDATE())
I have transaction table I am pulling transactions from and can't quite figure out how to get a sum based on a few items.
I am trying to figure out how to show the sum of the InvestorAssumption and group it based on the DealId, then DealTransType and show the sum of the InvestorAssumption.
So I want to see a total of the InvestorAssumption by quarter split up by DealTransType, then DealId.
Not sure how to do this and any help would be greatly appreciated.
Here is my current view.
Code:
SELECT TOP 100 PERCENT dbo.DealTransactions.DealTransId, dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, DATEADD(quarter,
DATEDIFF(quarter, 0, dbo.DealTransactions.DealTransDate), 0) AS TransDateQuart, dbo.DealTransactions.DealTransType,
dbo.DealTransTypes.DealTransTypeName AS TransactionType, dbo.DealTransactions.DealTransAmount,
dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100 AS InvestorAssumption,
dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransDate AS TransDateActual
FROM dbo.DealTransactions INNER JOIN
dbo.DSGvwInvestorPercentage ON dbo.DealTransactions.DealId = dbo.DSGvwInvestorPercentage.DealID INNER JOIN
dbo.DealTransTypes ON dbo.DealTransactions.DealTransType = dbo.DealTransTypes.DealTransTypeId
GROUP BY dbo.DealTransTypes.DealTransTypeName, dbo.DealTransactions.DealTransAmount, dbo.DealTransactions.DealId,
dbo.DSGvwInvestorPercentage.InvestorID, dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100,
dbo.DealTransactions.DealTransType, dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransId,
dbo.DealTransactions.DealTransDate
ORDER BY dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, dbo.DealTransTypes.DealTransTypeName,
dbo.DealTransactions.DealTransAmount
RE: Quarterly report - calculating for each of the next four months?
As I play with joins and functions to help mold my data.. I'm
thinking
about options.
I have a status table with termination status entries that will
dictate participation spans for members.. a member can have multiple
stop and start status entries in that status table. I've built an
sql function that given a member's ID and a month/year will return the
number of days the member particpated in the month.
I need to produce an SSRS report that given a starting month/year
will report days of activity for every member for the next four months.
So my report will look like this:
memberid 2007/06 2007/07 2007/08 2007/09
001 10 0 20 5
002 0 2 55 1
Should I build a view that given the starting yearmonth returns
month1 - 4's data in a row?
or is there a smarter design? Possibly a row for each month? or SSRS
formulas? Maybe the formula is gonna be a performance killer.
Thanks for any help or information!!!
I need to get results on quarterly basis, matching 2 quarters AUTOMATICALLY.
- As the new quarter starts, it needs to match the last quarter results.
SELECT DATEADD(mm, (QUARTER - 1) * 3, year_date) StartDate,
DATEADD(dd, -1, DATEADD(mm, QUARTER * 3, year_date)) EndDate,
QUARTER QuarterNo
[Code] ....
Here is my Query, I don't know whether I'm getting it right?
--Quarter 1
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter1
FROMdbo.tblRPT_Spend AS A INNER JOIN
dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
[Code] ....
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.
Hi,
I m Maran. I am trying to write a SQL Query to retrieve the following report format. But I'm not sure how to go about it.
Input values:
Starting Date: 09/14/2007
End Date: 12/06/2007
Monthly Report :
Start Date - End Date - Number of companies
09/14/2007 - 09/30/2007 1
10/01/2007 - 10/31/2007 0
11/01/2007 - 11/30/2007 4
12/01/2007 - 12/06/2007 0
Please its very urgent, Plz do the needful help. Actually this same report format i was posted already and got some methodology, but its not satisfied my requirements :( :(
Used Table: CompanyHistorytrackTable
companyId changed_date
50198 2007-09-05 13:11:17.000
48942 2007-09-14 12:42:30.000
48945 2007-11-06 12:05:31.000
47876 2007-11-14 10:58:21.000
43278 2007-11-16 16:14:25.000
43273 2007-11-16 16:16:11.000
51695 2008-02-04 11:05:09.000
47876 2008-01-21 14:10:02.000
44604 2008-02-04 19:33:02.000
46648 2008-02-04 19:35:30.000
Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028
Greetings;
I recieve a monthly phone bill on cd. I am trying to autoimport these CD's into a SQL server database with a DTS in MSSQL 2000. I have noticed on the bills every so often they change the structure of one of the ACCESS DB tables by one row or something small.
I am trying to figure out how I can test the structure of the database before trying to do the actual imports, I want to do this so someone else can actually do the imports as we get the bills.
Is this a possibility? Or am I going about this all wrong?
Thanks
nhas
I need to run a script on a monthly basis (e.g at midnight on the last day of the month) which selects all the records from that calendar month. I have the below so far however as each month has a different number of days in it this will not work well. Are there any date/time criteria I can use in my tsql statement which will only select records from that month no matter how many days are in the month? I obviously need to move away from manually updating the script each month depending upon how many days are in the month!
SELECT *FROM UserLogwhere logtime >= (getdate() - 30)order by logtime asc
cheers
marco
Here is example table and data
CREATE TABLE [dbo].[sales](
[date_value] [datetime]NOT NULL,
[monthly_total] [int] NOT NULL
)
insert into sales ( date_value, monthly_total)
[code]...
How can I create a query to get below result?
YearMonthMonthlySaleAccumulated Total
2012Jan110 110
2012Feb130 240
2012Mar30 270
2012Apr60 330
2012May10 340
[code]...
Hi All,
I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.
Input values:
Start Date: 2/17/2008
End Date : 5/8/2008
Output Format:
2/17/08 - 2/29/08 (Partial Month) 12
3/1/08 - 3/31/08 (Full month) 0
4/1/08 - 4/30/08 (Full month) 22
5/1/08 - 5/8/08 (Full month) 10
I want the above format of the monthly report. I really could use some help on this. thanks.
~ Maran
Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028
The Database will hold 2 tables. One of those includes dates. They are joint by a constraint using an ID. What i got to do is, store the tables in a different schema named after the month the data was created. I will have to keep the original tables aktive because this should work while accessing the tables but can flush the data to keep the database small. So i would end up with 2 tables in 1 schema for every month and the productive that keeps track of the current bookings.
I would have access to the enterprise edition if that changes anything at all.
I need to create a report containing the headcounts of the employees for each month. The data I can retrieve from the database looks like this:
Name
StartEmp
EndEmp
tom
1/jan/05
15/mrt/05
dirk
1/jan/05
31/mrt/06
jan
1/feb/05
NULL
In order to get the right information for the report the information should be represented in the following way:
January 2005
February 2005
March 2005
"April" 2005
Tom
1
1
0,5
1
Dirk
1
1
1
1
Jan
0
1
1
1
HeadCount
2
3
2,5
3
(there should be more data in the 2nd table but it's a sliced representation of the data. Sliced by 2 report parameters: startdate (=1 January 2005) and enddate (=30 April 2005) )
I need some help about building up the query to aggregate the info from table1 to the format of table2.
Hi,
I have to set up a project in SQLServer Express that will allow the export of tables from multiple PC's running SQLServer Express each month.
These have to be loaded into a single identical database on SQLServer Express each month for combined reporting.
so its basicaly
insert update on PC
export
import overlaying last months data (handle dup keys from the other pc's etc)
report
I've had a look at the SQLServer replication docs and got confused....
So if anyone can point me at appropriate documentation, or suggest a good method for this it would be appreciated
Thanks
Hi,
I have to set up a project in SQLServer Express that will allow the export of tables from multiple PC's running SQLServer Express each month.
These have to be loaded into a single identical database on SQLServer Express each month for combined reporting.
so its basicaly
insert update on PC
export
import overlaying last months data (handle dup keys from the other pc's etc)
report
I've had a look at the SQLServer replication docs and got confused....
Can I do this with replication ?
So if anyone can point me at appropriate documentation, or suggest a good method for this it would be appreciated
Thanks
hi,
I am using timeseries algorithm.I am training my model like the following
Date StudId Perf
5/1/2005 001 99
5/10/005 001 97.6
6/1/2005 001 94
6/10/2005 001 99
6/30/005 001 96
10/1/2005 001 100
Like that.
I need prediction Output like following
Date StudId Perf
10/1/2005 001 99
11/10/005 001 97.6
12/1/2005 001 94
1/10/2006 001 99
... how to write prediction query for this.
Thanks
Karthik.
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
I need to calculate monthly absence days for an employee using SQL Server 2008.
Need to calculate the number of absence days for each month when start and end of the absence dates are given
INPUT: 01/15/2010 05/25/2010
OUTPUT:
Jan 16
Feb 28
Mar 31
APR 30
May 25
I have created a database table in MSSQL 2000 like this
[empcode] [leave_date] [type] [reason]
100 2008-12-29 00:00:00.000 T Tour
100 2008-12-30 00:00:00.000 T Tour
101 2008-12-31 00:00:00.000 CL Casual Leave
102 2009-01-01 00:00:00.000 R Restricted holiday
100 2009-01-02 00:00:00.000 T Tour
This table contains only leave details.... but i need to create monthly attendance report such as below
empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 .............
100 P P P P S P T CL P P P S P P T ............
101 P T R R S R R T CL P P S P P P..............
102 P P P P S P P P P T T S CL P P P............
P-present
T-Tour
CL- causal leave
R- Restricted holiday
S-sunday
is there any way in SQL query to get the report like that.....
hello friends!
I want to display the reports in weekly format suppose
today is sept 27 2006, so i know from datepart(weekday,..) its value is 4 and end of this week is sept 30 2006 and again next week will start like that....also search should be monthly...
my report looks like
Weekly Report (09/27 - 10/12)
Week====09/27-09/30======10/01-10/07======10/08-10/12
Sales======50===============100===============80
like that my output looks like
T.I.A
I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column.
When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.
monthlyExpenses ytdExpenses
$1,000 $1,000
$2,000 $3,000
$3,000 $6,000
$2,000 $8,000
$5,000 $13,000
Hi,
My problem is this: a manager has to select 2 dates and from those 2 dates he should see all the times a certain movie has been rented FOR EACH MONTH. For example if he picks 11/1/2007 to 2/1/2008 he sees a total for nov, dec, jan etc. I have a uniqueid for movies called the 'upc' and i have the time and date it was rented 'rental time'
I have a stored procedure with parameters @periodStart and @periodEnd. I am just fine getting the total for the period. but I have no idea how to get the totals for each month. The hint i was given was "grouping data ranges." I have no idea how to tackle this
Maybe a case?
Thanks for your help guys. Let me know if any more info would help.
I would like to run a report for each month over two years. I am currentlyusing a date range like this. Then manually substitute the error_timebounds for each month and rerun the query. How can I script this so I canprogrammatically perform the substitution in a loop. Thanx in advance.select count(*) from application_errorswhere error_message like 'Time%'and error_time >= '1Apr2004' and error_time < '1May2004'
View 10 Replies View RelatedHello,
I'm scheduling monthly subscriptions in reporting services. I would like to receive subscriptions every 2nd Monday of the month. For instance, if the 1st is a Friday, then report should be delivered Monday, the 11th.
So my question is: in reporting services:
if I set the subscription for Monday - 2nd week, and the first day of the month is a Friday
- does it consider the week from 4 -> 10 as the second week (because month began the week before)
- or as the first week (because it takes into account only full weeks)?
Thanks a lot for any help!
Best regards,
Tiago
Hi,
My problem is this: a manager has to select 2 dates and from those 2 dates he should see all the times a certain movie has been rented FOR EACH MONTH. For example if he picks 11/1/2007 to 2/1/2008 he sees a total for nov, dec, jan etc. I have a uniqueid for movies called the 'upc' and i have the time and date it was rented 'rental time'
I have a stored procedure with parameters @periodStart and @periodEnd. I am just fine getting the total for the period. but I have no idea how to get the totals for each month. The hint i was given was "grouping data ranges." I have no idea how to tackle this
Maybe a case?
Thanks for your help guys. Let me know if any more info would help.
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
Hi guys,
I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months?
In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.
Thanks in advance for any suggestion.
Claudio