help with simple query using group by and where clause

have a table with sale_id, date, sales_person_id

i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.

How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?

How many sales person had atleast 25 sales each year 2003-2005

It is not clear how you have the date specified in the table. And also what does excluding prints mean? Is there some other column that tracks this detail? If so, you need to include the condition in the query below:

SELECT COUNT(*) as NumSalesPerson

FROM (

SELECT year(s.date) as yr, s.sales_person_id

FROM Sales AS s

WHERE s.date >= '20030101' and s.date <'20060101'

GROUP BY year(s.date)

HAVING COUNT(*) >= @.n -- can be 1 or 25 or whatever

) AS s1

help with simple query

i have a table with sale_id, date, sales_person_id
i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.is that exactly 1 sale or minimum of 1 sale per month?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1150210264.771723.205060@.u72g2000cwu.googlegroups.com...
> i have a table with sale_id, date, sales_person_id
> i need to find out the sales_person_id's who did 1 sales every month
> from jan 2003 and another query who did a sales every quarter.
>|||minimum of 1 sales per month
and minimum or 1 sale per quarter
Jack Vamvas wrote:
> is that exactly 1 sale or minimum of 1 sale per month?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "VJ" <vishal.sql@.gmail.com> wrote in message
> news:1150210264.771723.205060@.u72g2000cwu.googlegroups.com...|||Try,
declare @.sd datetime
declare @.ed datetime
set @.sd = '20030101'
set @.ed = getdate()
-- one or more sales per month
select
sales_person_id
from
(
select
convert(char(6), [date], 112) as c1,
sales_person_id
from
t1
where
[date] between @.sd and @.ed
group by
convert(char(6), [date], 112),
sales_person_id
) as t2
group by
sales_person_id
having
count(distinct c1) = (datediff(month, @.sd, @.ed) + 1)
-- one or more sales per quarter
select
sales_person_id
from
(
select
datename(year, [date]) + datename(quarter, [date]) as c1,
sales_person_id
from
t1
where
[date] between @.sd and @.ed
group by
datename(year, [date]) + datename(quarter, [date]),
sales_person_id
) as t2
group by
sales_person_id
having
count(distinct c1) = (datediff(quarter, @.sd, @.ed) + 1)
go
AMB
"VJ" wrote:

> i have a table with sale_id, date, sales_person_id
> i need to find out the sales_person_id's who did 1 sales every month
> from jan 2003 and another query who did a sales every quarter.
>|||Try this..
untested... But I guess it should work..
select sales_person_id from your_tbl
where year([date]) >= 2003 and sledate <= getdate()
group by sales_person_id
having count(distinct year([date])*100 + month([date])) =
datediff(mm,'2003-01-01',getdate())
For the quarter use datepart(qq,getdate())
Hope this helps.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||THanks
its from Jan 2003
so it means all all sales from jan 2003 till today
is ur query fine for that also
Alejandro Mesa wrote:
> Try,
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20030101'
> set @.ed = getdate()
> -- one or more sales per month
> select
> sales_person_id
> from
> (
> select
> convert(char(6), [date], 112) as c1,
> sales_person_id
> from
> t1
> where
> [date] between @.sd and @.ed
> group by
> convert(char(6), [date], 112),
> sales_person_id
> ) as t2
> group by
> sales_person_id
> having
> count(distinct c1) = (datediff(month, @.sd, @.ed) + 1)
> -- one or more sales per quarter
> select
> sales_person_id
> from
> (
> select
> datename(year, [date]) + datename(quarter, [date]) as c1,
> sales_person_id
> from
> t1
> where
> [date] between @.sd and @.ed
> group by
> datename(year, [date]) + datename(quarter, [date]),
> sales_person_id
> ) as t2
> group by
> sales_person_id
> having
> count(distinct c1) = (datediff(quarter, @.sd, @.ed) + 1)
> go
>
> AMB
>
> "VJ" wrote:
>|||small change.. sledate should be [date]
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:

> Try this..
> untested... But I guess it should work..
> select sales_person_id from your_tbl
> where year([date]) >= 2003 and sledate <= getdate()
> group by sales_person_id
> having count(distinct year([date])*100 + month([date])) =
> datediff(mm,'2003-01-01',getdate())
> For the quarter use datepart(qq,getdate())
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||HOW WILL I FIND OUT
How many sales people had atleast 100 sales each year from 2003-2005
and how many sales
VJ wrote:
> THanks
> its from Jan 2003
> so it means all all sales from jan 2003 till today
> is ur query fine for that also
> Alejandro Mesa wrote:|||Hi Alejandro ,
THANKS
I tried running your quesries in my database
i did select count(*) , sales_person_id
and I get result only with count of 42 in the count(*) for month and
count(*) of 14
for quarterly.
How do i see the total number of sales for each sales_person_id under
both conditions and also one more thing
how do i find out How many sales_person_id had atleast 25 sales each
year from 2003-2005 and what was the number of sales
Alejandro Mesa wrote:
> Try,
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20030101'
> set @.ed = getdate()
> -- one or more sales per month
> select
> sales_person_id
> from
> (
> select
> convert(char(6), [date], 112) as c1,
> sales_person_id
> from
> t1
> where
> [date] between @.sd and @.ed
> group by
> convert(char(6), [date], 112),
> sales_person_id
> ) as t2
> group by
> sales_person_id
> having
> count(distinct c1) = (datediff(month, @.sd, @.ed) + 1)
> -- one or more sales per quarter
> select
> sales_person_id
> from
> (
> select
> datename(year, [date]) + datename(quarter, [date]) as c1,
> sales_person_id
> from
> t1
> where
> [date] between @.sd and @.ed
> group by
> datename(year, [date]) + datename(quarter, [date]),
> sales_person_id
> ) as t2
> group by
> sales_person_id
> having
> count(distinct c1) = (datediff(quarter, @.sd, @.ed) + 1)
> go
>
> AMB
>
> "VJ" wrote:
>|||You can use a sub query with HAVING to get the number of sales people with a
t
least 25 sales
Use COMPUTE BY for the total number of sales for each sales condition.
Good Luck
"VJ" wrote:

> Hi Alejandro ,
> THANKS
> I tried running your quesries in my database
> i did select count(*) , sales_person_id
> and I get result only with count of 42 in the count(*) for month and
> count(*) of 14
> for quarterly.
> How do i see the total number of sales for each sales_person_id under
> both conditions and also one more thing
> how do i find out How many sales_person_id had atleast 25 sales each
> year from 2003-2005 and what was the number of sales
>
> Alejandro Mesa wrote:
>

help with query

i have a table with sale_id, date, sales_person_id
i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.
I need to find out how many sales people (count) made atleast 25 sales
each year from 2003 - 2005VJ,
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"VJ" wrote:

> i have a table with sale_id, date, sales_person_id
> i need to find out the sales_person_id's who did 1 sales every month
> from jan 2003 and another query who did a sales every quarter.
> I need to find out how many sales people (count) made atleast 25 sales
> each year from 2003 - 2005
>|||This does not have the answers to these questions, but it has what you
need to find the answers.
SELECT sales_person_id,
Months = count(distinct datediff(month, '1 Jan 2003',
sales_date),
Quarters = count(distinct datediff(quarter, '1 Jan 2003',
sales_date),
Y2003 = SUM(CASE WHEN datepart(year,sales_date) = 2003
THEN 1 ELSE 0 END),
Y2004 = SUM(CASE WHEN datepart(year,sales_date) = 2004
THEN 1 ELSE 0 END),
Y2005 = SUM(CASE WHEN datepart(year,sales_date) = 2005
THEN 1 ELSE 0 END)
FROM Sales
GROUP BY sales_person_id
Roy Harvey
Beacon Falls, CT
On 13 Jun 2006 10:59:05 -0700, "VJ" <vishal.sql@.gmail.com> wrote:

>i have a table with sale_id, date, sales_person_id
>i need to find out the sales_person_id's who did 1 sales every month
>from jan 2003 and another query who did a sales every quarter.
>I need to find out how many sales people (count) made atleast 25 sales
>each year from 2003 - 2005|||select sales_person_id, count(sale_id) 'total_sale' into #t1 from lot
where year(start) ='2003' group by artist_id having count(sale_id) >25
select sales_person_id, count(sale_id) 'total_sale' into #t2 from lot
where year(start) ='2004' group by artist_id having count(sale_id) >25
select sales_person_id, count(sale_id) 'total_sale' into #t3 from lot
where year(start) ='2005' group by sales_person_id having count(lot_id)
>25
select #t1.sales_person_id,
sum(#t1.[total_sale]+#t2.[total_sale]+#t3.[total_sale]) from #t1, #t2,
#t3
where #t1.sales_person_id = #t2.sales_person_id
and #t2.sales_person_id = #t3.sales_person_id
group by #t1.sales_person_id
Roy Harvey wrote:
> This does not have the answers to these questions, but it has what you
> need to find the answers.
> SELECT sales_person_id,
> Months = count(distinct datediff(month, '1 Jan 2003',
> sales_date),
> Quarters = count(distinct datediff(quarter, '1 Jan 2003',
> sales_date),
> Y2003 = SUM(CASE WHEN datepart(year,sales_date) = 2003
> THEN 1 ELSE 0 END),
> Y2004 = SUM(CASE WHEN datepart(year,sales_date) = 2004
> THEN 1 ELSE 0 END),
> Y2005 = SUM(CASE WHEN datepart(year,sales_date) = 2005
> THEN 1 ELSE 0 END)
> FROM Sales
> GROUP BY sales_person_id
> Roy Harvey
> Beacon Falls, CT
>
> On 13 Jun 2006 10:59:05 -0700, "VJ" <vishal.sql@.gmail.com> wrote:
>