SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 PLEASE HELP!!!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 02/20/2013 :  01:31:15  Show Profile  Reply with Quote
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
52317 Posts

Posted - 02/20/2013 :  01:35:16  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  01:43:55  Show Profile  Reply with Quote
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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/20/2013 :  01:47:28  Show Profile  Reply with Quote
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
Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 02/20/2013 :  02:02:44  Show Profile  Reply with Quote
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..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  02:13:06  Show Profile  Reply with Quote
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/

Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 02/20/2013 :  02:23:28  Show Profile  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  02:32:57  Show Profile  Reply with Quote
whats the datatype of RECEIVED_DATE and RECEIVED_TIME?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 02/20/2013 :  02:38:39  Show Profile  Reply with Quote
its varchar(1000) sir
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/20/2013 :  02:41:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  02:43:23  Show Profile  Reply with Quote
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/

Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 02/20/2013 :  03:06:13  Show Profile  Reply with Quote
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...?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  03:13:27  Show Profile  Reply with Quote
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/

Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 02/20/2013 :  03:41:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/20/2013 :  04:16:23  Show Profile  Reply with Quote
quote:
Originally posted by Yonkouturko

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 ...




you can do all you want using datetime field itself

if you want to search using date or time you can apply logic to separate out date and time part

see

http://visakhm.blogspot.in/2010/01/some-quick-tips-for-date-formating.html

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

the date range comparison can be like last method outlined here

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Edited by - visakh16 on 02/20/2013 04:17:37
Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 02/23/2013 :  02:21:31  Show Profile  Reply with Quote
@sir visakh16
thank you for the links i will study it!! and hope to learn it soon :)
again teaching me is really appreciated :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/23/2013 :  02:28:16  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000