We help IT Professionals succeed at work.

php MSSQL query case

mavmanau
mavmanau asked
on
Hello Experts,

thank you for taking a look at my question.

basically I have these 2 queries:
SELECT COUNT(Sta_Desc) FROM Full
Where Sta_Desc like 'SOff'
Or Sta_Desc like 'FSent'
And Lon_DateLoanAgreement between '2011-10-27' and '2011-10-28'
And Lgn_Name not like 'Cust Serv Repeat'
And Lgn_Name not like 'Cust Serv'

SELECT COUNT(Lon_DateLoanAgreement) FROM Full
Where Lon_DateLoanAgreement between '2011-10-27' and '2011-10-28'
And Lgn_Name not like 'Cust Serv Repeat'
And Lgn_Name not like 'Cust Serv'

that I wanted to combine.

I have done this:
$query = "SELECT count(case when (Sta_Desc like 'SOff') or (Sta_Desc like 'Fsent') then 1 else null end),COUNT(Lon_DateLoanAgreement) FROM Full
Where Lon_DateLoanAgreement between '2011-10-27' and '2011-10-28'
And Lgn_Name not like 'Cust Serv Repeat'
And Lgn_Name not like 'Cust Serv'";

but it is not quite working.  I know it is in the case somehow, but can't quite work it out.  The count is working for the second column I want, just not the first.  something to do with the or.  I have tried doing them as seperate ie fsent in one case, and soff as another but it doesn't do it.  I can do them as seperate queries and they work fine, but wanted to have them in one query!

Please help!!  any light you could shine on issue would be most appreciated!!

thank you very much!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
do you want the count the record only when condition is true ?

then try this

$query = "SELECT sum(case when (Sta_Desc like 'SOff') or (Sta_Desc like 'Fsent') then 1 else 0 end),COUNT(Lon_DateLoanAgreement) FROM Full
Where Lon_DateLoanAgreement between '2011-10-27' and '2011-10-28'
And Lgn_Name not like 'Cust Serv Repeat'
And Lgn_Name not like 'Cust Serv'";
# mavmanau

Here is your solution, I tested following code and it's work as well.
If you receive any errors be sure queries which you sent in this post was incorrect.

SELECT qry1.cnt1 , qry2.cnt2
	FROM
		(SELECT COUNT(Sta_Desc) AS `cnt1` FROM Full
			Where Sta_Desc like 'SOff'
			Or Sta_Desc like 'FSent'
			And Lon_DateLoanAgreement between '2011-10-27' and '2011-10-28'
			And Lgn_Name not like 'Cust Serv Repeat'
			And Lgn_Name not like 'Cust Serv') as `qry1`,
   		
   		(SELECT COUNT(Lon_DateLoanAgreement) AS `cnt2` FROM Full
			Where Lon_DateLoanAgreement between '2011-10-27' and '2011-10-28'
			And Lgn_Name not like 'Cust Serv Repeat'
			And Lgn_Name not like 'Cust Serv') as `qry2`; 

Open in new window


Syntax
SELECT table1.field1, table2.field2
FROM (
        SELECT COUNT( `field` ) AS `field1` FROM `TABLE_NAME`
) AS `table1` , (
SELECT COUNT( `field` ) AS `field2`
FROM `TABLE_NAME`
) AS `table2` 

Open in new window

mavmanauNetwork Engineer/Sysadmin

Author

Commented:
hmm both solutions work perfect!! Do I just share the points between you?  I ended up using the first solution however.
# mavmanau

Do everythings you think right.

but I think my solution is betterm because it's contain optimized syntax and don't use comparision and additional commands.

my commands here just count and return result in table, while first reply to your post contain clause,conditions and...
CERTIFIED EXPERT
Commented:
I think my soultion has less pverhead as I havn't used Subqueries

Subquery will take more time
# pratima_mcs

I know your experiences is good but I'll say something here for other users.

However is development resource usage is one of fundamental phases, but new method usually code should choice one of processor usage or memory usage.
Usually we develop with both rules, but processor usage is more important than memory usage.
in recursive function usually we spend our usage to memory and etc.

i just add 2 code inside of primary query. I mean "SELECT COUNT(Sta_Desc) AS `cnt1`..." and "SELECT COUNT(Sta_Desc) AS `cnt2`........" while in your code we have more than three nested query, in addition you add some conditions in your code.

place of using methods like that is triggers, not queries.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.