Computed column question

I have a SQL table that maintains a field on the status of a report being completed.

I have in the record the date the report is due (DateDue)

I also have a field called DaysLate which I have set to be a calculated field with formula:

DATEDIFF(dd, DateDue, GETDATE())

Thsi works but when the report is *not* late I'd like this to be null is there I way I can do this conditional calculation in a calculated field?

Regards

Cvive

CASE WHEN {Your formula}<0 THEN NULL ELSE {Your formula} END

|||

Many thanks for that - that did the job perfectly.

Clive

computed column

Hello,
I've a problem with inserting a record through a form in Access. The
recordsource is a view from sql server.
The view has a computed column. The problem occurs when there is an
insert/update on that view. The errors indicates that arithabort needs
to be set on, etc.
In my ado-connection i can fix that, but how do i do it when the view is
linked through odbc? Maybe a 'instead of trigger'would do it, but i
don't know how.Jason
Lookup SET ARITHABORT command in the BOL along with good examples
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u09uNxe5FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I've a problem with inserting a record through a form in Access. The
> recordsource is a view from sql server.
> The view has a computed column. The problem occurs when there is an
> insert/update on that view. The errors indicates that arithabort needs to
> be set on, etc.
> In my ado-connection i can fix that, but how do i do it when the view is
> linked through odbc? Maybe a 'instead of trigger'would do it, but i don't
> know how.|||Uri Dimant wrote:
> Jason
> Lookup SET ARITHABORT command in the BOL along with good examples
>
>
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:u09uNxe5FHA.1420@.TK2MSFTNGP09.phx.gbl...
>
>
>
Hi Uri,
I already looked at bol. In a ado-connection i used myconnection.execute
"set arithabort on"
But how to do it when the view is a linked accesstable? I use a filedsn.|||Jason
Probably you'll have to create a view in QA with this setting like
SET ARITHABORT ON
GO
CREATE VIEW myView AS
....
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eAjl94e5FHA.1032@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant wrote:
> Hi Uri,
> I already looked at bol. In a ado-connection i used myconnection.execute
> "set arithabort on"
> But how to do it when the view is a linked accesstable? I use a filedsn.|||Uri Dimant wrote:
> Jason
> Probably you'll have to create a view in QA with this setting like
> SET ARITHABORT ON
> GO
> CREATE VIEW myView AS
> ....
>
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eAjl94e5FHA.1032@.TK2MSFTNGP11.phx.gbl...
>
>
>
Hi Uri,
That doesn't work any other ideas?|||Try setting the database option arith_abort. Lookup sp_dboption in Books
Online.
ML

Composite or Primary Key

How do determine within SQL Server if key is a Primary Key
or Composite Key (Take two or more fields to uniquely
adentify a record.)? List Primary Key properties?
Thank You,
JimHi,
If a primary key is constructed with more than 1 columns then it is named as
Composite primary key.
Use the system stored procedure to identify the primary key.
sp_primary_keys_rowset @.table_name
If we have multiple entries with Ordinal (1,2,3..) then it is Composite key.
Note: you can even use sp_help <table name> to get the key information.
Thanks
Hari
MCDBA
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:04ea01c3da31$7f6983e0$a401280a@.phx.gbl...

quote:

---

> How do determine within SQL Server if key is a Primary Key
> or Composite Key (Take two or more fields to uniquely
> adentify a record.)? List Primary Key properties?
> Thank You,
> Jim

---

Composite or Primary Key

How do determine within SQL Server if key is a Primary Key
or Composite Key (Take two or more fields to uniquely
adentify a record.)? List Primary Key properties?
Thank You,
JimHi,
If a primary key is constructed with more than 1 columns then it is named as
Composite primary key.
Use the system stored procedure to identify the primary key.
sp_primary_keys_rowset @.table_name
If we have multiple entries with Ordinal (1,2,3..) then it is Composite key.
Note: you can even use sp_help <table name> to get the key information.
Thanks
Hari
MCDBA
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:04ea01c3da31$7f6983e0$a401280a@.phx.gbl...
> How do determine within SQL Server if key is a Primary Key
> or Composite Key (Take two or more fields to uniquely
> adentify a record.)? List Primary Key properties?
> Thank You,
> Jim

Complex Query help needed fast!

The goal is to take the low and high values out of each record, then get the
average of the remaining fields. For ex, ID1 has five values, remove 2 and
100 then add (40+20+10)/3...then do this for each record. The number of
values in the five fields can vary as shown below.
I have a table that looks like this:
ID F1 F2 F3 F4 F5 field names
1 100 40 20 2 10 Values
2 4 140 10 42
3 10 189 22
4 20 10 24 332 3
Thanks in advance for this query.One way is to use the unpivot operator. It's tested in MSSQL2005.
select id, (sum(f) - max(f) - min(f))/(count(*) - 2.0) avg
from (select id, f from test unpivot (f for t in (f1,f2,f3,f4,f5)) p) t
group by id
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.|||See if this helps. I'm assuming the blank column values contain NULL.
If they contain something else, you'll have to change F1 through F5 to
CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
and so on.
select
ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
go
Steve Kass
Drew University
KT wrote:

>The goal is to take the low and high values out of each record, then get th
e
>average of the remaining fields. For ex, ID1 has five values, remove 2 and
>100 then add (40+20+10)/3...then do this for each record. The number of
>values in the five fields can vary as shown below.
>
>
>I have a table that looks like this:
>
>ID F1 F2 F3 F4 F5 field names
>1 100 40 20 2 10 Values
>2 4 140 10 42
>3 10 189 22
>
>4 20 10 24 332 3
>
>
>Thanks in advance for this query.
>
>|||My solutions :) For the 2005 version, I like my solution for readability as
it is pretty straightforward. If you aren't using 2005, then Kass's is
really slick. Don't get me wrong, the person named mason's solution is
slicker than mine (and shorter) but I think that my solution is probably
more understandable later in the process. Either way, one of these'll do
you :)
Note that I use integer math, while Steve's uses floats. So my answers are
rounded off , while his arent
--create the table
create table looksLikeThis
(
id int primary key,
f1 int,
f2 int,
f3 int,
f4 int,
f5 int
)
insert into looksLikeThis
select 1, 100, 40, 20, 2, 10
union all
select 2, 4, 140, 10, 42, null
union all
select 3, 10, 189, 22,null, null
union all
select 4, 20, 10, 24, 332, 3
go
---
-- In 2005
---
--so much easier to do with the partition statement and the CTE. Allows the
first two
--views to be rolled up into one query pretty easy:
with shouldLookLikeThis as
(select *, row_number() over (partition by id order by value,uniqueifier )
as ordering
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null)
select id, avg(value) as averageValue
from shouldLookLikeThis
where ordering not in (select max(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
and ordering not in (select min(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
group by id
-- Using 2000 and recent versions
--
--normalize the table, including some value to make sure of uniqueness (very
important to the query
--so you don't lose rows if the min or max have multiple values
create view shouldLookLikeThis
as
select *
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null
go
--this view adds an ordering to the output (this is what the uniqueifier was
about
create view includeOrder
as
select id, value, (select count(*)
from shouldLookLikeThis s2
where shouldLookLikeThis.id = s2.id
and (shouldLookLikeThis.value <= s2.value
or (shouldLookLikeThis.value = s2.value
and shouldLookLikeThis.uniqueifier <=
s2.uniqueifier))
) as ordering
from shouldLookLikeThis
GO
--then just exclude the min and max orderwise
select id, avg(value) as averageValue
from includeOrder
where ordering not in (select max(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
and ordering not in (select min(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
group by id
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.
>|||I'm still learning MSSQL2005 features and the pivot/unpivot operators are an
interesting implementation. The idea is to transpose the rows into a column
so that we can use aggregate functions to calculate the average. If
"unpivot" is too unconventional, we can always use a case-based or a "union
all" derived table to achieve the same effect. I should add HAVING
COUNT(*)>2 to take care of the devide-by-0 situation.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
> My solutions :) For the 2005 version, I like my solution for readability
> as it is pretty straightforward. If you aren't using 2005, then Kass's is
> really slick. Don't get me wrong, the person named mason's solution is
> slicker than mine (and shorter) but I think that my solution is probably
> more understandable later in the process. Either way, one of these'll do
> you :)
> Note that I use integer math, while Steve's uses floats. So my answers
> are rounded off , while his arent
> --create the table
> create table looksLikeThis
> (
> id int primary key,
> f1 int,
> f2 int,
> f3 int,
> f4 int,
> f5 int
> )
> insert into looksLikeThis
> select 1, 100, 40, 20, 2, 10
> union all
> select 2, 4, 140, 10, 42, null
> union all
> select 3, 10, 189, 22,null, null
> union all
> select 4, 20, 10, 24, 332, 3
> go
> ---
> -- In 2005
> ---
> --so much easier to do with the partition statement and the CTE. Allows
> the first two
> --views to be rolled up into one query pretty easy:
> with shouldLookLikeThis as
> (select *, row_number() over (partition by id order by value,uniqueifier )
> as ordering
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null)
> select id, avg(value) as averageValue
> from shouldLookLikeThis
> where ordering not in (select max(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> and ordering not in (select min(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> group by id
> --
> -- Using 2000 and recent versions
> --
> --normalize the table, including some value to make sure of uniqueness
> (very important to the query
> --so you don't lose rows if the min or max have multiple values
> create view shouldLookLikeThis
> as
> select *
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null
> go
> --this view adds an ordering to the output (this is what the uniqueifier
> was about
> create view includeOrder
> as
> select id, value, (select count(*)
> from shouldLookLikeThis s2
> where shouldLookLikeThis.id = s2.id
> and (shouldLookLikeThis.value <= s2.value
> or (shouldLookLikeThis.value = s2.value
> and shouldLookLikeThis.uniqueifier <=
> s2.uniqueifier))
> ) as ordering
> from shouldLookLikeThis
> GO
> --then just exclude the min and max orderwise
> select id, avg(value) as averageValue
> from includeOrder
> where ordering not in (select max(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> and ordering not in (select min(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> group by id
> go
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "KT" <ktdev@.hotmail.com> wrote in message
> news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...|||No, the unpivot thing isn't what is unconventional. It is the:
sum(f) - max(f) - min(f))/(count(*) - 2.0)
part that makes the brain work harder than I cared to think about last
night. This is actually a more elagant solution too because it handles
duplicates easier.

> The idea is to transpose the rows into a column so that we can use
> aggregate functions to calculate the average
This is because SQL works well with rows, not vectors, particularly not of
variable length like this. This is part of what the Basically all we are
doing is rotating the set to be a SQL table in first normal form which then
makes it natural.
I know might go with mason's solution over mine, but that is so not easy to
admit :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm still learning MSSQL2005 features and the pivot/unpivot operators are
> an interesting implementation. The idea is to transpose the rows into a
> column so that we can use aggregate functions to calculate the average. If
> "unpivot" is too unconventional, we can always use a case-based or a
> "union all" derived table to achieve the same effect. I should add HAVING
> COUNT(*)>2 to take care of the devide-by-0 situation.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
>|||And 2.0 rather than 2 floats the whole thing. :p
In reality, it's often quantity over quality in people's eyes, and you are
right that readability may go with quantity in many cases. :p
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
> No, the unpivot thing isn't what is unconventional. It is the:
> sum(f) - max(f) - min(f))/(count(*) - 2.0)
> part that makes the brain work harder than I cared to think about last
> night. This is actually a more elagant solution too because it handles
> duplicates easier.
>
> This is because SQL works well with rows, not vectors, particularly not of
> variable length like this. This is part of what the Basically all we are
> doing is rotating the set to be a SQL table in first normal form which
> then makes it natural.
> I know might go with mason's solution over mine, but that is so not easy
> to admit :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "mason" <masonliu@.msn.com> wrote in message
> news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...|||Until performance gets involved, it is almost always the case :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:uJEVfUJPGHA.3144@.TK2MSFTNGP11.phx.gbl...
> And 2.0 rather than 2 floats the whole thing. :p
> In reality, it's often quantity over quality in people's eyes, and you are
> right that readability may go with quantity in many cases. :p
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
>|||This one worked perfectly...could you elaborate on what it is actually
doing.
Thanks Steve.
"Steve Kass" <skass@.drew.edu> wrote in message
news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See if this helps. I'm assuming the blank column values contain NULL.
> If they contain something else, you'll have to change F1 through F5 to
> CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
> and so on.
> select
> ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
> from (
> select
> ID,
> ColNum,
> case ColNum
> when 1 then F1
> when 2 then F2
> when 3 then F3
> when 4 then F4
> when 5 then F5
> end*1.0
> from yourTable
> cross join (
> select 1 union all select 2 union all
> select 3 union all select 4 union all select 5
> ) as F(ColNum)
> ) as T(ID,ColNum,Fval)
> group by ID
> go
> Steve Kass
> Drew University
> KT wrote:
>
the
and|||The cross join turns each single row such as
ID F1 F2 F3 F4 F5
--
101 13 24 35 46 NULL
into five separate rows like this:
ID, ColNum, Fval
--
101 1 13
101 2 24
101 3 35
101 4 46
101 5 NULL
Then it groups over ID values, finding the sum of
the Fval values, the number of those values that are
not blank (count(Fval)), and the largest and smallest
of the non-blank values. It gets the average you
need by adding the non-blank values, subtracting the
largest and smallest, and dividing by two less than
the number of non-blank values, all of this for each ID.
To understand it better, evaluate these queries separately
(there may be typos, but the idea is to look at it step by
step)
-- 1
select * from (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
-- 2
select * from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
order by ID, ColNum, Fval
-- 3
select
ID
COUNT(Fval) as count_fval,
SUM(Fval) as sum_fval,
MAX(Fval) as max_fval,
MIN(Fval) as min_fval
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
-SK
KT wrote:

>This one worked perfectly...could you elaborate on what it is actually
>doing.
>Thanks Steve.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>the
>
>and
>
>
>

Complex histogram question

I have a data series n long and want to show a histogram of the top 10
elements of this list, and show the position of a known record in this
top ten. (This is the easy bit).
Then, if the known record is not in the top 5, I want to display the
top 5 and add the known record at the end, as the 6th, but also
indicate the value of this record on the chart.
E.g I have my top 5 clients from a list of 15. I want to show my
client, Acme, where they are in this list. They turn out to be 15th,
so I want a histogram with the top 5, plus an 'sixth' element which is
Acme, showing it as the 15th in the list...
Below is the sort of chart I am after....
#
#
# 1
# 1 2 3
# 1 2 3 4
# 1 2 3 4 5
# 1 2 3 4 5 15th
##########################
Is this possible'
TIA,
Lisaselect item, value, count(t1.item)+1 rank
from items, items t1
where items.value > t1.value
having count(t1.item) < 5
union
select item, value, count(t1.item)+1 rank
from items, items t1
where item = @.item
There's probably an easier way using Top N.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Lisa" <budgenlj@.willis.com> wrote in message
news:9abd1432.0407090612.212701d6@.posting.google.com...
> I have a data series n long and want to show a histogram of the top 10
> elements of this list, and show the position of a known record in this
> top ten. (This is the easy bit).
> Then, if the known record is not in the top 5, I want to display the
> top 5 and add the known record at the end, as the 6th, but also
> indicate the value of this record on the chart.
> E.g I have my top 5 clients from a list of 15. I want to show my
> client, Acme, where they are in this list. They turn out to be 15th,
> so I want a histogram with the top 5, plus an 'sixth' element which is
> Acme, showing it as the 15th in the list...
> Below is the sort of chart I am after....
> #
> #
> # 1
> # 1 2 3
> # 1 2 3 4
> # 1 2 3 4 5
> # 1 2 3 4 5 15th
> ##########################
> Is this possible'
> TIA,
> Lisa

Complex counting, summing and grouping

I have a table of Sales statistics as follows, with each record bearing
invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
the salesRep and the SalesAmount the SalesRep contributed
Create Table
sales (invoiceID INT NOT NULL,
salesRepID INT NOT NULL,
SalesDept VARCHAR(5) NOT NULL,
salesAmount DECIMAL NOT NULL)
Each InvoiceID can contain contribution of multiple sales reps in multiple
sales department, each with a different sales amount contribution with the
following sample Data:
INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
from an earlier period
INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
A minus is denoted as a "refund".
I want to produce a query on the *number* (i.e. not amount) of sales
generated by each department, as follows
SalesDeptCountPerDept
QW90.5
QE90.33333
S71-0.66667
S731.33333
What is the simplest SQL that can be used to produce the above output?
Patrick,

> SalesDeptCountPerDept
> QW90.5
> QE90.33333
> S71-0.66667
> S731.33333
Can you explain where are those numbers coming from?
AMB
"Patrick" wrote:

> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDeptCountPerDept
> QW90.5
> QE90.33333
> S71-0.66667
> S731.33333
> What is the simplest SQL that can be used to produce the above output?
|||This is a wild guess in terms of what you meant; the result matches
though...
-- 2005
with c as
(
select
salesdept,
1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
from sales
)
select salesdept, sum(cnt) cntperdept
from c
group by salesdept;
-- 2000
select salesdept, sum(cnt) cntperdept
from
(
select
salesdept,
1.*sign(salesamount) /
(select count(*) from sales as s2
where s2.invoiceid = s1.invoiceid) as cnt
from sales as s1
) as d
group by salesdept;
A clearer picture of what you're after would allow less guess work...
Cheers,
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment
> of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?
|||Hi Itzik,

> This is a wild guess
Any number you can give us to play the lottery today :-))?
AMB
"Itzik Ben-Gan" wrote:

> This is a wild guess in terms of what you meant; the result matches
> though...
> -- 2005
> with c as
> (
> select
> salesdept,
> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
> from sales
> )
> select salesdept, sum(cnt) cntperdept
> from c
> group by salesdept;
> -- 2000
> select salesdept, sum(cnt) cntperdept
> from
> (
> select
> salesdept,
> 1.*sign(salesamount) /
> (select count(*) from sales as s2
> where s2.invoiceid = s1.invoiceid) as cnt
> from sales as s1
> ) as d
> group by salesdept;
> A clearer picture of what you're after would allow less guess work...
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>
|||Sure,
with
l0 as (select 0 as c union all select 0),
l1 as (select 0 as c from l0 as a, l0 as b),
l2 as (select 0 as c from l1 as a, l1 as b),
l3 as (select 0 as c from l2 as a, l2 as b),
nums as (select top(49) row_number() over(order by c) as n from l3),
choice as (select top(6) n from nums order by checksum(newid()))
select stuff(
(select ',' + cast(n as varchar(10)) as [text()]
from choice order by n for xml path('')), 1, 1, '');
;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...[vbcol=seagreen]
> Hi Itzik,
>
> Any number you can give us to play the lottery today :-))?
>
> AMB
>
> "Itzik Ben-Gan" wrote:
|||On May 16, 11:25 pm, Patrick <p...@.reply.newsgroup.msn.com> wrote:
> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?
Do you consider a negative amount as sales? Your first insert...
If yes then following:
select salesdept,count(*)
from sales
where salesamount > 0
group by salesdept
else...
select salesdept,count(*)
from sales
group by salesdept
|||Itzik,
LOL :-))))
Both are good ones.
Best wishes,
AMB
"Itzik Ben-Gan" wrote:

> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>
|||That is one of the most creative yet powerfully useless tsql statements I
have ever seen!!
Now, if I could just figure out how the hell it works . . . LOL
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>
|||HEY, wait a minute!! In non-scientific testing I have deterimined that the
output is weighted low-to-high from left to right.
I want my money back! :-))
Still chuckling about the code tho!
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>
|||One is reminded of the student in the movie 'PCU' that will
gladly pay for a term paper. He is reminded that he is basing
his academic career on a dead language some 5000 years old
when it is revealed that his subject is Sanskrit. And he is
quickly dismissed as no amount of money can overcome the
absurdity of the request. Hopefully, someday, the parallel
will dawn on you.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>

Complex counting, summing and grouping

I have a table of Sales statistics as follows, with each record bearing
invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
the salesRep and the SalesAmount the SalesRep contributed
Create Table
sales (invoiceID INT NOT NULL,
salesRepID INT NOT NULL,
SalesDept VARCHAR(5) NOT NULL,
salesAmount DECIMAL NOT NULL)
Each InvoiceID can contain contribution of multiple sales reps in multiple
sales department, each with a different sales amount contribution with the
following sample Data:
INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
from an earlier period
INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
A minus is denoted as a "refund".
I want to produce a query on the *number* (i.e. not amount) of sales
generated by each department, as follows
SalesDept CountPerDept
QW9 0.5
QE9 0.33333
S71 -0.66667
S73 1.33333
What is the simplest SQL that can be used to produce the above output?Patrick,
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
Can you explain where are those numbers coming from?
AMB
"Patrick" wrote:
> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?|||This is a wild guess in terms of what you meant; the result matches
though...
-- 2005
with c as
(
select
salesdept,
1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
from sales
)
select salesdept, sum(cnt) cntperdept
from c
group by salesdept;
-- 2000
select salesdept, sum(cnt) cntperdept
from
(
select
salesdept,
1.*sign(salesamount) /
(select count(*) from sales as s2
where s2.invoiceid = s1.invoiceid) as cnt
from sales as s1
) as d
group by salesdept;
A clearer picture of what you're after would allow less guess work...
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment
> of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?|||Hi Itzik,
> This is a wild guess
Any number you can give us to play the lottery today :-))?
AMB
"Itzik Ben-Gan" wrote:
> This is a wild guess in terms of what you meant; the result matches
> though...
> -- 2005
> with c as
> (
> select
> salesdept,
> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
> from sales
> )
> select salesdept, sum(cnt) cntperdept
> from c
> group by salesdept;
> -- 2000
> select salesdept, sum(cnt) cntperdept
> from
> (
> select
> salesdept,
> 1.*sign(salesamount) /
> (select count(*) from sales as s2
> where s2.invoiceid = s1.invoiceid) as cnt
> from sales as s1
> ) as d
> group by salesdept;
> A clearer picture of what you're after would allow less guess work...
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
> >I have a table of Sales statistics as follows, with each record bearing
> > invoiceID, salesRep who contributed towards the sales, the SalesDepartment
> > of
> > the salesRep and the SalesAmount the SalesRep contributed
> >
> > Create Table
> > sales (invoiceID INT NOT NULL,
> > salesRepID INT NOT NULL,
> > SalesDept VARCHAR(5) NOT NULL,
> > salesAmount DECIMAL NOT NULL)
> >
> > Each InvoiceID can contain contribution of multiple sales reps in multiple
> > sales department, each with a different sales amount contribution with the
> > following sample Data:
> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> > from an earlier period
> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> > A minus is denoted as a "refund".
> >
> > I want to produce a query on the *number* (i.e. not amount) of sales
> > generated by each department, as follows
> >
> > SalesDept CountPerDept
> > QW9 0.5
> > QE9 0.33333
> > S71 -0.66667
> > S73 1.33333
> >
> > What is the simplest SQL that can be used to produce the above output?
>|||Sure,
with
l0 as (select 0 as c union all select 0),
l1 as (select 0 as c from l0 as a, l0 as b),
l2 as (select 0 as c from l1 as a, l1 as b),
l3 as (select 0 as c from l2 as a, l2 as b),
nums as (select top(49) row_number() over(order by c) as n from l3),
choice as (select top(6) n from nums order by checksum(newid()))
select stuff(
(select ',' + cast(n as varchar(10)) as [text()]
from choice order by n for xml path('')), 1, 1, '');
;-)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
> Hi Itzik,
>> This is a wild guess
> Any number you can give us to play the lottery today :-))?
>
> AMB
>
> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>|||On May 16, 11:25 pm, Patrick <p...@.reply.newsgroup.msn.com> wrote:
> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?
Do you consider a negative amount as sales? Your first insert...
If yes then following:
select salesdept,count(*)
from sales
where salesamount > 0
group by salesdept
else...
select salesdept,count(*)
from sales
group by salesdept|||Itzik, you are such a http://en.wikipedia.org/wiki/Brainiac
;)
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>|||Itzik,
LOL :-))))
Both are good ones.
Best wishes,
AMB
"Itzik Ben-Gan" wrote:
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
> > Hi Itzik,
> >
> >> This is a wild guess
> >
> > Any number you can give us to play the lottery today :-))?
> >
> >
> > AMB
> >
> >
> > "Itzik Ben-Gan" wrote:
> >
> >> This is a wild guess in terms of what you meant; the result matches
> >> though...
> >>
> >> -- 2005
> >> with c as
> >> (
> >> select
> >> salesdept,
> >> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
> >> from sales
> >> )
> >> select salesdept, sum(cnt) cntperdept
> >> from c
> >> group by salesdept;
> >>
> >> -- 2000
> >> select salesdept, sum(cnt) cntperdept
> >> from
> >> (
> >> select
> >> salesdept,
> >> 1.*sign(salesamount) /
> >> (select count(*) from sales as s2
> >> where s2.invoiceid = s1.invoiceid) as cnt
> >> from sales as s1
> >> ) as d
> >> group by salesdept;
> >>
> >> A clearer picture of what you're after would allow less guess work...
> >>
> >> Cheers,
> >> --
> >> BG, SQL Server MVP
> >> www.SolidQualityLearning.com
> >> www.insidetsql.com
> >>
> >>
> >> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> >> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
> >> >I have a table of Sales statistics as follows, with each record bearing
> >> > invoiceID, salesRep who contributed towards the sales, the
> >> > SalesDepartment
> >> > of
> >> > the salesRep and the SalesAmount the SalesRep contributed
> >> >
> >> > Create Table
> >> > sales (invoiceID INT NOT NULL,
> >> > salesRepID INT NOT NULL,
> >> > SalesDept VARCHAR(5) NOT NULL,
> >> > salesAmount DECIMAL NOT NULL)
> >> >
> >> > Each InvoiceID can contain contribution of multiple sales reps in
> >> > multiple
> >> > sales department, each with a different sales amount contribution with
> >> > the
> >> > following sample Data:
> >> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
> >> > refund
> >> > from an earlier period
> >> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> >> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> >> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> >> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> >> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> >> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> >> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> >> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> >> > A minus is denoted as a "refund".
> >> >
> >> > I want to produce a query on the *number* (i.e. not amount) of sales
> >> > generated by each department, as follows
> >> >
> >> > SalesDept CountPerDept
> >> > QW9 0.5
> >> > QE9 0.33333
> >> > S71 -0.66667
> >> > S73 1.33333
> >> >
> >> > What is the simplest SQL that can be used to produce the above output?
> >>
> >>
>|||That is one of the most creative yet powerfully useless tsql statements I
have ever seen!!
Now, if I could just figure out how the hell it works . . . LOL
--
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>|||HEY, wait a minute!! In non-scientific testing I have deterimined that the
output is weighted low-to-high from left to right.
I want my money back! :-))
Still chuckling about the code tho!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>|||One is reminded of the student in the movie 'PCU' that will
gladly pay for a term paper. He is reminded that he is basing
his academic career on a dead language some 5000 years old
when it is revealed that his subject is Sanskrit. And he is
quickly dismissed as no amount of money can overcome the
absurdity of the request. Hopefully, someday, the parallel
will dawn on you.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>> Hi Itzik,
>> This is a wild guess
>> Any number you can give us to play the lottery today :-))?
>>
>> AMB
>>
>> "Itzik Ben-Gan" wrote:
>> This is a wild guess in terms of what you meant; the result matches
>> though...
>> -- 2005
>> with c as
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
>> from sales
>> )
>> select salesdept, sum(cnt) cntperdept
>> from c
>> group by salesdept;
>> -- 2000
>> select salesdept, sum(cnt) cntperdept
>> from
>> (
>> select
>> salesdept,
>> 1.*sign(salesamount) /
>> (select count(*) from sales as s2
>> where s2.invoiceid = s1.invoiceid) as cnt
>> from sales as s1
>> ) as d
>> group by salesdept;
>> A clearer picture of what you're after would allow less guess work...
>> Cheers,
>> --
>> BG, SQL Server MVP
>> www.SolidQualityLearning.com
>> www.insidetsql.com
>>
>> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
>> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>> >I have a table of Sales statistics as follows, with each record bearing
>> > invoiceID, salesRep who contributed towards the sales, the
>> > SalesDepartment
>> > of
>> > the salesRep and the SalesAmount the SalesRep contributed
>> >
>> > Create Table
>> > sales (invoiceID INT NOT NULL,
>> > salesRepID INT NOT NULL,
>> > SalesDept VARCHAR(5) NOT NULL,
>> > salesAmount DECIMAL NOT NULL)
>> >
>> > Each InvoiceID can contain contribution of multiple sales reps in
>> > multiple
>> > sales department, each with a different sales amount contribution with
>> > the
>> > following sample Data:
>> > INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a
>> > refund
>> > from an earlier period
>> > INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
>> > INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
>> > INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
>> > INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
>> > INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
>> > INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
>> > A minus is denoted as a "refund".
>> >
>> > I want to produce a query on the *number* (i.e. not amount) of sales
>> > generated by each department, as follows
>> >
>> > SalesDept CountPerDept
>> > QW9 0.5
>> > QE9 0.33333
>> > S71 -0.66667
>> > S73 1.33333
>> >
>> > What is the simplest SQL that can be used to produce the above output?
>>
>

Complex counting, summing and grouping

I have a table of Sales statistics as follows, with each record bearing
invoiceID, salesRep who contributed towards the sales, the SalesDepartment o
f
the salesRep and the SalesAmount the SalesRep contributed
Create Table
sales (invoiceID INT NOT NULL,
salesRepID INT NOT NULL,
SalesDept VARCHAR(5) NOT NULL,
salesAmount DECIMAL NOT NULL)
Each InvoiceID can contain contribution of multiple sales reps in multiple
sales department, each with a different sales amount contribution with the
following sample Data:
INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
from an earlier period
INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
A minus is denoted as a "refund".
I want to produce a query on the *number* (i.e. not amount) of sales
generated by each department, as follows
SalesDept CountPerDept
QW9 0.5
QE9 0.33333
S71 -0.66667
S73 1.33333
What is the simplest SQL that can be used to produce the above output?Patrick,

> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
Can you explain where are those numbers coming from?
AMB
"Patrick" wrote:

> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment
of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?|||This is a wild guess in terms of what you meant; the result matches
though...
-- 2005
with c as
(
select
salesdept,
1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
from sales
)
select salesdept, sum(cnt) cntperdept
from c
group by salesdept;
-- 2000
select salesdept, sum(cnt) cntperdept
from
(
select
salesdept,
1.*sign(salesamount) /
(select count(*) from sales as s2
where s2.invoiceid = s1.invoiceid) as cnt
from sales as s1
) as d
group by salesdept;
A clearer picture of what you're after would allow less guess work...
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment
> of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?|||Hi Itzik,

> This is a wild guess
Any number you can give us to play the lottery today :-))?
AMB
"Itzik Ben-Gan" wrote:

> This is a wild guess in terms of what you meant; the result matches
> though...
> -- 2005
> with c as
> (
> select
> salesdept,
> 1.*sign(salesamount) / count(*) over(partition by invoiceid) as cnt
> from sales
> )
> select salesdept, sum(cnt) cntperdept
> from c
> group by salesdept;
> -- 2000
> select salesdept, sum(cnt) cntperdept
> from
> (
> select
> salesdept,
> 1.*sign(salesamount) /
> (select count(*) from sales as s2
> where s2.invoiceid = s1.invoiceid) as cnt
> from sales as s1
> ) as d
> group by salesdept;
> A clearer picture of what you're after would allow less guess work...
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
> news:ADBCB957-DD1A-4C75-B850-F253070A1981@.microsoft.com...
>|||Sure,
with
l0 as (select 0 as c union all select 0),
l1 as (select 0 as c from l0 as a, l0 as b),
l2 as (select 0 as c from l1 as a, l1 as b),
l3 as (select 0 as c from l2 as a, l2 as b),
nums as (select top(49) row_number() over(order by c) as n from l3),
choice as (select top(6) n from nums order by checksum(newid()))
select stuff(
(select ',' + cast(n as varchar(10)) as [text()]
from choice order by n for xml path('')), 1, 1, '');
;-)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...[vbcol=seagreen]
> Hi Itzik,
>
> Any number you can give us to play the lottery today :-))?
>
> AMB
>
> "Itzik Ben-Gan" wrote:
>|||On May 16, 11:25 pm, Patrick <p...@.reply.newsgroup.msn.com> wrote:
> I have a table of Sales statistics as follows, with each record bearing
> invoiceID, salesRep who contributed towards the sales, the SalesDepartment
of
> the salesRep and the SalesAmount the SalesRep contributed
> Create Table
> sales (invoiceID INT NOT NULL,
> salesRepID INT NOT NULL,
> SalesDept VARCHAR(5) NOT NULL,
> salesAmount DECIMAL NOT NULL)
> Each InvoiceID can contain contribution of multiple sales reps in multiple
> sales department, each with a different sales amount contribution with the
> following sample Data:
> INSERT INTO sales VALUES( 15611252, 615, 'S71',-1000) 'denote a refund
> from an earlier period
> INSERT INTO sales VALUES( 18922115, 829, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 821, 'QW9',100 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',50 )
> INSERT INTO sales VALUES( 18922115, 712, 'QE9',-50)
> INSERT INTO sales VALUES( 18922116, 712, 'QE9',10 )
> INSERT INTO sales VALUES( 18922116, 615, 'S71',10 )
> INSERT INTO sales VALUES( 18922116, 625, 'S73',10 )
> INSERT INTO sales VALUES( 18922117, 625, 'S73',110 )
> A minus is denoted as a "refund".
> I want to produce a query on the *number* (i.e. not amount) of sales
> generated by each department, as follows
> SalesDept CountPerDept
> QW9 0.5
> QE9 0.33333
> S71 -0.66667
> S73 1.33333
> What is the simplest SQL that can be used to produce the above output?
Do you consider a negative amount as sales? Your first insert...
If yes then following:
select salesdept,count(*)
from sales
where salesamount > 0
group by salesdept
else...
select salesdept,count(*)
from sales
group by salesdept|||Itzik, you are such a http://en.wikipedia.org/wiki/Brainiac
;)
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>|||Itzik,
LOL :-))))
Both are good ones.
Best wishes,
AMB
"Itzik Ben-Gan" wrote:

> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>|||That is one of the most creative yet powerfully useless tsql statements I
have ever seen!!
Now, if I could just figure out how the hell it works . . . LOL
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>|||HEY, wait a minute!! In non-scientific testing I have deterimined that the
output is weighted low-to-high from left to right.
I want my money back! :-))
Still chuckling about the code tho!
TheSQLGuru
President
Indicium Resources, Inc.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:8238C311-5168-4BF4-A239-0D2B79EFEDCF@.microsoft.com...
> Sure,
> with
> l0 as (select 0 as c union all select 0),
> l1 as (select 0 as c from l0 as a, l0 as b),
> l2 as (select 0 as c from l1 as a, l1 as b),
> l3 as (select 0 as c from l2 as a, l2 as b),
> nums as (select top(49) row_number() over(order by c) as n from l3),
> choice as (select top(6) n from nums order by checksum(newid()))
> select stuff(
> (select ',' + cast(n as varchar(10)) as [text()]
> from choice order by n for xml path('')), 1, 1, '');
> ;-)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:93D61231-049A-4E16-9BB8-E8F8BE23052B@.microsoft.com...
>

Complete beginner needs help with record selection

As a complete beginner to Crystal Reports I hope I am not asking stupid questions of what looks like a very technical and knowledgeable forum...if I am please feel free to direct me elsewhere!

I am trying to select records using several Tables but filtered from a 'Transactions Table' but only want the last dated record (ie the last dated record is equal to [input date range]. In Access I would use a query asking for 'LastOf...[field name] - but I don't know how to select the LastOf in Crystal Reports. I have stumbled around the Help for ages...can anybody please advise me on what I am sure is a simple question!
Many ThanksOne way would be to group by the table.column you're interested in and add the 'lastof' clause into the group selection formula:

{table.column} = Maximum ({table.column})|||If you use Access query for that, then design report based on that query and supply that value as parameter

Compiled Code as a data source

Does anyone have any examples of a c# program and that returns a record set
from sql server that could be compiled and referenced as a data source in an
RDL file? If so I would sure like an example of the C# and the RDL file that
references it. Thanks.no.. but MS Access can do this for sure
On Apr 19, 7:10 am, Greg Larsen <gregalar...@.removeit.msn.com> wrote:
> Does anyone have any examples of a c# program and that returns a record set
> from sql server that could be compiled and referenced as a data source in an
> RDL file? If so I would sure like an example of the C# and the RDL file that
> references it. Thanks.|||Instead use web services using c# to directly access the datasource and
reports itself. A good sample is given in the BOL.
Amarnath
"Greg Larsen" wrote:
> Does anyone have any examples of a c# program and that returns a record set
> from sql server that could be compiled and referenced as a data source in an
> RDL file? If so I would sure like an example of the C# and the RDL file that
> references it. Thanks.|||yeah.. or you could write your own compiler and install linux
I mean wtf
fuck web services
fuck C#
can I put a form in a BI project and loop through all of the reports
in my PROJECT?
until I can do this; SSRS can fuck themselves
On Apr 19, 11:28 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Instead use web services using c# to directly access the datasource and
> reports itself. A good sample is given in the BOL.
> Amarnath
> "Greg Larsen" wrote:
> > Does anyone have any examples of a c# program and that returns a record set
> > from sql server that could be compiled and referenced as a data source in an
> > RDL file? If so I would sure like an example of the C# and the RDL file that
> > references it. Thanks.