|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
No Of Months - FunctionHi,
This is what I want to do: User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). Store Table : Store No. INT (IDENTITY) OpenDate (DATETIME) When the user calls a S Proc with the above parameters, I want to check whether the Store was opened for 8 Months from the OpenDate on the store, if the OpenDate is 22/02/2004 then the number of months should be calculated from the next month, not including the month the store opened. I would like to write this in a Function, please help. Thanks Vishal (vrajpu***@hotmail.com) writes:
Show quote > This is what I want to do: SELECT CASE WHEN datediff(MONTH, OpenDate, > > User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). > > Store Table : > Store No. INT (IDENTITY) > OpenDate (DATETIME) > > When the user calls a S Proc with the above parameters, I want to check > whether the Store was opened for 8 Months from the OpenDate on the > store, if the OpenDate is 22/02/2004 then the number of months should be > calculated from the next month, not including the month the store > opened. > > I would like to write this in a Function, please help. @year + CASE WHEN @month <= 9 THEN '0' ELSE '' END + ltrim(str(@month) + '01') + 1 >= @openmonth THEN 1 ELSE 0 END FROM tbl WHERE StoreNo = @StoreNo I'm asuming here that @year is a string and @month is passed as a number. I don't know exactly where you intend to use your function, but if the plan is to put into a bigger query for a WHERE condition, I would recommend you to not implement it as a function, at least not with table access, as this have performance negatively quite considerable. The only tricky thing here really is to form a date from @year and @month. Note that datediff counts number of period changes, so datediff (MONTH, '19990101', '19990228') and datediff(MONTH, '19990131', '19990201') yields the same result. Thus, we can work from OpenDate correctly, and just add one more month for the opening month. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx SELECT 'WasOpen'=
CASE WHEN DATEDIFF(mm, DATEADD(mm, DATEDIFF(mm, 0, OpenDate)+1, 0), --Month After store opened CAST(@Year + RIGHT('0' + CAST(@Month AS varchar(3)),2) + '01' AS datetime)) > @OpenMonths THEN 1 ELSE 0 FROM [Store Table] WHERE [Store No] = @StoreNo Assuming that both year and month variables are passed as characters, and the application does not place a '0' before the months 1 through 9. Show quote "Vishal" wrote: > Hi, > > This is what I want to do: > > User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). > > Store Table : > Store No. INT (IDENTITY) > OpenDate (DATETIME) > > When the user calls a S Proc with the above parameters, I want to check > whether the Store was opened for 8 Months from the OpenDate on the store, if > the OpenDate is 22/02/2004 then the number of months should be calculated > from the next month, not including the month the store opened. > > I would like to write this in a Function, please help. > > Thanks > > > using System;
using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean IsOpenedForEight(DateTime opened) { if (opened == DateTime.Parse("2/22/2004")) { if (DateTime.Parse("3/1/2004") >= DateTime.Now) return true; return false; } DateTime newDate = opened.AddMonths(8); if (newDate <= DateTime.Now) return true; return false; } }; -- Show quoteWilliam Stacey [MVP] "Vishal" <vrajpu***@hotmail.com> wrote in message news:ekQchGDCGHA.3744@TK2MSFTNGP10.phx.gbl... > Hi, > > This is what I want to do: > > User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). > > Store Table : > Store No. INT (IDENTITY) > OpenDate (DATETIME) > > When the user calls a S Proc with the above parameters, I want to check > whether the Store was opened for 8 Months from the OpenDate on the store, > if > the OpenDate is 22/02/2004 then the number of months should be calculated > from the next month, not including the month the store opened. > > I would like to write this in a Function, please help. > > Thanks > oops. This instead:
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean IsOpenedForEight(DateTime opened) { if (opened == DateTime.Parse("2/22/2004")) opened = DateTime.Parse("3/1/2004"); DateTime newDate = opened.AddMonths(8); if (newDate <= DateTime.Now) return true; return false; } }; -- Show quoteWilliam Stacey [MVP] "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:Ocfx5yMCGHA.3876@tk2msftngp13.phx.gbl... > using System; > using System.Data; > using System.Data.SqlClient; > using System.Data.SqlTypes; > using Microsoft.SqlServer.Server; > > public partial class UserDefinedFunctions > { > [Microsoft.SqlServer.Server.SqlFunction] > public static SqlBoolean IsOpenedForEight(DateTime opened) > { > if (opened == DateTime.Parse("2/22/2004")) > { > if (DateTime.Parse("3/1/2004") >= DateTime.Now) > return true; > return false; > } > > DateTime newDate = opened.AddMonths(8); > if (newDate <= DateTime.Now) > return true; > return false; > } > }; > > -- > William Stacey [MVP] > > "Vishal" <vrajpu***@hotmail.com> wrote in message > news:ekQchGDCGHA.3744@TK2MSFTNGP10.phx.gbl... >> Hi, >> >> This is what I want to do: >> >> User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). >> >> Store Table : >> Store No. INT (IDENTITY) >> OpenDate (DATETIME) >> >> When the user calls a S Proc with the above parameters, I want to check >> whether the Store was opened for 8 Months from the OpenDate on the store, >> if >> the OpenDate is 22/02/2004 then the number of months should be calculated >> from the next month, not including the month the store opened. >> >> I would like to write this in a Function, please help. >> >> Thanks >> > > Hi,
Im trying to run the following in SQL Query Analyzer & getting an error, I have just hardcoded the numbers for testing SELECT CASE WHEN datediff(MONTH, store.open_date, 2005 + CASE WHEN 11 <= 9 THEN '0' ELSE '' END + ltrim(str(11) + '01') + 1 >= 8 THEN 1 ELSE 0 END FROM tbl WHERE StoreNo = @StoreNo ERROR MESSAGE is - Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near '>'. Thanks, looking forward to some more help. Show quote "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:eLKS73MCGHA.2356@tk2msftngp13.phx.gbl... > oops. This instead: > > using System; > using System.Data; > using System.Data.SqlClient; > using System.Data.SqlTypes; > using Microsoft.SqlServer.Server; > > public partial class UserDefinedFunctions > { > [Microsoft.SqlServer.Server.SqlFunction] > public static SqlBoolean IsOpenedForEight(DateTime opened) > { > if (opened == DateTime.Parse("2/22/2004")) > opened = DateTime.Parse("3/1/2004"); > > DateTime newDate = opened.AddMonths(8); > if (newDate <= DateTime.Now) > return true; > return false; > } > }; > > -- > William Stacey [MVP] > > "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message > news:Ocfx5yMCGHA.3876@tk2msftngp13.phx.gbl... >> using System; >> using System.Data; >> using System.Data.SqlClient; >> using System.Data.SqlTypes; >> using Microsoft.SqlServer.Server; >> >> public partial class UserDefinedFunctions >> { >> [Microsoft.SqlServer.Server.SqlFunction] >> public static SqlBoolean IsOpenedForEight(DateTime opened) >> { >> if (opened == DateTime.Parse("2/22/2004")) >> { >> if (DateTime.Parse("3/1/2004") >= DateTime.Now) >> return true; >> return false; >> } >> >> DateTime newDate = opened.AddMonths(8); >> if (newDate <= DateTime.Now) >> return true; >> return false; >> } >> }; >> >> -- >> William Stacey [MVP] >> >> "Vishal" <vrajpu***@hotmail.com> wrote in message >> news:ekQchGDCGHA.3744@TK2MSFTNGP10.phx.gbl... >>> Hi, >>> >>> This is what I want to do: >>> >>> User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). >>> >>> Store Table : >>> Store No. INT (IDENTITY) >>> OpenDate (DATETIME) >>> >>> When the user calls a S Proc with the above parameters, I want to check >>> whether the Store was opened for 8 Months from the OpenDate on the >>> store, if >>> the OpenDate is 22/02/2004 then the number of months should be >>> calculated >>> from the next month, not including the month the store opened. >>> >>> I would like to write this in a Function, please help. >>> >>> Thanks >>> >> >> > > Vishal (vrajpu***@hotmail.com) writes:
Show quote > Im trying to run the following in SQL Query Analyzer & getting an error, I So the general recommendation is that you should include:> have just hardcoded the numbers for testing > > SELECT CASE WHEN datediff(MONTH, store.open_date, > 2005 + CASE WHEN 11 <= 9 THEN '0' ELSE '' END + > ltrim(str(11) + '01') + 1 >= 8 > THEN 1 > ELSE 0 > END > FROM tbl > WHERE StoreNo = @StoreNo > > > ERROR MESSAGE is - > Server: Msg 170, Level 15, State 1, Line 7 > Line 7: Incorrect syntax near '>'. > > Thanks, looking forward to some more help. 1) CREATE TABLE statements for your table. 2) INSERT statements with sample data. 3) The desired result given the sample. This makes it very easy to copy and paste to develop a tested solution. If the person who is asking is not prepared to make the effort of providing a test case, I tend to just type something up, and yes, it may have syntax errors. The assumption is then that the person asking is at least prepared to sort out the syntax errors himself. Yes, I could point to where the syntax error is, but somehow I don't really feel like spoon-feeding adult people. When I share a solution, my hope is that the person who asks looks at it, and tries to understand it, and eventually learns something for the future. The above will not work anyway, even if you fix the syntax error. As I noted in my previous post, the year was assumed to be a string. Now it's an integer, so the entire expression will be converted to a number (because of the type-precendence rules in SQL Server) and it goes downhill from there. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanx Erland for your input, I have been trying to get this working since a
day now and I am just getting started with SQL. Regards Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns97376DE415F2Yazorman@127.0.0.1... > Vishal (vrajpu***@hotmail.com) writes: >> Im trying to run the following in SQL Query Analyzer & getting an error, >> I >> have just hardcoded the numbers for testing >> >> SELECT CASE WHEN datediff(MONTH, store.open_date, >> 2005 + CASE WHEN 11 <= 9 THEN '0' ELSE '' END + >> ltrim(str(11) + '01') + 1 >= 8 >> THEN 1 >> ELSE 0 >> END >> FROM tbl >> WHERE StoreNo = @StoreNo >> >> >> ERROR MESSAGE is - >> Server: Msg 170, Level 15, State 1, Line 7 >> Line 7: Incorrect syntax near '>'. >> >> Thanks, looking forward to some more help. > > So the general recommendation is that you should include: > > 1) CREATE TABLE statements for your table. > 2) INSERT statements with sample data. > 3) The desired result given the sample. > > This makes it very easy to copy and paste to develop a tested solution. > > If the person who is asking is not prepared to make the effort of > providing > a test case, I tend to just type something up, and yes, it may have syntax > errors. The assumption is then that the person asking is at least prepared > to sort out the syntax errors himself. > > Yes, I could point to where the syntax error is, but somehow I don't > really feel like spoon-feeding adult people. When I share a solution, > my hope is that the person who asks looks at it, and tries to understand > it, and eventually learns something for the future. > > The above will not work anyway, even if you fix the syntax error. As > I noted in my previous post, the year was assumed to be a string. Now > it's an integer, so the entire expression will be converted to a number > (because of the type-precendence rules in SQL Server) and it goes > downhill from there. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx --I created a test table:
CREATE TABLE [dbo].[Store] ( [StoreNo] [int] IDENTITY (1, 1) NOT NULL , [OpenDate] [datetime] NOT NULL ) ON [PRIMARY] GO --I'm assuming the parameters are numeric because you did not have any quotes in your original post. --Also, I assumed a four digit year. If you always send a four digit year, remove the century logic. --The code constructs a search limit date using an unformatted string in yyyymmdd format which is safe regardless of the computers regional and date format settings. --declare and set parameters to simulate what would be in the stored procedure declare @StoreNo int declare @Month int declare @Year int declare @OpenMonths int set @StoreNo = 3 set @Month = 11 set @Year = 2005 set @OpenMonths = 8 --add the century, if needed if len(@Year) = 2 begin if @Year < 50 begin set @Year = @Year + 2000 end else begin set @Year = @Year + 1900 end end SELECT CASE WHEN DATEDIFF(MONTH, OpenDate, CAST(@Year as char(4)) + RIGHT('0' + CAST(@Month as varchar(2)), 2) + '01') >= @OpenMonths THEN 1 ELSE 0 END FROM dbo.Store WHERE StoreNo = @StoreNo Hope that helps, Joe Show quote "Vishal" wrote: > Hi, > > This is what I want to do: > > User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). > > Store Table : > Store No. INT (IDENTITY) > OpenDate (DATETIME) > > When the user calls a S Proc with the above parameters, I want to check > whether the Store was opened for 8 Months from the OpenDate on the store, if > the OpenDate is 22/02/2004 then the number of months should be calculated > from the next month, not including the month the store opened. > > I would like to write this in a Function, please help. > > Thanks > > > oops...i forgot about the function syntax...
CREATE FUNCTION [dbo].[IsStoreOpen] (@StoreNo as int, @Year as int, @Month as int, @OpenMonths as int) RETURNS bit AS BEGIN declare @IsStoreOpen bit SELECT @IsStoreOpen = CASE WHEN DATEDIFF(MONTH, OpenDate, CAST(@Year as char(4)) + RIGHT('0' + CAST(@Month as varchar(2)), 2) + '01') >= @OpenMonths THEN 1 ELSE 0 END FROM dbo.Store WHERE StoreNo = @StoreNo RETURN @IsStoreOpen END --execute the function select dbo.IsStoreOpen(1,2005,11,8) --execute the function while pulling data from the Store table select OpenDate, dbo.IsStoreOpen(StoreNo,2005,11,8) as IsStoreOpen from dbo.Store order by StoreNo Hope that helps, Joe Show quote "Joe from WI" wrote: > --I created a test table: > CREATE TABLE [dbo].[Store] ( > [StoreNo] [int] IDENTITY (1, 1) NOT NULL , > [OpenDate] [datetime] NOT NULL > ) ON [PRIMARY] > GO > > --I'm assuming the parameters are numeric because you did not have any > quotes in your original post. > --Also, I assumed a four digit year. If you always send a four digit year, > remove the century logic. > --The code constructs a search limit date using an unformatted string in > yyyymmdd format which is safe regardless of the computers regional and date > format settings. > > --declare and set parameters to simulate what would be in the stored procedure > declare @StoreNo int > declare @Month int > declare @Year int > declare @OpenMonths int > set @StoreNo = 3 > set @Month = 11 > set @Year = 2005 > set @OpenMonths = 8 > > --add the century, if needed > if len(@Year) = 2 > begin > if @Year < 50 > begin > set @Year = @Year + 2000 > end > else > begin > set @Year = @Year + 1900 > end > end > > SELECT CASE > WHEN DATEDIFF(MONTH, OpenDate, CAST(@Year as char(4)) + RIGHT('0' + > CAST(@Month as varchar(2)), 2) + '01') >= @OpenMonths > THEN 1 > ELSE 0 > END > FROM dbo.Store > WHERE StoreNo = @StoreNo > > Hope that helps, > Joe > > "Vishal" wrote: > > > Hi, > > > > This is what I want to do: > > > > User enters parameters StoreNo. Month(11), Year(2005), Open Months(8). > > > > Store Table : > > Store No. INT (IDENTITY) > > OpenDate (DATETIME) > > > > When the user calls a S Proc with the above parameters, I want to check > > whether the Store was opened for 8 Months from the OpenDate on the store, if > > the OpenDate is 22/02/2004 then the number of months should be calculated > > from the next month, not including the month the store opened. > > > > I would like to write this in a Function, please help. > > > > Thanks > > > > > >  |
|||||||||||||||||||||||
Other interesting topics