September 4, 2012 at 11:34 am
I have a query that looks like this:
select program, Completed_task, max(timestamp)
from WorkQueue
where (type = 'Software' and status = 'Active') or
(type = 'Hardware' and status = 'Active') or
(type = 'Software/Hardware' and status = 'Active')
group by program, Completed_Task
I'm trying to pull out the data with the most current date such as 8/29, however, once I add the Completed_task column to my SQL,
its pulling me back 8/1 data as well.
below is my data output. I only want to see 2012-08-29 data only, what am I missing my my query to do this?
program Completed_Task Date Completed
Network Wire 15% 2012-08-29
Hardware Failure 58% 2012-08-29
Keyboard 87% 2012-08-29
Software Upgrade 96% 2012-08-29
Keyboard 98% 2012-08-29
Network Jack 96% 2012-08-01
Software Upgrade 92% 2012-08-01
Hardware Failure 68% 2012-08-01
September 4, 2012 at 11:38 am
My guess is that you're missing this:
HAVING max(timestamp) >= '20120829'
AND max(timestamp) < '20120830'
September 4, 2012 at 11:47 am
Depending on your data, since we are now in September, and you want data for say 2012-08-29 where you may have data from before and after this date you may need this (not sure why you'd do the group by though):
select
program,
Completed_task,
max(timestamp)
from
WorkQueue
where
((type = 'Software' and status = 'Active') or
(type = 'Hardware' and status = 'Active') or
(type = 'Software/Hardware' and status = 'Active'))
and timestamp >= '2012-08-29' and timestamp < '2012-08-30'
group by
program,
Completed_Task;
September 4, 2012 at 11:48 am
Luis Cazares (9/4/2012)
My guess is that you're missing this:HAVING max(timestamp) >= '20120829'
AND max(timestamp) < '20120830'
that works if I hardcode the date, however, the data will change monthly and if I change it like
select program, Completed_task, max(timestamp)
from WorkQueue
where (type = 'Software' and status = 'Active') or
(type = 'Hardware' and status = 'Active') or
(type = 'Software/Hardware' and status = 'Active')
group by program, Completed_Task
having max(timestamp) >= max(timestamp)
i still get the records back that I dont want such as 2012-08-01
September 4, 2012 at 11:50 am
How do you decide what date you want from your data?
September 4, 2012 at 11:51 am
Lynn Pettis (9/4/2012)
How do you decide what date you want from your data?
that date is in the table, so when I do select max(timestamp) from workqueue, that's the most current date in the table
September 4, 2012 at 11:54 am
SQL_NuB (9/4/2012)
Lynn Pettis (9/4/2012)
How do you decide what date you want from your data?that date is in the table, so when I do select max(timestamp) from workqueue, that's the most current date in the table
Is this a DATE value or a DATE/TIME value? In other words, what is the declared data type for the timestamp column.
September 4, 2012 at 12:02 pm
Are you serious?
having max(timestamp) >= max(timestamp)
That's like using 1=1
What you're trying to do should be something like this (I like to use variables but there are ways to do it in the same query):
DECLARE @MaxDate as datetime
SELECT @MaxDate = max(timestamp)
from WorkQueue
where (type = 'Software' and status = 'Active') or
(type = 'Hardware' and status = 'Active') or
(type = 'Software/Hardware' and status = 'Active')
SELECT program, Completed_task, timestamp --max(timestamp)
FROM WorkQueue
WHERE timestamp >= DATEADD( DD, DATEDIFF(DD, 0,@MaxDate), 0)
AND timestamp < DATEADD( DD, DATEDIFF(DD, 0,@MaxDate) + 1, 0)
AND ((type = 'Software' and status = 'Active')
OR (type = 'Hardware' and status = 'Active')
OR (type = 'Software/Hardware' and status = 'Active'))
--GROUP BY program, Completed_Task
I'm not sure if you need to remove the MAX and GROUP BY, as it depends on your data.
September 4, 2012 at 12:07 pm
Or something like this:
select
program,
Completed_task,
timestamp
from
WorkQueue
where
((type = 'Software' and status = 'Active') or
(type = 'Hardware' and status = 'Active') or
(type = 'Software/Hardware' and status = 'Active'))
and timestamp >= (select cast(max(timestamp) as date) from WorkQueue);
September 4, 2012 at 12:15 pm
Lynn Pettis (9/4/2012)
SQL_NuB (9/4/2012)
Lynn Pettis (9/4/2012)
How do you decide what date you want from your data?that date is in the table, so when I do select max(timestamp) from workqueue, that's the most current date in the table
Is this a DATE value or a DATE/TIME value? In other words, what is the declared data type for the timestamp column.
its stored in the column as [2012-08-29] the column is defined as an date date field
September 4, 2012 at 12:18 pm
SQL_NuB (9/4/2012)
Lynn Pettis (9/4/2012)
SQL_NuB (9/4/2012)
Lynn Pettis (9/4/2012)
How do you decide what date you want from your data?that date is in the table, so when I do select max(timestamp) from workqueue, that's the most current date in the table
Is this a DATE value or a DATE/TIME value? In other words, what is the declared data type for the timestamp column.
its stored in the column as [2012-08-29] the column is defined as an date date field
Then the following should pull the data you are requesting:
select
program,
Completed_task,
timestamp
from
WorkQueue
where
((type = 'Software' and status = 'Active') or
(type = 'Hardware' and status = 'Active') or
(type = 'Software/Hardware' and status = 'Active'))
and timestamp = (select max(timestamp) from WorkQueue)
group by
program,
Completed_Task;
September 4, 2012 at 12:41 pm
Lynn Pettis (9/4/2012)
SQL_NuB (9/4/2012)
Lynn Pettis (9/4/2012)
SQL_NuB (9/4/2012)
Lynn Pettis (9/4/2012)
How do you decide what date you want from your data?that date is in the table, so when I do select max(timestamp) from workqueue, that's the most current date in the table
Is this a DATE value or a DATE/TIME value? In other words, what is the declared data type for the timestamp column.
its stored in the column as [2012-08-29] the column is defined as an date date field
Then the following should pull the data you are requesting:
select
program,
Completed_task,
timestamp
from
WorkQueue
where
((type = 'Software' and status = 'Active') or
(type = 'Hardware' and status = 'Active') or
(type = 'Software/Hardware' and status = 'Active'))
and timestamp = (select max(timestamp) from WorkQueue)
group by
program,
Completed_Task;
thanks, that worked. thanks for your help, this was driving me nuts
September 4, 2012 at 1:14 pm
A small change in the query:
select
program,
Completed_task,
timestamp
from
WorkQueue
where type IN ('Software', 'Hardware', 'Software/Hardware')
and status = 'Active'
and timestamp = (select max(timestamp) from WorkQueue);
September 4, 2012 at 1:15 pm
Luis Cazares (9/4/2012)
A small change in the query:
select
program,
Completed_task,
timestamp
from
WorkQueue
where type IN ('Software', 'Hardware', 'Software/Hardware')
and status = 'Active'
and timestamp = (select max(timestamp) from WorkQueue);
Saw that too, just didn't feel like rewriting it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply