Simplified Query - 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

General SQL Server Forums

New to SQL Server Programming

Simplified Query

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-10-03 : 05:04:44

Hello SQL Experts,
This is a part of my SP,in which I'll pass the Start Date as @SDt,End Date as @EDt,Glcode as @Glcode,Location as @Loccode

I want to fetch datas for a Glcode which is quiet the opposite when Glcode is with Drcrflag as 'D' then I want all data that is there in 'C' and vice versa.

Cocode Brcode Loccode Trno Trtype Trdt Drcrflag Glcode
C1 B1 L1 1 TTT 01/01/2007 D 105000
C1 B1 L1 1 TTT 01/01/2007 C 200000

When @Glcode='105000'

Fetch
Cocode Brcode Loccode Trno Trtype Trdt Drcrflag Glcode
C1 B1 L1 1 TTT 01/01/2007 C 200000

Give me a better method to accomplish my task faster.

Select Refno,Refdt,Towhom,Narration,
Glcode,Slcode,
CASE Drcrflag WHEN 'C' THEN Ltrim(Rtrim(Str(Tramt,14,2))) ELSE '0.00' END Debit,
CASE Drcrflag WHEN 'D' THEN Ltrim(Rtrim(Str(Tramt,14,2))) ELSE '0.00' END Credit
from Journal_transnarr SQL2XML
Where (Cocode+Brcode+Loccode+Convert(Varchar,Trno)+Trtype+Convert(Varchar,Trdt,103)+Drcrflag)
IN
(Select (Cocode+Brcode+Loccode+Convert(Varchar,Trno)+Trtype+Convert(Varchar,Trdt,103)+(Case When Drcrflag='D' then 'C' Else 'D' End))
from Trans_Journal Where (Refdt>=CONVERT(DATETIME, @SDt,103) and Refdt<=CONVERT(DATETIME, @EDt,103))
and Glcode=@GLCode and Loccode=@Loccode)
Order By ORD,Refdt,Refno
for XML AUTO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 05:07:31

Please edit your post and put [c o d e] and [ / c o d e ] tags around the query.
Note that spaces in the tags should be removed.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 05:08:38

Rather than concatenating and using IN it would be better to use a JOIN - which will be able to utilise indexes, and thus perform faster

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 05:16:33

[code]SELECT SQL2XML.RefNo,
SQL2XML.RefDt,
SQL2XML.ToWhom,
SQL2XML.Narration,
SQL2XML.GlCode,
SQL2XML.SlCode,
CASE SQL2XML.DrcrFlag
WHEN 'C' THEN LTRIM(RTRIM(STR(SQL2XML.Tramt, 14, 2)))
ELSE '0.00'
END Debit,
CASE SQL2XML.DrcrFlag
WHEN 'D' THEN LTRIM(RTRIM(STR(SQL2XML.Tramt, 14, 2)))
ELSE '0.00'
END Credit
FROM Journal_TransNarr AS SQL2XML
WHERE EXISTS (
SELECT *
FROM Trans_Journal AS tj
WHERE SQL2XML.CoCode = tj.CoCode
AND SQL2XML.BrCode = tj.BrCode
AND SQL2XML.LocCode = tj.LocCode
AND SQL2XML.TrNo = tj.TrNo
AND SQL2XML.TrType = tj.TrType
AND DATEDIFF(DAY, 0, SQL2XML.TrDt) = DATEDIFF(DAY, 0, tj.TrDt)
AND SQL2XML.DrcrFlag = CASE tj.Drcrflag
WHEN 'D' THEN 'C'
ELSE 'D'
END
AND tj.GlCode = @GlCode
AND tj.LocCode = @LocCode
AND tj.RefDt >= DATEADD(DAY, DATEDIFF(DAY, 0, @sDt), 0)
AND tj.RefDt < DATEADD(DAY, DATEDIFF(DAY, 0, @eDt), 1)
)
ORDER BY ORD,
RefDt,
RefNo
FOR XML AUTO[/code]

E 12°55'05.25"
N 56°04'39.16"

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-10-03 : 05:51:12

Thanks for your reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources