Sql fudge / counts across dates

[darkgen]

I have a simple table which includes two date fields;
Ticket
Creation
Resolved
And I'm trying to generate a count of Tickets created and resolved by day, week, month. However my abysmal tsql knowledge means I'm struggling to get the query to return the Ticket counts correctly. Here is my query in it's current format;

SELECT        CreationDate, COUNT(TICKETS) AS Tickets, SUM(CASE WHEN ResolvedDate IS NULL THEN 0 ELSE 1 END) AS ResolvedCount
FROM            table
GROUP BY CreationDate
ORDER BY CreationDate

I can see the flaw in the logic, as it's grouping the ResolvedCount with the CreationDate but I'm struggling to find examples online of how to accomplish what I'm after.

I've just realised as I type this, a dreadful bodge would be to create two separate queries and then join them.

Any pointers, much appreciated.

Cheers, Paul.

 

[darkgen]

What do you want the query to return?

A count of Tickets created and Tickets resolved on each day. Admittedly I'm then fudging it as I then group the returns by the creation date rather than a _date_.


Thanks all for replies, I'll take a look at this again tomorrow and report back . . .

 

[darkgen]

Appreciate that Ogoshi, with a slight tweak it pulls together the data is much more accurate way.

As an aside I'm now getting into a pickle with converting a string to a datetime and just to a date.

Date format is as follows (MM/DD/YYYY HH:MM:SS AM (or PM) or M/D/YYYY HH:MM:SS AM (or PM)

CONVERT(varchar(10), LEFT(TimeCreated, 10), 103) AS Date, CONVERT(varchar(20), TimeCreated, 110) AS newdate

Returns the values as expected, but when ordered it then seems to treat the format differently and orders it in an odd numerical way.

10/1/2014
10/10/2014
10/2/2014
10/21/2014

Rather than to continue fudging my way along I altered the datasource via way of;

@{label=TimeCreated;expression={$_.TimeCreated.ToString("yyyy-MM-dd HH:mm:ss")}}

Thanks all again!