need help to write a query

Question

Post reply

need help to write a query

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

patla4u SSCrazy Points: 2103 More actions · Answer 1

Thank you so much.

It is worked in some condition.

In below condition, it is not working.

t_mcnot_citgt_trdt t_shft t_wght(No column name)

F01 1010052016-01-12 00:00:00.0004 0.000.00

F01 1010052016-01-12 00:00:00.0003 0.000.00

F01 1010052016-01-12 00:00:00.0002 0.000.00

F01 1010052016-01-12 00:00:00.0001 0.000.00

F01 1010052016-01-11 00:00:00.0004 17.9617.96

F01 1010052016-01-11 00:00:00.0003 0.000.00

F01 1010052016-01-11 00:00:00.0002 0.000.00

F01 1010052016-01-11 00:00:00.0001 0.000.00

I am expecting order by shift as well.

t_mcnot_citgt_trdt t_shft t_wght(No column name)

F01 1010052016-01-12 00:00:00.0004 0.0017.96

F01 1010052016-01-12 00:00:00.0003 0.0017.96

F01 1010052016-01-12 00:00:00.0002 0.0017.96

F01 1010052016-01-12 00:00:00.0001 0.0017.96

F01 1010052016-01-11 00:00:00.0004 17.9617.96

F01 1010052016-01-11 00:00:00.0003 0.000.00

F01 1010052016-01-11 00:00:00.0002 0.000.00

F01 1010052016-01-11 00:00:00.0001 0.000.00

Luis Cazares SSC Guru Points: 183686 More actions · Answer 2

Of course it's not working, I made it carry over the weight if the shifts are on the same day. If you are able to understand the query, the correction is simple with some changes in the conditions. If you don't understand the query, you shouldn't use it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

patla4u SSCrazy Points: 2103 More actions · Answer 3

I understood the query but I am not able to return the result. I tried my best to get result. Please help me.

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

patla4u (2/8/2016)
I understood the query but I am not able to return the result. I tried my best to get result. Please help me.

Post what you've tried.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

patla4u SSCrazy Points: 2103 More actions · Answer 5

SELECT o.*,

COALESCE( NULLIF(o.t_wght, 0), x.t_wght, 0)

FROM [TBRTEK002] o

OUTER APPLY (

SELECT TOP 1 max(i.t_wght) as t_wght

FROM [TBRTEK002] i

WHERE i.t_mcno = o.t_mcno

AND i.t_citg = o.t_citg

AND i.t_shft < o.t_shft

-- ORDER BY i.t_mcno,i.t_citg,i.t_trdt desc,i.t_shft DESC

) x

WHERE o.t_mcno = 'F01'

ORDER BY [t_mcno],[t_citg],t_trdt desc, t_shft desc;

Luis Cazares SSC Guru Points: 183686 More actions · Answer 6

patla4u (2/8/2016)
SELECT o.*,
COALESCE( NULLIF(o.t_wght, 0), x.t_wght, 0)
FROM [TBRTEK002] o
OUTER APPLY (
SELECT TOP 1 max(i.t_wght) as t_wght
FROM [TBRTEK002] i
WHERE i.t_mcno = o.t_mcno
AND i.t_citg = o.t_citg
AND i.t_shft < o.t_shft
-- ORDER BY i.t_mcno,i.t_citg,i.t_trdt desc,i.t_shft DESC
) x
WHERE o.t_mcno = 'F01'
ORDER BY [t_mcno],[t_citg],t_trdt desc, t_shft desc;

Why did you commented the order by? That's important to ensure that the correct row is chosen. Why aren't you comparing the date? You need something to be either on a previous shift the same day or a previous day with any shift. (Just translate this to T-SQL)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

patla4u SSCrazy Points: 2103 More actions · Answer 7

Thanks for your reply.

I was doing in different scenario, that's why i remove order by. I knew, order by is used to select top 1 wght based on asc and des.

I removed the date because, i am thinking, it will pick up previous date.

In this t-sql, coalesce is used to returned the first non-null expression in the list. Also Outer apply returns the matching and not matching rows.

Let me try few more hours and get back to you.

Thanks

patla4u SSCrazy Points: 2103 More actions · Answer 8

Hi,

I tried again but I am not to produce result as i expected. Please help me and provide query.

Thanks

patla4u SSCrazy Points: 2103 More actions · Answer 9

Hi,, After doing my best try,

I believe, below query should work.

SELECT o.*,

COALESCE( NULLIF( o.t_wght, 0), x.t_wght,y.t_wght, 0)

FROM [TBRTEK002] o

OUTER APPLY (

SELECT top 1 i.t_wght

FROM [TBRTEK002] i

WHERE i.t_mcno = o.t_mcno

AND i.t_citg = o.t_citg

AND i.t_trdt = o.t_trdt

AND i.t_shft < o.t_shft

AND i.t_wght > 0

ORDER BY i.t_shft DESC

) x

OUTER APPLY (

SELECT top 1 c.t_wght as t_wght

FROM [TBRTEK002] c

WHERE c.t_mcno = o.t_mcno

AND c.t_citg = o.t_citg

AND C.t_trdt < o.t_trdt

AND c.t_wght > 0

ORDER BY c.t_shft desc

) y

WHERE o.t_mcno = 'F01'

ORDER BY [t_mcno],[t_citg],[t_trdt] desc,[t_shft] desc

Thanks

Luis Cazares SSC Guru Points: 183686 More actions · Answer 10

Did you try something like this?

SELECT o.*,

COALESCE( NULLIF( o.t_wght, 0), x.t_wght, 0)

FROM [TBRTEK002] o

OUTER APPLY (

SELECT TOP 1 i.t_wght

FROM [TBRTEK002] i

WHERE i.t_mcno = o.t_mcno

AND i.t_citg = o.t_citg

AND (i.t_trdt = o.t_trdt

OR i.t_shft < o.t_shft)

AND i.t_wght > 0

ORDER BY i.t_shft DESC

) x

WHERE o.t_mcno = 'F03'

ORDER BY t_trdt desc, t_shft desc;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

patla4u SSCrazy Points: 2103 More actions · Answer 11

patla4u

SSCrazy

Points: 2103

February 10, 2016 at 12:07 pm

#1857835

Yes,, I checked but it wan't work.

Thanks