dwain.c (4/19/2012)

---

Drew, you said:

After that, you should ship it off to your reporting tool to do rest. You are going to have to pivot the results to get your counts...

I realize it was just an example, but if there were a 2008-specific feature that was required for the job,...

It looks like the first line is a grand total for Civil.

Part of the problem is that you're using T-SQL as a reporting tool and it's not. You'd be...

GilaMonster (4/18/2012)

---

How do you know they're inconsequential for the particular job?...

dwain.c (4/18/2012)

---

However, this only works when the period is specifically 8 days long. If you need that period to vary, you're only recourse is to use dynamic SQL and...

It would help if you formatted your code using the IFCode Shortcuts code="sql" so that we could more easily read your code.

select empname ,(

select case when empno is null...

You'd probably get more responses if you posted in the correct forum. There is a forum specifically for SQL Server 2008 Administration.

Drew

I should issue a caveat on that code. It is NOT necessarily equivalent to the original query. They will produce the same results if the data includes each...

As a note of comparison, ColdCoffee's approach requires four scans of the table and requires a total of 32 logical reads, whereas my queries only require one logical read per...

Here's an improved version of my original query. It only scans the table once instead of twice.

SELECT u.SourceA, u.SourceB, t.Counts

FROM #Temp1 AS t

CROSS APPLY(

SELECT SourceA, SourceB

UNION

SELECT SourceB, SourceA

) AS...

That format has a leading zero on both the day and the month (05/04/2012). The expected results given drops the leading zero on the day,...

You're looking for a UNION, although you're duplicating a lot of your data by doing so. I'm not sure why you would want to do this.

SELECT SourceA, SourceB, Counts

FROM...

Well, I think the point is that the format drops the leading zero on days, but not months, so...

Crystal Reports has a crosstab object that was specifically designed for this type of operation. It's MUCH, MUCH easier to do this Crystal than in T-SQL.

Drew

Here are two different ways to do it.

DECLARE @dateString DATETIME

SET @dateString = '2012-04-05'

SELECT @dateString,

CASE WHEN DATEPART(DAY, @dateString) < 10

THEN STUFF(CONVERT(varchar(10), @dateString, 103), 1, 1, '')

ELSE CONVERT(varchar(10), @datestring, 103)

END

,RIGHT(CONVERT(varchar(10), @dateString,...