Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sbt1
Yak Posting Veteran
89 Posts |
 Posted - 2005-08-12 : 10:32:43
|
| I have this query:SELECT Tasks.TaskID, Tasks.Customer, TaskDates.Resource, TaskDates.DateValue, TaskDates.CallFirstRequired, TaskDates.Confirmed, TaskDates.Locked, TaskDates.Status, TaskDates.Resource FROM Tasks INNER JOIN TaskDates ON Tasks.TaskID = TaskDates.TaskID WHERE (TaskDates.Type LIKE 'Preventive%' AND TaskDates.Status='Scheduled'AND TaskDates.DateValue <(GETDATE()-60))ORDER BY Tasks.TaskID, Tasks.Customer,TaskDates.DateValue DESCI get this:4590 Customer A Smith 01/05/2005 ......4590 Customer A Jones 01/20/2005 ......5000 Customer A Jones 02/15/2005 ......9112 Customer B Brown 01/13/2005 ......But I want this:4590 Customer A January 2005 ......5000 Customer A February 2005 ......9112 Customer B January 2005 ......in other words, I just want to know which months have any records that are found by the query, and just display the month and year.Any help would be appreciated! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-12 : 10:45:52
|
| If you use this In Front End like VB, you can use Format functionIf you want to do this in query itself, you have to use Convert functionMadhivananFailing to plan is Planning to fail |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-08-12 : 11:24:55
|
| As Mad said, something like this:DATENAME(m, TaskDates.DateValue) + ' ' + CONVERT(char(4), YEAR(TaskDates.DateValue)) |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2005-08-12 : 11:52:53
|
| I didn't explain my point correctly... converting to show the month name is simple and not what I need help with.I need help with trimming the resulting record set down so I get only ONE record for each month |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-13 : 00:48:49
|
| Try thisSelect TaskId,Customer,min(DateField) from yourTable group by TaskId,CustomerMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Need query help