Transact SQL :: Return A Query Based On Dateadd Function
Aug 3, 2015
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
View 5 Replies
ADVERTISEMENT
Apr 30, 2015
We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation. What we need to do is break up the total quantity passed to the function and return the total of prices found. In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.
Low Qty High Qty Fee Actual Qty Price
0 7500 0.025 7500 187.50
7501 15000 0.020 2221 44.42
Below is the table definition that we have to work with (ugghh).
CREATE TABLE [dbo].[BillingPriceTable](
[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[PriceTableCode] [varchar](10) NULL,
[PriceTableName] [varchar](40) NULL,
[Code] ....
What we have so far is shown below. The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range. So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628
The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort.
ALTER FUNCTION [dbo].[fn_GetPrice]
(
@plincdvarchar(3),
@pgrpcodevarchar(4),
@pitmcodevarchar(4),
@qtydecimal(10,1) = 1,
@corpnbrvarchar(9)
)
[Code] ....
View 9 Replies
View Related
Jun 15, 2015
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)
WHEN 'Monday' then 'Monday'
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')
END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
View 7 Replies
View Related
Apr 11, 2015
I created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.
I am running a query but it never ends to run.
This is the function:
USE [RA_dev]
GO
/****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
This is how I am trying to use it in the View:
Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE
View 2 Replies
View Related
Feb 23, 2004
I have events which require certain things be done several days before the event and things be done several days after the event. I attempted to use the DATEADD function to subtract 3 days from the event date. The SQL Statement I created did just that, but, it displays 3 days back from today's date.
There are 2 tables:
CalendarCategories -- Table
CalCategoryID -- int field
CalCategoryName -- varchar field
CalendarEvents -- Table
CalCategoryID -- int Field
Title -- varchar Field
StartDate -- DateTime Field
I have to perform some tasks 3 days before the event. So, TODAY, I want to see a listing of those events which are scheduled for 3 days FROM NOW.
This is my current SQL Statement:
SELECT DATEADD(d,-3,StartDate) AS [Update Payoffs & Ins], Title AS [Closing Description] FROM CalendarEvents WHERE datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate())
This SQL Statement takes TODAY'S events and gives them a date of February 20. See example of the results at http://www.joelwilliamslaw.com/DesktopDefault.aspx?tabid=141
This is what I have already done relative to my calendar listings:
Specific Event Types for the Current Month :
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories
on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID
where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Specific Event Types for the Current Day:
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories
on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID
where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Your assistaince is much appreciated.
Joel
View 6 Replies
View Related
Jul 22, 2015
I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:
User Material = Wood-MDF
Preset Material = MDF Panel
I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?
View 2 Replies
View Related
Jun 23, 2015
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values
[Code] .....
View 2 Replies
View Related
Sep 19, 2007
Hello, DECLARE @x DECIMAL
SET @x = 65.554
SELECT ROUND(@x, 1)--this returns 66
SELECT ROUND(65.554, 1)--this returns 65.600 can someone explain to me why is like that?
Thank you
View 3 Replies
View Related
May 12, 2015
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
View 13 Replies
View Related
Jun 29, 2015
I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID,
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
View 4 Replies
View Related
Sep 29, 2015
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
View 20 Replies
View Related
Aug 12, 2013
I have a database that has a table that contains basic demographics
•First_name
•Last_name
•Address
•City
•State
•Zip phone
•email
•dob
•etc
•etc
Contained in this table are a lot of duplicate names, and even different addresses. I would like to return an entire row of UNIQUE information based upon “first_name + last name”. Now I know just enough of sql to pull out unique first + last names, but not the entire row based upon this unique information.
View 5 Replies
View Related
Feb 21, 2008
Hi ALL!anyone can help me resolve this problem.I create SQL sentence and using DATEADD to Update DATETIME FIELDand i need Increase 1 Year and Month is 04 and day is 01.example: origin datetime field : 2007/06/26result after update is : 2008/04/01so by DATEADD(yy,1, datefield)?Have any expression for datefield to set month and day like what i need?Thanks .
View 2 Replies
View Related
Jul 23, 2007
Hi,
Can someone please tell me why the following won't work:
Code:
SELECT * FROM Validation where CourseDate > DateAdd(wk, -3, GetDate())
I want to select all records where the coursedate is more than 3 weeks old.
Thanks.
View 2 Replies
View Related
Jan 3, 2005
:eek: Hi,
I'm getting problem while using DATEADD Function.
When I use DATEADD(MONTH, 1 '01/31/2005'), it returns 02/28/2005 and when I use DATEADD(MONTH, 1 '02/28/2005'), it returns 03/28/2005 but I want the result as 03/31/2005 ie last day of the month.
View 10 Replies
View Related
Nov 9, 2006
hi,
can something tell me what function do i need if i want to know what happen the last three hours?
for example: dateadd (hh, -3, getdate())
it seems not working?!!
View 2 Replies
View Related
May 23, 2008
I'm using a query that is not retrieving all of the data it should because of the datetime stamp. If I use the following in the where clause:
{table.date} between '4/1/2008' and dateadd(day, 1 '4/30/2008')
that works fine for April but not for other months, where I might get more data from the following month than I should.
Is there a way to add hours, minutes, and seconds (23:59:59), all at the same time, without moving ahead an entire day?
View 8 Replies
View Related
Jul 23, 2005
I need help in T-SQL.I am using DATEADD function and I want to add 6 months to a date. Butit does not return me the rusults, which I wante.g. SELECT DATEADD(m,'20040630') returns 20041230which is logical correct? But I want it to return end of month (i.e.20041231)Any help in this context will be highly appreciatedAbdul N. Khan
View 2 Replies
View Related
Sep 7, 2007
Problem:
I have two nearly identical querys that are running. One of the query's is using the SQL DATEADD function. These query's are running on a SLQ2005 MSDE. There is now service pack four applied to this server.
The question is:
Are there any known issues with the DATEADD function in SQL 2005? This query is used in a report that has been in use for about two years. There were some changes made to the server (BIOS upgrade) including adding this service pack. These changes did not cause any other SQL issues. This query now takes almost five minutes to run. The nearly identical query (the difference is the date is passed in as a parameter rather than using the DATEADD function) takes from one to two seconds. These reports have a history of nearly identical running time.
What is a good way to try and correct the issue or at least to try and isolate the problem? Have there been any known issues with this function?
Thanks for any help!
View 2 Replies
View Related
Aug 22, 2000
DATEADD(par1, par2, par3)
I am using the dateadd function to return a recordset. The function seems to allow me to plug variables into parameter2 if its a valid number type, and paramter3 as long as its a valid date type. How ever the function is not allowing me to use a variable into parameter1. I have tried a varchar and all other data types and can't get it to work. Any thoughts?
View 1 Replies
View Related
May 18, 2007
Hi, I encountered a strange error today. I have an Integer parameter "hours" that I am trying to use in my SQL Query.
DATEADD(hh, @hours, @startTime)
It works if I have it set such as DATEADD(hh, 8, @startTime), but I need that parameter there for what I need.
I get this error:
Error Source: System.Data
Error Message: Failed to convert parameter value from Decimal to DateTime. I tried a variety of CInt and other conversion functions to no avail.
Any ideas?
View 5 Replies
View Related
Mar 7, 2008
I currently have this statement:
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,
I would like to return however instead of column name 'MonthLessEleven' the actual month and year that is -11 from getdate().
I've tried the month(dateadd("m',-11, getdate())
but it returns syntax errors. I'm doing this in Visual Studio. Could anyone help me out? Thanks.
View 10 Replies
View Related
Jan 4, 2008
Im using the DateAdd Function to establish a future date base on the required time for a series of events to transpire. I'd like to exclude weekends, does anyone know a way to do this?
Thanks in advance
Alex
View 1 Replies
View Related
Oct 9, 2015
I have a table which contains a 2 columns "LoanID" and "LoanStatus" and the values in "LoanStatus" are either 0 or 1 in it. If I run a select statement; I get:
LoanID LoanStatus
1 0
2 1
3 1
4 1
5 0
I need a query to return the following from the LoanStatus (distinct Values):
1 NA
0 Current
-1 Late
View 8 Replies
View Related
Oct 2, 2007
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
View 3 Replies
View Related
Jan 21, 2008
Hello all:
How can I return the result of a SELECT statement from a stored procedure (function)?
CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN
// Is this right??
RETURN SELECT * FROM authors
END
Thanks!
View 12 Replies
View Related
Aug 9, 2007
Hello,
I have do a sql function for return a list of element from a query send in variable.
When I test the function on self I have no problem.
But when I use the function in a sql query I have problem.
example :
Code SnippetSELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL,
dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
AS V_PROCESS_ITEMS
FROM APPLI_SUPPLIER INNER JOIN
APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))
This is the error :
Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24
Only functions and extended stored procedures can be executed from within a function.
When I do an exec of the function I have this problem :
Code Snippet
exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'
This is the function
Code Snippet
CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM]
(@QUERY AS VARCHAR(3900)=null)
RETURNS varchar(8000)
AS
BEGIN
-- Insert statements for procedure here
declare @v_List_ITEM as varchar(8000)
set @v_List_ITEM=''
if @QUERY is not null
Begin
declare @cur_Lect_ITEM CURSOR;
declare @FUNCTION AS NVARCHAR(4000);
Declare @ITEM as VARCHAR(255);
SET @FUNCTION = 'set @mainCursor=cursor for ' + @QUERY + ' for read only open @mainCursor'
EXEC sp_executesql @FUNCTION,N'@mainCursor cursor output', @cur_Lect_ITEM output
fetch next from @cur_Lect_ITEM into @ITEM
while @@fetch_status=0
begin
set @v_List_ITEM=@ITEM + ' ; ' + @v_List_ITEM
fetch next from @cur_Lect_ITEM into @ITEM
end
deallocate @cur_Lect_ITEM
SET @v_List_ITEM=REPLACE(REPLACE(@v_List_ITEM, CHAR(13), ''), CHAR(10), '')
set @v_List_ITEM=left(@v_List_ITEM,len(@v_List_ITEM)-3)
End
RETURN @v_List_ITEM
END
Can you help me please?
Thank you
View 11 Replies
View Related
May 22, 2015
I have got this in my query,
WHERE (((tblVehicles.Veh_Parts_Date)>=DateAdd("m",-12,Date()))
How do I say IIF Null return values anyway?
View 7 Replies
View Related
Jul 21, 2015
What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100 and therefore the TSQL would look like
SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc
Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.
View 6 Replies
View Related
Oct 16, 2015
I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below
select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date"
from mantis_bug_table bug
INNER JOIN mantis_category_table cat on cat.id = bug.category_id
LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version
[code]....
View 12 Replies
View Related
Jul 8, 2015
My overall problem is to do some string manipulation and then DATEADD, but I still don't know why I getting what SQL is giving me.
To get the prior month, I use the following code;
SELECT
(
SELECT
RIGHT('00' + CAST(
CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
WHEN 0 THEN 12
ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1
END AS varchar(2)), 2)
)--, -- Month
--CAST(CAST(LEFT(MAX(Eff_Period), 4) AS int) - 1 AS varchar(4)) -- Year
FROM
Current_Membership;
This correctly gives me the proper month number for the prior month, in this case, '06' since the current month is '07.'
But, for debugging, I wanted to test to make sure it would properly adjust for January - '01' - by replacing MAX(Eff_Period) with '201501.'
It did. Worked fine. Then, to make really sure, I put in '201502.'
Instead of the single record of '01,' it shot out 177,209 rows of all '02's!
I put back in the MAX(Eff_Period) instead of the date string constant and, worked fine. Swapped back in the date string - boom! - 177,209 rows again.
Like I said, I can use DATEADD - Now I'm really confused. I changed the date string to '201501' just to verify before posting this, and it spit out 177,209 rows of '01's! So, to keep from altering things and to maintain the edit history, I started up another query and C&P'd everything to it. Then, on the original, I backed up to an earlier version, one that worked. That listing is the same as above, but the commented out comma in line 9 and the commented CAST in the line after it - to get the year - were uncommented.
Now, it works with both '201501' and '201502.' But, if I replace the comments, basically removing the CAST with the year, it slams back with 177,209 rows!
View 6 Replies
View Related
Sep 16, 2015
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings.
- SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
View 14 Replies
View Related
Oct 1, 2015
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0
View 4 Replies
View Related
