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 Report That Shows Late Sales Orders

Hello Everyone,
I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
reports is coming from a SQL 2000 EE database. All running on Server 2003.
I've been asked to create a report that shows all sales orders that are past
thier due date. I'm having some difficulty in coming up with a way to do this.
The table I'm pulling the information from is named oe_hdr (Order Entry
Header)
In the table there is a field named req_date (required date). This field is
used by the sales staff to enter in the date that the order is due to ship
from our facility. There is also a field named complete. This field is
checked when the order is invoiced, I think.
The sales staff wants a report that shows all orders that have not shipped
by thier due date.
Based on the info I've provided does anyone have an idea how I can make this
report. Maybe an expression or something. If this is not enough info, please
let me know.
Thanks.On May 7, 11:50 am, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Hello Everyone,
> I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> reports is coming from a SQL 2000 EE database. All running on Server 2003.
> I've been asked to create a report that shows all sales orders that are past
> thier due date. I'm having some difficulty in coming up with a way to do this.
> The table I'm pulling the information from is named oe_hdr (Order Entry
> Header)
> In the table there is a field named req_date (required date). This field is
> used by the sales staff to enter in the date that the order is due to ship
> from our facility. There is also a field named complete. This field is
> checked when the order is invoiced, I think.
> The sales staff wants a report that shows all orders that have not shipped
> by thier due date.
> Based on the info I've provided does anyone have an idea how I can make this
> report. Maybe an expression or something. If this is not enough info, please
> let me know.
> Thanks.
So you want the report to ONLY show late orders and nothing else?
Put a where clause that includes this:
select *
from oe_hdr
where getdate() >= req_date and complete = ?
I'm not sure what data is put into that complete field, but whatever
indicated that is it NOT complete, then it should be put where I
placed the question marks. The script will look for any orders where
the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
req_date and the order has not completed - therefore it is overdue.
I hope this helps. A helpful tip, it might be better for your
company's privacy to make up names for tables. You never know how
people may use your information.|||Thanks Ayman,
Since writing my query, I found this in Visual Studio help;
=IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
What this does is compare the value of the importantdate with todays date
and if the importandate is greater than a day old, it will format the date
font as red, otherwise blue. This gets me closer to what I'm looking for.
I'll massage this and see what i come up with. I will also give your
suggestion a go as well.
Thanks for the privacy info.
"Ayman" wrote:
> On May 7, 11:50 am, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Hello Everyone,
> > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > I've been asked to create a report that shows all sales orders that are past
> > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > The table I'm pulling the information from is named oe_hdr (Order Entry
> > Header)
> > In the table there is a field named req_date (required date). This field is
> > used by the sales staff to enter in the date that the order is due to ship
> > from our facility. There is also a field named complete. This field is
> > checked when the order is invoiced, I think.
> >
> > The sales staff wants a report that shows all orders that have not shipped
> > by thier due date.
> >
> > Based on the info I've provided does anyone have an idea how I can make this
> > report. Maybe an expression or something. If this is not enough info, please
> > let me know.
> > Thanks.
> So you want the report to ONLY show late orders and nothing else?
> Put a where clause that includes this:
> select *
> from oe_hdr
> where getdate() >= req_date and complete = ?
> I'm not sure what data is put into that complete field, but whatever
> indicated that is it NOT complete, then it should be put where I
> placed the question marks. The script will look for any orders where
> the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> req_date and the order has not completed - therefore it is overdue.
> I hope this helps. A helpful tip, it might be better for your
> company's privacy to make up names for tables. You never know how
> people may use your information.
>|||On May 7, 12:25 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Thanks Ayman,
> Since writing my query, I found this in Visual Studio help;
> =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> What this does is compare the value of the importantdate with todays date
> and if the importandate is greater than a day old, it will format the date
> font as red, otherwise blue. This gets me closer to what I'm looking for.
> I'll massage this and see what i come up with. I will also give your
> suggestion a go as well.
> Thanks for the privacy info.
> "Ayman" wrote:
> > On May 7, 11:50 am, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Hello Everyone,
> > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > I've been asked to create a report that shows all sales orders that are past
> > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > Header)
> > > In the table there is a field named req_date (required date). This field is
> > > used by the sales staff to enter in the date that the order is due to ship
> > > from our facility. There is also a field named complete. This field is
> > > checked when the order is invoiced, I think.
> > > The sales staff wants a report that shows all orders that have not shipped
> > > by thier due date.
> > > Based on the info I've provided does anyone have an idea how I can make this
> > > report. Maybe an expression or something. If this is not enough info, please
> > > let me know.
> > > Thanks.
> > So you want the report to ONLY show late orders and nothing else?
> > Put a where clause that includes this:
> > select *
> > from oe_hdr
> > where getdate() >= req_date and complete = ?
> > I'm not sure what data is put into that complete field, but whatever
> > indicated that is it NOT complete, then it should be put where I
> > placed the question marks. The script will look for any orders where
> > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > req_date and the order has not completed - therefore it is overdue.
> > I hope this helps. A helpful tip, it might be better for your
> > company's privacy to make up names for tables. You never know how
> > people may use your information.
Thats a good way to show all the data and filter it by color. Hint:
you can put nicer colors by using their numbers like "#dedab5" in
place of red or blue. If you select the entire data row, then put
that IIF command in the background color box under properties. It will
make the entire row change color as opposed to one cell - usually
easier to see.|||Ayman,
Do you have any suggestions on how to use the Datediff in my query to pull
the reports that are a day late?
Thanks.
"Ayman" wrote:
> On May 7, 12:25 pm, Damon Johnson
> <DamonJohn...@.discussions.microsoft.com> wrote:
> > Thanks Ayman,
> > Since writing my query, I found this in Visual Studio help;
> >
> > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> >
> > What this does is compare the value of the importantdate with todays date
> > and if the importandate is greater than a day old, it will format the date
> > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > I'll massage this and see what i come up with. I will also give your
> > suggestion a go as well.
> >
> > Thanks for the privacy info.
> >
> > "Ayman" wrote:
> > > On May 7, 11:50 am, Damon Johnson
> > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > Hello Everyone,
> > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> >
> > > > I've been asked to create a report that shows all sales orders that are past
> > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> >
> > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > Header)
> > > > In the table there is a field named req_date (required date). This field is
> > > > used by the sales staff to enter in the date that the order is due to ship
> > > > from our facility. There is also a field named complete. This field is
> > > > checked when the order is invoiced, I think.
> >
> > > > The sales staff wants a report that shows all orders that have not shipped
> > > > by thier due date.
> >
> > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > report. Maybe an expression or something. If this is not enough info, please
> > > > let me know.
> > > > Thanks.
> >
> > > So you want the report to ONLY show late orders and nothing else?
> > > Put a where clause that includes this:
> > > select *
> > > from oe_hdr
> > > where getdate() >= req_date and complete = ?
> >
> > > I'm not sure what data is put into that complete field, but whatever
> > > indicated that is it NOT complete, then it should be put where I
> > > placed the question marks. The script will look for any orders where
> > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > req_date and the order has not completed - therefore it is overdue.
> >
> > > I hope this helps. A helpful tip, it might be better for your
> > > company's privacy to make up names for tables. You never know how
> > > people may use your information.
> Thats a good way to show all the data and filter it by color. Hint:
> you can put nicer colors by using their numbers like "#dedab5" in
> place of red or blue. If you select the entire data row, then put
> that IIF command in the background color box under properties. It will
> make the entire row change color as opposed to one cell - usually
> easier to see.
>|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.|||On May 7, 1:26 pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Ayman,
> Do you have any suggestions on how to use the Datediff in my query to pull
> the reports that are a day late?
> Thanks.
> "Ayman" wrote:
> > On May 7, 12:25 pm, Damon Johnson
> > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > Thanks Ayman,
> > > Since writing my query, I found this in Visual Studio help;
> > > =IIF(DateDiff("d",Fields!ImportantDate.Value, Now())>1,"Red","Blue")
> > > What this does is compare the value of the importantdate with todays date
> > > and if the importandate is greater than a day old, it will format the date
> > > font as red, otherwise blue. This gets me closer to what I'm looking for.
> > > I'll massage this and see what i come up with. I will also give your
> > > suggestion a go as well.
> > > Thanks for the privacy info.
> > > "Ayman" wrote:
> > > > On May 7, 11:50 am, Damon Johnson
> > > > <DamonJohn...@.discussions.microsoft.com> wrote:
> > > > > Hello Everyone,
> > > > > I'm running SQL Server Reporting Services on SQL2005 SE. The data for the
> > > > > reports is coming from a SQL 2000 EE database. All running on Server 2003.
> > > > > I've been asked to create a report that shows all sales orders that are past
> > > > > thier due date. I'm having some difficulty in coming up with a way to do this.
> > > > > The table I'm pulling the information from is named oe_hdr (Order Entry
> > > > > Header)
> > > > > In the table there is a field named req_date (required date). This field is
> > > > > used by the sales staff to enter in the date that the order is due to ship
> > > > > from our facility. There is also a field named complete. This field is
> > > > > checked when the order is invoiced, I think.
> > > > > The sales staff wants a report that shows all orders that have not shipped
> > > > > by thier due date.
> > > > > Based on the info I've provided does anyone have an idea how I can make this
> > > > > report. Maybe an expression or something. If this is not enough info, please
> > > > > let me know.
> > > > > Thanks.
> > > > So you want the report to ONLY show late orders and nothing else?
> > > > Put a where clause that includes this:
> > > > select *
> > > > from oe_hdr
> > > > where getdate() >= req_date and complete = ?
> > > > I'm not sure what data is put into that complete field, but whatever
> > > > indicated that is it NOT complete, then it should be put where I
> > > > placed the question marks. The script will look for any orders where
> > > > the getdate() [THIS IS TODAY'S DATE] is greater than or equal to the
> > > > req_date and the order has not completed - therefore it is overdue.
> > > > I hope this helps. A helpful tip, it might be better for your
> > > > company's privacy to make up names for tables. You never know how
> > > > people may use your information.
> > Thats a good way to show all the data and filter it by color. Hint:
> > you can put nicer colors by using their numbers like "#dedab5" in
> > place of red or blue. If you select the entire data row, then put
> > that IIF command in the background color box under properties. It will
> > make the entire row change color as opposed to one cell - usually
> > easier to see.
try using Today() instead of now since it is a date function. The
Syntax seems correct just make sure you use it in the actual report
properties section. So select the entire row (not the titles, but
where the data is on your table/matrix) and press properties. Under
background color, select EXPRESSION and put in the expression. You
can use TRANSPARENT as the color that is used for orders that are not
late as opposed to BLUE. There is also a section about visibility
under properties but it's pretty tricky and I've wasted hours on it.
Fancy colors should impress the boss : D
Let me know how it works out, I have a suggestion for a case statement
if needed.sql

HELP WITH RANKING !( please)

Hello Everybody:

Somebody know how I can create a nested ranking from a analysis services
cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
clients....b.buy only 100 articles......how many clients ...expressed in %
, buy just 200 articles parent level using a measure and then sort
a child level using the same measure... something like:

I got it nex table :
Location client_id Sales article vendor_id
city 1 122230 100 01
City1 122231 200 05
City1 122232 500 02
City1 122233 100 04
City 2 122234 100 02
City2 122235 100 02
City2 122236 200 03
City3 122237 300 01
City3 122238 400 01
City3 122239 200 03

I want show this : 40% clients . Buy 100 articles
30% clients . Buy 200 articles
10% clients .Buy 300 articles
10% clients . Buy 400 articles
10% clients . Buy 500 articles

or this : the 70 % of clients buy between 100 and 200 aticles !!

i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx

PLEASE HELP ME MADE THIS!!!!

write me too at lealy_lenn@.hotmail.com

--
hi all !

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Use this logic

declare @.t table(i int)
insert into @.t values(100)
insert into @.t values(200)
insert into @.t values(300)
insert into @.t values(100)
insert into @.t values(400)
insert into @.t values(100)
insert into @.t values(300)
insert into @.t values(600)
insert into @.t values(200)
insert into @.t values(600)
select i, s*100/Total as percentage from (
select i,count(i) as s ,(select count(i) from @.t) as total from @.t
group by i) T

Madhivanan|||Heathon via SQLMonster.com wrote:
> Hello Everybody:
> Somebody know how I can create a nested ranking from a analysis services
> cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
> clients....b.buy only 100 articles......how many clients ...expressed in %
> , buy just 200 articles parent level using a measure and then sort
> a child level using the same measure... something like:
> I got it nex table :
> Location client_id Sales article vendor_id
> city 1 122230 100 01
> City1 122231 200 05
> City1 122232 500 02
> City1 122233 100 04
> City 2 122234 100 02
> City2 122235 100 02
> City2 122236 200 03
> City3 122237 300 01
> City3 122238 400 01
> City3 122239 200 03
>
> I want show this : 40% clients . Buy 100 articles
> 30% clients . Buy 200 articles
> 10% clients .Buy 300 articles
> 10% clients . Buy 400 articles
> 10% clients . Buy 500 articles
>
> or this : the 70 % of clients buy between 100 and 200 aticles !!
> i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx
> PLEASE HELP ME MADE THIS!!!!
> write me too at lealy_lenn@.hotmail.com

You might get a better response in microsoft.public.sqlserver.olap

Simon

Help with Query

I have two tables, one with all the items (iv00101) and one with history of sales per month (iv30102). I need to make a query where it displays the item number, the item description, and the sum of the sales of the months I select.

for example:

select a.itemnmbr as Number, a.itemdesc as Description, sum(b.smrysales) as Sales
from iv00101 as a left join iv30102 as b on a.itemnmbr = b.itemnmbr
where (b.month = 12 and b.year=2003)

that gives me no problem when the item has a sales history on december/2003. But if an item was created january/2004 and I make the same query, that item doesn't appear beacuse it has no sales history on december/2003. I need it to appear with the sum(b.smrysales) as Sales = 0

any ideas?change WHERE to AND so that the conditions involving b.month and b.year are part of the ON clause

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

Help with missing data in query

Hello and thanks for your efforts,
I have a table with Part, MonthSold, ItemsSold
i need to generate a view comparing this years sales to lastyears sales and
their differences by month.
this was my first shot at it:
SELECT DATENAME(month, inv_Monthly_Sales.MonthSold) AS Month,
SUM(inv_Monthly_Sales.ItemsSold) AS ThisYear, SUM(yr2.ItemsSold) AS LastYear,
SUM(inv_Monthly_Sales.ItemsSold - yr2.ItemsSold) AS
Comparison
FROM inv_Monthly_Sales INNER JOIN
inv_Monthly_Sales AS yr2 ON inv_Monthly_Sales.Part
= yr2.Part AND MONTH(inv_Monthly_Sales.MonthSold) = MONTH(yr2.MonthSold)
WHERE (YEAR(inv_Monthly_Sales.MonthSold) = @.Yr) AND
(inv_Monthly_Sales.Part = @.Part) AND (YEAR(yr2.MonthSold) = @.Yr - 1) AND
(yr2.Part = @.Part)
GROUP BY DATENAME(month, inv_Monthly_Sales.MonthSold),
MONTH(inv_Monthly_Sales.MonthSold), MONTH(yr2.MonthSold)
ORDER BY MONTH(inv_Monthly_Sales.MonthSold)
this works great if there exists data for all 12 months of both years.
if any month is missing on any year i get back nothing.
how can i make it generate the missing columns if there is no data for that
month
i.e. during march and april no gizmos were sold so there won't be a record
any sale for that month. i need a 0 placed in that column if it didn't exist.
i tried using isnull on the sum but it didn't help
please enlighten me if you can.
On Tue, 14 Aug 2007 20:08:01 -0700, SLIMSHIM wrote:

>Hello and thanks for your efforts,
>I have a table with Part, MonthSold, ItemsSold
>i need to generate a view comparing this years sales to lastyears sales and
>their differences by month.
>this was my first shot at it:
(snip)
>this works great if there exists data for all 12 months of both years.
>if any month is missing on any year i get back nothing.
>how can i make it generate the missing columns if there is no data for that
>month
Hi slimshim,
You'll have to use a seperate table with all 12 months in it. You can
either create it on the fly as a derived table, or create a permanent
table in your DB as a one-time operation. In the query below, I presume
the latter; the query expects a table dbo.Months, with at least the two
columns MonthNo and MonthName.
SELECT m.MonthName AS MONTH,
SUM(yr.ItemsSold) AS ThisYear,
SUM(yr2.ItemsSold) AS LastYear,
SUM(yr.ItemsSold - yr2.ItemsSold) AS Comparison
FROM dbo.Months AS m
LEFT JOIN inv_Monthly_Sales AS yr
ON yr.Part = @.Part
AND YEAR(yr.MonthSold) = @.Yr
AND MONTH(yr.MonthSold) = m.MonthNo
LEFT JOIN inv_Monthly_Sales AS yr2
ON yr2.Part = @.Part
AND YEAR(yr2.MonthSold) = @.Yr - 1
AND MONTH(yr2.MonthSold) = m.MonthNo
GROUP BY m.MonthNo, m.MonthName
ORDER BY m.MonthNo;
Note: If your inv_Month_Sales table is indexed on the MonthSold column,
you should rewrite the date selection to the form MonthSold >= (first
day of month) AND MonthSold < (first day of next month). Let me know if
you need help with that.
Yet another note - the query is untested. Please see www.aspfaq.com/5006
if you prefer a tested reply, or if you want to post followup questions.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" wrote:
[vbcol=seagreen]
> On Tue, 14 Aug 2007 20:08:01 -0700, SLIMSHIM wrote:
SELECT COALESCE (m.MonthName, 'Total') AS MONTH,
ISNULL(SUM(yr.ItemsSold), 0) AS ThisYear, ISNULL(SUM(yr2.ItemsSold), 0) AS
LastYear,
SUM(ISNULL(yr.ItemsSold, 0) - ISNULL(yr2.ItemsSold,
0)) AS Comparison
FROM (SELECT 1 AS monthId, 'Jan' AS MonthName
UNION ALL
SELECT 2 AS Expr1, 'Feb' AS Expr2
UNION ALL
SELECT 3 AS Expr1, 'Mar' AS Expr2
UNION ALL
SELECT 4 AS Expr1, 'Apr' AS Expr2
UNION ALL
SELECT 5 AS Expr1, 'May' AS Expr2
UNION ALL
SELECT 6 AS Expr1, 'Jun' AS Expr2
UNION ALL
SELECT 7 AS Expr1, 'Jul' AS Expr2
UNION ALL
SELECT 8 AS Expr1, 'Aug' AS Expr2
UNION ALL
SELECT 9 AS Expr1, 'Sep' AS Expr2
UNION ALL
SELECT 10 AS Expr1, 'Oct' AS Expr2
UNION ALL
SELECT 11 AS Expr1, 'Nov' AS Expr2
UNION ALL
SELECT 12 AS Expr1, 'Dec' AS Expr2) AS m
LEFT OUTER JOIN
inv_Monthly_Sales AS yr ON yr.Part = @.Part AND
YEAR(yr.MonthSold) = @.Yr AND MONTH(yr.MonthSold) = m.monthId LEFT OUTER JOIN
inv_Monthly_Sales AS yr2 ON yr2.Part = @.Part AND
YEAR(yr2.MonthSold) = @.Yr - 1 AND MONTH(yr2.MonthSold) = m.monthId
GROUP BY m.MonthName WITH ROLLUP
ORDER BY Month
thanx in advance
> (snip)
> Hi slimshim,
> You'll have to use a seperate table with all 12 months in it. You can
> either create it on the fly as a derived table, or create a permanent
> table in your DB as a one-time operation. In the query below, I presume
> the latter; the query expects a table dbo.Months, with at least the two
> columns MonthNo and MonthName.
> SELECT m.MonthName AS MONTH,
> SUM(yr.ItemsSold) AS ThisYear,
> SUM(yr2.ItemsSold) AS LastYear,
> SUM(yr.ItemsSold - yr2.ItemsSold) AS Comparison
> FROM dbo.Months AS m
> LEFT JOIN inv_Monthly_Sales AS yr
> ON yr.Part = @.Part
> AND YEAR(yr.MonthSold) = @.Yr
> AND MONTH(yr.MonthSold) = m.MonthNo
> LEFT JOIN inv_Monthly_Sales AS yr2
> ON yr2.Part = @.Part
> AND YEAR(yr2.MonthSold) = @.Yr - 1
> AND MONTH(yr2.MonthSold) = m.MonthNo
> GROUP BY m.MonthNo, m.MonthName
> ORDER BY m.MonthNo;
> Note: If your inv_Month_Sales table is indexed on the MonthSold column,
> you should rewrite the date selection to the form MonthSold >= (first
> day of month) AND MonthSold < (first day of next month). Let me know if
> you need help with that.
> Yet another note - the query is untested. Please see www.aspfaq.com/5006
> if you prefer a tested reply, or if you want to post followup questions.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
> You did a great job . thank you.
i"m including my final code it has one bug i can't get the sort order
straight
i.e. jan feb mar ......Total
it comes in alphabeticaly
|||On Wed, 15 Aug 2007 17:02:04 -0700, SLIMSHIM wrote:

>i"m including my final code it has one bug i can't get the sort order
>straight
>i.e. jan feb mar ......Total
>it comes in alphabeticaly
Hi slimshim,
That's because you asked it to order on the month name column :-)
Change the last par tof the query to
GROUP BY m.MonthName WITH ROLLUP
ORDER BY MIN(m.MonthId)
I was first about to suggest to include MonthId in the GROUP BY, but I'm
not sure if the WITH ROLLUP option likes that. The workaround I chose is
to use an aggregate function for the ORDER BY.
If that doesn't work (you didn't follow the instructions I linked to
that would have enabled me to test before posting), then change the end
to
GROUP BY m.MonthID WITH ROLLUP
ORDER BY m.MonthID
and change the first line to read
SELECT MAX(COALESCE(m.MonthName, 'Total')) AS MONTH,
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" wrote:

> On Wed, 15 Aug 2007 17:02:04 -0700, SLIMSHIM wrote:
>
> Hi slimshim,
> That's because you asked it to order on the month name column :-)
> Change the last par tof the query to
> GROUP BY m.MonthName WITH ROLLUP
> ORDER BY MIN(m.MonthId)
> I was first about to suggest to include MonthId in the GROUP BY, but I'm
> not sure if the WITH ROLLUP option likes that. The workaround I chose is
> to use an aggregate function for the ORDER BY.
>
> If that doesn't work (you didn't follow the instructions I linked to
> that would have enabled me to test before posting), then change the end
> to
> GROUP BY m.MonthID WITH ROLLUP
> ORDER BY m.MonthID
> and change the first line to read
> SELECT MAX(COALESCE(m.MonthName, 'Total')) AS MONTH,
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
THanx for the help
I did go to that site But I couldn't figure out how to upload anything.
after i tried your suggested changed this is what my result looks like
Jan000
Total12012
Feb000
Mar000
Apr000
May000
Jun000
Jul12012
Aug000
Sep000
Oct000
Nov000
Dec000
Total falls under jan not dec as it should.
I appreciate all your help.
I was wondering if I could use COMPUTE intead of coalese to get the total
heading to the bottom ?
THanx again
SLIMSHIM
|||On Thu, 16 Aug 2007 19:38:04 -0700, SLIMSHIM wrote:

>THanx for the help
>I did go to that site But I couldn't figure out how to upload anything.
Hi slimshim,
You don't need to upload anything. The site describes the information
you need to supply to give people the best chance to help you. You just
read that site, assemble the information, then post that information in
your next question.

>after i tried your suggested changed this is what my result looks like
>Jan000
>Total12012
>Feb000
>Mar000
>Apr000
>May000
>Jun000
>Jul12012
>Aug000
>Sep000
>Oct000
>Nov000
>Dec000
I'm surpried - for a quick test on some scratch data, I got the total as
the very first line.
Try changing the ORDER BY clause to read either
GROUP BY m.MonthName WITH ROLLUP
ORDER BY GROUPING(m.MonthName), MIN(m.MonthId)
or
GROUP BY m.MonthID WITH ROLLUP
ORDER BY GROUPING(m.MonthID), m.MonthID
depending on which version of the query you are now using.

>I was wondering if I could use COMPUTE intead of coalese to get the total
>heading to the bottom ?
COMPUTE is a deprecated feature and will be removed in a future version
of SQL Server. Don't use it for new work, and replace it if you have it
in existing code.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Help with Joins

Hi All,
I'm trying to write a query that will return the number of products sold,
and the total value of each products sales.
Heres what I have so far:
SELECT COUNT(cartrows.idProduct) AS QtySold, cartrows.idProduct AS
idProduct
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct
ORDER BY cartrows.idProduct
This works Ok, but I only want to return orders where the status is either
1, 2 or 7. What I came up with is below, which returns one line per
idProduct, per OrderStatus:
SELECT TOP 100 PERCENT COUNT(cartrows.idProduct) AS QtySold,
cartrows.idProduct AS idProduct,
carthead.orderStatus
FROM cartrows INNER JOIN
carthead ON cartrows.idOrder = carthead.idOrder
GROUP BY cartrows.idProduct, carthead.orderStatus
HAVING (carthead.orderStatus IN ('1', '2', '7'))
ORDER BY cartrows.idProduct
I guess this is the result I expect from this, I'm just not sure what I need
to do so I only get one line per idProduct, with the qty sold, only from
orders with an orderStatus of 1, 2 or 7.
Any help will be much appreciated. I've include table design statements
below.
Thanks!
Simon.
CREATE TABLE [carthead] (
[idOrder] [int] IDENTITY (1, 1) NOT NULL ,
[idCust] [int] NULL ,
[orderDate] [datetime] NULL ,
[orderDateInt] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[randomKey] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[subTotal] [float] NULL ,
[taxTotal] [float] NULL ,
[shipmentTotal] [float] NULL ,
[Total] [float] NULL ,
[shipmentMethod] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[lastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[customerCompany] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[phone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[address] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[city] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[locCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[zip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[shippingName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingAddress] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingCity] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocState] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingLocCountry] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[shippingZip] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[paymentType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardType] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardNumber] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[cardExpMonth] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[cardExpYear] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardVerify] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[cardName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[generalComments] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[orderStatus] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[auditInfo] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[storeComments] [text] COLLATE Latin1_General_CI_AS NULL ,
[storeCommentsPriv] [text] COLLATE Latin1_General_CI_AS NULL ,
[adjustAmount] [float] NULL ,
[adjustReason] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[discCode] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[discPerc] [float] NULL ,
[discTotal] [float] NULL ,
[shippingPhone] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[handlingFeeTotal] [float] NULL ,
[idAffiliate] [int] NULL ,
[commPerc] [float] NULL ,
[otherFeeTotal] [float] NULL ,
[backOrder] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[idOrder]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [cartrows] (
[idCartRow] [int] IDENTITY (1, 1) NOT NULL ,
[idOrder] [int] NULL ,
[idProduct] [int] NULL ,
[sku] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[quantity] [int] NULL ,
[unitPrice] [float] NULL ,
[unitWeight] [float] NULL ,
[description] [varchar] (250) COLLATE Latin1_General_CI_AS NULL ,
[downloadCount] [int] NULL ,
[downloadDate] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[taxExempt] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[idDiscProd] [int] NULL ,
[discAmt] [float] NULL ,
PRIMARY KEY CLUSTERED
(
[idCartRow]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GODDL, great! Thanks!
Move the condition to the WHERE clause (untested, since you haven't provided
any sample data):
select count(cartrows.idProduct) as QtySold
,cartrows.idProduct as idProduct
from cartrows
inner join carthead
on cartrows.idOrder = carthead.idOrder
where (carthead.orderStatus in ('1', '2', '7'))
group by cartrows.idProduct
order by cartrows.idProduct
As I see it you want to restrict the result before the rows are grouped -
this is what WHERE does. The HAVING clause restricts results *after* the row
s
have been grouped.
ML
http://milambda.blogspot.com/|||Thank you for your reply - Especially the explanation of WHERE Vs HAVING.
Simon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0C331587-2374-4C79-A6BD-12F590E0FBC4@.microsoft.com...
> DDL, great! Thanks!
> Move the condition to the WHERE clause (untested, since you haven't
> provided
> any sample data):
> select count(cartrows.idProduct) as QtySold
> ,cartrows.idProduct as idProduct
> from cartrows
> inner join carthead
> on cartrows.idOrder = carthead.idOrder
> where (carthead.orderStatus in ('1', '2', '7'))
> group by cartrows.idProduct
> order by cartrows.idProduct
> As I see it you want to restrict the result before the rows are grouped -
> this is what WHERE does. The HAVING clause restricts results *after* the
> rows
> have been grouped.
>
> ML
> --
> http://milambda.blogspot.com/|||NP. Just remember which NG works for you. ;)
ML
http://milambda.blogspot.com/