removing of sub 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

removing of sub query

Author

Topic

sanjeev124
Starting Member

5 Posts

Posted - 2012-07-31 : 23:40:49

i have to calculate sum and count of diffent columns of a single table on different condition basis.My follwong query is working but it impact on performance i want to remove sub queries..

select distinct lDeviceID as isourceid, '1' iSP, 'SP' sSP,convert (varchar, dTransferDate,101) dTransferDate,

(select COUNT(FileType) from FileStore.dbo.DailyFileTransDataSize d
where d.Filetype in(128,129)and d.lDeviceID=a.lDeviceID and convert(varchar(10), d.dTransferDate,101)=convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as DCefiletype,
(select COUNT(filetype) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(130) and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation ) as VUefiletype ,
(select COUNT(filetype) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(131)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as VDOefiletype ,
(select sum(DataSize) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(128,129)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as DClFileSize,
(select sum(DataSize) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(130)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as VUlFileSize,
(select sum(DataSize) from SVIntermediateFileStore.dbo.DailyFileTransDataSize d where d.Filetype in(131)and d.lDeviceID=a.lDeviceID and convert(varchar(10),d.dTransferDate,101) =convert(varchar(10),a.dTransferDate,101) and d.lInvoiceOrganisation=a.lInvoiceOrganisation) as VDOlFileSize
from SVIntermediateFileStore.dbo.DailyFileTransDataSize a inner join
InvoiceOrganisation SP on (a.lInvoiceOrganisation = SP.lInvoiceOrganisation and charindex(','+ltrim(rtrim(cast(SP.lInvoiceOrganisation as varchar)))+',', ','+ltrim(rtrim(10))+',') > 0)
and CAST(dTransferDate As DATE) >= CAST( '1june2012' AS DATE) and CAST(dTransferDate As DATE) <= CAST( '30june2012' AS DATE)
and lDeviceID>0
and FileType in(128,129,130,131)
group by lDeviceID,FileType,DataSize,dTransferDate,SP.sOrganisationName,a.lInvoiceOrganisation

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-31 : 23:54:38

you can simplified it to


SELECT ....
       d.VUefiletype, d.VDOefiletype
FROM   SVIntermediateFileStore.dbo.DailyFileTransDataSize a
INNER JOIN InvoiceOrganisation SP ON ....
INNER JOIN
(
select 	d.lDeviceID, d.lInvoiceOrganisation, convert(varchar(10), d.dTransferDate,101) as dTransferDate, 
	VUefiletype 	= COUNT(case when d.Filetype in (130) then FileType end),
	VDOefiletype	= COUNT(case when d.Filetype in (131) then FileType end),
	DClFileSize	= SUM(case when d.Filetype in (128, 129) then DataSize end),
	VUlFileSize	= SUM(case when d.Filetype in (130) then DataSize end),
	VDOlFileSize	= SUM(case when d.Filetype in (131) then DataSize end)
from 	FileStore.dbo.DailyFileTransDataSize d
group by d.lDeviceID, d.lInvoiceOrganisation, convert(varchar(10), d.dTransferDate,101)
) d  ON  a.lDeviceID = d.lDeviceID
     AND a.lInvoiceOrganisation = d.lInvoiceOrganisation
     AND convert(varchar(10),a.dTransferDate,101) = d.dTransferDate

KH
[spoiler]Time is always against us[/spoiler]

Rushad
Starting Member

3 Posts

Posted - 2012-08-01 : 00:14:41

Hi Team,

I have a simple question for you guys... I ran this below query into my principals database and I want to know if it did any damage to the database. If yes then how do I re-correct it. Hope you understand that I am a biginner.

Declare @mystring varchar(100)
Select @mystring = 'my_filename.pdf'

SELECT @mystring = Substring(@mystring, 1, (len(@mystring) - 4) )

Thanks in advance...

Thanks,
RS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-01 : 00:37:33

Rushad,

Please post your question as a new thread

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources