| Author |
 Topic  |
|
|
sanjeev124
Starting Member
5 Posts |
 Posted - 07/31/2012 : 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
|
Edited by - sanjeev124 on 07/31/2012 23:45:58
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 07/31/2012 : 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
Time is always against us
|
 |
|
|
Rushad
Starting Member
India
3 Posts |
Posted - 08/01/2012 : 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)
Singapore
16746 Posts |
Posted - 08/01/2012 : 00:37:33
|
Rushad,
Please post your question as a new thread
KH
Time is always against us
|
|
|
| |
Topic |
|
|
|
removing of sub query
