Parameter Q?
-
Hi,
I am trying to modify following query by adding year parameter and month parameter. Here is the snippet.
(
manufacturing_start_time >= @startdate
and (manufacturing_end_time > @startdate or manufacturing_end_time is null)
or
(
manufacturing_start_time < @startdate
and (manufacturing_end_time > @startdate or manufacturing_end_time is null)
)
-----changing it to below by year and month, but getting less number of rows ?
(
year(manufacturing_start_time) >= @year and (month(manufacturing_start_time)>= @month)
and ((year(manufacturing_end_time) > @year and month(manufacturing_end_time) > @month ) or manufacturing_end_time is null)
or
(
(year(manufacturing_start_time) < @year and month(manufacturing_start_time) < @month)
and ((year(manufacturing_end_time)> @year and month(manufacturing_end_time)> @month )or manufacturing_end_time is null)
)
)
Thanks.
-
You've got at least one copy/paste error in there:
and year(manufacturing_end_time) > @month
-
Sorry but you need to give the table definitions and a wider description of what you're trying to do. We don't know from what you've posted what each variable's datatype is, how it's being used, etc.
Please could you post the table definitions and clarify your goal.
Also - don't know if this will help you but typically you would use DATEADD and DATEDIFF functions to help you compare, contrast and sort DATETIME type data.
Example:
DECLARE @myDateTime DATETIME
DECLARE @yourDateTime DATETIME
DECLARE @difference INT
SET @myDateTime = GETDATE()
SET @yourDateTime = DATEADD(hh,-12,@myDateTime) -- sets @yourDateTime to be 12 hours ago
SET @difference = DATEDIFF(hh, @myDateTime, @yourDateTime) -- returns 12
You might be able to use these functions to help you when designing your query.
Thanks.
EDIT: Forgot about DATEPART (this might be helpful when pulling out the month and year of your manufacturing dates):
DECLARE @theDay INT
DECLARE @theMonth INT
DECLARE @theYear INT
SET @theDay = DATEPART(dd,GETDATE())
SET @theMonth = DATEPART(mm,GETDATE())
SET @theYear = DATEPART(yyyy,GETDATE())
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me. -
Thank you for your responses. just corrected that.
sometimes the manufacturing goes beyond the dates specified, therefore the code includes all those scenarios when the manufacturing went outside the time-limits specified.
In the report, the results should be shown by year and month . so attempt is to add year and month parameter instead of using @startdate and @enddate and still be able to see the results by month.
declare @startdate datetime,
@enddate datetime
set @startdate='1/1/2012'
set @enddate='1/31/2012'
Create table #item
(item varchar(10),
manufacturing_start_time datetime,
manufacturing_end_time datetime)
insert into #item (item ,manufacturing_start_time,manufacturing_end_time)
select 'AAAA' , '1/1/2012', '2/1/2012'
union all
select 'BBBB' , '1/10/2012', null
union all
select 'CCCC' , '12/15/2011', null
union all
select 'DDDD' , '1/12/2012', '6/5/2012'
union all
select 'EEEE' , '4/1/2011', '5/4/2011'
select * from #item
where (manufacturing_start_time >= @startdate and (manufacturing_end_time > @startdate or manufacturing_end_time is null)
or (manufacturing_start_time < @startdate and (manufacturing_end_time > @startdate or manufacturing_end_time is null)) )
drop table #item
-
Your initial criteria allows for the ending date to be null. The other two don't allow for that (i.e. filter those out), which is why you are getting fewer rows.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I tried that (removing ending dates to be null) and but it is not working.Did you mean something else?
-
Oh - my bad. the formatting made those statements disappear.
That said your year and month criteria isn't equivalent to using the date. let's take a look at this:
year(manufacturing_start_time) >= @year and (month(manufacturing_start_time)>= @month)
and ((year(manufacturing_end_time) > @year and month(manufacturing_end_time) > @month ) or manufacturing_end_time is null)
You're stating that the end time is either null or at least in a future year . Similarly - the month must be HIGHER that the month parameter (note that isn't that a month is in the future, it's that the ending month's number is higher). So given the criteria you gave, the earliest ending date you'd want is 2/1/2013.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
You're probably looking at something like this instead if you NEED to use the month and year criteria:
(
year(manufacturing_start_time) >= @year and (month(manufacturing_start_time)>= @month)
and (
(year(manufacturing_end_time) > @year ) OR
(year(manufacturing_end_time) = @year and month(manufacturing_end_time) >= @month ) or
manufacturing_end_time is null
)
or
--etc....
The reason for the emphasis on NEED above is that you're actually moving away from a more efficient query. using the Month() and Year() functions in your WHERE clause essentially guarantees a scan, since the functions have to evaluate row by row.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
derek.colley (6/20/2012)
Sorry but you need to give the table definitions and a wider description of what you're trying to do. We don't know from what you've posted what each variable's datatype is, how it's being used, etc.Please could you post the table definitions and clarify your goal.
Also - don't know if this will help you but typically you would use DATEADD and DATEDIFF functions to help you compare, contrast and sort DATETIME type data.
Example:
DECLARE @myDateTime DATETIME
DECLARE @yourDateTime DATETIME
DECLARE @difference INT
SET @myDateTime = GETDATE()
SET @yourDateTime = DATEADD(hh,-12,@myDateTime) -- sets @yourDateTime to be 12 hours ago
SET @difference = DATEDIFF(hh, @myDateTime, @yourDateTime) -- returns 12
You might be able to use these functions to help you when designing your query.
Thanks.
EDIT: Forgot about DATEPART (this might be helpful when pulling out the month and year of your manufacturing dates):
DECLARE @theDay INT
DECLARE @theMonth INT
DECLARE @theYear INT
SET @theDay = DATEPART(dd,GETDATE())
SET @theMonth = DATEPART(mm,GETDATE())
SET @theYear = DATEPART(yyyy,GETDATE())
How about showing us what the expected results of the query should be, I work better seeing what I am trying to accomplish then just getting a verbal description. Use the sample data you posted to provide the expected results.
-
Thank you Matt, Rick, Derek, Lynn. Greatly appreciate your time and help on this.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply