Help with count and group by

tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc... a
nd the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--
--Without clear DDLs ( www.aspfaq.com/5006 ), it is hard to write up a clean
query.
As for a general solution, join the table, derive the age based on the date
of birth column and group by that value with aggregate function COUNT on the
SELECT list. Search the archives of this newsgroup for examples of finding
age from date of birth.
Anith|||try this:
--
this is untested as ddl is not given.
--
SELECT COUNT(DISTINCT VisitID) as nofovisits,
DATEDIFF(YEARS,BIRTHDAY,GETDATE()),COUNT
(distinct ChildID ) FROM tbVisitLog
INNER JOIN tbChild on tbVisitLog.ChildID = tbChild.ChildID
GROUP BY DATEDIFF(YEARS,BIRTHDAY,GETDATE())
--
Regards
R.D
--Knowledge gets doubled when shared
"Sonny Sablan" wrote:

> tbChild
> ChildID | ChildName | Birthday
> tbVisitLog
> VisitID | ChildID |
> I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
and the total number of visits...
> Example:
> Age | # of Children | # of Visits
> 2 300 350
> 3 500 750
> Sonny
>
> --
> --|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
I see from the names of the pseudo-code tables that (1) you have only
one child, not children (2) They have tuberculosis as shown by the
"tb-" prefix. (3) that these are not tables since they have no keys.
Do know the ISO-11179 naming standards and wehat DDL is?
The specification did not include the dates of the visits, so we cannot
determine what happens as the same child gets older and has visits at
age (n), age (n+1), then skips a year to visit at age (n+3), etc.
Also, a birthdate is a fixed date while a birthday is a month-day pair
that represents a set of event in a lifetime.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Do know the ISO-11179 naming standards and wehat DDL is?
DDL? That sounds a bit like DDT. It's probably poisonous.
Seriously, if you are actually interested in helping people in these
newsgroup, then don't use cryptic stuff like DDL, say CREATE TABLE
statements, so they know what they are talking about.
As for 11179, I would expect not very many care about it, least of all
people who have started to work with SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Sonny,
Its going to be something along these lines i think, hopefully it will give
you a start...
-- gets child ages...
select distinct dateadiff( year, birthday, getdate() )
from tblChild
-- get counts by years...
select ages.age,
count_children = ( select count(*)
from tblChild c
where dateadiff( year, c.birthday,
getdate() ) = ages.age ),
count_visits = ( select count(distinct v.VisitID)
from tblChild c
inner join tblVisitLog v on v.Child
= c.Child
where dateadiff( year, c.birthday,
getdate() ) = ages.age )
from (
select distinct age = dateadiff( year, birthday, getdate() )
from tblChild ) as ages
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:OkVJsQ41FHA.2076@.TK2MSFTNGP14.phx.gbl...
tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
and the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--|||> I see from the names of the pseudo-code tables that (1) you have only
> one child, not children (2)
A set should be singular, that is more logical in the real world. Having a
single row in a table Children makes no sense.

> They have tuberculosis as shown by the
> "tb-" prefix. (3) that these are not tables since they have no keys.
This is a practice often used in large systems, it is good to group object
names.
tb[a table]Child[of one or more Child's]
As usual your arrogance is unhelpful, this is a community of both novice and
experts, if you cannot handle that then i would suggest you consider your
contribution [or lack of it].
I notice your book targets people that are both novice and expert, that
makes sense from a commercial perspective - its a pitty you don't adopt that
pose here too; perhaps then you wouldn't have the reputation you have at the
moment which is that of an arrogant tosser.
Only you could interpret birthday in that way, if you read the post you
would understand otherwise.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130077723.236813.131320@.g49g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> I see from the names of the pseudo-code tables that (1) you have only
> one child, not children (2) They have tuberculosis as shown by the
> "tb-" prefix. (3) that these are not tables since they have no keys.
> Do know the ISO-11179 naming standards and wehat DDL is?
> The specification did not include the dates of the visits, so we cannot
> determine what happens as the same child gets older and has visits at
> age (n), age (n+1), then skips a year to visit at age (n+3), etc.
> Also, a birthdate is a fixed date while a birthday is a month-day pair
> that represents a set of event in a lifetime.
>|||This was a big help...
Thank you
--
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uH4Nul$1FHA.3524@.tk2msftngp13.phx.gbl...
> Hi Sonny,
> Its going to be something along these lines i think, hopefully it will
give
> you a start...
> -- gets child ages...
> select distinct dateadiff( year, birthday, getdate() )
> from tblChild
> -- get counts by years...
> select ages.age,
> count_children = ( select count(*)
> from tblChild c
> where dateadiff( year, c.birthday,
> getdate() ) = ages.age ),
> count_visits = ( select count(distinct v.VisitID)
> from tblChild c
> inner join tblVisitLog v on
v.Child
> = c.Child
> where dateadiff( year, c.birthday,
> getdate() ) = ages.age )
> from (
> select distinct age = dateadiff( year, birthday, getdate() )
> from tblChild ) as ages
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:OkVJsQ41FHA.2076@.TK2MSFTNGP14.phx.gbl...
> tbChild
> ChildID | ChildName | Birthday
> tbVisitLog
> VisitID | ChildID |
> I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
> and the total number of visits...
> Example:
> Age | # of Children | # of Visits
> 2 300 350
> 3 500 750
> Sonny
>
> --
> --
>|||I used this query...
SELECT TOP 100 PERCENT tbVisitLog.CenterID,
DATEDIFF(YYYY, tbChild.Birthday, GETDATE()) AS Age,
COUNT(DISTINCT tbVisitLog.ChildID) AS ChildCount,
COUNT(tbVisitLog.ChildID) AS VisitCount
FROM tbVisitLog INNER JOIN
tbChild ON tbVisitLog.ChildID = tbChild.ChildID
GROUP BY DATEDIFF(YYYY, tbChild.Birthday, GETDATE()), tbVisitLog.CenterID
Thanks for the help.
Sonny
--
--
"Sonny Sablan" <sonny@.sablan.org> wrote in message news:OkVJsQ41FHA.2076@.TK2
MSFTNGP14.phx.gbl...
tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc... a
nd the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--
--|||>> A set should be singular, that is more logical in the real world.<<
No, collective nouns -- Forest, not Trees, not Tree. Children, not
Child, not "sons and daughters", etc. A set is a collection and
should be named as such.
And I thought that the "tb-" thing was funny. I also do "vw-" means
"VolksWagen" and get a laugh out of that one.
That is common US English usage. It makes a really big difference. I
had an old Cobol system that only had the birthday, but had pruned the
year out of the birthdate to get it. It was damn useless for anything
but sending a card to someone -- even long after they were dead.
Does the UK or other dialects make them synonyms?

Help with complex query

Hi Everyone,

I need help writing the following query. I have to group my data by Department and have a field that will calculation the number of minutes that employee worked in that department. So basically I take the total number of minutes worked in the department and divide it by the total number of minutes the employee worked for the specified date range.

--

Agent Name: John Doe

Date Range: 1/1/2007 - 6/30/2007

RowID Work Minutes in Dept Total Work Minutes Dept

1 26355 52920 Service

2 9000 52920 Parts

3 17565 52920 Dispatch

Service = 26355 / 52920 = 0.499 = 50%

Parts = 9000 / 52920 = 0.17 = 17%

Dispatch= 17565 / 52920 = 0.33 = 33%

--

How can I accomplish this?

I am using SQL Server 2005 Express

Thank You

Assuming your table is the grouped sum's by department:

Code Snippet

create table #t1 (RowID int, [Work Minutes] int, [Total Work Minutes] int, Dept varchar(20) )

insert into #t1

select 1, 26355, 52920, 'Service'

union all select 2, 9000, 52920, 'Parts'

union all select 3, 17565, 52920, 'Dispatch'

select Dept, ' = ' + convert(varchar(15), [Work Minutes]) + ' / ' + convert(varchar(15), [Total Work Minutes]),

round(([Work Minutes]*100.00)/[Total Work Minutes], 0) as 'Percentage'

from #t1

|||

DaleJ,

The table data is not grouped. Thats what makes this query complex.

|||

SamCosta wrote:

DaleJ,

The table data is not grouped. Thats what makes this query complex.

Can you right click the tables that you are using and choose "Script Table As..." and "Create To.." and post those back here. Once we have your structure we can help further.|||

And some additional sample data.

It's not that difficult, but would like to get it right the first time (or two )

|||

Code Snippet

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),
Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

This query outputs:

EmpID DeptID AvgPLevel DeptWorkMI

28899 Service 2 17244

28899 Parts 3 9000

28899 Dispatch 1 27836

I need to then group the query results by EmpID to get a total Average Productivity Level

Result:

EmpID AvgPLevel

28899 2

How to calculate total productivity level:

(2 * 17244 / 54080) + (3 * 9000 / 54080) + (1 * 27836 / 54080) = 1.65 = Level 2

54080 is the total number of minutes worked in ALL departs. (17244 + 9000 + 27836 = 54080)

Thank You

|||

See if this does what you need:

Code Snippet

;WITH base

AS

(

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),

Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

),

Totals

AS

(

SELECT EmpID, SUM(DeptWorkMi) AS TotalMinutes

FROM base

GROUP BY EmpID

)

SELECT b.EmpID, ROUND(SUM(1.0 * b.AvgPLevel * b.DeptWorkMI / t.TotalMinutes), 0) as AvgPLevel

FROM base b

INNER JOIN Totals AS t

ON b.EmpID = t.EmpID

GROUP BY b.EmpID

|||

Thank you DaleJ. I was able to solve this problem using the CTE query example you provided.

help with a group by query

Hi Could somebody please help

I have the below query:
select frf.EDGE_RECURRENCE_KEY, min(td.sql_date)
from future_revenue_fact frf, EMBEDDED_EDGE_REV_ITEMS eeri, attribution_dimension ad, attribution_units_fact au, time_dimension td
where frf.ATTRIBUTION_TRANSACTION_KEY = ad.ATTRIBUTION_TRANSACTION_KEY
and ad.ATTRIBUTION_ROLE = 'Salesperson'
and ad.ATTR_UNIT_TRANSACTION_KEY = au.ATTR_UNIT_TRANSACTION_KEY
and frf.EDGE_RECURRENCE_KEY = eeri.EMBEDDED_EDGE_ID
and eeri.EMBEDDED_EDGE_VERSION_NO = 0
and frf.REVENUE_RECORD_TIME_KEY = td.TIME_KEY
and frf.REVENUE_TYPE = 'Embedded Edge'
and au.ATTRIBUTION_UNIT_NAME = 'Darren Starr'
group by frf.EDGE_RECURRENCE_KEY

This query works fine, however I need to somehow just return min(td.sql_date) in the select statement and not frf.EDGE_RECURRENCE_KEY as the min(td.sql_date) needs to feed as into another query eg:
select *
from table x
where sql_date in --> here i need to return the min(sql_date) using the first query.

Is there anyway around this, besides using a stored proc??yes, there is any easy way: remove frf.EDGE_RECURRENCE_KEY from both the SELECT and the GROUP BY (i.e. remove the GROUP BY completely)

which table is table x? are there any other tables besides table x in the outer query?|||Or, if you still want the minimum (now in the subquery) to refer to only the rows with an identical frf.EDGE_RECURRENCE_KEY, remove the GROUP BY, but add a correlated WHERE condition:
... AND frf.EDGE_RECURRENCE_KEY = corr.EDGE_RECURRENCE_KEY
where "corr" would be the table alias name for future_revenue_fact in the outer query.

help SP Code

I type Code in SP

Exec ('INSERT INTO XXX (A,B,C)

Select A,'Y8',C From YYY

GROUP BY A,B');

But it has error, Value 'ABC' not correct

Why, Thanks

William

Exce SP Code have error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Y8'.

|||

Try this:

EXEC ('INSERT INTO XXX (A,B,C) Select A,''Y8'',C From YYY GROUP BY A,B');

Chris

|||

If you want to use the single quote (‘) with in the sql string you have to use the escape sequence.

Many languages support \ as escape sequential char. But in sql server the same char need to be repeated (twice).

Example:

Select @.a = 'Sql Server''s'

So you have to change your query as follow as,

Exec (

'INSERT INTO XXX (A,B,C)

Select A,''Y8'',C From YYY

GROUP BY A,B'

);

Help setting up datasets

I have a report that needs to show three different counts in the first 3
columns. This will be on a group header line. The detail for the group
needs to show the data that is used to get the third count. My report footer
needs show a sum of the three counts.
I have two different ideas on how do accomplish this but run into problems
with each. If I use two datasets I can only reference the counts in an
aggregate when my table dataset is the details. I can not (or donâ't know
how) to set an expression to get the value from a dataset where the keys are
equal. My other solution is a single dataset with the three counts included
in each row. In the group heading I can do =First(Fields!count1,value) but I
can not sum that way because I can not perform an aggregate on an aggregate.
Anyone have any ideas on this?The "easiest" (or should I say "only") way I've found to pull off
rather complex aggregation for RS is to use a stored procedure to come
up with summaries in a temp table(s), and then run my queries against
those temp table (or tables) to give me a tabular output that is as
close as possible to what my report needs to look like.
On Mar 28, 4:53 pm, simmonsj_98 <simmons...@.discussions.microsoft.com>
wrote:
> I have a report that needs to show three different counts in the first 3
> columns. This will be on a group header line. The detail for the group
> needs to show the data that is used to get the third count. My report footer
> needs show a sum of the three counts.
> I have two different ideas on how do accomplish this but run into problems
> with each. If I use two datasets I can only reference the counts in an
> aggregate when my table dataset is the details. I can not (or don't know
> how) to set an expression to get the value from a dataset where the keys are
> equal. My other solution is a single dataset with the three counts included
> in each row. In the group heading I can do =First(Fields!count1,value) but I
> can not sum that way because I can not perform an aggregate on an aggregate.
> Anyone have any ideas on this?

help required :- Table space management in sql server 2000

Hi ,

if we have two file group in a particular sql server 2000 database (c and d drive), and in that database suppose one particular table (location c drive) is growing very fast, i want to move it to D: drive file group. so how we can do it.

Thanks

Shiva

You can create or rebuild an existing clustered index on the table specifying the other filegroup. The data and the clustered index always reside on the same filegroup so creating or rebuilding the clustered index on the other filegroup will force the data over to the other filegroup.

-Sue

Help reqd on permission & security

Hi Every body,
I have newly joined this group. I am new to DB administration.
I wanted some information as to if my Server crashes (which has) & i reinstall SQL server, will restoring master database restore all my permissions & security which was set before crash. It would be great in anybody can help me on this.
Regards,
KrishnaRestoring MASTER database (rebuilding and then restoring) would get you back with your logins, default database and language assignments, and fixed server roles. The rest is stored in user databases.