How to do Dynamic counts!

Question

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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 · Answer 2

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