Hi guys,
I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
@ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.
All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.
My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?
I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way? Many thanks.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???
SELECT * FROMtbl_Feedback WHERE tbl_Feedback.DateIn >= @startDate GO
--- ADO Web page
' Stored Procedure Name: sp_009_SiteLead ' Site Lead Default Query
set cmd=server.CreateObject("ADODB.command") set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput) set cmd.ActiveConnection=DBConnection cmd.CommandText="sp_009_SiteLead" cmd.Parameters.Append peDate cmd.Parameters.Append psdate psDate.Value = "10/01/2004" Set rsObj = CreateObject("ADODB.Recordset") rsObj.ActiveConnection = DBConnection rsObj.CursorLocation = adUseClient rsObj.PageSize= 20 rsObj.Open cmd
Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it. Can anyone help?
Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission. How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?
At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.
I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0 ,@OriginalMax DECIMAL=0 AS DECLARE @CRI8 VARCHAR(500)SELECT @CRI1='' SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax' SELECT @Criteria = ......+ @CRI8 When I execute this stored procedure, I get the following error message. SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin'. Msg 137, Level 15, State 2, Line 1 Must declare the variable '@OriginalMin Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
I have a Stored Procedure as followsUSE [MyDataBase] GO /****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[SPLogins] ( @LoginName varchar(50), @LoginD int output, ) AS Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
I am trying to check for a null value in the Stored Procedure. I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value What Is the correct wayt to go about it.
Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks.
HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
Hi, guys I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table. could you correct my code, if you know what is the problem?
thanks
My contract table DDL: ************************************************** ***
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
My insert stored procedure is: ************************************************** *****
create proc sp_insert_new_contract ( @contractDate[smalldatetime], @tuition [money], @studentId[char](4), @contactId[int]) as
if not exists (select studentid from student where studentid = @studentId) begin print 'studentid is not a valid id' return -1 end
if not exists (select contactId from contact where contactId = @contactId) begin print 'contactid is not a valid id' return -1 end begin transaction
/*Error Check */ if @@error !=0 or @@rowcount !=1 begin rollback transaction print ‘Insert is failed’ return -1 end print ’New contract has been added’
I am creating a table that will check the age range for an attribute called Age. I am experiencing trouble when I run the code. Am I writing this code correctly?
Hi Guys, Need your help on this problem. Let say table name call tbl_range and 2 field call No1 and No2 I have this set of record :- No1 No2 1000 2000 2001 3000 5000 6000
My problem, i want to check if user insert another set no No1 : 1500 No2 : 2500. So, this means that range already clash with another range. If this happened it will return 2 record (1000 - 2000) and (2001 - 3000). Can it be done and how?
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.
I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:
Date Temperature 09/01/2012 73.5 09/02/2012 75.2 09/03/2012 76.3 09/04/2012 73.3 09/05/2012 77.0 09/06/2012 74.5 and so on.
I am using this to get the dates listed: WITH CTE_DatesTable AS ( SELECT CAST('20120901' as date) AS [Date] UNION ALL SELECT DATEADD(dd, 1, [Date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [Date]) <= '20120930' ) SELECT [Date] FROM CTE_DatesTable
How could I get the temperature if I did a sub-query here?
I have a report that I need to run on 2 different date ranges.
Both report's data is 2 days behind today's date. so... WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2) OR SOMETHING LIKE THAT, NO BIGGIE HERE
The 2nd report is a month to date report. This is the 1 I can't figure out. WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)
So that would look like WHERE reportdate between 1/1/2007 and 1/21/2007
My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this WHERE reportdaye between 2/1/2007 and 1/30/2007
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year. Any ideas?
We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.
I have a CommisionAudit table that tracks changes in commission rate with the following values.
ID | Commission Change | UpdatedOn ---------------------------------------------- 1 | Change from 20->25 | 03/07/2007 09:00:00 ---------------------------------------------- 2 | Change from 25->35 | 10/09/2007 17:00:00 ---------------------------------------------- 3 | Change from 35->20 | 01/10/2007 16:00:00 ---------------------------------------------- 4 | Change from 20->26 | 11/10/2007 10:00:00 ----------------------------------------------
with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.
There is a stored procedure to run each Sunday. I would like to get the order date from previous Sunday through Saturday. Right now we code as "OrderDate Between convert(datetime, '05/02/99') AND convert(datetime, '05/08/99') " Each week the dates have to be manually changed. How to use the date function to make it automated?