Trouble Writing Stored Procedure To Retrieve Records By Selected Month And Year
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!
hi, i am generating report for my project. my table goes like ID VASID VASSID JanMail JanVisit JanPhone JanComment FebMail FebVisit FebPhone FebComment................. DecPhone 1 25 4 True False False Mail me False True False Visit me
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively.. once user check on Mail and select a month suppose January in my report,the report should generate only January Comments. here user can select any number of months.if user selects january,february and march, report should generate respective months Comments. Please help me in writing SQL Query to get the comments.......
if any one know the solution for the above problem,Please help me
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.
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 m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS SALES Sales Net Sales 444.65 Sales Sales LESS TRD 22.55 SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD) Finance LESS ED LESS ED 40 Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... .. production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime, @todate datetime, @category varchar(200) as begin create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' ) begin select responsibility, dept, category, round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
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 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 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'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?
I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource2"> <Columns> <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False" ReadOnly="True" SortExpression="member_id" /> <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" /> <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" /> <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" /> <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" /> <asp:CommandField ShowEditButton="True" /> <asp:CommandField ShowDeleteButton="True" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>" SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure" DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" > <DeleteParameters> <asp:Parameter Name="member_id" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO
could someone please let me know if i am declaring the parameter wrong or have the wrong VB CODE. I receive my 3 column headers in my datagrid but the parameter isn't doing anything STORED PROCEDURE CREATE PROCEDURE USP_Machcusidsearch @Machcusid nvarchar OUTPUTAS SELECT dbo.Machine.machcustomID, dbo.Problem.ProblemDesc, dbo.Request.ReqDateFROM dbo.Machine INNER JOIN dbo.Request ON dbo.Machine.machID = dbo.Request.MachID INNER JOIN dbo.Problem ON dbo.Request.ProblemID = dbo.Problem.ProblemIDwhere machcustomID = @MachcusidGO VB.NET CODE Private Sub LSBmachcusid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LSBmachcusid.SelectedIndexChanged SqlDataAdapter2.Fill(DsMachcusidsearch1) SqlSelectCommand2.Parameters("@Machcusid").Value = LSBmachcusid.SelectedItem.Value DGstatussearch.DataBind() End Sub End Class
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!
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
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
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
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??
I have calenders activated in our reports.So, I want only those date values to be enabled for the year I am selecting . Is there any option for that?????
When I try to run these queries in my stored procedure by hardcoding officeids, they run fine, but when I use the parameter @officeid and then try to run the procedure by using getEmployeeInfo_proc '001' I get no results. Can anyone see what I could be doing wrong? Thanks Here is my code:
1 SET QUOTED_IDENTIFIER ON 2 GO 3 SET ANSI_NULLS ON 4 GO 5 6 7 ALTER PROCEDURE getEmployeeInfo_proc (@officeid varchar(10))AS 8 select distinct 9 a.emplid, a.name,a.last_name, a.first_name, a.status, a.officeid, 10 b.deptid_child_node, b.dept_child_descr, a.job_descr, 11 d.officename 12 from employeeinfo_vie a, 13 dept_all_nodes_tbl b, 14 office_tbl d 15 where 16 a.status in ('A','L','P') and 17 a.officeid = d.officeid and 18 a.deptid = b.deptid_child_node and 19 a.officeid = '@officeid' 20 21 UNION 22 23 select distinct 24 e.emplid, e.name, e.last_name, e.first_name, e.status, e.officeid, 25 b.deptid_child_node, b.dept_child_descr, f.job_descr, 26 d.officename 27 from phone_admin_tbl e, 28 dept_all_nodes_tbl b, 29 office_tbl d, 30 jobs_tbl f 31 where 32 e.status in ('A','L','P') and 33 e.officeid = d.officeid and 34 e.deptid = b.deptid_child_node and 35 e.job_code = f.job_code and 36 e.officeid = '@officeid' 37 order by name 38 39 return 40 41 GO 42 SET QUOTED_IDENTIFIER OFF 43 GO 44 SET ANSI_NULLS ON 45 GO 46 47 48 49 50
I'm having trouble with this stored procedure, it gets down to the insert and then times out. Can anyone see why the insert might be wrong? I did check and at least one of the workshops is open and the @Status is showing it as Open on the client.ALTER PROCEDURE [dbo].[AddNewWorkshopAssigned]
SET @Status = (SELECT CASE WHEN (tblWorkshop.Workshop_Status) = 'Canceled' THEN 'Canceled' WHEN (tblWorkshop.Workshop_Size - COALESCE(COUNT(tblWorkshopsAttended.client_ID),0)) >= 1 THEN 'Open' ELSE 'Closed' END AS Current_Status FROM tblWorkshop INNER JOIN tblWorkshopsAttended ON tblWorkshop.Workshop_ID = tblWorkshopsAttended.workshop_id WHERE tblWorkshopsAttended.Workshop_ID=@Workshop_ID AND tblWorkshopsAttended.Wait_list = 0 GROUP BY tblWorkshop.Workshop_ID,tblWorkshop.Workshop_Size,tblWorkshop.Workshop_Status)
--Change Workshop Status if NOT canceled. If @Status <> 'Canceled' UPDATE tblWorkshop SET Workshop_Status=@Status WHERE tblWorkshop.Workshop_ID=@Workshop_ID
IF @Status = 'Open'
INSERT INTO tblWorkshopsAttended (Workshop_ID,Client_ID,Wait_list,DateEntered,EnteredBy)
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
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...
- 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
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 :)
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 ?
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?
I'm trying to use the "sp_columns" stored procedure to pull in some information about a table in my db, but I'm continuing to get the same error:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'sp_columns' expects parameter '@table_name', which was not supplied.
I'm not sure why, but I've re-written my code several times and can't figure out just why this error is happening. Here's a snippet of the code:
Private Function GetDataType(ByVal columnName As String, ByVal table As String) As String Dim con As New OdbcConnection(ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection")) Dim com As New OdbcCommand("sp_columns", con) com.CommandType = CommandType.StoredProcedure
Dim param As New OdbcParameter("@table_name", table) param.OdbcType = OdbcType.VarChar com.Parameters.Add(param)
param = New OdbcParameter("@column_name", columnName) param.OdbcType = OdbcType.VarChar com.Parameters.Add(param)
Dim dr As OdbcDataReader Dim name As String
con.Open() dr = com.ExecuteReader() 'THIS TRIGGERS THE ERROR While dr.Read name = dr("TYPE_NAME") End While dr.Close() con.Close()
Dear Forum, I am adding a new column name to my Stored Procedure called HeadlinerID. It is an Int that is 4 characters long. I seem to be putting this in incorrectly in my stored procedure. I have tried it like: @HeadlinerID int(4), and @HeadlinerID int, and both ways I get the error below: Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’. Is there a trick to putting in integers in a stored procedure?
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?
