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.

 

[HungryHippos]

Hmm not a total solution here by any means, but you will probably want to group by a subset of the date field, using either CAST or CONVERT for your grouping operation. For example this:

GROUP BY CAST (CreationDate AS date)

This would group your items by the short date I believe.

 

[tidusjar]

Would something like this work?

SELECT        CreationDate, COUNT(TICKETS) AS Tickets, (SELECT COUNT(*) FROM TABLE WHERE ResolvedDate IS NOT NULL) AS ResolvedCount
FROM            table
GROUP BY CreationDate
ORDER BY CreationDate

What do you want the query to return?

 

[Tom B]

Surely you just need to group by both creation date and whether it was resolved or not?

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

 

[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 . . .

 

[Ogoshi]

This might work. I don't have access to SQL server atm. The first bit builds a unique list of dates, although I'm not 100% it will contain duplicates or not. I don't think it will as union should remove them I understand.

DECLARE @Temp TABLE (Date DATETIME)
INSERT INTO @Temp
SELECT DISTINCT (CONVERT (VARCHAR, CreationDate, 103))
FROM Table
UNION
SELECT DISTINCT (CONVERT (VARCHAR, ResolvedDate, 103))
FROM Table

SELECT Date
,COUNT(TBL1.*) AS Opened
,COUNT(TBL2.*) AS Closed
FROM @Temp TMP LEFT OUTER JOIN
Table TBL1 ON TMP.Date = CONVERT (VARCHAR, TBL1.CreationDate, 103) LEFT OUTER JOIN
Table TBL2 ON TMP.Date = CONVERT (VARCHAR, TBL2.ResolvedDate, 103)

 

[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!

 

[Dolph]

Easiest way to change a date with time to just plan date for grouping is as follows.

Cast(floor(cast datevariable as float)) as datetime)

This will give you all the dates with a time of midnight, and means when you order the column the dates will be in the correct order.

It works by converting the date time to an floating point number, where the numbers before the decimal point represent the date, and the numbers after represent the time. The floor command then removes the number after the decimal without rounding, and finally you convert back to a datetime