| Author |
 Topic  |
|
|
Yonkouturko
Starting Member
Philippines
36 Posts |
 Posted - 02/20/2013 : 01:31:15
|
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_JobOrder_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
UNION
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_Technician_Received_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
ORDER BY Table_JobOrder_Information.ID DESC
this is a sample out:
2013-34 Other UNDER REPAIR DIAGNOSE / REPAIR 2013/02/19 06:17:41 PM NULL 1 2013/02/20 01:47:00 PM 34
2013-34 Other UNDER REPAIR DIAGNOSE / REPAIR 2013/02/19 06:17:41 PM NULL admin 2013/02/19 06:18:23 PM 34
2013-34 Other UNDER REPAIR DIAGNOSE / REPAIR 013/02/19 06:17:41 PM NULL kenneth 2013/02/20 10:41:14 AM 34
i can't MAX out
same job # with different TECHNICIAN HANDLER
with the recent TECHNICIAN is on the top of the list according to date and time handled
please help.. please please please!!!! i really don't know what to do!!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/20/2013 : 01:35:16
|
how do you expect us to know what your problem is?
Explain us what you want by giving current output as against your expected with column names (otherwise we cant understand which column value is which)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/20/2013 : 01:43:55
|
My best guess is
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY JOB_ORDER_NO ORDER BY RECEIVED_DATE DESC,RECEIVED_TIME DESC) AS Seq
FROM
(
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_JobOrder_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
UNION
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_Technician_Received_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
)t
)r
WHERE Seq=1
ORDER BY ID DESC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1407 Posts |
Posted - 02/20/2013 : 01:47:28
|
i thought you want to order result set by recent technician..
If yes, See this example..
GO
declare @tab TABLE(job varchar(10), tech varchar(10),date1 date, time1 time)
INSERT INTO @tab
SELECT '2013-34', '1', '2013/02/19', '06:18:23 PM' union all
SELECT '2013-34', 'kenneth', '2013/02/20', '01:47:00 PM' union all
SELECT '2013-34', 'admin', '2013/02/20', '10:41:14 AM'
SELECT *
FROM @tab
ORDER BY job desc, date1 desc, time1 desc
--
Chandu |
|
|
|
Yonkouturko
Starting Member
Philippines
36 Posts |
Posted - 02/20/2013 : 02:02:44
|
IM SO SORRY!!! MY BAD FOR THIS POST...
heres the sample output
2013-20 Laptop For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/02/18 11:02:03 AM
2013-22 Desktop For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/02/18 11:16:02 AM
2013-23 Laptop For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/02/18 11:20:29 AM
2013-24 Laptop For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/02/18 11:40:27 AM
2013-25 Desktop For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/02/18 11:43:43 AM
2013-26 Laptop For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/02/18 01:24:28 PM
2013-27 Laptop For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/02/18 01:34:04 PM
2013-29 Other For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/01/19 10:12:52 AM
2013-32 Other For Repair DIAGNOSE / FOR CONFIRMATION IN-HOUSE SERVICE 2013/01/19 11:01:01 AM
2013-34 Other UNDER REPAIR DIAGNOSE / REPAIR IN-HOUSE SERVICE 2013/02/19 06:17:41 PM 1 2013/02/20 01:47:00 PM
2013-34 Other UNDER REPAIR DIAGNOSE / REPAIR IN-HOUSE SERVICE 2013/02/19 06:17:41 PM admin 2013/02/19 06:18:23 PM
2013-34 Other UNDER REPAIR DIAGNOSE / REPAIR IN-HOUSE SERVICE 2013/02/19 06:17:41 PM kenneth 2013/02/20 10:41:14 AM
2013-36 Desktop For Repair FOR ASSEMBLE New IN-HOUSE SERVICE 2013/02/19 09:37:22 PM FOR ASSEMBLE ASAP
JOB # should not repeat but here in this sample output... job # = '2013-34' repeat 3x because it has 3 technicians handled on different date/time now i want to filter out the most recent handler of a job #( in this case job #= '2013-34' recent technician is "1" the last two is "admin" and "kenneth" )
along with the other job #
for the record... this sql is for monitoring purposes it means... succeeding job # is required and as long as it does not repeat
IM REALLY SORRY sir BANDI(chandu) and sir VISAKH16... i hope i make my self clear...
forgive for my lousy post... i haven't got any good sleep at all.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/20/2013 : 02:13:06
|
My earlier suggestion will give you want you're looking for
ie
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY JOB_ORDER_NO ORDER BY RECEIVED_DATE DESC,RECEIVED_TIME DESC) AS Seq
FROM
(
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_JobOrder_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
UNION
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_Technician_Received_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
)t
)r
WHERE Seq=1
ORDER BY ID DESC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Yonkouturko
Starting Member
Philippines
36 Posts |
Posted - 02/20/2013 : 02:23:28
|
Sir Visakh16 i almost got it... only one problem...
recent technician "1" did not come out.. the 2nd technician show..which is "kenneth"
supposedly technician "1" will be shown... and not technician "kenneth"
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/20/2013 : 02:32:57
|
whats the datatype of RECEIVED_DATE and RECEIVED_TIME?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Yonkouturko
Starting Member
Philippines
36 Posts |
Posted - 02/20/2013 : 02:38:39
|
| its varchar(1000) sir |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1407 Posts |
Posted - 02/20/2013 : 02:41:55
|
quote:
Originally posted by Yonkouturko
its varchar(1000) sir
why you need VARCHAR(1000) for DATE, TIME types of data? If possible alter those respective columns to DATE and TIME types....
If you don't have permission to alter table, then apply following approach
ROW_NUMBER() OVER (PARTITION BY JOB_ORDER_NO ORDER BY CAST(RECEIVED_DATE AS DATE) DESC, CAST(RECEIVED_TIME AS TIME) DESC) AS Seq
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/20/2013 : 02:43:23
|
thats the reason. then make it like
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY JOB_ORDER_NO ORDER BY CONVERT(datetime,RECEIVED_DATE + ' ' +RECEIVED_TIME) DESC) AS Seq
FROM
(
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_JobOrder_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
UNION
SELECT Table_JobOrder_Information.JOB_ORDER_NO, Table_JobOrder_Information.ITEM_TYPE, Table_JobOrder_Information.REPAIR_STATUS,
Table_JobOrder_Information.ACTION_TO_BE_TAKEN, Table_JobOrder_Information.RECEIVE_TYPE, Table_JobOrder_Information.SERVICE_TYPE,
Table_JobOrder_Information.DATE_RECEIVED, Table_JobOrder_Information.TIME_RECEIVED, Table_JobOrder_Information.PROBLEMS,
Table_Technician_Received_Information.TECHNICIAN, Table_Technician_Received_Information.RECEIVED_DATE,
Table_Technician_Received_Information.RECEIVED_TIME, Table_JobOrder_Information.ID
FROM Table_JobOrder_Information LEFT OUTER JOIN
Table_Technician_Received_Information ON Table_JobOrder_Information.JOB_ORDER_NO = Table_Technician_Received_Information.JOB_ORDER_NO
WHERE (NOT (Table_Technician_Received_Information.REPAIR_STATUS IN('RELEASED', 'CANCELLED', 'RELEASED / BACKJOB', 'RELEASED / BACKJOB WITH ADDITIONAL','FINISHED / OK', 'RETURN TO CLIENT / NO PARTS AVAILABLE'))) AND (NOT (Table_JobOrder_Information.DATE_RECEIVED = '2013/02/20'))
)t
)r
WHERE Seq=1
ORDER BY ID DESC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Yonkouturko
Starting Member
Philippines
36 Posts |
Posted - 02/20/2013 : 03:06:13
|
im using sql server 2008 R2
sir @CHANDU, it is because i get date and time mixed up in one column... and
i can't get the exact date for date without having a time of i guess "00:00:00" or "12:00:00"
like this sir chandu "2013-01-09 00:00:00.000" and it really affects my searching in my monitoring modules and other search modules..
SIR VISAKH16 THANK YOU SO MUCH! it work perfectly!!!
for the both teachers' IM REALLY SORRY FOR A LOUSY POST....
is there any other way around this DATE and TIME issue...? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/20/2013 : 03:13:27
|
nope...only way is to store them in date based datatyped field
one question though, why do you need two fields? why cant you store date and time in same field with datetime datatype?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Yonkouturko
Starting Member
Philippines
36 Posts |
Posted - 02/20/2013 : 03:41:39
|
sometimes i wants to search sometimes.. by time only... or date only..
now... the problem i encountered was you cannot search without the date and time...or you will not get the desired result
another thing is that...i cannot use daterange(like BETWEEN @DATE and @DATE2 or @TIME and @TIME2) search in that kind of datatype it gives different result.. i really dont know why.. thats why in my case... i separate them ...
|
Edited by - Yonkouturko on 02/20/2013 03:45:58 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
|
|
Yonkouturko
Starting Member
Philippines
36 Posts |
Posted - 02/23/2013 : 02:21:31
|
@sir visakh16
thank you for the links i will study it!! and hope to learn it soon :)
again teaching me is really appreciated :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/23/2013 : 02:28:16
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
PLEASE HELP!!!!
