how to minimize subquery - 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

how to minimize subquery

Author

Topic

sanjeev124
Starting Member

5 Posts

Posted - 2012-08-01 : 01:41:38

how to minimize a subquery for a single table .
calculate sum and count of columns of a single table on condition basis.

select e.iReadInWith as isourceid, '1' iSP, 'SP' sSP,convert (varchar, dTransferStartDate,101) dTransferDate,(select COUNT(efiletype) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(129) and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as DCefiletype,(select COUNT(efiletype) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(130) and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VUefiletype ,
(select COUNT(efiletype) from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(131)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VDOefiletype ,
(select sum(convert(decimal,lOriginalFileSize))/1048576 from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(128,129)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as DClFileSize,
(select sum(convert(decimal,lOriginalFileSize))/1048576 from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(130)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VUlFileSize,
(select sum(convert(decimal,lOriginalFileSize))/1048576 from SVIntermediateFileStore.dbo.FileTransfer d where d.eFiletype in(131)and d.iReadInWith=e.iReadInWith and d.guserid in (e.guserid)) as VDOlFileSize
from SVIntermediateFileStore.dbo.FileTransfer e,InvoiceOrganisation SP
where e.gUserID in (
select gUserID From Users where lAccountID in
(
select lAccountID from Account where lInvoiceOrganisation in
(
select lInvoiceOrganisation from InvoiceOrganisation where
SP.lInvoiceOrganisation=InvoiceOrganisation.lInvoiceOrganisation and (charindex(','+ltrim(rtrim(cast(InvoiceOrganisation.lInvoiceOrganisation as varchar)))+',', ','+ltrim(rtrim(@sSPID))+',') > 0)
)
)
)--and e.dTransferStartDate between @dtStartDate and @dtEndDate
and CAST(e.dTransferStartDate As DATE) >=CAST(@dtStartDate As DATE) and CAST(e.dTransferStartDate as DATE)<=CAST(@dtEndDate as DATE)
and e.iReadInWith=@sSelectedSource
and e.eState=3
and e.eTransferDirection=1
and eFileType in(128,129,130,131)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-08-01 : 02:06:37

First we should see formatted code:

SELECT e.ireadinwith                              AS isourceid,
       '1'                                        iSP,
       'SP'                                       sSP,
       CONVERT (VARCHAR, dtransferstartdate, 101) dTransferDate,
       (SELECT Count(efiletype)
        FROM   svintermediatefilestore.dbo.filetransfer d
        WHERE  d.efiletype IN( 129 )
               AND d.ireadinwith = e.ireadinwith
               AND d.guserid IN ( e.guserid ))    AS DCefiletype,
       (SELECT Count(efiletype)
        FROM   svintermediatefilestore.dbo.filetransfer d
        WHERE  d.efiletype IN( 130 )
               AND d.ireadinwith = e.ireadinwith
               AND d.guserid IN ( e.guserid ))    AS VUefiletype,
       (SELECT Count(efiletype)
        FROM   svintermediatefilestore.dbo.filetransfer d
        WHERE  d.efiletype IN( 131 )
               AND d.ireadinwith = e.ireadinwith
               AND d.guserid IN ( e.guserid ))    AS VDOefiletype,
       (SELECT Sum(CONVERT(DECIMAL, loriginalfilesize)) / 1048576
        FROM   svintermediatefilestore.dbo.filetransfer d
        WHERE  d.efiletype IN( 128, 129 )
               AND d.ireadinwith = e.ireadinwith
               AND d.guserid IN ( e.guserid ))    AS DClFileSize,
       (SELECT Sum(CONVERT(DECIMAL, loriginalfilesize)) / 1048576
        FROM   svintermediatefilestore.dbo.filetransfer d
        WHERE  d.efiletype IN( 130 )
               AND d.ireadinwith = e.ireadinwith
               AND d.guserid IN ( e.guserid ))    AS VUlFileSize,
       (SELECT Sum(CONVERT(DECIMAL, loriginalfilesize)) / 1048576
        FROM   svintermediatefilestore.dbo.filetransfer d
        WHERE  d.efiletype IN( 131 )
               AND d.ireadinwith = e.ireadinwith
               AND d.guserid IN ( e.guserid ))    AS VDOlFileSize
FROM   svintermediatefilestore.dbo.filetransfer e,
       invoiceorganisation SP
WHERE  e.guserid IN (SELECT guserid
                     FROM   users
                     WHERE  laccountid IN (SELECT laccountid
                                           FROM   account
                                           WHERE  linvoiceorganisation IN
                                                  (SELECT
                                                  linvoiceorganisation
                                                                           FROM
                                                  invoiceorganisation
                                                                           WHERE
       SP.linvoiceorganisation =
       invoiceorganisation.linvoiceorganisation
       AND ( Charindex(
       ','
       + Ltrim(Rtrim(Cast(invoiceorganisation.linvoiceorganisation AS
       VARCHAR
       )))
       + ',', ',' + Ltrim(Rtrim(@sSPID)) + ',') > 0 ))))
       --and e.dTransferStartDate between @dtStartDate and @dtEndDate 
       AND Cast(e.dtransferstartdate AS DATE) >= Cast(@dtStartDate AS DATE)
       AND Cast(e.dtransferstartdate AS DATE) <= Cast(@dtEndDate AS DATE)
       AND e.ireadinwith = @sSelectedSource
       AND e.estate = 3
       AND e.etransferdirection = 1
       AND efiletype IN( 128, 129, 130, 131 )

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources