February 29, 2012 at 11:15 am
Hi all,
I am a newbie in SQL, i stuck with my query, can you help me to solve it?
Here is my query:
SELECT [System].[dbo].[Machine_Control_History].parent_id
,[System].[dbo].[Machine_Control_History].start_datetime
,[System].[dbo].[Machine_Control_History].end_datetime
,[System].[dbo].[Machine_Control_History].machine_status
,[System].[dbo].[Machine_Control_History].active_user
,[System].[dbo].[Machine_Control_History].notes
FROM [System].[dbo].[Machine_Control_History]
How do you write WHERE ? to get
I want a report from 2/19- 2/26 with all the system running from this time, not between this time, because it will not get start time before it and end in that week.
1. If start_datetime is before 2/19 but end_datetime is between 2/19-2/26 then start_datetime is 2/19 00:00:00 000
2. If start time is after 2/19 but endtime is between 2/19-2/26 then starttime is that start time.
How you do that? this is auto generate at midnight around 00:30AM every Sunday to get this report for every week. and how we get every 2 weeks too?
Thanks.
February 29, 2012 at 11:43 am
it would easier to understand, if you care to provide some create table scripts and sample data ...with your expected output.
but initial thought is,
pseudo code : "WHERE end_datetime is between 2/19-2/26"
....from the way I read your question, the start date is irrelevant.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 29, 2012 at 12:35 pm
I did not create database, only try to run cron script to get report, here is the sample of the out put
42,2012-02-23 11:17:22.000,2012-02-23 12:28:34.850,setup,johnny,test
42,2012-02-23 12:28:35.000,2012-02-23 12:29:12.150,active,jonny,test
50,2012-02-23 12:30:12.000,2012-02-25 16:51:04.037,active,jonny,test
3,2012-02-21 12:12:24.000,2012-02-21 13:42:52.793,active,timmy,test
25,2012-02-21 13:47:39.000,2012-02-22 11:46:23.980,active,timmy,test
24,2012-02-23 16:51:53.000,2012-02-25 16:52:43.733,active,johhny,test it out
24,2012-02-23 16:53:42.000,2012-02-24 10:32:33.870,active,jimmy,test it
18,2012-02-23 11:42:55.000,2012-02-24 11:43:03.950,active,timmy,test
The problem, i cannot get report of
27,2012-02-14 11:17:22.000,2012-02-23 12:28:34.850,active,johnny,test
because i use between sunday 2/19 to saturday 2/25 23:59:59.000
I want to get this info as
27,2012-02-19 00:00:00.000,2012-02-23 12:28:34.850,active,johnny,test
include in my report so i can get time using (has been using during a week) to run these tests
Thanks.
February 29, 2012 at 12:43 pm
to get this output....what query did you use, specifically what is in the "WHERE" clause?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 29, 2012 at 12:53 pm
Here are my full query and it did not get what i want
/****** Script for SelectTopNRows command from SSMS ******/
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT [System].[dbo].[Machine_Control_History].parent_id
,[System].[dbo].[Machine_Control_History].start_datetime
,[System].[dbo].[Machine_Control_History].end_datetime
,[System].[dbo].[Machine_Control_History].machine_status
,[System].[dbo].[Machine_Control_History].active_user
,[System].[dbo].[Machine_Control_History].notes
FROM [System].[dbo].[Machine_Control_History]
WHERE [System].[dbo].[Machine_Control_History].active_user BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY [System].[dbo].[Machine_Control_History].active_user
February 29, 2012 at 1:38 pm
Keep in mind that nobody here can see your screen and we are not familiar with your project.
You said
Here are my full query and it did not get what i want
What does that mean? Did it return extra data, no data, not enough data, incorrect data?
As was asked before, can you provide some ddl, sample data and the desired output based on that sample data? Without some details the best anybody can do is guess.
_______________________________________________________________
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/
February 29, 2012 at 1:50 pm
i have an idea that the date criteria is causing your problem...just a guess 🙂
pls confirm version of SQL
pls provide "create table script" for table [System].[dbo].[Machine_Control_History]
pls provide sample data to insert into [System].[dbo].[Machine_Control_History] that represents the problem you are having.
I am sure that the forum will sort this for you asap once you provide the above.
good luck and kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 29, 2012 at 2:39 pm
sabercats (2/29/2012)
Here are my full query and it did not get what i want/****** Script for SelectTopNRows command from SSMS ******/
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT [System].[dbo].[Machine_Control_History].parent_id
,[System].[dbo].[Machine_Control_History].start_datetime
,[System].[dbo].[Machine_Control_History].end_datetime
,[System].[dbo].[Machine_Control_History].machine_status
,[System].[dbo].[Machine_Control_History].active_user
,[System].[dbo].[Machine_Control_History].notes
FROM [System].[dbo].[Machine_Control_History]
WHERE [System].[dbo].[Machine_Control_History].active_user BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY [System].[dbo].[Machine_Control_History].active_user
Like the others have said, more information please... meanwhile... take a look at this query. Note the case in the Select, and the changed WHERE clause (you were comparing a username to a date).
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT [System].[dbo].[Machine_Control_History].parent_id
,start_datetime = CASE WHEN [System].[dbo].[Machine_Control_History].start_datetime < @2SundaysAgo THEN @2SundaysAgo ELSE [System].[dbo].[Machine_Control_History].start_datetime END
,[System].[dbo].[Machine_Control_History].end_datetime
,[System].[dbo].[Machine_Control_History].machine_status
,[System].[dbo].[Machine_Control_History].active_user
,[System].[dbo].[Machine_Control_History].notes
FROM [System].[dbo].[Machine_Control_History]
WHERE [System].[dbo].[Machine_Control_History].end_datetime BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY [System].[dbo].[Machine_Control_History].active_user
February 29, 2012 at 3:03 pm
Sorry, my query is
****** Script for SelectTopNRows command from SSMS ******/
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT [System].[dbo].[Machine_Control_History].parent_id
,[System].[dbo].[Machine_Control_History].start_datetime
,[System].[dbo].[Machine_Control_History].end_datetime
,[System].[dbo].[Machine_Control_History].machine_status
,[System].[dbo].[Machine_Control_History].active_user
,[System].[dbo].[Machine_Control_History].notes
FROM [System].[dbo].[Machine_Control_History]
WHERE [System].[dbo].[Machine_Control_History].start_datetime BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY [System].[dbo].[Machine_Control_History].start_datetime
My query can execute and give me the results i want, but missing some data which have start_datetime before last 2 Sunday but endtime in the last week. How do i get these data and replace that start_datetime with last 2 Sunday time?
February 29, 2012 at 3:08 pm
sabercats (2/29/2012)
Sorry, my query is
****** Script for SelectTopNRows command from SSMS ******/
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT [System].[dbo].[Machine_Control_History].parent_id
,[System].[dbo].[Machine_Control_History].start_datetime
,[System].[dbo].[Machine_Control_History].end_datetime
,[System].[dbo].[Machine_Control_History].machine_status
,[System].[dbo].[Machine_Control_History].active_user
,[System].[dbo].[Machine_Control_History].notes
FROM [System].[dbo].[Machine_Control_History]
WHERE [System].[dbo].[Machine_Control_History].start_datetime BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY [System].[dbo].[Machine_Control_History].start_datetime
My query can execute and give me the results i want, but missing some data which have start_datetime before last 2 Sunday but endtime in the last week. How do i get these data and replace that start_datetime with last 2 Sunday time?
????
OR (start_datetime < @2SundaysAgo AND endtime between @lastSaturday and getdate())
????
Still no details so this is totally untested.
_______________________________________________________________
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/
February 29, 2012 at 5:57 pm
If i change the query to this
declare @dow int
declare @2SundaysAgo datetime
declare @lastSaturday datetime
select @dow = datepart(dw, getdate())
select @2SundaysAgo = getdate() - (7 + (@dow - 1))
select @lastSaturday = getdate() - (@dow) + 1
select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))
select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))
SELECT [System].[dbo].[Machine_Control_History].parent_id
,[System].[dbo].[Machine_Control_History].start_datetime
,[System].[dbo].[Machine_Control_History].end_datetime
,[System].[dbo].[Machine_Control_History].machine_status
,[System].[dbo].[Machine_Control_History].active_user
,[System].[dbo].[Machine_Control_History].notes
FROM [System].[dbo].[Machine_Control_History]
WHERE [System].[dbo].[Machine_Control_History].end_datetime BETWEEN @2SundaysAgo AND @lastSaturday
ORDER BY [System].[dbo].[Machine_Control_History].start_datetime
Then i will collect all the records end date in that week, and use linux shell to replace time of starttime < @2SundaysAgo to make starttime = @2SundaysAgo, then it will be okay.
We cannot do that in sql using case of start_datetime ?
Thanks,
March 1, 2012 at 7:38 am
OK I will ask one last time. Please provide some ddl and sample data along with a clear explanation of what you are looking for. You have been asked multiple times and you keep coming back with no details and vague questions.
Maybe if you post these details somebody else will come along and help you. Good luck and I hope you get your issue resolved.
_______________________________________________________________
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/
March 1, 2012 at 12:52 pm
Sorry, i am newbie and really don't understand what you mean "Please provide some ddl and sample data along with a clear explanation of what you are looking for."
I thought i explained everything 🙁 . It is okay, i will use perl or unix to replace start time before the last 2 Sunday .
Thanks.
March 1, 2012 at 1:05 pm
sabercats (3/1/2012)
Sorry, i am newbie and really don't understand what you mean "Please provide some ddl and sample data along with a clear explanation of what you are looking for."I thought i explained everything 🙁 . It is okay, i will use perl or unix to replace start time before the last 2 Sunday .
Thanks.
No problem with being a newbie, everybody was a newbie at one point. 🙂 However, here are some quotes from posts through this whole thread.
it would easier to understand, if you care to provide some create table scripts and sample data ...with your expected output.
As was asked before, can you provide some ddl, sample data and the desired output based on that sample data? Without some details the best anybody can do is guess.
As was asked before, can you provide some ddl, sample data and the desired output based on that sample data? Without some details the best anybody can do is guess.
pls confirm version of SQL
pls provide "create table script" for table [System].[dbo].[Machine_Control_History]
pls provide sample data to insert into [System].[dbo].[Machine_Control_History] that represents the problem you are having.
I am sure that the forum will sort this for you asap once you provide the above.
See the first link in my signature. As I said before, we can't see over your shoulder. It is not that nobody here is willing to help, it is that you have not provided enough information for an answer.
To spell is very clearly here is what is typically required to get a decent answer.
1) ddl - this is short for data definition language, commonly called "Create table scripts"
2) sample data - this can generic/obfuscated to protect your real data and should be INSERT statements.
3) desired output - this is a clear explanation of what you want as the desired output or result of your code.
Think about being able to hand this off to another developer and the clarity required. The descriptions and explanations need to be clear. We don't have any knowledge of your project, environment or business. This is why more attention to this type of detail is required.
Don't be discouraged with the community here because it is full of really great and helpful people. Next time you post read the link in my signature first. It will save you hours of frustration and reward you with tested and accurate code, usually very quickly.
If you still want some help with your current issue read that article and follow the advice.
_______________________________________________________________
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/
March 1, 2012 at 1:48 pm
sabercats (3/1/2012)
Sorry, i am newbie and really don't understand what you mean "Please provide some ddl and sample data along with a clear explanation of what you are looking for."I thought i explained everything 🙁 . It is okay, i will use perl or unix to replace start time before the last 2 Sunday .
Thanks.
Hi sabercats
...this may sound stupid...but please confirm that you are using Microsoft SQL Server and the version (eg 2005/2008/2008R2)
if you are not clear on how to provide table definitions and data insert scripts, then pls post back and we will provide you more assistance on these important matters
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply