How to do Dynamic counts!

• Nuts

  SSCrazy

  Points: 2547

  More actions

  August 15, 2010 at 5:43 pm

  #226155

  Hi Guys

  I need to report on the total registrations on weekly basis for a Diabetes project at work...

  Here is the create table statement..

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [tt](

  [Doc Name][nvarchar](20) NULL,

  [RegID] [nchar](10) NULL,

  [Start Date] [datetime] NULL

  ) ON [PRIMARY]

  Here is some sample data

  INSERT INTO [tt]

  (RegID,[Start Date],[doc name])

  SELECT '1','1 jun 2010','doc1' UNION ALL

  SELECT '2','3 jun 2010','doc2' UNION ALL

  SELECT '3','15 jun 2010','doc3' UNION ALL

  SELECT '4','17 jul 2010','doc1'

  Now I want to create a table which counts the registrations dynamically each week ...

  The result table should be something like this which has the unique count of RegID

  Doc Name Week of 1st June 2010 Week of 8th June 2010 Week of 15 june 2010

  Doc1 12 124 135

  Doc2 10 66 666

  .

  .

  .

  Can it be done is the form of stored proc where I am able to enter the data as a parameter???

  Thanks for your help

  Cheers

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  August 15, 2010 at 9:06 pm

  #1207134

  To answer your question, "Yes".

  To solve your problem, please see the following. Post back if you have questions but try the code first... you can do some amazing stuff in T-SQL. 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)
  

• jayraj0071

  SSC Journeyman

  Points: 86

  More actions

  August 24, 2010 at 12:01 pm

  #1211197

  Here is the sample query... for the above post

  select [Doc Name],

  count(case when [Start Date] between '2010-06-01 00:00:00.000' and '2010-06-07 00:00:00.000' then cast(RegID as int) end) as [Week of 1st June 2010],

  count(case when [Start Date] between '2010-06-08 00:00:00.000' and '2010-06-15 00:00:00.000' then cast(RegID as int) end) as [Week of 8th June 2010],

  count(case when [Start Date] between '2010-06-16 00:00:00.000' and '2010-06-22 00:00:00.000' then cast(RegID as int) end) as [Week of 16th June 2010],

  count(case when [Start Date] between '2010-06-23 00:00:00.000' and '2010-06-29 00:00:00.000' then cast(RegID as int) end) as [Week of 23rd June 2010]

  from tt