Need Serious Help with a query

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need Serious Help with a query

Author

Topic

mido
Starting Member

12 Posts

Posted - 2012-01-10 : 13:01:58

i've done it,

thank you anyway

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 13:12:05

cant spot any syntactical issues in query
few bad practises found

1. you're using old join syntax. Try to use new ANSI style syntax as it improves clarity and code readability especially when there are large number of tables involved.
2. Use short aliases for tables rather than repeating tablenames everywhere
3. always specify a length while casting to string data types or else below might happen

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

4.I dont think you require all the expressions you currently have on GROUP BY. it would probably be better to do grouping and aggregation inside a derived table and then joining it to main query with expressions

I cant comment/suggest anything on your resultset and reason why its not giving expected outcome as I dont know how tables are related, what data are in them,what inputs you pass and what you're expecting as output from the data

in such cases probably easiest approach will be to post some data to illustrate the relationships from table and then output you require in below format without posting whole query itself

see guideline on how to post question below
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 13:20:27

First have the patience to read through the link posted and provide supporting information in required format. Then somebody will be able to take a look and help you out!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mido
Starting Member

12 Posts

Posted - 2012-01-11 : 13:33:55

I've modified the code and was able to generate it successfully and get result as expected

Kristen
Test

22859 Posts

Posted - 2012-01-11 : 13:38:37

So now you've deleted your original post so that the help that Visakh has kindly given his time to give you will now make no sense to anyone else coming along.

mido
Starting Member

12 Posts

Posted - 2012-01-11 : 20:27:30

no actually, my posts were too big and useless for anyone looking for anything useful, and after all no one would read all things i have posted, that is why i've deleted it. if your upset, i can kindly re post them again, no problem...

mido
Starting Member

12 Posts

Posted - 2012-01-11 : 20:28:08

this is the old code

SELECT
  dbo.Billing.Debtor,
  dbo.ProfileDetails.CompanyName,
  dbo.MMPROFILES.DESCRIPTION,
  cast(dbo.BillingRatingPlanProvisionings.Validity/12 as nvarchar)+' Years' 'ContractValidity',
  SUM (dbo.Billing.EndCustomerPrice) 'Revenue',
  COUNT (dbo.Billing.ID) as SumOfSMS,
  dbo.BillingRatingPlanProvisionings.Balance,
  ValidFrom =
	CASE WHEN dbo.BillingRatingPlanProvisionings.ValidFrom) IS NULL THEN 'Account Still Ready To Use',
	
 ValidUntil =  
 CASE WHEN convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 107) < convert(varchar,GETDATE() , 107) THEN 'EXPIRED' 
 WHEN dbo.BillingRatingPlanProvisionings.ValidUntil IS NULL THEN 'Account Still Ready To Use' 
 ELSE convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 107),
  convert(varchar,dbo.Billing.LastUpdateTime, 107) as Time
FROM
  dbo.Billing,
  dbo.MMPROFILES,
  dbo.ProfileDetails,
  dbo.BillingRatingPlanProvisionings
WHERE
  ( dbo.ProfileDetails.ID=dbo.MMPROFILES.ID  )
  AND  ( dbo.Billing.Debtor=dbo.MMPROFILES.PROFNAME  )
  AND  ( dbo.BillingRatingPlanProvisionings.ID=dbo.MMPROFILES.ID  )
  AND  ( dbo.MMPROFILES.ADRNAME LIKE '%API%'  )
  AND  ( Convert ( char(11),dbo.Billing.LastUpdateTime) = Convert ( char(11),GETDATE() )  )
GROUP BY
  dbo.Billing.Debtor, 
  dbo.ProfileDetails.CompanyName, 
  dbo.MMPROFILES.DESCRIPTION, 
  ContractValidity, 
  dbo.BillingRatingPlanProvisionings.Balance, 
  ActivationDate, 
  ContractEndDate, 
  Time
ValidUntil =  
 CASE WHEN convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 107) < convert(varchar,GETDATE() , 107) THEN 'EXPIRED' 
 WHEN dbo.BillingRatingPlanProvisionings.ValidUntil IS NULL THEN 'Account Still Ready To Use' 
 ELSE convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 107) 
 END

mido
Starting Member

12 Posts

Posted - 2012-01-11 : 20:28:35

And this is the new code :

SELECT
  dbo.BillingRatingPlanProvisionings.clientid,
	dbo.Billing.Debtor,
	dbo.ProfileDetails.CompanyName,
	dbo.MMPROFILES.DESCRIPTION,
	cast(dbo.BillingRatingPlanProvisionings.Validity/12 as nvarchar)+' Years' 'Validity',
	 SUM (dbo.Billing.EndCustomerPrice) 'Revenue',
  SUM (dbo.Billing.MsgCount) 'SMS Count',
	dbo.BillingRatingPlanProvisionings.Balance,
	ValidFrom =
	CASE WHEN dbo.BillingRatingPlanProvisionings.ValidFrom IS NULL THEN 'Account Still Ready To Use' 
	ELSE convert(varchar,dbo.BillingRatingPlanProvisionings.ValidFrom, 107) END,
	ValidUntil =  
 CASE WHEN (convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 105) < convert(varchar,GETDATE() , 105)) THEN 'EXPIRED' 
 WHEN dbo.BillingRatingPlanProvisionings.ValidUntil IS NULL THEN 'Account Still Ready To Use' 
 ELSE convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 107) END,
	 CAST(YEAR(dbo.Billing.LastUpdateTime) AS VARCHAR(4)) 'Time'
FROM
  dbo.Billing,
	dbo.ProfileDetails,
	dbo.MMPROFILES,
	dbo.BillingRatingPlanProvisionings
WHERE
dbo.Billing.Debtor=dbo.MMPROFILES.PROFNAME
AND ( dbo.ProfileDetails.ID=dbo.MMPROFILES.ID  )
AND dbo.MMPROFILES.ID=dbo.BillingRatingPlanProvisionings.clientid
AND  ( dbo.MMPROFILES.ADRNAME LIKE '%API%'  )
AND Convert ( char(11),dbo.Billing.LastUpdateTime) = Convert ( char(11),GETDATE() )  
GROUP BY
 dbo.BillingRatingPlanProvisionings.clientid,
 dbo.Billing.Debtor, 
 dbo.ProfileDetails.CompanyName,
 dbo.MMPROFILES.DESCRIPTION,
 cast(dbo.BillingRatingPlanProvisionings.Validity/12 as nvarchar)+' Years',
 dbo.BillingRatingPlanProvisionings.Balance,
	CASE WHEN dbo.BillingRatingPlanProvisionings.ValidFrom IS NULL THEN 'Account Still Ready To Use' 
	ELSE convert(varchar,dbo.BillingRatingPlanProvisionings.ValidFrom, 107) END,  
 CASE WHEN convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 105) < convert(varchar,GETDATE() , 105) THEN 'EXPIRED' 
 WHEN dbo.BillingRatingPlanProvisionings.ValidUntil IS NULL THEN 'Account Still Ready To Use' 
 ELSE convert(varchar,dbo.BillingRatingPlanProvisionings.ValidUntil, 107) END,
CAST(YEAR(dbo.Billing.LastUpdateTime) AS VARCHAR(4))

mido
Starting Member

12 Posts

Posted - 2012-01-11 : 20:29:09

i hope this can help someone

Kristen
Test

22859 Posts

Posted - 2012-01-12 : 03:44:18

quote:
Originally posted by mido

no actually, my posts were too big and useless for anyone looking for anything useful, and after all no one would read all things i have posted, that is why i've deleted it.

That's not how the forum works. The "conversation" becomes pointless if people delete their posts from the middle of it.

quote:
i can kindly re post them again, no problem...

Thanks you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources