I am using the following simple stored procedure to dispaly data between 2
date ranges.
CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
(
@.manucode varchar(50),
@.brand varchar(50),
@.StartDate datetime,
@.EndDate datetime
)
AS
SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIPT,
ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
FROM ITEMHIST INNER JOIN
ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND( ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
GO
Now the prblem is that when I enterd start date as 1 Sept 2004 and end date
as 1Aug 2005, then there is no data displayed. I know the problem is with th
e
last 2 "AND" clauses of my stored procedure. Please help. I want to display
data between the 2 date ranges.
Thanks
--
pmudAND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate) )
AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
Year(@.StartDate ) )
Did you mean EndDate here, and not StartDate, on the last line? Anyway, I'm
not sure that breaking down a datetime into month and year to calculate a
range is very wise. If you're interested in blocking by months only, have
you considered adding a column to ITEMHIST that represents the month and
year combined, e.g. 20050101, 20050201, etc. This makes querying by date
ranges a true date range query, instead of separating the components of the
date and assuming that the range will always have a starting month and year
and then the ending date is starting month - 1 and ending year + 1.
A
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO,
> ITEMS.DESCRIPT,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end
> date
> as 1Aug 2005, then there is no data displayed. I know the problem is with
> the
> last 2 "AND" clauses of my stored procedure. Please help. I want to
> display
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Try,
...
WHERE
(ITEMS.MANUCODE = @.manucode or @.manucode is null )
AND (ITEMS.BRAND=@.brand or @.brand is null)
AND (ITEMS.ACTIVE='T')
AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
between (Year(@.StartDate ) * 100) + Month(@.StartDate)
AND (Year(@.EndDate) * 100) + Month(@.EndDate)
If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
do not expect sql server to perform an index s
in these indexes. Usingthose columns in an expression, limit then to be considered search arguments
.
AMB
"pmud" wrote:
> Hi,
> I am using the following simple stored procedure to dispaly data between 2
> date ranges.
> CREATE PROCEDURE [dbo].[sp_Triotek_MasterPOS]
> (
> @.manucode varchar(50),
> @.brand varchar(50),
> @.StartDate datetime,
> @.EndDate datetime
> )
> AS
> SELECT ITEMHIST.PERIOD, ITEMHIST.PER_Q_SI, ITEMS.ITEMNO, ITEMS.DESCRIP
T,
> ITEMS.BRAND, ITEMS.MANUCODE, ITEMS.Q_ON_RMA, ITEMS.Q_ON_RESER,
> ITEMHIST.FISCAL_YR, ITEMS.Q_ON_ORDER, ITEMS.QTY_STK
> FROM ITEMHIST INNER JOIN
> ITEMS ON ITEMHIST.ITEMNO = ITEMS.ITEMNO
> INNER JOIN MANUFACT ON ITEMS.MANUCODE=MANUFACT.CODE
> WHERE (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND( ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> GO
> Now the prblem is that when I enterd start date as 1 Sept 2004 and end dat
e
> as 1Aug 2005, then there is no data displayed. I know the problem is with
the
> last 2 "AND" clauses of my stored procedure. Please help. I want to displa
y
> data between the 2 date ranges.
> Thanks
> --
> pmud|||Adding a computed column as Aaron mentioned would seem to be a much better
long term solution that what you are doing now. That said, if you are
unable to alter the schema, give this a try. Replace the last two AND
conditions with the following:
cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
varchar) as datetime) between @.StartDate and @.EndDate
--Brian
(Please reply to the newsgroups only.)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
> AND (ITEMHIST.PERIOD > Month(@.StartDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate) )
> AND (ITEMHIST.PERIOD < Month(@.EndDate) AND ITEMHIST.FISCAL_YR =
> Year(@.StartDate ) )
> Did you mean EndDate here, and not StartDate, on the last line? Anyway,
> I'm not sure that breaking down a datetime into month and year to
> calculate a range is very wise. If you're interested in blocking by
> months only, have you considered adding a column to ITEMHIST that
> represents the month and year combined, e.g. 20050101, 20050201, etc.
> This makes querying by date ranges a true date range query, instead of
> separating the components of the date and assuming that the range will
> always have a starting month and year and then the ending date is starting
> month - 1 and ending year + 1.
> A
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:F524E910-7FF5-43B4-957D-2814DAE7D6BD@.microsoft.com...
>|||Hi ,
Yes, I meant @.EndDate in the last line. Is there any other way of doing
this. It is not necesaary to do month and year separately. i did it that way
bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
month) and FISCAL_YEAR fields.
Is there any way I can do it together rather than breaking it down...If not,
then can you please explain your menthod in detail..
Thansk for all your help..
--
pmud
"Alejandro Mesa" wrote:
> Try,
>
> ...
> WHERE
> (ITEMS.MANUCODE = @.manucode or @.manucode is null )
> AND (ITEMS.BRAND=@.brand or @.brand is null)
> AND (ITEMS.ACTIVE='T')
> AND (ITEMHIST.FISCAL_YR * 100) + ITEMHIST.PERIOD
> between (Year(@.StartDate ) * 100) + Month(@.StartDate)
> AND (Year(@.EndDate) * 100) + Month(@.EndDate)
> If there are indexes in table [ITEMHIST] by [FISCAL_YR] and / or [PERIOD],
> do not expect sql server to perform an index s
in these indexes. Using> those columns in an expression, limit then to be considered search argumen
ts.
>
> AMB
> "pmud" wrote:
>|||Hi,
Actually I just now checked, Aaron was right, I by chance wrote @.StartDate
instaed of @.EndDate in the sp and after changing ti , it works. I apologize.
Though I would really like to underastand the enw methods you all told me.I
didnt quite understand them...Can you please explain your solutions...
I really appreciate all your help
--
pmud
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||It is hard for me to give you a good explanation because my englis is far
from good, but I am posting an example so you can get the idea.
I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
(FISCAL_YEAR * 100) + PERIOD
and the same with the start and end dates. Then I am selecting just where
this number is between the ones from start and end date.
create table t1 (
c1 int not null identity primary key,
c2 int not null check (c2 between 1900 and 3000),
c3 int not null check (c3 between 1 and 12)
)
go
insert into t1(c2, c3) values(2000, 1)
insert into t1(c2, c3) values(2000, 5)
insert into t1(c2, c3) values(2001, 6)
insert into t1(c2, c3) values(2002, 8)
insert into t1(c2, c3) values(2005, 7)
insert into t1(c2, c3) values(2005, 8)
go
declare @.sd datetime
declare @.ed datetime
set @.sd = '20000201'
set @.ed = '20050701'
select
c1,
c2,
c3,
(c2 * 100) + c3 as c4,
(year(@.sd) * 100) + month(@.sd) as c5,
(year(@.ed) * 100) + month(@.ed) as c6
from
t1
where
(c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
100) + month(@.ed)
order by
c2, c3
go
drop table t1
go
AMB
"pmud" wrote:
> Hi ,
> Yes, I meant @.EndDate in the last line. Is there any other way of doing
> this. It is not necesaary to do month and year separately. i did it that w
ay
> bcoz the ITEMHIST table does not have a Date field but separate PERIOD(
> month) and FISCAL_YEAR fields.
> Is there any way I can do it together rather than breaking it down...If no
t,
> then can you please explain your menthod in detail..
> Thansk for all your help..
> --
> pmud
>
> "Alejandro Mesa" wrote:
>|||Hi Aljendro,
Thanks for talking the time and explaining it. I appreciate it. Your
explanation was very helpful and whatever little remaining doubt I have I
think when I will implement it myself, that will make it still clearer.
Thanks for ur help.
--
pmud
"Alejandro Mesa" wrote:
> It is hard for me to give you a good explanation because my englis is far
> from good, but I am posting an example so you can get the idea.
> I am creating a number based on FISCAL_YEAR and PERIOD (yyyymm):
> (FISCAL_YEAR * 100) + PERIOD
> and the same with the start and end dates. Then I am selecting just where
> this number is between the ones from start and end date.
> create table t1 (
> c1 int not null identity primary key,
> c2 int not null check (c2 between 1900 and 3000),
> c3 int not null check (c3 between 1 and 12)
> )
> go
> insert into t1(c2, c3) values(2000, 1)
> insert into t1(c2, c3) values(2000, 5)
> insert into t1(c2, c3) values(2001, 6)
> insert into t1(c2, c3) values(2002, 8)
> insert into t1(c2, c3) values(2005, 7)
> insert into t1(c2, c3) values(2005, 8)
> go
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20000201'
> set @.ed = '20050701'
> select
> c1,
> c2,
> c3,
> (c2 * 100) + c3 as c4,
> (year(@.sd) * 100) + month(@.sd) as c5,
> (year(@.ed) * 100) + month(@.ed) as c6
> from
> t1
> where
> (c2 * 100) + c3 between (year(@.sd) * 100) + month(@.sd) and (year(@.ed) *
> 100) + month(@.ed)
> order by
> c2, c3
> go
> drop table t1
> go
>
> AMB
>
> "pmud" wrote:
>|||Hi Brian,
I used the cast statement as it is.. and it works...I am trying to
undersatnd how it exactly works though...
pmud
"Brian Lawton" wrote:
> Adding a computed column as Aaron mentioned would seem to be a much better
> long term solution that what you are doing now. That said, if you are
> unable to alter the schema, give this a try. Replace the last two AND
> conditions with the following:
> cast(cast(ITEMHIST.PERIOD as varchar) + '-1-' + cast(ITEMHIST.FISCAL_YR as
> varchar) as datetime) between @.StartDate and @.EndDate
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:%23yOQwrpoFHA.2180@.TK2MSFTNGP15.phx.gbl...
>
>|||Basically it just converts your Period and FiscalYear combination into a
date with the format mm/01/yyyy. As a datetime datatype, it then does the
comparison to the @.StartDate and @.EndDate via the BETWEEN.
--Brian
(Please reply to the newsgroups only.)
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:0AD179F0-3096-48B9-88D3-AD7C9F9BE88E@.microsoft.com...
> Hi Brian,
> I used the cast statement as it is.. and it works...I am trying to
> undersatnd how it exactly works though...
> --
> pmud
>
> "Brian Lawton" wrote:
>
No comments:
Post a Comment