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
 General SQL Server Forums
 New to SQL Server Programming
 Help with joining status
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kroni
Starting Member

4 Posts

Posted - 07/29/2013 :  08:31:03  Show Profile  Reply with Quote
Hello Everyone!

this is my first post here. I have certain issues with sql code i have to modify

SELECT status, COUNT(status) AS #machines, MIN(recorddate) AS startdate FROM citrix
WHERE pool='POOL6668'
recorddate BETWEEN '2013-07-18' AND '2013-07-18 22:00'
GROUP BY status
ORDER BY status, startdate;

Im using this inside a VBL script to create a Excel sheet.

Now my Problem is with the status part. There are 4 choices i could get back "used,unknown, ready or accessdenied"

Now i want to extract those 4 status into an excel sheet into a graph. BUT i want to extract two together into one colum (so i want to put unknown and accessdenied together), so instead of 4 status choices i want only 3 to appear on my graph (1,2+4,3)

so how do i put these together into a nice SQL command?





Edited by - Kroni on 07/29/2013 08:33:22

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 07/29/2013 :  08:40:20  Show Profile  Reply with Quote
SELECT CASE WHEN status IN ('unknown', 'accessdenied') THEN 'BothTogether'
ELSE status END CustomisedStatus

,COUNT(status) AS #machines, MIN(recorddate) AS startdate
FROM citrixc
WHERE pool='POOL6668'
recorddate BETWEEN '2013-07-18' AND '2013-07-18 22:00'
GROUP BY CASE WHEN status IN ('unknown', 'accessdenied') THEN 'BothTogether' ELSE status END
ORDER BY status, startdate;

--
Chandu
Go to Top of Page

Kroni
Starting Member

4 Posts

Posted - 07/29/2013 :  08:53:39  Show Profile  Reply with Quote
there is still a mistake there.

i think it is that when i do COUNT(status) and i get BothTogether instead, he might not be happy with that.

or Code: 8127, SQL State: S1000] Column "citrix.status" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]


Edited by - Kroni on 07/29/2013 08:54:10
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3656 Posts

Posted - 07/29/2013 :  09:04:58  Show Profile  Reply with Quote
To fix the order by problem, make the change to Chandu's query as shown below
ORDER BY CustomisedStatus, startdate;
Regarding the counts, if you have aggregated the four statuses into 3 custom statuses, doesn't it make sense that you get only 3 rows, and so one count for each row. How shold the 3 statuses and four counts be returned if indeed you want to get 4 counts?
Go to Top of Page

Kroni
Starting Member

4 Posts

Posted - 07/29/2013 :  09:18:51  Show Profile  Reply with Quote
well i want to get back 3 counts indeed and yea 3 rows because im basically turning 2 counts into one (the unknown and accessdenied) thus also just having 3 rows.

the bothtogether row, the used, and the ready row

not really sure what your question is, but im starting to confuse myself by now too.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3656 Posts

Posted - 07/29/2013 :  09:33:13  Show Profile  Reply with Quote
My question was about what you said here:
quote:
there is still a mistake there.
i think it is that when i do COUNT(status) and i get BothTogether instead, he might not be happy with that.

If Chandu's query does not give you what you are looking for, if you can post a sample of the exact output that you want to get, someone should be able to modify the query to meet your requirements.
Go to Top of Page

Kroni
Starting Member

4 Posts

Posted - 07/29/2013 :  10:00:04  Show Profile  Reply with Quote
I feel like the querry is good now, but i need further testing next part is to modify the VB-script to give me the excel output i want.

regards
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