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

Two tables:

TBL1
HID NAME
-- ---
1 C1
2 C2
3 C3
4 C4

TBL2
CID HID CStartdate CEndDate
-- -- ---- ----
1 1 01 Jan 02 31 Dec 02
2 1 01 Jan 03 31 Dec 03
3 1 01 Jan 04 31 Dec 04
4 2 01 Jan 03 31 Dec 03
5 3 01 Jan 00 31 Dec 00
6 3 01 Jan 01 31 Dec 01
7 3 01 Jan 02 31 Dec 02
8 4 01 Jan 03 31 Dec 03

I'm looking for the query that will bring back the rows with the latest enddates for each of the HIDs, i.e

HID Name CStartdate CEndDate
-- --- ---- ----
1 C1 01 Jan 04 31 Dec 04
2 C2 01 Jan 03 31 Dec 03
3 C3 01 Jan 02 31 Dec 02
4 C4 01 Jan 03 31 Dec 03

Obviously a JOIN but also somewhere a group by on HID with max(Cenddate)? I'm having no luck, would appreciate some help...

Thanks
GregSomething along these lines?

USE Northwind
GO
SELECT *
FROM Orders o
INNER JOIN [Order Details] d
ON o.OrderId = d.OrderId
WHERE EXISTS (SELECT *
FROM Orders x
GROUP BY OrderId
HAVING MAX(x.OrderDate) = o.OrderDate
AND x.OrderId = o.OrderId)
GO|||Try something like this:

select t1.HID,t1.NAME,max(t2.CStartdate), max(t2.CEndDate)
from TBL1 t1
join TBL2 t2 on t2.HID=t1.HID
group by t1.HID,t1.NAME

If CStartdate<>EndDate for record - it needs to change logic...|||I think the second query is nearly there. However, Startdate is not equal to enddate, and hypothetically for a particular HID there may be an endate entry which isn't the latest but has a startdate later than the startdate for the entry with the latest enddate. This is extremely unlikely, but I want to make sure the query is robust.

Basically the first table is a list of individual hardware items, each referenced by a single HID.

The second table is a list of support contracts for the hardware. Over a period of time there will be multiple support contracts for each item, each covering a specific period of time (startdate/enddate).

I want the query to return me information about the hardware (from the first table) with information on the latest support contract. I thought a table join querying on the Max(enddate) and grouping by HID would work, but can't quite get there.......my logic generates errors :-)

g.|||Something like this maybe?

select tbl2.HID, tbl1.Name, tbl2.CStartDate, tbl2.CEndDate
from tbl2 inner join (select HID, Max(CEndDate) as EndDate from tbl2 group by HID) as Temp1
on tbl2.HID = Temp1.HID
inner join tbl1 on tbl2.HID = tbl1.HID
where tbl2.CEndDate = Temp1.EndDate|||That looks to have done the trick.
Cheers!

G.

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