PLEASE HELP!!!! - SQL Server Forums

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

PLEASE HELP!!!!

Author

Topic

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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
Yak Posting Veteran

59 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 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
Yak Posting Veteran

59 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 02:32:57

whats the datatype of RECEIVED_DATE and RECEIVED_TIME?

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

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-02-20 : 02:38:39

its varchar(1000) sir

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 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
Yak Posting Veteran

59 Posts

Posted - 2013-02-20 : 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

52326 Posts

Posted - 2013-02-20 : 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
Yak Posting Veteran

59 Posts

Posted - 2013-02-20 : 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 ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-20 : 04:16:23

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/

Yonkouturko
Yak Posting Veteran

59 Posts

Posted - 2013-02-23 : 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

52326 Posts

Posted - 2013-02-23 : 02:28:16

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources