If you have excel, just create a visual representation of columns and the values you want to be in the rows (based off of your sample data) , copy the...

THanks for posting the DDL and sample data; one last thing, can you please show us the expected result out of your sample data?

I dont think we can control DST/GMT times. You will have to take the help of CLR functions to do the adjustments.

User LEFT JOIN for the tables, dont use INNER JOIN.

Can you set up the sample data in this readily usable format?

CREATE TABLE #Festival (

EventID [int] IDENTITY(1,1) NOT NULL,

[ID] [int] NULL ,

[EventType] varchar (50) NULL,

[EventTLocation] varchar (50)...

Welcome to sqlservercentral.com! I see that you are very new to this site. Please take time to read the general forum etiquittes on how to post your question to milk...

The query with and without the third join works fine for me! What is the error that you are seeing?

Yes! SUM(CASE...END) is an alternative to PIVOT. It is called as cross-tab . Read more about that in Jeff's article here http://www.sqlservercentral.com/articles/T-SQL/63681/

masonvann (4/12/2012)

---

I don't...

A much better version:

SELECT

Male = SUM( CASE WHEN Person_Gender = 'Male ' THEN 1 ELSE 0 END )

,Female...

It should be

Select [31] , [27]

from

(

SELECT occurrence_post, 1 as z

FROM tblPostOpOccurrences

) as s

PIVOT

(

SUM(z)

FOR occurrence_post IN ([31],[27])

)

as p

THis?

SELECT Male = SUM (S.Male) ,Female = SUM(s.Female) ,Averg = MAX(s.Averg)

FROM

(

SELECT

Male = CASE WHEN Person_Gender = 'Male ' THEN...

Hi Rod,

I can visualize your question a bit, but can you please post some data and expected result in consumable format ?

Like given in the link below?

http://www.sqlservercentral.com/articles/Best+Practices/61537/

With 744 visits and...

WHy dont u add the AVG statment in your inner query and use it as one of the columns in PIVOT Statement?

First we should know what according to your system is considered as a junk character. With that, we have mechanisms to weed the rows that contain one or more junk...

Something like this?

SELECT product_id

FROM <YOutable>

WHERE cat_id = 69

INTERSECT

SELECT product_id

FROM <YOutable>

WHERE cat_id = 30

Please read this artcile on multiple ways to do that

http://www.sqlservercentral.com/articles/T-SQL/88244/

The code i showed...