CrossTab or Pivot

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  July 29, 2010 at 11:46 am

  #227352

  Hi

  I have a View: View_WTP as:

  CP CodeWeekStartDateTotal Pageviews

  510142010-06-2713939018

  510142010-07-0428825016

  510142010-07-1114976381

  510142010-07-1838481346

  530372010-06-2732939

  530372010-07-0446860

  530372010-07-1123942

  530372010-07-1847442

  Now When I run this Query:

  CREATE VIEW View_WeeklyPivot

  AS

  SELECT [CP Code],

  SUM(CASE WHEN WeekStartDate = '2010-07-04' THEN "Total Pageviews" ELSE 0 END) AS [2010-07-04],

  SUM(CASE WHEN WeekStartDate = '2010-07-11' THEN "Total Pageviews" ELSE 0 END) AS [2010-07-11],

  SUM(CASE WHEN WeekStartDate = '2010-07-18' THEN "Total Pageviews" ELSE 0 END) AS [2010-07-18],

  SUM("Total Pageviews") AS TotalPageviews

  FROM View_WTP

  GROUP BY [CP Code]

  GO

  It Gives Me the following result:

  CP Code2010-06-272010-07-042010-07-112010-07-18TotalPageviews

  510141393901828825016149763813848134696221761

  5303732939 46860 23942 47442151183

  Now If I Add New data into the table for next week say for 2010-07-25. I would have to alter the query and add new line "SUM(CASE WHEN WeekStartDate = '2010-07-25' THEN "Total Pageviews" ELSE 0 END) AS [2010-07-25]" to display this week as a column in View_WeeklyPivot.

  I want to write a query so that whenever I add data for new weeks, it should automatically recogonise the new date(week) and pivot that into new column i.e without altering the query.

  How can I do That?

  Thanks

• divyanth

  SSCarpal Tunnel

  Points: 4303

  More actions

  July 29, 2010 at 1:47 pm

  #1200030

  It has to be done dynamically.. Let me ask you this 1st. how many weeks data do you want to display.. is it monthly or specific number of weeks from today..depending on that you need to declare variables and then use those variables as part of the dynamic SQL statement..

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  July 29, 2010 at 2:15 pm

  #1200044

  Hi

  I want to define a date range so that the query automatically picks up weeks from the table and convert then into columns. And for total number of weeks, it will depend on the date range. I already have a query to display specific weeks but as I told you, I have to update that query for every new week.

  I was lookin into this query to define a date range:

  DECLARE @StartDate DATETIME

  DECLARE @EndDate DATETIME

  SET @StartDate = '2008-06-29'

  SET @EndDate = '2008-01-15'

  DECLARE @SwapDate DATETIME

  SELECT @SwapDate = @EndDate,

  @EndDate = @StartDate,

  @StartDate = @SwapDate

  WHERE @EndDate < @StartDate

  SELECT @StartDate = DATEADD(mm,DATEDIFF(mm,0,@StartDate),0),

  @EndDate = DATEADD(mm,DATEDIFF(mm,0,@EndDate)+1,0)

  SELECT @StartDate,@EndDate

  But I'm not sure how to use it with crosstab query to get the required results.

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  July 29, 2010 at 11:04 pm

  #1200122

  You need some tools for that. Fortunately, you can find them all in one place. Please see the following link...

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 2, 2010 at 4:18 pm

  #1201294

  Hi

  Below is My Table:

  CREATE TABLE DSA

  (

  [CP Code] char(5),

  [Date] datetime,

  [Total Pageviews] int

  )

  INSERT INTO DSA

  SELECT '51014','Jun 27 2010 12:00AM','2623221', UNION ALL

  SELECT '51014','Jun 28 2010 12:00AM','1985718', UNION ALL

  SELECT '51014','Jun 29 2010 12:00AM','1815712', UNION ALL

  SELECT '51014','Jun 30 2010 12:00AM','2126104', UNION ALL

  SELECT '51014','Jul 1 2010 12:00AM','1702672', UNION ALL

  SELECT '51014','Jul 2 2010 12:00AM','1666468', UNION ALL

  SELECT '51014','Jul 3 2010 12:00AM','2019123', UNION ALL

  SELECT '51014','Jul 4 2010 12:00AM','1783373', UNION ALL

  SELECT '51014','Jul 5 2010 12:00AM','1890672', UNION ALL

  SELECT '51014','Jul 6 2010 12:00AM','2424314', UNION ALL

  SELECT '51014','Jul 7 2010 12:00AM','2197743', UNION ALL

  SELECT '51014','Jul 8 2010 12:00AM','2097884', UNION ALL

  SELECT '51014','Jul 9 2010 12:00AM','2029226', UNION ALL

  SELECT '51014','Jul 10 2010 12:00AM','1989296', UNION ALL

  SELECT '51014','Jul 11 2010 12:00AM','1833109', UNION ALL

  SELECT '51014','Jul 12 2010 12:00AM','2398424', UNION ALL

  SELECT '51014','Jul 13 2010 12:00AM','2365550', UNION ALL

  SELECT '51014','Jul 14 2010 12:00AM','1810926', UNION ALL

  SELECT '51014','Jul 15 2010 12:00AM','2483211', UNION ALL

  SELECT '51014','Jul 16 2010 12:00AM','2327007', UNION ALL

  SELECT '51014','Jul 17 2010 12:00AM','1758154', UNION ALL

  SELECT '51014','Jul 18 2010 12:00AM','2517437', UNION ALL

  SELECT '51014','Jul 19 2010 12:00AM','2019041', UNION ALL

  SELECT '51014','Jul 20 2010 12:00AM','2097835', UNION ALL

  SELECT '51014','Jul 21 2010 12:00AM','2471075', UNION ALL

  SELECT '51014','Jul 22 2010 12:00AM','3644609', UNION ALL

  SELECT '51014','Jul 23 2010 12:00AM','2773555', UNION ALL

  SELECT '51014','Jul 24 2010 12:00AM','3717121', UNION ALL

  SELECT '53037','Jun 27 2010 12:00AM','6059', UNION ALL

  SELECT '53037','Jun 28 2010 12:00AM','6310', UNION ALL

  SELECT '53037','Jun 29 2010 12:00AM','6394', UNION ALL

  SELECT '53037','Jun 30 2010 12:00AM','4627', UNION ALL

  SELECT '53037','Jul 1 2010 12:00AM','3402', UNION ALL

  SELECT '53037','Jul 2 2010 12:00AM','3115', UNION ALL

  SELECT '53037','Jul 3 2010 12:00AM','3032', UNION ALL

  SELECT '53037','Jul 4 2010 12:00AM','2995', UNION ALL

  SELECT '53037','Jul 5 2010 12:00AM','3122', UNION ALL

  SELECT '53037','Jul 7 2010 12:00AM','3040', UNION ALL

  SELECT '53037','Jul 8 2010 12:00AM','3638', UNION ALL

  SELECT '53037','Jul 9 2010 12:00AM','3767', UNION ALL

  SELECT '53037','Jul 10 2010 12:00AM','3745', UNION ALL

  SELECT '53037','Jul 11 2010 12:00AM','3576', UNION ALL

  SELECT '53037','Jul 12 2010 12:00AM','3860', UNION ALL

  SELECT '53037','Jul 13 2010 12:00AM','3123', UNION ALL

  SELECT '53037','Jul 14 2010 12:00AM','3458', UNION ALL

  SELECT '53037','Jul 15 2010 12:00AM','3516', UNION ALL

  SELECT '53037','Jul 16 2010 12:00AM','3233', UNION ALL

  SELECT '53037','Jul 17 2010 12:00AM','3176', UNION ALL

  SELECT '53037','Jul 18 2010 12:00AM','3250', UNION ALL

  SELECT '53037','Jul 19 2010 12:00AM','3342', UNION ALL

  SELECT '53037','Jul 20 2010 12:00AM','3157', UNION ALL

  SELECT '53037','Jul 21 2010 12:00AM','3133', UNION ALL

  SELECT '53037','Jul 22 2010 12:00AM','3970', UNION ALL

  SELECT '53037','Jul 23 2010 12:00AM','3611', UNION ALL

  SELECT '53037','Jul 24 2010 12:00AM','3258', UNION ALL

  SELECT '60199','Jun 27 2010 12:00AM','1320009', UNION ALL

  SELECT '60199','Jun 28 2010 12:00AM','780962', UNION ALL

  SELECT '60199','Jun 29 2010 12:00AM','949653', UNION ALL

  SELECT '60199','Jun 30 2010 12:00AM','1086996', UNION ALL

  SELECT '60199','Jul 1 2010 12:00AM','1534519', UNION ALL

  SELECT '60199','Jul 2 2010 12:00AM','1123436', UNION ALL

  SELECT '60199','Jul 3 2010 12:00AM','937690', UNION ALL

  SELECT '60199','Jul 4 2010 12:00AM','693642', UNION ALL

  SELECT '60199','Jul 5 2010 12:00AM','1025405', UNION ALL

  SELECT '60199','Jul 6 2010 12:00AM','396889', UNION ALL

  SELECT '60199','Jul 7 2010 12:00AM','449148', UNION ALL

  SELECT '60199','Jul 8 2010 12:00AM','389064', UNION ALL

  SELECT '60199','Jul 9 2010 12:00AM','573831', UNION ALL

  SELECT '60199','Jul 10 2010 12:00AM','1155328', UNION ALL

  SELECT '60199','Jul 11 2010 12:00AM','1104293', UNION ALL

  SELECT '60199','Jul 12 2010 12:00AM','780170', UNION ALL

  SELECT '60199','Jul 13 2010 12:00AM','771110', UNION ALL

  SELECT '60199','Jul 14 2010 12:00AM','719243', UNION ALL

  SELECT '60199','Jul 15 2010 12:00AM','767354', UNION ALL

  SELECT '60199','Jul 16 2010 12:00AM','837526', UNION ALL

  SELECT '60199','Jul 17 2010 12:00AM','1419438', UNION ALL

  SELECT '60199','Jul 18 2010 12:00AM','1395861', UNION ALL

  SELECT '60199','Jul 19 2010 12:00AM','786528', UNION ALL

  SELECT '60199','Jul 20 2010 12:00AM','836900', UNION ALL

  SELECT '60199','Jul 21 2010 12:00AM','641297', UNION ALL

  SELECT '60199','Jul 22 2010 12:00AM','628898', UNION ALL

  SELECT '60199','Jul 23 2010 12:00AM','572505', UNION ALL

  SELECT '60199','Jul 24 2010 12:00AM','1389859', UNION ALL

  SELECT '60200','Jun 27 2010 12:00AM','462027', UNION ALL

  SELECT '60200','Jun 28 2010 12:00AM','512267', UNION ALL

  SELECT '60200','Jun 29 2010 12:00AM','740734', UNION ALL

  SELECT '60200','Jun 30 2010 12:00AM','713378', UNION ALL

  SELECT '60200','Jul 1 2010 12:00AM','620728', UNION ALL

  SELECT '60200','Jul 2 2010 12:00AM','720289', UNION ALL

  SELECT '60200','Jul 3 2010 12:00AM','337603', UNION ALL

  SELECT '60200','Jul 4 2010 12:00AM','396845', UNION ALL

  SELECT '60200','Jul 5 2010 12:00AM','281479', UNION ALL

  SELECT '60200','Jul 6 2010 12:00AM','220685', UNION ALL

  SELECT '60200','Jul 7 2010 12:00AM','265962', UNION ALL

  SELECT '60200','Jul 8 2010 12:00AM','361803', UNION ALL

  SELECT '60200','Jul 9 2010 12:00AM','490857', UNION ALL

  SELECT '60200','Jul 10 2010 12:00AM','812089', UNION ALL

  SELECT '60200','Jul 11 2010 12:00AM','624099', UNION ALL

  SELECT '60200','Jul 12 2010 12:00AM','477345', UNION ALL

  SELECT '60200','Jul 13 2010 12:00AM','463716', UNION ALL

  SELECT '60200','Jul 14 2010 12:00AM','540742', UNION ALL

  SELECT '60200','Jul 15 2010 12:00AM','773325', UNION ALL

  SELECT '60200','Jul 16 2010 12:00AM','466823', UNION ALL

  SELECT '60200','Jul 17 2010 12:00AM','488246', UNION ALL

  SELECT '60200','Jul 18 2010 12:00AM','770761', UNION ALL

  SELECT '60200','Jul 19 2010 12:00AM','351239', UNION ALL

  SELECT '60200','Jul 20 2010 12:00AM','293287', UNION ALL

  SELECT '60200','Jul 21 2010 12:00AM','460205', UNION ALL

  SELECT '60200','Jul 22 2010 12:00AM','499275', UNION ALL

  SELECT '60200','Jul 23 2010 12:00AM','443317', UNION ALL

  SELECT '60200','Jul 24 2010 12:00AM','435663', UNION ALL

  SELECT '60201','Jun 27 2010 12:00AM','463378', UNION ALL

  SELECT '60201','Jun 28 2010 12:00AM','591326', UNION ALL

  SELECT '60201','Jun 29 2010 12:00AM','418724', UNION ALL

  SELECT '60201','Jun 30 2010 12:00AM','555522', UNION ALL

  SELECT '60201','Jul 1 2010 12:00AM','458153', UNION ALL

  SELECT '60201','Jul 2 2010 12:00AM','391728', UNION ALL

  SELECT '60201','Jul 3 2010 12:00AM','225859', UNION ALL

  SELECT '60201','Jul 4 2010 12:00AM','428696', UNION ALL

  SELECT '60201','Jul 5 2010 12:00AM','382274', UNION ALL

  SELECT '60201','Jul 6 2010 12:00AM','417103', UNION ALL

  SELECT '60201','Jul 7 2010 12:00AM','348050', UNION ALL

  SELECT '60201','Jul 8 2010 12:00AM','559362', UNION ALL

  SELECT '60201','Jul 9 2010 12:00AM','501761', UNION ALL

  SELECT '60201','Jul 10 2010 12:00AM','846562', UNION ALL

  SELECT '60201','Jul 11 2010 12:00AM','571405', UNION ALL

  SELECT '60201','Jul 12 2010 12:00AM','521841', UNION ALL

  SELECT '60201','Jul 13 2010 12:00AM','697591', UNION ALL

  SELECT '60201','Jul 14 2010 12:00AM','467645', UNION ALL

  SELECT '60201','Jul 15 2010 12:00AM','465051', UNION ALL

  SELECT '60201','Jul 16 2010 12:00AM','614360', UNION ALL

  SELECT '60201','Jul 17 2010 12:00AM','315732', UNION ALL

  SELECT '60201','Jul 18 2010 12:00AM','369787', UNION ALL

  SELECT '60201','Jul 19 2010 12:00AM','612215', UNION ALL

  SELECT '60201','Jul 20 2010 12:00AM','565229', UNION ALL

  SELECT '60201','Jul 21 2010 12:00AM','432130', UNION ALL

  SELECT '60201','Jul 22 2010 12:00AM','482936', UNION ALL

  SELECT '60201','Jul 23 2010 12:00AM','342344', UNION ALL

  SELECT '60201','Jul 24 2010 12:00AM','359864', UNION ALL

  I wanted to convert this data into weekly data. For that, I used the query below and generated a VIEW(View_WTP).

  Query:

  CREATE VIEW View_WTP

  AS

  WITH CTE AS

  (

  select [CP Code],

  StartDate = DateAdd(day, -DatePart(weekday, [Date])+1, [Date]),

  [TotalPageviews]

  from DSA

  )

  SELECT [CP Code],

  SUM(CASE WHEN StartDate = '2010-06-27' THEN TotalPageviews ELSE 0 END) AS [2010-06-27],

  SUM(CASE WHEN StartDate = '2010-07-04' THEN TotalPageviews ELSE 0 END) AS [2010-07-04],

  SUM(CASE WHEN StartDate = '2010-07-11' THEN TotalPageviews ELSE 0 END) AS [2010-07-11],

  SUM(CASE WHEN StartDate = '2010-07-18' THEN TotalPageviews ELSE 0 END) AS [2010-07-18],

  SUM(TotalPageviews)AS TotalPageviews

  FROM CTE

  GROUP BY [CP Code]

  GO

  SELECT * FROM View_WTP

  ORDER BY [CP Code]

  GO

  Now in the above query, I had to fill in the date by myself and I want to write a query so that it automatically recogonise the date, convert it into weekly basis and pivot the weekly dates as columns so that I dont have to manually alter the query everytime. For This, I was trying to edit the below mentioned query. Obviously its not giving me the required result.

  Query

  --- Find the Endpoints of the Date Range

  DECLARE @StartDate DATETIME

  DECLARE @EndDate DATETIME

  SET @StartDate = '2010-01-10'

  SET @EndDate = '2010-12-11'

  DECLARE @SwapDate DATETIME

  SELECT @SwapDate = @EndDate,

  @EndDate = @StartDate,

  @StartDate = @SwapDate

  WHERE @EndDate < @StartDate

  SELECT @StartDate = DATEADD(W,DATEDIFF(W,0,@StartDate),0),

  @EndDate = DATEADD(W,DATEDIFF(W,0,@EndDate)+1,0)

  --===== Declare the variables that will contain the dynamic SQL

  DECLARE @SQL1 NVARCHAR(4000),

  @SQL2 NVARCHAR(4000),

  @SQL3 NVARCHAR(4000)

  --===== Create the "static" section of the code

  SELECT @SQL1 = 'SELECT CASE WHEN GROUPING([CP Code]) = 1 THEN ''Total'' ELSE [CP Code] END AS [CP Code],' +CHAR(10)

  --===== Create the "Mostly Static" section of the code

  SELECT @SQL3 =

  ' SUM(TotalPageviews) AS TotalPageviews

  FROM

  (

  SELECT DATEADD(W,DATEDIFF(W,0,Date),0) AS WeekDate,

  [CP Code],

  SUM(TotalPageviews) AS TotalPageviews

  FROM View_DSA

  WHERE Date >= ' + QUOTENAME(@StartDate,'''') + '

  AND Date < ' + QUOTENAME(@EndDate,'''') + '

  GROUP BY DATEADD(W,DATEDIFF(W,0,Date),0), [CP Code]

  ) d

  GROUP BY [CP Code] WITH ROLLUP

  '

  --===== The "Hard" Part - Concatenation to Form the Date Columns

  SELECT @SQL2 = COALESCE(@SQL2,'')

  + ' SUM(CASE WHEN WeekDate = ' + QUOTENAME(d.WeekName,'''')

  + ' THEN Total ELSE 0 END) AS [' + d.WeekName + '],' + CHAR(10)

  FROM (--==== Produces the list of MonthNames in the mmm yyyy format

  SELECT N,

  STUFF(CONVERT(CHAR(11),DATEADD(W, N-1, @StartDate),100),4,3,'') AS WeekName

  FROM Master.dbo.Tally WHERE N <= DATEDIFF(W,@StartDate,@EndDate)

  ) d

  ORDER BY d.N

  PRINT @SQL1 + @SQL2 + @SQL3

  GO

  Can you tell me whats all wrong with this query and how can I use it to get the results?

  Thanks

• sturner

  SSC-Insane

  Points: 22411

  More actions

  August 5, 2010 at 12:08 pm

  #1203026

  I think that if you changed this:

  SELECT [CP Code],

  SUM(CASE WHEN StartDate = '2010-06-27' THEN TotalPageviews ELSE 0 END) AS [2010-06-27],

  SUM(CASE WHEN StartDate = '2010-07-04' THEN TotalPageviews ELSE 0 END) AS [2010-07-04],

  SUM(CASE WHEN StartDate = '2010-07-11' THEN TotalPageviews ELSE 0 END) AS [2010-07-11],

  SUM(CASE WHEN StartDate = '2010-07-18' THEN TotalPageviews ELSE 0 END) AS [2010-07-18],

  SUM(TotalPageviews)AS TotalPageviews

  to this, it might work for you:

  declare @sql varchar(6000)

  declare @today datetime

  set @today = convert(char(10),getdate(),101)

  set @sql = 'SELECT [CP Code],'

  set @sql = @sql + 'SUM(CASE WHEN StartDate = '''+convert(char(10),dateadd(week,-4,@today),101)+''' THEN TotalPageviews ELSE 0 END) AS ['+convert(char(10),dateadd(week,-4,@today),101)+'],'

  set @sql = @sql + 'SUM(CASE WHEN StartDate = '''+convert(char(10),dateadd(week,-3,@today),101)+''' THEN TotalPageviews ELSE 0 END) AS ['+convert(char(10),dateadd(week,-3,@today),101)+'],'

  set @sql = @sql + 'SUM(CASE WHEN StartDate = '''+convert(char(10),dateadd(week,-2,@today),101)+''' THEN TotalPageviews ELSE 0 END) AS ['+convert(char(10),dateadd(week,-2,@today),101)+'],'

  set @sql = @sql + 'SUM(CASE WHEN StartDate = '''+convert(char(10),dateadd(week,-1,@today),101)+''' THEN TotalPageviews ELSE 0 END) AS ['+convert(char(10),dateadd(week,-1,@today),101)+'],'

  set @sql = @sql + 'SUM(TotalPageviews)AS TotalPageviews'

  set @sql = @sql + ' FROM CTE GROUP BY [CP Code]'

  exec(@sql)

  The above won't know anything about your CTE so you may have to work from a temp table or an actual view perhaps.

  The probability of survival is inversely proportional to the angle of arrival.

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 5, 2010 at 12:40 pm

  #1203051

  Hi

  I don't want to write a query for every week. I want that the query should recogonise any week like say within a year(or the date range specified).

  I just want to modify my query with something like below where I have specified the date range.

  DECLARE @StartDate DATETIME

  DECLARE @EndDate DATETIME

  SET @StartDate = '2010-01-10'

  SET @EndDate = '2010-12-11'

  DECLARE @SwapDate DATETIME

  SELECT @SwapDate = @EndDate,

  @EndDate = @StartDate,

  @StartDate = @SwapDate

  WHERE @EndDate < @StartDate

  SELECT @StartDate = DATEADD(W,DATEDIFF(W,0,@StartDate),0),

  @EndDate = DATEADD(W,DATEDIFF(W,0,@EndDate)+1,0)

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  August 5, 2010 at 7:34 pm

  #1203214

  Novicejatt (8/5/2010)

  ---

  Hi

  I don't want to write a query for every week. I want that the query should recogonise any week like say within a year(or the date range specified).

  I just want to modify my query with something like below where I have specified the date range.

  DECLARE @StartDate DATETIME

  DECLARE @EndDate DATETIME

  SET @StartDate = '2010-01-10'

  SET @EndDate = '2010-12-11'

  DECLARE @SwapDate DATETIME

  SELECT @SwapDate = @EndDate,

  @EndDate = @StartDate,

  @StartDate = @SwapDate

  WHERE @EndDate < @StartDate

  SELECT @StartDate = DATEADD(W,DATEDIFF(W,0,@StartDate),0),

  @EndDate = DATEADD(W,DATEDIFF(W,0,@EndDate)+1,0)

  Thanks

  Did you read the article at the link I posted or not?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 6, 2010 at 11:22 am

  #1203699

  Hi

  Yes I did. But I don't have so much experience in SQL. Also I'm working in weeks. I tried to modify the query but could not get it to work.

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  August 9, 2010 at 9:31 pm

  #1204483

  Novicejatt (8/6/2010)

  ---

  Hi

  Yes I did. But I don't have so much experience in SQL. Also I'm working in weeks. I tried to modify the query but could not get it to work.

  Thanks

  My apologies. I lost track of this thread. Have you figured it out or do you still need help on this?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 10, 2010 at 11:46 am

  #1204936

  Hi

  Yes, I still need help with this.

  Thanks

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 12, 2010 at 2:09 pm

  #1206316

  Hi

  Any Help on this Topic Guys.?

  Thanks

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 12, 2010 at 5:58 pm

  #1206368

  Hi

  I Modified the Query as :

  DECLARE @StartDate DATETIME

  DECLARE @EndDate DATETIME

  SET @StartDate = '2010-06-27'

  SET @EndDate = '2011-01-03'

  DECLARE @SwapDate DATETIME

  SELECT @SwapDate = @EndDate,

  @EndDate = @StartDate,

  @StartDate = @SwapDate

  WHERE @EndDate < @StartDate

  SELECT @StartDate = DATEADD(W,DATEDIFF(W,0,@StartDate),0),

  @EndDate = DATEADD(W,DATEDIFF(W,0,@EndDate)+1,0)

  DECLARE @SQL1 NVARCHAR(4000),

  @SQL2 NVARCHAR(4000),

  @SQL3 NVARCHAR(4000)

  SELECT @SQL1 = 'SELECT CASE WHEN GROUPING([CP Code]) = 1 THEN TotalPageviews ELSE TotalPageviews END AS TotalPageviews,'+CHAR(10)

  SELECT @SQL3 =

  ' SUM(TotalPageviews) AS TotalPageviews

  FROM (

  SELECT DATEADD(WW,DATEDIFF(WW,0,Date),0)+5 AS WeekEndDate,

  [CP Code],

  SUM(TotalPageviews) AS TotalPageviews

  FROM View_DSA

  WHERE Date >= ' + QUOTENAME(@StartDate,'''') + '

  AND Date < ' + QUOTENAME(@EndDate,'''') + '

  GROUP BY DATEADD(WW,DATEDIFF(WW,0,Date),0), [CP Code]

  ) d

  GROUP BY [CP Code], TotalPageviews

  '

  SELECT @SQL2 = COALESCE(@SQL2,'')

  + ' SUM(CASE WHEN WeekEndDate = ' + QUOTENAME(d.WeekEndDate,'''')

  + ' THEN TotalPageviews ELSE 0 END) AS [' + d.WeekEndDate + '],' + CHAR(10)

  FROM (

  SELECT N,

  STUFF(CONVERT(CHAR(20),DATEADD(WW, N-1, '20100627'),101),1,0,'') AS WeekEndDate

  FROM Tally

  WHERE N <= DATEDIFF(WW,@StartDate,@EndDate)

  ) d

  ORDER BY d.N

  EXEC (@SQL1 + @SQL2 + @SQL3)

  GO

  Its giving me two similar Total Pageviews Columns and other two date columns without any data(Sorry for the format of the Data)

  TotalPageviews06/27/2010 07/04/2010 TotalPageviews

  81000810

  2289002289

  8475008475

  9475009475

  203070020307

  266290026629

  365220036522

  377970037797

  415230041523

  665840066584

  691210069121

  996080099608

  12606500126065

  12786500127865

  14256200142562

  14538500145385

  15524700155247

  16691300166913

  22029000220290

  22083900220839

  28902300289023

  39722300397223

  40356400403564

  40767300407673

  44701500447015

  50509600505096

  50930100509301

  59770700597707

  60683000606830

  64195300641953

  64845500648455

  64942100649421

  70179100701791

  80145400801454

  84046200840462

  91664100916641

  1030679001030679

  1170370001170370

  1554981001554981

  1738326001738326

  1759899001759899

  1879503001879503

  2469619002469619

  2588903002588903

  2625093002625093

  2829720002829720

  3013105003013105

  3104690003104690

  3266016003266016

  3365899003365899

  3483808003483808

  4107026004107026

  4683307004683307

  5713284005713284

  6579837006579837

  7733265007733265

  .

  .

  .

  .

  .

  Can you guys tell me whats wrong with the query?

  Thanks

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  August 13, 2010 at 1:30 pm

  #1206905

  Hi

  Solved.

  Thanks.