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 |
|
markyp18
Starting Member
1 Post |
 Posted - 2014-10-03 : 10:41:34
|
| Hi, I have created a sql query that is pulling the right data, but now I need to apply a distinct sub query to it.....I think !!!The query is:-SELECT TimeTable.TTTimeSheetRecordNo, MyDepotManager.DMDepotManager, MyDepotManager.DMLoginName, TimeTable.TTEngineerNo, TimeTable.TTRecordNo, TimeTable.TTStartDate, TimeTable.TTCostCode, TimeTable.TTTimeTakenMinsFROM MyDepotManager INNER JOIN MyEngineerTable ON MyDepotManager.DMLoginName = MyEngineerTable.UELoginName INNER JOIN TimeTable ON MyEngineerTable.UEEngineerNo = TimeTable.TTEngineerNo WHERE (TimeTable.TTCostCode = 'cr') AND (TimeTable.TTEngineerNo = 301) OR (TimeTable.TTCostCode = 'cd') AND (TimeTable.TTEngineerNo = 301) OR (TimeTable.TTCostCode = 'cs') AND (TimeTable.TTEngineerNo = 301) Order by TTRecordNo descThe data it shows is as follows:-TTTimeSheetRecordNo DMDepotManager DMLoginName TTEngineerNo TTRecordNo TTStartDate TTCostCode TTTimeTakenMins1265300 manager name1 301 1157661 2014-10-03 00:00:00.000 CR 21265300 manager name2 301 1157661 2014-10-03 00:00:00.000 CR 21265289 manager name1 301 1157650 2014-10-03 00:00:00.000 CR 291265289 manager name2 301 1157650 2014-10-03 00:00:00.000 CR 29The query pulls the time taken from a timesheet, but because we have 2 controllers looking after the engineer, each line shows twice showing twice as many timesheet hrs as it should do !!!!I need the TTRecordNo to show once in the dataset, 2 lines rather than 4, so I thought a distinct sub query would work....but I could not get it to work properlyAny help with this would be appreciated.CheersMark. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-03 : 11:49:37
|
| Looking at your output, the first two (also the last two) only differ by the DMDepotManager column. Working with the same input data, what should your output look like? |
 |
|
|
|
|
|
|
|
SQL Query "Distinct" Problem