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:
>