SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 mySQL to MSSQL conversion help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

102 Posts

Posted - 04/21/2013 :  20:07:03  Show Profile  Reply with Quote
Hi Guys need some help with converting this part of a query from MySQL, I need to run this on MSSQL.

Select (SUM(if(rating = 5, 1, 0))-SUM(if(RATING < 4, 1, 0)))/SUM(if(RATING > 0, 1, 0))*100 as RECENT_NPS,
SUM(if(RATING > 0, 1, 0)) as RECENT_RATINGS,
............



MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/21/2013 :  20:41:28  Show Profile  Reply with Quote
You may want to use case statements where you currently have if statements;
for example replace

SUM(if(rating = 5, 1, 0))

with

SUM(CASE rating
	WHEN  5 THEN 1
	WHEN  1 THEN 1
	WHEN  0 THEN 1
	ELSE 0
	END)

Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 04/21/2013 :  20:54:03  Show Profile  Reply with Quote
Thanks for that would the < and > be something like:
SUM(CASE rating
< 4 THEN 1
< 1 THEN 1
< 0 THEN 1
ELSE 0
END)

?
Go to Top of Page

2revup
Posting Yak Master

102 Posts

Posted - 04/21/2013 :  21:04:53  Show Profile  Reply with Quote
I think it should be this could you please confirm?

Select SUM(case rating when 5 then 1
when 1 then 1
when 0 then 1
else 0 end)-
SUM(case when RATING < 4 then 1
when rating < 1 then 1
when rating < 0 then 1
else 0 end)/
SUM(case when RATING > 0 then 1
when rating > 1 then 1
when rating > 0 then 1
else 0 end)*100 as RECENT_NPS,
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/21/2013 :  22:46:50  Show Profile  Reply with Quote
That should do it.
Just make sure to enclose your numerator and denominator in parenthesis.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/22/2013 :  01:21:01  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

You may want to use case statements where you currently have if statements;
for example replace

SUM(if(rating = 5, 1, 0))

with

 This should be 
SUM(CASE rating
	WHEN  5 THEN 1
	ELSE 0
     END
    )





--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/22/2013 :  01:26:23  Show Profile  Reply with Quote
quote:
Originally posted by 2revup

Hi Guys need some help with converting this part of a query from MySQL, I need to run this on MSSQL.
Select (SUM(if(rating = 5, 1, 0))-SUM(if(RATING < 4, 1, 0)))/SUM(if(RATING > 0, 1, 0))*100 as RECENT_NPS,
SUM(if(RATING > 0, 1, 0)) as RECENT_RATINGS,
............

Select 
	(SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END)-SUM(CASE WHEN RATING < 4 THEN 1 ELSE 0 END))/SUM(CASE WHEN RATING > 0 THEN 1 ELSE 0 END)*100 as RECENT_NPS,
	SUM(CASE WHEN RATING > 0 TEHN 1 ELSE 0 END) as RECENT_RATINGS


--
Chandu
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/22/2013 :  14:01:48  Show Profile  Reply with Quote
My bad.
I misinterpreted MYSQL IF statement...
Thanks for the correction Chandu.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000