SP Crosstab-Sub Select Solution?

• blinton25

  SSC Enthusiast

  Points: 113

  More actions

  July 17, 2003 at 9:44 am

  #82729

  Hello,

  How are you today?

  I have 2 tables:

  Country_Codes

  Which contains:

  CODE-Code Name for a country e.g U.S for United States

  DESCRT-Name of the country e.g United States of America

  REGION-Region identifier, e.g Americas, Europe, Asia

  and

  Book1

  Which contains:

  COUNTRY-Country they are from

  Arrival Date-Date person arrives

  I run the following crosstab which allows me to specify a country and obtain the total number of people coming from that country

  @Country nvarchar (255) = NULL,

  @ArrivalDateFrom DATETIME,

  @ArrivalDateTo DATETIME,

  AS SELECT

  a.DESCRT,

  COUNT(*) AS 'TouristCount'

  FROM

  Country_Codes AS a INNER JOIN Book1 AS b ON a.Code = b.Country

  where (1=1)

  AND(b.Country = @Country or @Country = 'ALL')

  and b.Arrival_Date BETWEEN ISNULL(@ArrivalDateFrom, b.Arrival_Date) AND ISNULL(@ArrivalDateTo, b.Arrival_Date)

  GROUP BY a.DESCRT

  GO

  However I now want to modify to allow a user to specify and obtain totals for a region, the issue being that the region is not present in book1, although the country which belongs to the region is. Any suggestions on how I should approach this?

  Thanks

• blinton25

  SSC Enthusiast

  Points: 113

  More actions

  July 17, 2003 at 7:56 pm

  #465941

  Hello,

  Got it done:

  SELECT

  a.Region Region,

  COUNT(*) AS 'TouristCount'

  FROM

  Country_Codes AS a INNER JOIN Book1 AS b ON a.Code = b.Country

  where region = 'canada'

  GROUP BY a.Region