Monthly Report
Apr 15, 2008
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
View 3 Replies
ADVERTISEMENT
Dec 14, 2007
hai iam new to ssrs, please help me.
i have student billbale information assume what ever data it. i need to to dispaly total amount for the student at
Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.
thanks to advanced
Jacks v
View 1 Replies
View Related
Apr 5, 2008
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
View 3 Replies
View Related
Jan 4, 2009
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.....
View 20 Replies
View Related
Sep 27, 2006
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
View 2 Replies
View Related
Oct 24, 2007
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
View 2 Replies
View Related
Aug 13, 2007
Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
3. There are around 500 employees data im dealing with
4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...
View 7 Replies
View Related
Oct 9, 2014
I have this small project, I have this report that have the total of order along with the date of the order
SELECT sf.ORDER_QNT, dd.ACTUAL_DATE, dd.MONTH_NUMBER
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;
ORDER_QNT ACTUAL_DATE MONTH_NUMBER
1100 05/01/13 1
100 05/01/13 1
140 06/01/13 1
110 07/01/13 1
200 08/01/13 1
500 08/01/13 1
230 08/01/13 1
500 08/01/13 1
200 08/01/13 1
53 15/01/13 1
53 22/01/13 1
Now, I want to get the average for that month (average per day).
SELECT sum(sf.ORDER_QNT)/31 as AVGPERDAY
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;
AVGPERDAY MONTH_NUMBER
---------- ------------
113.785714 1
but instead putting 31, I'd like to pull the totaldays from the actual_date using the Extract function so I try this
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(to_date('05/01/13','dd/mm/rr'))) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;
AVGPERDAY MONTH_NUMBER
---------- ------------
113.785714 1
The result is nice, but now when I change the date with the dd.actual_date it gives error
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;
Error at Command Line : 1 Column : 53
Error report -
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
View 1 Replies
View Related
Nov 26, 2007
hi there
I am using SQL Server 2005 with Reporting Services (Using the Visual side - not direct code)
I am having problems understanding the dates. eg where to put them,
I want a report that runs on the 1st day of the month for the previous month. I know you can set up something in subscriptions but then how do I get my report header to say from .......to...............
I have been through the AW reports but can't see what I need.
Happy if someone wants to direct me to somewhere that has date examples.
cheers
Dianne
View 5 Replies
View Related
Jan 2, 2007
Daily report generating Monthly rollup stats
I have a daily report which each morning generates monthly information for the current month which was implemented in December. Everything was working correctly untill January 1st. On the 1st the report generated blank since it was suppose to generate 1-31 Dec but but the currently month was Jan, so it failed. How do I program it so if it is the 1st of a month generates the previous month but still would generate current month but while in the current month? Any help is appreciated.
SELECT GETDATE() - 1 AS rptdate, Errors.WTG_ID, lookup.Phase, Errors.STATUS_TYPE, Errors.STATUS_CODE, STATUS_CODES.STATUS_DEF, Errors.TIME_STAMP,
Errors.ANSI_TIME, lookup.WTG_TYPE, Errors.POSITION
FROM Errors INNER JOIN lookup ON Errors.WTG_ID = lookup.WTG_id RIGHT OUTER JOIN STATUS_CODES ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE
WHERE (STATUS_CODES.STATUS_DEF IS NOT NULL) AND (Errors.TIME_STAMP BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
ORDER BY Errors.WTG_ID, Errors.TIME_STAMP, position
View 5 Replies
View Related
Sep 1, 2015
I need to calculate the success rate of our OS Patch deployments. the data from system is stored in SQL with corresponding states (installed, missing, ...)
I would need to provide monthly report that shows how successful the deployment was for particular patchgroup. I have the following 2 dummy tables (just used as example)
table5 ==> Table containing patch groups + patches
table6 ==> Table with machines names, patches and patch state
select * from table5
pgrouppatch
GROUP1PATCH1
GROUP1PATCH2
GROUP1PATCH3
GROUP1PATCH4
[Code] ....
Result would be
pgroup install missing
group1 80% 20%
group2 50% 50%
group3 55% 45%
Ideally I would like to do this in T-SQL but if necessary can also do this in .NET Function. Only mention this but both Patchgroup and machines are dynamic each month can be different.
below are the sql scripts to create tables and populate with data
CREATE TABLE [dbo].[table5](
[pgroup] [varchar](128) NOT NULL,
[patch] [nvarchar](128) NOT NULL
)
CREATE TABLE [dbo].[table6](
[machinename] [varchar](128) NOT NULL,
[Code] ...
View 9 Replies
View Related
May 28, 2008
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
View 5 Replies
View Related
Jan 7, 2011
Currently, I have a report that takes two parameters: StartDate and EndDate.
I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well with StartDate and EndDate because the parameter is static. What is the most elegant way to implement this change?
View 5 Replies
View Related
Aug 4, 2006
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
View 2 Replies
View Related
Jan 14, 2008
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
View 2 Replies
View Related
Mar 7, 2014
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]...
View 6 Replies
View Related
Mar 27, 2014
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.
View 3 Replies
View Related
Jul 24, 2006
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.
View 4 Replies
View Related
May 2, 2007
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
View 3 Replies
View Related
May 3, 2007
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
View 5 Replies
View Related
Jan 14, 2007
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.
View 6 Replies
View Related
May 27, 2002
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
View 1 Replies
View Related
May 30, 2012
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
View 7 Replies
View Related
Oct 15, 2006
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
View 20 Replies
View Related
Dec 5, 2007
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.
View 1 Replies
View Related
Feb 1, 2006
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 Related
Jan 25, 2007
Hello,
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
View 3 Replies
View Related
Dec 5, 2007
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.
View 1 Replies
View Related
Mar 3, 2008
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
View 7 Replies
View Related
Dec 7, 2006
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.
View 7 Replies
View Related
May 7, 2012
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
View 2 Replies
View Related
Apr 3, 2008
I have a table based around requisitions, and each requisition has a number of positions. That number can change over time through updates to pertinent rows rather than through transaction-like records that record an entire history, and I'm only able to get a monthly snapshot of the table. What I decided to do is still use one table for OLAP (fact_requisitions) but add a column called period_key that refers to the month the data comes from. So if I have two months of data then the table has each requisition twice, possibly with differing position counts, and new requisitions from the second month are only present once. Then I tried to filter the MDX query like so:
SELECT {
([Dim TimeRequestClosed].[Year - MonthNumber].[Year_Text].&[2008].&[1],[Dim Requisitions].[Period].[Period Key].&[200801])
}
ON COLUMNS,
NON EMPTY
{
([Dim Location].[Region Name].MEMBERS, [Dim Location].[Period Key].&[200801])
}
ON ROWS
FROM
[Requisitions]
WHERE
[Measures].[Request Closed Date Count]
This query doesn't work even though the data is there, it just returns nulls. Am I going about this all wrong? If not, what might I be doing wrong, and how would I get the query to return more than one period (e.g. tell Dim Requisition to match up with Dim Location on the period key)?
View 2 Replies
View Related
Mar 6, 2014
How to create a stored procedure, or many stored procedures for generating a monthly sales report in our company.
We have two tables:
ITEM_SALES which consists of:
Item_ID
Name
Store
Sales_Date
Sales_Price
Quantity
And then
ITEM_DISCOUNT which consists of:
Item_ID
Name
Store
Sales_Price
Date_From
Date_To
Explanation: After each month, our different stores will send us a report on which discounts they had. These discounts can vary from one, to many days and therefor we have the Date_From, Date_to.
To make this sales report, i need a procedure that first fetches all sales in ITEM_SALES, then checks all the discounts in ITEM_DISCOUNT and overwrites the ones that have similar Item_ID, Name, and Store for a selected period.
Example: So if a item originally had a sales_price on 99,- and then had a discount sales_price to 79,- for 2014-01-02 to 2014-01-10 it has to be overwritten for that period so the report shows the right numbers.
View 6 Replies
View Related
