April 17, 2012 at 2:37 pm
Hi all I use ling2sql and datatabe to fill a report using c#.
I have this query below for a single month (I am only interested in month and year, using a custom datetime picker mmmmYYYY)
SELECT Con1, Con10, Con2, Con3, Con4, Con5, Con6, Con7, Con8, Con9, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Section, Waittime, spare8 FROM DocketTB WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, Docket_Date)) AND (DATEPART(mm, @date1) = DATEPART(mm, Docket_Date))
Ho can pass 2 paratmeters @date1 and @date2 to query mathcing months and year eg:
if I pass 1/2/2012 as date1 and 6/04/2012 as date2 the query should return all records for the month and year from 1st day of month 2 2012 to last day of month 4 2012
Jay
April 17, 2012 at 2:49 pm
Here is some code to try:
SELECT
Con1,
Con10,
Con2,
Con3,
Con4,
Con5,
Con6,
Con7,
Con8,
Con9,
Docket_Category,
Docket_Date,
Docket_DateRaised,
Docket_EngFinish,
Docket_EngStart,
Docket_EngineerName,
Docket_Id,
Docket_Machine,
Docket_Number,
Docket_Status,
Docket_SubCategory,
Duration,
Module,
Monitor_Time,
Operator_Name,
Section,
Waittime,
spare8
FROM
DocketTB
WHERE
DocketDate >= DATEADD(mm, DATEDIFF(mm, 0, @Date1), 0) AND DocketDate < DATEADD(mm, DATEDIFF(mm, 0, @Date2) + 1, 0)
;
April 17, 2012 at 2:50 pm
--edited out, essentially same code, lol.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 17, 2012 at 3:02 pm
Lynn Pettis (4/17/2012)
Here is some code to try:
SELECT
Con1,
Con10,
Con2,
Con3,
Con4,
Con5,
Con6,
Con7,
Con8,
Con9,
Docket_Category,
Docket_Date,
Docket_DateRaised,
Docket_EngFinish,
Docket_EngStart,
Docket_EngineerName,
Docket_Id,
Docket_Machine,
Docket_Number,
Docket_Status,
Docket_SubCategory,
Duration,
Module,
Monitor_Time,
Operator_Name,
Section,
Waittime,
spare8
FROM
DocketTB
WHERE
DocketDate >= DATEADD(mm, DATEDIFF(mm, 0, @Date1), 0) AND DocketDate < DATEADD(mm, DATEDIFF(mm, 0, @Date2) + 1, 0)
;
I have not tried yet but just looking I cant see the year match ?
April 17, 2012 at 3:08 pm
Here is an example with some data.
--these are your parameters
declare @date1 datetime = convert(datetime, '20120201')
declare @date2 datetime = convert(datetime, '20120406')
select @date1, @date2
--now we want a variable to hold these calculated values.
--Storing this will allow to keep our query sargable for best index usage.
declare @StartDate datetime, @EndDate datetime
select @StartDate = dateadd(mm, datediff(mm, 0, @date1), 0) -- Beginning of this month
select @EndDate = dateadd(mm, datediff(mm, 0, @date2) + 1, 0) -- Beginning of next month
select @StartDate, @EndDate
;with myTable(SomeValue, MyDate)
as
(
select 'Value1', CONVERT(datetime, '1/1/2012')
union all
select 'Value2', CONVERT(datetime, '2/13/2012')
union all
select 'Value3', CONVERT(datetime, '3/1/2012')
union all
select 'Value4', CONVERT(datetime, '4/11/2012')
union all
select 'Value5', CONVERT(datetime, '5/18/2012')
union all
select 'Value6', CONVERT(datetime, '6/3/2012')
union all
select 'Value7', CONVERT(datetime, '6/27/2012')
union all
select 'Value8', CONVERT(datetime, '8/13/2012')
)
select * from myTable
where MyDate between @StartDate and @EndDate
The date calculations were borrowed courtesy of Lynn. http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
Notice in the example I provided ddl and sample data. In future posts if you can provide both of those it will go a long way to getting good answers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2012 at 3:09 pm
jerome.morris (4/17/2012)
Lynn Pettis (4/17/2012)
Here is some code to try:
SELECT
Con1,
Con10,
Con2,
Con3,
Con4,
Con5,
Con6,
Con7,
Con8,
Con9,
Docket_Category,
Docket_Date,
Docket_DateRaised,
Docket_EngFinish,
Docket_EngStart,
Docket_EngineerName,
Docket_Id,
Docket_Machine,
Docket_Number,
Docket_Status,
Docket_SubCategory,
Duration,
Module,
Monitor_Time,
Operator_Name,
Section,
Waittime,
spare8
FROM
DocketTB
WHERE
DocketDate >= DATEADD(mm, DATEDIFF(mm, 0, @Date1), 0) AND DocketDate < DATEADD(mm, DATEDIFF(mm, 0, @Date2) + 1, 0)
;
I have not tried yet but just looking I cant see the year match ?
Doesn't need it. If @date1 is passed in as '20120205', the date arithmetic will convert that date to '20120201 00:00:00.000'. If @date2 is passed in as 20120406, the date arithmetic will convert that date to '20120501 00:00:00.000',
The query would then return all records with a DocketDates in February 2012, March 2012, and April 2012.
If you have an index defined on the DocketDate column (which would make a good clustered index), this query will use it, where as the query you were writing would need to do a table or clustered index scan.
April 17, 2012 at 3:11 pm
Sean Lange (4/17/2012)
Here is an example with some data.
--these are your parameters
declare @date1 datetime = convert(datetime, '20120201')
declare @date2 datetime = convert(datetime, '20120406')
select @date1, @date2
--now we want a variable to hold these calculated values.
--Storing this will allow to keep our query sargable for best index usage.
declare @StartDate datetime, @EndDate datetime
select @StartDate = dateadd(mm, datediff(mm, 0, @date1), 0) -- Beginning of this month
select @EndDate = dateadd(mm, datediff(mm, 0, @date2) + 1, 0) -- Beginning of next month
select @StartDate, @EndDate
;with myTable(SomeValue, MyDate)
as
(
select 'Value1', CONVERT(datetime, '1/1/2012')
union all
select 'Value2', CONVERT(datetime, '2/13/2012')
union all
select 'Value3', CONVERT(datetime, '3/1/2012')
union all
select 'Value4', CONVERT(datetime, '4/11/2012')
union all
select 'Value5', CONVERT(datetime, '5/18/2012')
union all
select 'Value6', CONVERT(datetime, '6/3/2012')
union all
select 'Value7', CONVERT(datetime, '6/27/2012')
union all
select 'Value8', CONVERT(datetime, '8/13/2012')
)
select * from myTable
where MyDate between @StartDate and @EndDate
The date calculations were borrowed courtesy of Lynn. http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
Notice in the example I provided ddl and sample data. In future posts if you can provide both of those it will go a long way to getting good answers.
Slight change to the code from Sean:
--these are your parameters
declare @date1 datetime = convert(datetime, '20120201');
declare @date2 datetime = convert(datetime, '20120406');
select @date1, @date2;
--now we want a variable to hold these calculated values.
--Storing this will allow to keep our query sargable for best index usage.
declare @StartDate datetime, @EndDate datetime;
select @StartDate = dateadd(mm, datediff(mm, 0, @date1), 0); -- Beginning of this month
select @EndDate = dateadd(mm, datediff(mm, 0, @date2) + 1, 0); -- Beginning of next month
select @StartDate, @EndDate;
with myTable(SomeValue, MyDate)
as
(
select 'Value1', CONVERT(datetime, '1/1/2012')
union all
select 'Value2', CONVERT(datetime, '2/13/2012')
union all
select 'Value3', CONVERT(datetime, '3/1/2012')
union all
select 'Value4', CONVERT(datetime, '4/11/2012')
union all
select 'Value5', CONVERT(datetime, '5/18/2012')
union all
select 'Value6', CONVERT(datetime, '6/3/2012')
union all
select 'Value7', CONVERT(datetime, '6/27/2012')
union all
select 'Value8', CONVERT(datetime, '8/13/2012')
)
select * from myTable
where MyDate >= @StartDate and MyDate < @EndDate;
April 17, 2012 at 3:26 pm
Storing this will allow to keep our query sargable for best index usage.
Is that really necessary?
Won't SQL treat computations on a declared variable as a constant, so they are still sarg-able anyway?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 17, 2012 at 3:29 pm
ScottPletcher (4/17/2012)
Storing this will allow to keep our query sargable for best index usage.
Is that really necessary?
Won't SQL treat computations on a declared variable as a constant, so they are still sarg-able anyway?
Could you elaborate? Not sure what you are asking for here without some reference.
April 17, 2012 at 3:31 pm
Lynn Pettis (4/17/2012)
ScottPletcher (4/17/2012)
Storing this will allow to keep our query sargable for best index usage.
Is that really necessary?
Won't SQL treat computations on a declared variable as a constant, so they are still sarg-able anyway?
Could you elaborate? Not sure what you are asking for here without some reference.
I believe he was referring to my original post. And yes you are correct. It would be fine in the where clause because of the constant. Just putting together the example and such...guess I went a bit overboard.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2012 at 3:34 pm
ScottPletcher (4/17/2012)
Storing this will allow to keep our query sargable for best index usage.
Is that really necessary?
Won't SQL treat computations on a declared variable as a constant, so they are still sarg-able anyway?
If you mean is there a difference between this:
...
DocketDate >= DATEADD(mm, DATEDIFF(mm, 0, @Date1), 0) AND DocketDate < DATEADD(mm, DATEDIFF(mm, 0, @Date2) + 1, 0)
;
and this:
declare @startdate datetime, @enddate datetime;
set @startdate = DATEADD(mm, DATEDIFF(mm, 0, @Date1), 0);
set @enddate = DATEADD(mm, DATEDIFF(mm, 0, @Date2) + 1, 0);
...
DocketDate >= @startdate AND DocketDate < @enddate;
No, there isn't.
April 17, 2012 at 4:00 pm
Lynn Pettis (4/17/2012)
jerome.morris (4/17/2012)
Lynn Pettis (4/17/2012)
Here is some code to try:
SELECT
Con1,
Con10,
Con2,
Con3,
Con4,
Con5,
Con6,
Con7,
Con8,
Con9,
Docket_Category,
Docket_Date,
Docket_DateRaised,
Docket_EngFinish,
Docket_EngStart,
Docket_EngineerName,
Docket_Id,
Docket_Machine,
Docket_Number,
Docket_Status,
Docket_SubCategory,
Duration,
Module,
Monitor_Time,
Operator_Name,
Section,
Waittime,
spare8
FROM
DocketTB
WHERE
DocketDate >= DATEADD(mm, DATEDIFF(mm, 0, @Date1), 0) AND DocketDate < DATEADD(mm, DATEDIFF(mm, 0, @Date2) + 1, 0)
;
I have not tried yet but just looking I cant see the year match ?
Doesn't need it. If @date1 is passed in as '20120205', the date arithmetic will convert that date to '20120201 00:00:00.000'. If @date2 is passed in as 20120406, the date arithmetic will convert that date to '20120501 00:00:00.000',
The query would then return all records with a DocketDates in February 2012, March 2012, and April 2012.
If you have an index defined on the DocketDate column (which would make a good clustered index), this query will use it, where as the query you were writing would need to do a table or clustered index scan.
thank you so much for your detailed explanation, work great
Jay
April 17, 2012 at 4:08 pm
Glad to help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply