Click here to Skip to main content
15,880,725 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
C#
SELECT       
T0.ClgCode, T0.DocNum, T0.Recontact, T1.CardName, T1.DocTotal,T0.CntctType,
T0.Details, T0.Notes, T0.CntctTime, T1.DocDate, T2.U_NAME, T1.DocStatus,T3.QryGroup52 as 'Swati',
T3.QryGroup53 as 'Janet', T3.QryGroup54 as 'Padmini', T3.QryGroup56 as 'Abdel',
T3.QryGroup57 as 'Nitin'
FROM           
dbo.OCLG AS T0 INNER JOIN
dbo.OQUT AS T1 ON T0.DocNum = T1.DocNum INNER JOIN
dbo.OUSR AS T2 ON T0.AttendUser = T2.USERID inner join
dbo.OCRD AS T3 ON T0. CardCode=T3.CardCode
where T1.series='223' and T0.docnum=6821
Posted
Updated 5-Jan-16 23:29pm
v2
Comments
_Asif_ 6-Jan-16 5:19am    
show us 10 sample rows and the desired result in tabular form
Master Vinu 6-Jan-16 5:27am    
ClgCode DocNum Recontact CardName DocTotal
10828 6821 2015-11-23 00:00:00.000 A. CO PICARD SEALING 100460.000000
10829 6821 2015-12-01 00:00:00.000 A. CO PICARD SEALING 100460.000000
10906 6821 2015-12-02 00:00:00.000 A. CO PICARD SEALING 100460.000000

i wan last row cause its maximum one or you can say latest one. docnum 6821 is common. T0.RECONTACT IS DATE FIELD.

1 solution

SQL
SELECT TOP 1      
T0.ClgCode, T0.DocNum, T0.Recontact, T1.CardName, T1.DocTotal,T0.CntctType,
T0.Details, T0.Notes, T0.CntctTime, T1.DocDate, T2.U_NAME, T1.DocStatus,T3.QryGroup52 as 'Swati',
T3.QryGroup53 as 'Janet', T3.QryGroup54 as 'Padmini', T3.QryGroup56 as 'Abdel',
T3.QryGroup57 as 'Nitin'
FROM           
dbo.OCLG AS T0 INNER JOIN
dbo.OQUT AS T1 ON T0.DocNum = T1.DocNum INNER JOIN
dbo.OUSR AS T2 ON T0.AttendUser = T2.USERID inner join
dbo.OCRD AS T3 ON T0. CardCode=T3.CardCode
where T1.series='223' and T0.docnum=6821
ORDER BY T0.Recontact DESC


for each docnum:
SQL
SELECT ClgCode, DocNum, ReContact, CardName, DocTotal, CntctType,
Details, Notes, CntctTime, DocDate, U_NAME, DocTotal, Swati, Janet, PadMini, Abdel, Nitin FROM
(
SELECT ROW_NUMBER OVER (PARTITION BY T0.docnum ORDER BY T0.Recontact DESC) AS rowno,
T0.ClgCode, T0.DocNum, T0.Recontact, T1.CardName, T1.DocTotal,T0.CntctType,
T0.Details, T0.Notes, T0.CntctTime, T1.DocDate, T2.U_NAME, T1.DocStatus,T3.QryGroup52 as 'Swati',
T3.QryGroup53 as 'Janet', T3.QryGroup54 as 'Padmini', T3.QryGroup56 as 'Abdel',
T3.QryGroup57 as 'Nitin'
FROM

dbo.OCLG AS T0 INNER JOIN
dbo.OQUT AS T1 ON T0.DocNum = T1.DocNum INNER JOIN
dbo.OUSR AS T2 ON T0.AttendUser = T2.USERID inner join
dbo.OCRD AS T3 ON T0. CardCode=T3.CardCode
) as x
where x.rowno = 1
 
Share this answer
 
v3
Comments
Master Vinu 6-Jan-16 5:52am    
digimanaus,

what if i wan it for all docnum.
Herman<T>.Instance 6-Jan-16 7:12am    
in that case you need the Row_Number function like added to the given solution.
You are working on the SQL N-th pattern.
Master Vinu 6-Jan-16 7:44am    
thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900