Different Criteria within the same View/Report

Question

Post reply

Different Criteria within the same View/Report

jwellington

Ten Centuries

Points: 1062
More actions
April 22, 2008 at 10:53 pm

#185644

Hi,
I would like to create a report or view that outlines data in a particular format.
Month FirstCount SecondCount ThirdCount
June x y z
July z w z
August......
I would like the user to input the month, and then return information for that month (and successive months) based on different criteria for each column (ie. FirstColumn).
For example, the first column may count the number of dates that fall within that month, and the second column may calculate the number of employees active in that month.
What would be the best way to do this?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

COUNT()

GROUP BY

--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)
Intro to Tally Tables and Functions

jwellington Ten Centuries Points: 1062 More actions · Answer 2

Thanks, I can use Count and Group By on one column with one set of criteria,

but I would like to have more than one column showing different values for that month (ie. each column will have a different set of criteria and use a different field to count from).

Thanks

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]Post some real life data with desired o/p.

Mahesh[/font]

MH-09-AM-8694

Jonathan Tomkins Ten Centuries Points: 1248 More actions · Answer 4

It sounds like you might want to do a few joins...

e.g. (making something random up with a cte, I'm not saying this is good ;)):

declare @t_sample table(

[id] int

,[mnth] nvarchar(50)

,[some_number] int

)

insert into @t_sample values(6, 'June', 1)

insert into @t_sample values(6, 'June', 2)

insert into @t_sample values(7, 'July', 4)

insert into @t_sample values(7, 'July', 8)

insert into @t_sample values(7, 'July', 16)

insert into @t_sample values(8, 'August', 9)

;with [e]( [id], [mnth], [some_number] )

as ( select [id], [mnth], [some_number] from @t_sample )

select

e1.[mnth] [month]

,e2.[Count] [count_for_month]

,e3.[Average] [average_for_month]

,e4.[SomeTotal] [total_so_far]

from (

select distinct id, mnth from e

) e1

left join (

select id, count(*) [Count]

from e

group by id

) e2 on e2.id = e1.id

left join (

select id, avg(some_number) [Average]

from e

group by id

) e3 on e3.id = e1.id

left join (

select ea.id, sum(eb.some_number) [SomeTotal]

from (select distinct id from e) ea, e eb

where eb.id <= ea.id

group by ea.id

) e4 on e4.id = e1.id

order by e1.id