Writing A Monthly Report With Stored Procedure
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
ADVERTISEMENT
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
Jun 25, 2014
I am making a stored procedure for monthly sales. In the stored procedure we have a Discount. This discount can be fetched from three different tables. If the discount is not in id.rabatt, it should fetch from dp.rabatt, if its not there, it should fetch from ds.rabatt. So the first two ones can be empty, while the last one always has a discount..
Im having big trouble designing the WHEN part of the procedure.
CASE (
when
Isnull(id.rabatt, Isnull(u.rabatt, id.rabatt)) then..
when
Isnull(dp.rabatt, Isnull(x.rabatt, id.rabatt)) then..
when
Isnull(ds.rabatt, Isnull(y.rabatt, id.rabatt)) then..
end)
AS 'Discount',
The reason i have to use Isnull is that inside each Discount table, i also have two different discounts, one that lasts forever(2999) and one that have a selected period. Like i show here:
LEFT OUTER JOIN discount AS id
ON id.identifiers = isa.identifiers
AND id.store = BV.name
AND id.from_date <= isa.sales_date
AND id.to_date >= isa.sales_date
AND id.to_date < '2999-01-01'
LEFT OUTER JOIN discount AS u
ON u.identifiers = isa.identifiers
AND u.to_date = '2999-01-01'
The two others tables are designed in similar ways
View 1 Replies
View Related
Mar 8, 2005
I have a Orders and OrderDetails table having the columns listed below:
Orders (OrderID, OrderDate, CustomerID, ...)
OrderDetails (OrderID, ProductID, UnitPrice, Quantity, ...)
Now, I want to obtain monthly sales from the data in the two tables by passing in a Year parameter. How to develop such a stored procedure? I have no idea where to get started. I was thinking to call a SELECT statement on each month. But, things trouble me are:
1. how to loop through each month to make a SELECT query for each month for a given Year? Use a cursor or what?
2. how to determine month boundary for a given year and construct the where clause on OrderDate using the month boundary for the SELECT query ? What happens if it is a leap year?
3. how to stop processing for the rest of the year when last order month is done?
Any suggestion would be appreciated. Thanks.
View 1 Replies
View Related
Dec 20, 2007
I'm developing a library and want to display the alphabets across the screen. When a user clicks on one of the alphabets I want all titles beginning with that letter to appear on the screen.
How would I write this stored procedure?
My table is called Titles
Fields: Title ID Titles
Thanks!
View 2 Replies
View Related
Feb 2, 2008
How would I write a stored procedure to get * where duedate is lessthan and not equal to today's date. Is this right?select * from librarywhere duedate < != getdate()Thanks
View 2 Replies
View Related
Oct 30, 2007
hi,
i am new to this.
in my aspx page, i am having a dropdown which has two value
Name
EmpID
if user clicks on name from drop down, user has enter the name of employee in a textbox. And if user clicks on EmpID, he has to enter the employee ID in textbox.
Based on selection the data will be displayed in grid view.
In my database, i have a table called EmpInfo, which has three fields - EmpName, EmpID and State.
can anybody help me in writing a stored procedure for this??
Thanks
Jaimin
View 3 Replies
View Related
Jul 7, 2006
Hi,
I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0.
Please refer some good tutorial for it.
Regards,Imran Ghani
View 1 Replies
View Related
Mar 7, 2005
I am trying to write a stored procedure which would execute following logic:
- The stored procedure takes 2 optional parameters @StartDate and @EndDate
@StartDate Datetime = null
@EndDate Datetime = null
- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below
CREATE PROCEDURE SearchDocumentTable
@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null
AS
IF ( @StartDate IS NULL)
Select @StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@FName,'%')
AND LName LIKE ISNULL(@LName,'%' + NULL)
AND ID LIKE ISNULL(@ID,'%' + NULL)
AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated
Thanks in advance :)
View 7 Replies
View Related
Sep 26, 2013
Want to write from a table variable to a text file from a stored procedure.Read about xp_cmdshell bcp etc. but worried because it's supposed to be a security problem and needs to be from a permanent database.Also am getting error "The EXECUTE permission was denied on the object 'xp_cmdshell'..."
1. Is xp_cmdshell a bad idea to use even if I get permissions ?
2. Can a "permanent" table be used in a stored procedure starting out fresh each time with 0 rows rather than use a table variable ?
View 2 Replies
View Related
Mar 29, 2006
hi
Writing to text file from table/view is done using osql,bcp etc. How do we write output of stored procedure into text file??
Thank you
View 4 Replies
View Related
Aug 7, 2007
Was wondering which is better, writing the SQL code within the execute SQL task or calling a SP from within it with respect to performance? and what is the best practice? Also is there any way to incoporate the logic of a cursur within SSIS?
Thanks
View 3 Replies
View Related
Mar 29, 2007
Hi Everybody,
I am trying to update a column Percentage in a table named Critical Doctors with a column named
PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from
tblPercent.
I am getting an error message for the following query.
Have Two tables
1.CriticalDoctors
2.tblPercent
update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=
, >, >= or when the subquery is used as an expression.
The statement has been terminated.
Pls give me reply on how to write a stored procedure so that I can equate the percentage column
with one value and also check the condition with one value.
Thanking you guys in advance.
madhav
View 4 Replies
View Related
Feb 26, 2015
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
View 6 Replies
View Related
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
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
May 30, 2008
suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?
public void storeOperate(string stringParameter,string name) { string[] strs=stringParameter.Split('&'); SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString()); SqlCommand cmd=new SqlCommand(name,conn); cmd.CommandText=name; cmd.CommandType=CommandType.StoredProcedure; foreach(string str in strs) { cmd.Parameters.Add(".....",SqlDbType........).Value=str; //my trouble } conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.Dispose(); }
View 2 Replies
View Related
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
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
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
Apr 26, 2006
I get this error message:
Semantic query execution failed.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type.
The report model does have a perspective.
Anyone else seen this?
View 9 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
Sep 23, 2004
Hi,
Is there a way I can display the data returned from more than one stored procedure in a report using reporting services. Or can I use more than one dataset in the report????
View 3 Replies
View Related
Apr 19, 2004
i have the stored procedure in the SQl and i m using the crystal report how to pass the paramaters to the stored procedure ..from the crystal report .:confused:
pls its urgent:mad:
View 1 Replies
View Related
Jul 16, 2007
Hi, there,
I have a report that requires to join a lot of tables.
May I ask if I can use a stored procedure to make all the joins and populate a temp table, then make the report sit on the temp table. so every time the user runs the report, it will kick off the stored procedure which will truncate the temp table and repopulate it, then show the report.
Thanks
View 4 Replies
View Related
Aug 22, 2007
Hi
I'm not sure if this is possible, i want to create a report from a stored procedure, all the stored procedure does is select data from my database, however, the code is 1800+ lines of code, it executes in about 1 minute, which is fine. My problem is that i can't paste 1800+ lines of code into the dataset in Reporting Services, so is it possible to get the values from my stored procedure into reporting services so that i can use them to design and execute my report?
Or musst i find an alternative way to do this?
Any help or suggestions will be greatly appreaciated and welcome.
Thanks in advance
Kind Regards
Carel Greaves
View 3 Replies
View Related
