on 06-14-2017 2:48 PM
Hi Team,
How to run this query when using parameter {using the last updated filed rather than the created by field } ?
SELECT T1.[U_NAME]'Created BY', T0.[DocNum], T0.[DocDate], T0.[Cardname]'Customer Name', T2.[SlpName], T0.[DocTotal], T0.[GrosProfit],T0.comments FROM OQUT T0 INNER JOIN OUSR T1 ON T0.[UserSign] = T1.[USERID] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] WHERE T0.[DocDate] between [%0] and [%1] Order by T1.u_name ,T0.docdate
thanks for help me for query i want below output,
DECLARE @FRDATE DATE = /* SELECT FROM dbo.ADOC T0 WHERE T0.updatedate >= */ '[%0]'
DECLARE @TODATE DATE = /* SELECT FROM dbo.ADOC T0 WHERE T0.updatedate <= */ '[%1]'
select a.[Created BY],
A.[DocNum],
A.[DocDate],
A.[Customer Name],
A.[SlpName],
A.[DocTotal],
A.[GrosProfit],
A.Comments,
A.[Updated Date] as 'Last update date' from
( SELECT T1.[U_NAME]'Created BY', T0.[DocNum], T0.[DocDate], T0.[Cardname]'Customer Name', T2.[SlpName], T0.[DocTotal], T0.[GrosProfit],T0.comments,
(select U_NAME from Ousr where Ousr.UserId in (select top 1 A.UserSign2 from ADOC A where A.Docentry = T0.Docentry and A.ObjType = 23 ORDER BY A.UpdateDate DESC,UpdateTS DESC)) as 'updated By',
(select top 1 A.UpdateDate from ADOC A where A.Docentry = T0.Docentry and A.ObjType = 23 ORDER BY A.UpdateDate DESC,UpdateTS DESC) as 'updated Date' FROM OQUT T0
INNER JOIN OUSR T1 ON T0.[UserSign] = T1.[USERID]
INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] )
A where a.[Updated Date] between @FRDATE and @TODATE
Order by A.[Created BY] ,A.docdate
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this,
SELECT T1.[U_NAME]'Created BY', T0.[DocNum], T0.[DocDate], T0.[Cardname]'Customer Name', T2.[SlpName], T0.[DocTotal], T0.[GrosProfit],T0.comments FROM OQUT T0 INNER JOIN OUSR T1 ON T0.[UserSign2] = T1.[USERID] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] WHERE T0.[DocDate] between [%0] and [%1] Order by T1.u_name ,T0.docdate
Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks sir,
i want below requirement.
when i have try this Query also as per requirement. below query.
query not working properly ? Help me for same.
/* select * from OQUT x */ DECLARE @FROM AS DATETIME DECLARE @TO AS DATETIME SET @FROM = /* x.updatedate */ [%0] SET @TO = /* x.updatedate */ [%1] SELECT B.[Created BY], B.[DocNum], B.[DocDate], B.[Customer Name], B.[SlpName], B.[GrosProfit], B.[Comments], B.[Updated By], B.[Updated Date] from ( SELECT T1.[U_NAME]'Created BY', T0.[DocNum], T0.[DocDate], T0.[Cardname]'Customer Name', T2.[SlpName], T0.[DocTotal], T0.[GrosProfit],T0.comments, (select U_NAME from Ousr where Ousr.UserId in(select top 1 A.UserSign2 from ADOC A where A.Docentry = T0.Docentry and A.ObjType = 23 AND ( A.[updatedate] BETWEEN @FROM AND @TO) ORDER BY A.UpdateDate DESC))'Updated By', (select top 1 A.UpdateDate from ADOC A where A.Docentry = T0.Docentry and A.ObjType = 23 AND ( A.[updatedate] BETWEEN @FROM AND @TO) ORDER BY A.UpdateDate DESC)'Updated Date' FROM OQUT T0 INNER JOIN OUSR T1 ON T0.[UserSign] = T1.[USERID] INNER JOIN OSLP T2 ON T0.[SlpCode] = T2.[SlpCode] inner join ADOC A on T0.docentry = A.DocEntry WHERE ( A.[updatedate] BETWEEN @FROM AND @TO) group by T1.u_name ,T0.docdate,T0.[DocNum],T0.[Cardname],T2.[SlpName],T0.[DocTotal],T0.[GrosProfit],T0.comments, t0.DocEntry )B where B.[Updated Date] is not NULL
Regards,
Datta P.
I didn`t understand what you want to do.
There is a similar thread you opened some time ago
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.