Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
Chuck
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>
|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckSELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.T
K2MSFTNGP05.phx.gbl...
> Hello,
>
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
>
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
>
> Table:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
>
> The Query Should Return:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
>
> Thanks,
>
> Chuck
>
>|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL query

Hi,

Let's say I have a Customer table and an Order table. The latter is linked to the former through foreign key CustomerID. Now, I want to create a SQL statement which, given a CustomerID, returns the corresponding row in Customer table PLUS a field indicating the total number of orders this particular customer has. How can I achieve this with a single SQL statement?

Thanks in advance

--USE Northwind

SELECT Customers.CustomerID, SUM(Orders.OrderID) AS TotalOrders

FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

GROUP BY Customers.CustomerID

|||Thanks, this is exactly what I want, except I need to change the aggregate function to Count instead of Sum.

Help with SQL Query

I have a financial database which tracks charges and payments form clients.
I need to create a query which returns only those months when a client
hasn't paid in full and the amount still owing. So, if the table looks like
this:
client date(mm/dd/yyyy) charge
payment
smith 12/01/2006 400.00
smith 12/23/2006
250.00
smith 12/28/2005
50.00
smith 01/01/2006 400.00
smith 01/13/2006
400.00
jones 02/01/2006 400.00
jones 02/05/2006
350.00
the resulting data would look like this:
smith 12/2005 100.00
jones 02/2006 50.00
thanks.
steve.Since you didn't provide DDL or insert scripts this is what I cooked up
I don't think the data for smith is correct, and what is the full
amount? 800?
create table blah (client varchar(50),date datetime, charge decimal
(10,2))
insert into blah
select 'smith', '12/01/2006', 400.00 union all
select 'smith', '12/23/2006', 250.00 union all
select 'smith' , '12/28/2005', 50.00 union all
select 'smith' , '01/01/2006', 400.00 union all
select 'smith' , '01/13/2006', 400.00 union all
select 'jones' , '02/01/2006', 400.00 union all
select 'jones' , '02/05/2006', 350.00
select client,800 -sum(charge) ,datepart(m,date) ,datepart(yyyy,date)
from blah
group by client,datepart(m,date),datepart(yyyy,da
te)
having sum(charge) < 800
http://sqlservercode.blogspot.com/|||SELECT
CLIENT,CONVERT(VARCHAR,DATEPART(mm,getda
te()))+'/'+CONVERT(VARCHAR,DATEPART(
year,getdate())) AS MONTHYEAR, CHARGE
WHERE CHARGE <= 100
Lemme me know if this is what your are looking for...
Thanks,
Sree
"molsonexpert" wrote:

> I have a financial database which tracks charges and payments form clients
.
> I need to create a query which returns only those months when a client
> hasn't paid in full and the amount still owing. So, if the table looks lik
e
> this:
> client date(mm/dd/yyyy) charge
> payment
> smith 12/01/2006 400.00
> smith 12/23/2006
> 250.00
> smith 12/28/2005
> 50.00
> smith 01/01/2006 400.00
> smith 01/13/2006
> 400.00
> jones 02/01/2006 400.00
> jones 02/05/2006
> 350.00
> the resulting data would look like this:
> smith 12/2005 100.00
> jones 02/2006 50.00
> thanks.
> steve.
>
>|||select client
, max(date) as LastActivityDate
-- Assuming the last date soemthign happened is the one you want to show
, sum(charge-payment) as Balance
-- above can also be:
--, Sum(charge) - sum(payment) as Balance
from MyPaymentsTable
group by client
having sum(charge-payment) <> 0
-- the above returns anyone with a credit or debit balance
-- the below returns only clients who owe money
-- or having sum(charge-payment) > 0
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:%23bQ2OayKGHA.952@.TK2MSFTNGP10.phx.gbl...
> I have a financial database which tracks charges and payments form
clients.
> I need to create a query which returns only those months when a client
> hasn't paid in full and the amount still owing. So, if the table looks
like
> this:
> client date(mm/dd/yyyy) charge
> payment
> smith 12/01/2006 400.00
> smith 12/23/2006
> 250.00
> smith 12/28/2005
> 50.00
> smith 01/01/2006 400.00
> smith 01/13/2006
> 400.00
> jones 02/01/2006 400.00
> jones 02/05/2006
> 350.00
> the resulting data would look like this:
> smith 12/2005 100.00
> jones 02/2006 50.00
> thanks.
> steve.
>|||-- This return months where the clients
-- charges exceed the payments. I'm
-- assuming that in your test data, the two entries for
-- 'smith' in december should be for 2005 not 2006
SELECT Client,
DATEPART(year,thedate),
DATEPART(month,thedate),
SUM(COALESCE(Charge,0)-COALESCE(Payment,0))
FROM ChargesAndPayments
GROUP BY client,
DATEPART(year,thedate),
DATEPART(month,thedate)
HAVING SUM(COALESCE(Charge,0)-COALESCE(Payment,0))>0|||I should have included the isnull or coalesce around the charge and payment
fields as markc600 did below.
Without the isnull or coalesce, you will get back nulls instead of the
correct values.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23x5XzwyKGHA.3260@.TK2MSFTNGP11.phx.gbl...
> select client
> , max(date) as LastActivityDate
> -- Assuming the last date soemthign happened is the one you want to show
> , sum(charge-payment) as Balance
> -- above can also be:
> --, Sum(charge) - sum(payment) as Balance
> from MyPaymentsTable
> group by client
> having sum(charge-payment) <> 0
> -- the above returns anyone with a credit or debit balance
> -- the below returns only clients who owe money
> -- or having sum(charge-payment) > 0
> "molsonexpert" <imdrunk@.work.ca> wrote in message
> news:%23bQ2OayKGHA.952@.TK2MSFTNGP10.phx.gbl...
> clients.
> like
>

Help with SQL Query

Dear group,
I need to create a stored procedure to return some data. The trick is the
data needs to be AGGREGATED with a SUM of commissions against each line for
each broker...
I have a table (TRADE) with the following data:
trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
1, 1, 1, 150, 13/06/2006
2, 2, 1, 100, 13/06/2006
3, 1, 1, 75, 14/06/2006
4, 1, 2, 165, 14/06/2006
5, 3, 1, 33.50, 14/06/2006
I want to display (for a DataTable to be used in a Crystal Report) a grid
where the headers will be:
Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id =
2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
(where tradeType_id = 2), Sum Monthly Total.
So that the query, when run on (14/06/2006), will look like:
1, 75, 165, 240, 225, 165, 190
2, null, null, null, 100, null, 100
3, 33.50, null, 33.50, 33.50, null, 33.50
The concept here is that I have a table which contains trades that a broker
has made. Each trade has a commission_amount column and a datestamp. I need
to be able to produce a report which has daily totals for different trade
types, but where the data is AGGREGATED by broker_id. All the SQL I've been
coming up with has been a total mess.
Can anyone assist with the above problem?
Many thanks!
MikeLiddle,
I think the trickiest thing here is the grouping. Is the monthly
total the running total since the first of the month, or just the sum
or trade type 2 records for a broker on a given day?
You can pretty easily group by date, broker id, and trade type.
SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
sumcom
FROM TRADE
GROUP BY broker_id, tradetype_id, datestamp
but the most straightforward way to get it into the format you want is
to do two subqueries and join them back together. But, what that looks
like will depend on whether you're looking for a running total or not.
Ion
Liddle Feesh wrote:
> Dear group,
> I need to create a stored procedure to return some data. The trick is the
> data needs to be AGGREGATED with a SUM of commissions against each line fo
r
> each broker...
> I have a table (TRADE) with the following data:
> trade_id (PK), broker_id, tradeType_id, commission_amount, datestamp
> 1, 1, 1, 150, 13/06/2006
> 2, 2, 1, 100, 13/06/2006
> 3, 1, 1, 75, 14/06/2006
> 4, 1, 2, 165, 14/06/2006
> 5, 3, 1, 33.50, 14/06/2006
> I want to display (for a DataTable to be used in a Crystal Report) a grid
> where the headers will be:
> Broker ID, Daily Total (where tradeType_id = 1), Daily Total (tradeType_id
=
> 2), Sum Daily Total, Monthly Total (where tradeType_id = 1), Monthly Total
> (where tradeType_id = 2), Sum Monthly Total.
> So that the query, when run on (14/06/2006), will look like:
> 1, 75, 165, 240, 225, 165, 190
> 2, null, null, null, 100, null, 100
> 3, 33.50, null, 33.50, 33.50, null, 33.50
> The concept here is that I have a table which contains trades that a broke
r
> has made. Each trade has a commission_amount column and a datestamp. I nee
d
> to be able to produce a report which has daily totals for different trade
> types, but where the data is AGGREGATED by broker_id. All the SQL I've bee
n
> coming up with has been a total mess.
> Can anyone assist with the above problem?
> Many thanks!
> Mike|||<ionFreeman@.gmail.com> wrote in message
news:1150320207.374658.305960@.f6g2000cwb.googlegroups.com...
> Liddle,
> I think the trickiest thing here is the grouping. Is the monthly
> total the running total since the first of the month, or just the sum
> or trade type 2 records for a broker on a given day?
There are only four aggregated calculated fields, those are the daily totals
for trade_Type 1 and 2 and the monthly totals for trade_Type 1 and 2. The
sum is just an addition of those two fields and can be calculated easily by
hand.

> You can pretty easily group by date, broker id, and trade type.
> SELECT broker_id, tradetype_id, datestamp, SUM(commission_amount) AS
> sumcom
> FROM TRADE
> GROUP BY broker_id, tradetype_id, datestamp
> but the most straightforward way to get it into the format you want is
> to do two subqueries and join them back together. But, what that looks
> like will depend on whether you're looking for a running total or not.
Cheers, Jon - that's close, but not quite right. I'm looking for a distinct
group, so that if there are only 2 broker_id's, there will be only two rows
and the SUM() data will be aggregated on those rows.
Thanks!sql

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckThis is a multi-part message in MIME format.
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message =news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> > With the following table how would I create a query that would return =all > rows whos EndDate minus StartDate is more than 28 Days.
> > TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> > Table:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
> > > The Query Should Return:
> > ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
> > > Thanks,
> > Chuck > >
--=_NextPart_000_086D_01C6D8D2.0BEDBF60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

SELECT
=ID
, =CustomerID
, =StartDate
, =EndDate
FROM Customers
WHERE datediff( day, StartDate, =EndDate ) > 28
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Charles A. Lackman" wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...> =Hello,> > With the following table how would I create a query that would =return all > rows whos EndDate minus StartDate is more than 28 =Days.> > TableName: Customers> ID - Integer> CustomerID - VarChar> StartDate - Date> EndDate - Date> > =Table:> > ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 2 Mike1 &n=bsp; 8/25/06 =9/15/06> 3 Dinah &n=bsp; 8/23/06 9/1/06> 4 James &n=bsp; 7/11/06 8/30/06> > > The Query Should Return:> => ID CustomerID StartDate EndDate> 1 Chuck1 &=nbsp; 9/1/06 =9/30/06> 4 James &n=bsp; 7/11/06 8/30/06> > > Thanks,> > Chuck => >

---

--=_NextPart_000_086D_01C6D8D2.0BEDBF60--|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>sql

Help with SQL 2005 Report Designer with multiple data sets

Hi,
I am trying to create a drilthrough report in report designer. I know
ho to do it if I have a single query but I do not know how to do it if
I have multiple. What is happening is that I tried to create a signle
query where I can then create a groupings and then have the report
drill through the groupings but while trying to create my query I
found that I could not create the query that I wanted being that I was
getting duplicate results in my comments column (I wanted to also add
the comments). If the user did not make a comment then it was
automatically added with the first comment made in the for that
grouping.
So I decided to make two queries. One is for selecting all of the
pending items (workflow based system) and the other for all of the
comments made for each workflow (for this report they are called
ECO). The queries work just fine if they run seperatly in the report,
but if I choose to group them so that there is a expand or cllapse
button under the user then I get the following errors:
[rsPostSortAggregateInGroupFilterExpression] A grouping filter
expression for the table =18table1=19 uses aggregate function First, Last
or Previous. These aggregate functions cannot be used in grouping
filters.
[rsAggregateInGroupExpression] A group expression for the table
'table1' includes an aggregate function. Aggregate functions cannot
be used in group expressions.
Build complete -- 2 errors, 0 warnings
The following are my new datasets:
-- dataset 1 selecting all of the ECOs that are in pending status
SELECT dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
dbo.ChangeEntry.Number as [ECO Number],
dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [Full
Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
UserProfile_1.Email AS [Initiator's Email],
dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
dbo.ChangeSignoff.StageOrder,
dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
FROM dbo.ChangeEntry INNER JOIN
dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
INNER JOIN
dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
INNER JOIN
dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
JOIN
dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
JOIN
dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
UserProfile_1.ID INNER JOIN
dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
JOIN
dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
WHERE dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D 1
AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
-- dataset 2 for selecting all of the comments made for each ECO in
pending status
SELECT ChangeType.Type, SignoffStages.StageName AS [Stage Name],
ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
UserProfile.FullName AS [Full Name],
UserProfile.Email, UserProfile_1.FullName AS Initiator,
UserProfile_1.Email AS [Initiator's Email],
ChangeEntry.RaisedOn AS Initiated,
ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
ChangeSignoff.DateSigned, ChangeEntry.Reason,
ChangeEntry.Description, ChangeStatus.Status,
ChangeComments.Comment
FROM ChangeEntry INNER JOIN
ChangePriority ON ChangeEntry.Priority =3D
ChangePriority.ID INNER JOIN
ChangeSignoff ON ChangeEntry.ID =3D
ChangeSignoff.ECOID INNER JOIN
UserProfile ON ChangeSignoff.UserID =3D
UserProfile.ID INNER JOIN
ChangeType ON ChangeEntry.ChangeType =3D
ChangeType.ID INNER JOIN
UserProfile AS UserProfile_1 ON
ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
SignoffStages ON ChangeSignoff.StageID =3D
SignoffStages.ID INNER JOIN
ChangeStatus ON ChangeEntry.Status =3D
ChangeStatus.ID INNER JOIN
ChangeComments ON dbo.ChangeSignoff.ECOID =3D
dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
ChangeComments.UserID
WHERE dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D 1
AND
ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
Thank you for all of your help in advanceOn Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> Hi,
> I am trying to create a drilthrough report in report designer. =A0I know
> ho to do it if I have a single query but I do not know how to do it if
> I have multiple. =A0What is happening is that I tried to create a signle
> query where I can then create a groupings and then have the report
> drill through the groupings but while trying to create my query I
> found that I could not create the query that I wanted being that I was
> getting duplicate results in my comments column (I wanted to also add
> the comments). =A0If the user did not make a comment then it was
> automatically added with the first comment made in the for that
> grouping.
> So I decided to make two queries. =A0One is for selecting all of the
> pending items (workflow based system) and the other for all of the
> comments made for each workflow (for this report they are called
> ECO). =A0The queries work just fine if they run seperatly in the report,
> but if I choose to group them so that there is a expand or cllapse
> button under the user then I get the following errors:
> [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> expression for the table =A0table1 =A0uses aggregate function First, Last
> or Previous. =A0These aggregate functions cannot be used in grouping
> filters.
> [rsAggregateInGroupExpression] A group expression for the table
> 'table1' includes an aggregate function. =A0Aggregate functions cannot
> be used in group expressions.
> Build complete -- 2 errors, 0 warnings
> The following are my new datasets:
> -- dataset 1 selecting all of the ECOs that are in pending status
> SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> dbo.ChangeEntry.Number as [ECO Number],
> =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [=Full
> Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> dbo.ChangeSignoff.StageOrder,
> =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> FROM =A0dbo.ChangeEntry INNER JOIN
> dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
> INNER JOIN
> dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> INNER JOIN
> dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
> JOIN
> dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
> JOIN
> dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> UserProfile_1.ID INNER JOIN
> dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> JOIN
> dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> -- dataset 2 for selecting all of the comments made for each ECO in
> pending status
> SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],
> ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Full =Name],
> UserProfile.Email, UserProfile_1.FullName AS Initiator,
> UserProfile_1.Email AS [Initiator's Email],
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Initia=ted,
> ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> ChangeSignoff.DateSigned, ChangeEntry.Reason,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Chang=eStatus.Status,
> ChangeComments.Comment
> FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntry.=Priority =3D
> ChangePriority.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry.I=D =3D
> ChangeSignoff.ECOID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff.U=serID =3D
> UserProfile.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Chan=geType =3D
> ChangeType.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1 O=N
> ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSignoff=.StageID =3D
> SignoffStages.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.St=atus =3D
> ChangeStatus.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.ChangeSi=gnoff.ECOID =3D
> dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> ChangeComments.UserID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND
> ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> Thank you for all of your help in advance
just an idea but unsure how to do it
I was wonder if I could use expressions in the second query that will
link each comment to the user
My report design is basically
1st row =3D header
2nd row =3D grouping it just shows the ECO number and when you click on
the + it show the user title, username and the date they signed the
ECO (if they signed it) - This works
3rd row =3D grouping: it shows the comments when you click on the user
cell (+ symbol). right now it will show the first comment on all
because it does not know where they go. This is the second query
I am not sure how I can link thw 3rd row with the user. I figure
maybe I could use an expression but I am not sure how to do it. I am
new to report builder
Thank you for all of your help|||On Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> Hi,
> I am trying to create a drilthrough report in report designer. =A0I know
> ho to do it if I have a single query but I do not know how to do it if
> I have multiple. =A0What is happening is that I tried to create a signle
> query where I can then create a groupings and then have the report
> drill through the groupings but while trying to create my query I
> found that I could not create the query that I wanted being that I was
> getting duplicate results in my comments column (I wanted to also add
> the comments). =A0If the user did not make a comment then it was
> automatically added with the first comment made in the for that
> grouping.
> So I decided to make two queries. =A0One is for selecting all of the
> pending items (workflow based system) and the other for all of the
> comments made for each workflow (for this report they are called
> ECO). =A0The queries work just fine if they run seperatly in the report,
> but if I choose to group them so that there is a expand or cllapse
> button under the user then I get the following errors:
> [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> expression for the table =A0table1 =A0uses aggregate function First, Last
> or Previous. =A0These aggregate functions cannot be used in grouping
> filters.
> [rsAggregateInGroupExpression] A group expression for the table
> 'table1' includes an aggregate function. =A0Aggregate functions cannot
> be used in group expressions.
> Build complete -- 2 errors, 0 warnings
> The following are my new datasets:
> -- dataset 1 selecting all of the ECOs that are in pending status
> SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> dbo.ChangeEntry.Number as [ECO Number],
> =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as [=Full
> Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> dbo.ChangeSignoff.StageOrder,
> =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> FROM =A0dbo.ChangeEntry INNER JOIN
> dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID
> INNER JOIN
> dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> INNER JOIN
> dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER
> JOIN
> dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER
> JOIN
> dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> UserProfile_1.ID INNER JOIN
> dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> JOIN
> dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> -- dataset 2 for selecting all of the comments made for each ECO in
> pending status
> SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],
> ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Full =Name],
> UserProfile.Email, UserProfile_1.FullName AS Initiator,
> UserProfile_1.Email AS [Initiator's Email],
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Initia=ted,
> ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> ChangeSignoff.DateSigned, ChangeEntry.Reason,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Chang=eStatus.Status,
> ChangeComments.Comment
> FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntry.=Priority =3D
> ChangePriority.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry.I=D =3D
> ChangeSignoff.ECOID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff.U=serID =3D
> UserProfile.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Chan=geType =3D
> ChangeType.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1 O=N
> ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSignoff=.StageID =3D
> SignoffStages.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.St=atus =3D
> ChangeStatus.ID INNER JOIN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.ChangeSi=gnoff.ECOID =3D
> dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> ChangeComments.UserID
> WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required =3D =1
> AND
> ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> Thank you for all of your help in advance
I was wonder if I could use expressions in the second query that will
link each comment to the user
My report design is basically
1st row =3D header
2nd row =3D grouping it just shows the ECO number and when you click on
the + it show the user title, username and the date they signed the
ECO (if they signed it) - This works
3rd row =3D grouping: it shows the comments when you click on the user
cell (+ symbol). right now it will show the first comment on all
because it does not know where they go. This is the second query
I am not sure how I can link thw 3rd row with the user. I figure
maybe I could use an expression but I am not sure how to do it. I am
new to report builder
Thank you for all of your help|||On Apr 28, 10:36=A0am, Tazo <whitethoma...@.gmail.com> wrote:
> On Apr 28, 9:26=A0am, Tazo <whitethoma...@.gmail.com> wrote:
>
>
> > Hi,
> > I am trying to create a drilthrough report in report designer. =A0I know=
> > ho to do it if I have a single query but I do not know how to do it if
> > I have multiple. =A0What is happening is that I tried to create a signle=
> > query where I can then create a groupings and then have the report
> > drill through the groupings but while trying to create my query I
> > found that I could not create the query that I wanted being that I was
> > getting duplicate results in my comments column (I wanted to also add
> > the comments). =A0If the user did not make a comment then it was
> > automatically added with the first comment made in the for that
> > grouping.
> > So I decided to make two queries. =A0One is for selecting all of the
> > pending items (workflow based system) and the other for all of the
> > comments made for each workflow (for this report they are called
> > ECO). =A0The queries work just fine if they run seperatly in the report,=
> > but if I choose to group them so that there is a expand or cllapse
> > button under the user then I get the following errors:
> > [rsPostSortAggregateInGroupFilterExpression] A grouping filter
> > expression for the table =A0table1 =A0uses aggregate function First, Las=t
> > or Previous. =A0These aggregate functions cannot be used in grouping
> > filters.
> > [rsAggregateInGroupExpression] A group expression for the table
> > 'table1' includes an aggregate function. =A0Aggregate functions cannot
> > be used in group expressions.
> > Build complete -- 2 errors, 0 warnings
> > The following are my new datasets:
> > -- dataset 1 selecting all of the ECOs that are in pending status
> > SELECT =A0dbo.ChangeType.Type, SignoffStages.StageName as [Stage Name],
> > dbo.ChangeEntry.Number as [ECO Number],
> > =A0 =A0 =A0 =A0 dbo.ChangePriority.Priority, dbo.UserProfile.FullName as= [Full
> > Name], dbo.UserProfile.Email, UserProfile_1.FullName AS Initiator,
> > =A0 =A0 =A0 =A0 UserProfile_1.Email AS [Initiator's Email],
> > dbo.ChangeEntry.RaisedOn as Initiated, dbo.ChangeSignoff.StageActive,
> > dbo.ChangeSignoff.StageOrder,
> > =A0 =A0 =A0 =A0 dbo.ChangeSignoff.DateSigned, dbo.ChangeEntry.Reason,
> > dbo.ChangeEntry.Description, dbo.ChangeStatus.Status
> > FROM =A0dbo.ChangeEntry INNER JOIN
> > dbo.ChangePriority ON dbo.ChangeEntry.Priority =3D dbo.ChangePriority.ID=
> > INNER JOIN
> > dbo.ChangeSignoff ON dbo.ChangeEntry.ID =3D dbo.ChangeSignoff.ECOID
> > INNER JOIN
> > dbo.UserProfile ON dbo.ChangeSignoff.UserID =3D dbo.UserProfile.ID INNER=
> > JOIN
> > dbo.ChangeType ON dbo.ChangeEntry.ChangeType =3D dbo.ChangeType.ID INNER=
> > JOIN
> > dbo.UserProfile UserProfile_1 ON dbo.ChangeEntry.RaisedBy =3D
> > UserProfile_1.ID INNER JOIN
> > dbo.SignoffStages ON ChangeSignoff.StageID =3D SignoffStages.ID INNER
> > JOIN
> > dbo.ChangeStatus ON dbo.ChangeEntry.Status =3D dbo.ChangeStatus.ID
> > WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required ==3D 1
> > AND ChangeType.Type like '%CPD%' AND ChangeStatus.Status =3D 'Pending'
> > ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> > -- dataset 2 for selecting all of the comments made for each ECO in
> > pending status
> > SELECT =A0 =A0 ChangeType.Type, SignoffStages.StageName AS [Stage Name],=
> > ChangeEntry.Number AS [ECO Number], ChangePriority.Priority,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile.FullName AS [Ful=l Name],
> > UserProfile.Email, UserProfile_1.FullName AS Initiator,
> > UserProfile_1.Email AS [Initiator's Email],
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.RaisedOn AS Init=iated,
> > ChangeSignoff.StageActive, ChangeSignoff.StageOrder,
> > ChangeSignoff.DateSigned, ChangeEntry.Reason,
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeEntry.Description, Cha=ngeStatus.Status,
> > ChangeComments.Comment
> > FROM =A0 =A0 =A0 =A0 ChangeEntry INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangePriority ON ChangeEntr=y.Priority =3D
> > ChangePriority.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeSignoff ON ChangeEntry=.ID =3D
> > ChangeSignoff.ECOID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile ON ChangeSignoff=.UserID =3D
> > UserProfile.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeType ON ChangeEntry.Ch=angeType =3D
> > ChangeType.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 UserProfile AS UserProfile_1= ON
> > ChangeEntry.RaisedBy =3D UserProfile_1.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SignoffStages ON ChangeSigno=ff.StageID =3D
> > SignoffStages.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeStatus ON ChangeEntry.=Status =3D
> > ChangeStatus.ID INNER JOIN
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ChangeComments ON dbo.Change=Signoff.ECOID =3D
> > dbo.ChangeComments.ECOID AND ChangeSignoff.UserID =3D
> > ChangeComments.UserID
> > WHERE =A0dbo.ChangeEntry.Expired =3D 0 and dbo.ChangeSignoff.Required ==3D 1
> > AND
> > ChangeType.Type like 'CPD%' AND ChangeStatus.Status =3D 'Pending'
> > ORDER BY dbo.ChangeEntry.Number, dbo.ChangeSignoff.StageOrder
> > Thank you for all of your help in advance
> just an idea but unsure how to do it
> I was wonder if I could use expressions in the second query that will
> link each comment to the user
> My report design is basically
> 1st row =3D header
> 2nd row =3D grouping it just shows the ECO number and when you click on
> the + it show the user title, username and the date they signed the
> ECO (if they signed it) - This works
> 3rd row =3D =A0grouping: it shows the comments when you click on the user
> cell (+ symbol). right now it will show the first comment on all
> because it does not know where they go. This is the second query
> I am not sure how I can link thw 3rd row with the user. =A0I figure
> maybe I could use an expression but I am not sure how to do it. =A0I am
> new to report builder
> Thank you for all of your help- Hide quoted text -
> - Show quoted text -
OK, well I figured it out......
I figured out that you cannot have a report utilize the different
datasets and link them together in a grouping (creating a drillthough
report). Instead what you need to do is create another report that
utilizes the same datasource or a datasource that has data that can
relate the datasource on the main report and set parameters in it. In
the layout view just use textboxes and drag the operated field. From
there you go back to the main report and drag the subreport control in
one of the cells and click properties. Select the second report that
you created and then click the parameters tab. In the parameter name
section use the dropdown menu and the select the parameters (the
dropdown menu will auto fill with the parameters from the subreport).
In the parameters value field select the column that you want to pass
to the subreport.
=46rom there is it all the same for creating a drillthough report
Everything should work out perfectly; although there migh be another
way.
Thank You

Help with simple query involving 3 tables

Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:

create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go

create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go

create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go

I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @.MinDate - @.MaxDate)

Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--- ------ ----- ------ --
HR 5 7 12 .84
Finance 10 15 25 1.75

So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.

One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!SELECT U.name, sum(J.NumberPrintedPages), sum(J.NumberPrintedPages * P.PaperCost)
FROM Jobs J INNER JOIN User_Tbl U ON J.User_ID = U.ID
INNER JOIN Printer_Tbl P ON J.Printer_ID = P.ID
GROUP BY U.Name

This example shows, how to join your tables, and how to return two of your fields. Since a user may have used 0 to n printers in his jobs, there isn't a clear indication of your fields #PagesPrinter1 and #PagesPrinter2.|||He wants a crosstab query.

Nicomachus, look up CROSSTAB in Books Online and you will see an excelent example of how to accomplish this using CASE statements. Unfortunately, it requires considerable programming to make your crosstab queries dynamic as the number of columns (printers) changes.
Supposedly this feature will be built into the next version of SQL Server, but in any case when you make your output dynamic you are going to have a hard time building reports around it, because the output format will not be consistent.

You are really best served by outputting your data in a standard normalized format and then letting your reporting application (Crystal, Access, whatever...) handle formatting as a crosstab.

Help with roles in SQL Server Reporting Services 2005

I know how to create roles but how do you add users to them, and then roles to different levels? This section of MSDN doesn't tell much

http://msdn2.microsoft.com/en-us/library/ms157397(en-US,SQL.90).aspx

You add users to the roles by navigating to the item you wish to secure. You then click on the property tab, then the security sub-tab. From here you can click on 'New Role Assignment'. Now you can add a user or group and assign the role you wish for them to have on this item.

You can also do this at the site level for the site level permissions. Click on 'Site Settings' in the upper right hand corner. Then click on 'Configure site-wide security'. From here you can again add users and assign them different system roles.

-Daniel

help with restore filegroup

hi !
I need help with restore a filegroup. Below are steps
which I made, what is wrong '
Steps:
- create a new filegroup "SE" and assign to it a few
tables. - OK
- make a backup new created filegroup "SE" - OK
- make a backup log file - OK
- drop one table "A1" from filegroup "SE" - OK
- make a backup log file (necessary to restore)- OK
- make a restore filegroup "SE" and log files - OK
my question is why after restore filegroup I haven't
droppped table "A1" '
I readed a documentation on microsoft - I think that all
is done ok.
Maybe I missing some details or something else '
thank for any answer, and sorry for my english The table A! should not be there after the file group restore. Are you
saying that it is there? If so, can you please post the CREATE DATABASE,
CREATE TABLE, ALTER DATABASE, BACKUP and RESTORE statements with which we
can reproduce this? It is always easier to talk about these things when
having statements available instead of guessing what you are doing (and
possibly how you are clicking etc in Enterprise Manager).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rafal" <anonymous@.discussions.microsoft.com> wrote in message
news:198701c410c8$0e2a66e0$3501280a@.phx.gbl...
> hi !
> I need help with restore a filegroup. Below are steps
> which I made, what is wrong '
> Steps:
> - create a new filegroup "SE" and assign to it a few
> tables. - OK
> - make a backup new created filegroup "SE" - OK
> - make a backup log file - OK
> - drop one table "A1" from filegroup "SE" - OK
> - make a backup log file (necessary to restore)- OK
> - make a restore filegroup "SE" and log files - OK
> my question is why after restore filegroup I haven't
> droppped table "A1" '
> I readed a documentation on microsoft - I think that all
> is done ok.
> Maybe I missing some details or something else '
> thank for any answer, and sorry for my english |||hi again
thanks for answer Tibor,
Where is my problem ? - I have a database where are some
tables. A few from them grow up much more than rest. My
problem is to backup often tables where is bigger increase
of rows. Then I created a group and assigned to it these
tables with big increase of rows.
What I want to do ? - I want to backup only one group
which I created.
Here is the example code that I execute in QueryAnalyzer.
All of them are executed without errors but result is not
satisfactory
Steps:
1. create database:
CREATE DATABASE TEST
ON PRIMARY
( NAME = TEST_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\testDat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP TestGroup1
( NAME = TESTGROUP_dat,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\TestGroupDat.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'TEST_log',
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\TestLog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
2. use database
use test
3. create tables in database
first
create table A1 (
id int,
name char(50),
age int
)
ON 'TestGroup1'
second
create table A2(
id int,
field char(50)
)
ON 'PRIMARY'
4. insert example data
insert a2 values (1,'third')
insert a1 values (1,'Czesiek',12)
insert a1 values (2,'Wieseik',23)
insert a1 values (3,'Misiek',42)
5. backup interesting group
backup database test filegroup = 'testgroup1'
to disk = 'c:\testdb\gr1.bak'
6.backup log
backup log test
to disk ='c:\testdb\testlog.log'
7. delete data on table a1 (simulate losing data)
delete from a1 where age > 12
8. backup log (needed to restore filegroup)
backup log test
to disk = 'c:\testdb\beforRestore.log'
9. restore group
restore database test
file = 'testgroup_dat',
filegroup ='testgroup1'
from disk = 'c:\testdb\gr1.bak'
10. restore log
restore log test
from disk = 'c:\testdb\testlog.log'
with norecovery
11.restore last log
restore log test
from disk = 'c:\testdb\beforrestore.log'
with recovery
Is it done in a good order or not ?
Why after restore group and all log files I haven't
deleted in step 7 data ?
any suggestion ?
where is bug ?
thanks, rafal|||I'm not 100% certain on what you want to achieve, but I guess that you want
to undo the deletions. However,
you cannot use filegroup backup for this. When you do a restore of a partial
backup (file or filegroup), you
have to restore all log backups taken after that point in time, including th
at last one. No point in time, as
SQL server wouldn't know how to synchronize the work done against the filegr
oup (which you do not restore in
full) to the other part of the database. This is all documented in Books Onl
ine. If you want to communicate
this further, please use my below script. I have revised it a bit, so you do
n't have to create directories,
and also so you can execute it all in one go.
DROP DATABASE TEST
GO
CREATE DATABASE TEST
ON PRIMARY
( NAME = TEST_dat,
FILENAME = 'c:\testDat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP TestGroup1
( NAME = TESTGROUP_dat,
FILENAME = 'c:\TestGroupDat.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'TEST_log',
FILENAME = 'c:\TestLog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--2. use database
USE test
--3. create tables in databasefirst
create table A1 ( id int, name char(50), age int ) ON 'TestGroup1'
--second
create table A2( id int, field char(50) ) ON 'PRIMARY'
--4. insert example data
insert a2 values (1,'third')
insert a1 values (1,'Czesiek',12)
insert a1 values (2,'Wieseik',23)
insert a1 values (3,'Misiek',42)
--5. backup interesting group
backup database test filegroup = 'testgroup1' to disk = 'c:\gr1.bak' WITH IN
IT
--backup database test to disk = 'c:\gr1.bak' WITH INIT
--6.backup log
backup log test to disk ='c:\testlog.log' WITH INIT
--7. delete data on table a1 (simulate losing data)
BEGIN TRAN x WITH MARK 'x'
delete from a1 where age > 12
COMMIT TRAN
--8. backup log (needed to restore filegroup)
backup log test to disk = 'c:\beforRestore.log' WITH INIT
--8.5 use master
USE master
--9. restore group
restore database test filegroup ='testgroup1' from disk = 'c:\gr1.bak'
--restore database test file = 'testgroup_dat', filegroup ='testgroup1' fro
m disk = 'c:\gr1.bak'
--restore database test from disk = 'c:\gr1.bak' with norecovery
--10. restore log
restore log test from disk = 'c:\testlog.log' with norecovery
--11.restore last log
restore log test from disk = 'c:\beforrestore.log' with recovery
, STOPBEFOREMARK = 'x'
GO
--Is the data still there?
SELECT * FROM test..a1
SELECT * FROM test..a2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"rafal" <anonymous@.discussions.microsoft.com> wrote in message news:1c4501c411a5$12e74940$3
a01280a@.phx.gbl...
> hi again
> thanks for answer Tibor,
> Where is my problem ? - I have a database where are some
> tables. A few from them grow up much more than rest. My
> problem is to backup often tables where is bigger increase
> of rows. Then I created a group and assigned to it these
> tables with big increase of rows.
> What I want to do ? - I want to backup only one group
> which I created.
> Here is the example code that I execute in QueryAnalyzer.
> All of them are executed without errors but result is not
> satisfactory
> Steps:
> 1. create database:
> CREATE DATABASE TEST
> ON PRIMARY
> ( NAME = TEST_dat,
> FILENAME = 'c:\program files\microsoft sql
> server\mssql\data\testDat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
> FILEGROUP TestGroup1
> ( NAME = TESTGROUP_dat,
> FILENAME = 'c:\program files\microsoft sql
> server\mssql\data\TestGroupDat.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'TEST_log',
> FILENAME = 'c:\program files\microsoft sql
> server\mssql\data\TestLog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> 2. use database
> use test
> 3. create tables in database
> first
> create table A1 (
> id int,
> name char(50),
> age int
> )
> ON 'TestGroup1'
> second
> create table A2(
> id int,
> field char(50)
> )
> ON 'PRIMARY'
> 4. insert example data
> insert a2 values (1,'third')
> insert a1 values (1,'Czesiek',12)
> insert a1 values (2,'Wieseik',23)
> insert a1 values (3,'Misiek',42)
> 5. backup interesting group
> backup database test filegroup = 'testgroup1'
> to disk = 'c:\testdb\gr1.bak'
> 6.backup log
> backup log test
> to disk ='c:\testdb\testlog.log'
> 7. delete data on table a1 (simulate losing data)
> delete from a1 where age > 12
> 8. backup log (needed to restore filegroup)
> backup log test
> to disk = 'c:\testdb\beforRestore.log'
> 9. restore group
> restore database test
> file = 'testgroup_dat',
> filegroup ='testgroup1'
> from disk = 'c:\testdb\gr1.bak'
> 10. restore log
> restore log test
> from disk = 'c:\testdb\testlog.log'
> with norecovery
> 11.restore last log
> restore log test
> from disk = 'c:\testdb\beforrestore.log'
> with recovery
>
> Is it done in a good order or not ?
> Why after restore group and all log files I haven't
> deleted in step 7 data ?
> any suggestion ?
> where is bug ?
> thanks, rafal

help with report

Hi everybody!

Need to create report according to example below

User will input start & end date

From Date:______ ToDate:________

Then the report should create output like this:

CustomerID: Number of orders:

John 5

Jim 12

Aileen 21

I tried to accomplish that with Northwind / Orders, had no success.

Thank you in advance.

Alex

Is your question about how to write the SELECT statement to retrive the data, or how to use Reporting Services to create a formatted report?

It would help to tell us what you've tried; that might give us a clue why you've not been successful.

Don|||

Hi Don,

my question is about how to write the SELECT statement to retrive the data

Thanks for the reply

|||You're going to keep what you've tried a close secret, eh? Heh.

This does what you want, except that it doesn't include the join to get the customer name:

SELECT [CustomerID]
 ,COUNT(*)AS [Numberof orders]
FROM [Orders]
WHERE OrderDateBETWEEN'6/1/1997'AND'6/30/1997'GROUP BY CustomerID

Don|||

Thanks again Don,

I did tried to use that Select string, I have no errors but also no results.

I am using datagrid to display the result of the query.

This is the code that I use as ASP.NET, please take a pick.

I must to admit that I'm very green in SQL area.

<%@. Page Language="VB" %><%@. import Namespace="System.Data.SqlClient" %><%@. import Namespace="System.Data.SqlTypes" %><script runat="server"> shared public dim startDate as date shared public dim endDate as date sub load_page() startDate = "6/1/1997" endDate = "6/30/1997" dgOrders.DataSource = orders(startDate, endDate ) dgOrders.DataBind() end sub Function orders(ByVal fromDate As Date, ByVal toDate As Date) As System.Data.IDataReader Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Northwind'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim orderNumb as integer Dim queryString As String queryString = "SELECT [Orders].[CustomerID] ,COUNT(*) AS [orderNumb] FROM [Orders] WHERE OrderDate BETWEEN startDate AND endDate GROUP BY CustomerID" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_fromDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_fromDate.ParameterName = "@.fromDate" dbParam_fromDate.Value = fromDate dbParam_fromDate.DbType = System.Data.DbType.DateTime dbCommand.Parameters.Add(dbParam_fromDate) Dim dbParam_toDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_toDate.ParameterName = "@.toDate" dbParam_toDate.Value = toDate dbParam_toDate.DbType = System.Data.DbType.DateTime dbCommand.Parameters.Add(dbParam_toDate) dbConnection.Open Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Return dataReader End Function</script><html><head></head><body> <form runat="server"> <p> </p> <p> <asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundColumn DataField="orderNumb" HeaderText="CustomerID"></asp:BoundColumn> <asp:BoundColumn DataField="Orders" HeaderText="Number of Orders"></asp:BoundColumn> </Columns> </asp:DataGrid> </p> </form></body></html>

|||

try this:

BETWEEN @.startDate AND @.endDate

|||Actually, it would be

BETWEEN @.fromDateAND @.toDate

based on what are apparently the parameter names.

Don|||

Thank you guys for helping me here,

i tried that:BETWEEN @.fromDateAND @.toDate

still get empti web page, no errors though.

I probably missing somthing here, but i don't where to look cause there is no compilation error of any kind.

Thanks again

|||

Can you try this:

<asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="True"> </asp:DataGrid>

You don't have the orders column in your data source.

|||

Thank Limno

did tried that - get the same result - blank web page.

may be using the datagrid isn't such a good idea for this case?

just don't know how to display the query result in any other way.

Alex

|||

The datareader for the datagrid looks fine to me. Can you confirm that you can run the sql query in your database with the results you are expecting? (hard code your dates in the query).

If you can get something from your database, we will be close to get something in the code.

|||

Hi Limno,

I did try this Select statement in SQL Query Analiser:

SELECT [CustomerID]
     ,COUNT(*)AS [Numberof orders]
 FROM [Orders]
WHERE OrderDateBETWEEN'6/1/1997'AND'6/30/1997'

GROUP BY CustomerID

and did get the following result:

ANTON 1
AROUT 1
BERGS 1
BLAUS 1
BLONP 3
BSBEV 1
ERNSH 1
FAMIA 1
FOLKO 1
...

I went through my code again and again but dont find the problem.

Please advice

Alex

|||

Here you go:

<%@. Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

Public Shared startDate As Date
Public Shared endDate As Date

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

startDate = "6/1/1997"
endDate = "6/30/1997"
dgOrders.DataSource = orders(startDate, endDate)
dgOrders.DataBind()
End Sub

Function orders(ByVal fromDate As Date, ByVal toDate As Date) As System.Data.IDataReader
Dim connectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("Personal").ConnectionString
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)


Dim queryString As String
queryString = "SELECT [Orders].[CustomerID] ,COUNT(*) AS [orderNumb] FROM [Orders] WHERE OrderDate BETWEEN @.startDate AND @.endDate GROUP BY CustomerID"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_fromDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_fromDate.ParameterName = "@.startDate"
dbParam_fromDate.Value = fromDate
dbParam_fromDate.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_fromDate)

Dim dbParam_toDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_toDate.ParameterName = "@.endDate"
dbParam_toDate.Value = toDate
dbParam_toDate.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_toDate)

dbConnection.Open()
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundColumn DataField="CustomerID" HeaderText="CustomerID"></asp:BoundColumn>
<asp:BoundColumn DataField="orderNumb" HeaderText="Number of Orders"></asp:BoundColumn>
</Columns>
</asp:DataGrid>

</div>
</form>
</body>
</html>

|||

You are great Limno!

That works right from the beginning.

Didn't had the chance to examine the code yet, I'm curies how did you find the problem?

Oh…, is this two lines Must have in my code?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

Thank a lotLimno

Alex

|||

Hi Alex:

Actually, your page load event syntax is not right. It would save you a lot of headaches to use the IDE's intellisence. Pick the Page from left dropdown list , then find the event you want from right dropdown list.

Not the two lines you think. you can test them out.

Help with Relational Division

I am trying to create a matrix of counts to help identify our data holdings.
Thanks to Joe Celko I now know I need to perform relational division to find
datasets that match the criteria set up in a secondary table.
I'd be happy to initially just get the counts for each category, as I could
pass the "DSType" parameter in on the fly.
Problem is I can't figure out how from the "Plane/Hanger" example how to
form the relational division query to come up with the counts. Or even a
view listing the datasets that match each "Category"s "Topic" criteria.
There are 4 main tables involved:
-Datasets that we provide information on.
-Types of data that they hold (Regional, Historical, Global, etc.)
-Topics that describe the datasets (Ocean, Carbon, Sea Level, etc.)
-GOOS Matrix that lists Categories that we want to correlate the datasets to
The SQL script to create the tables and insert data into them is at:
http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
A PDF of the relationships between the tables is at:
http://oceanic.cms.udel.edu/matrix/..._GOOSMatrix.pdf
And a PDF of the desired matrix view is at:
http://oceanic.cms.udel.edu/matrix/...trix_Output.pdf
TIA,
D
--
"If Pro is the opposite of Con, does that mean that Congress is the opposite
of Progress?"
--GallegherDoes this help? (using Joes's example):
CREATE TABLE PilotSkills (pilot CHAR(15) NOT NULL, plane CHAR(15) NOT
NULL, PRIMARY KEY (pilot, plane));
CREATE TABLE Hangar (plane CHAR(15) NOT NULL PRIMARY KEY);
INSERT INTO PilotSkills (pilot, plane)
SELECT 'Celko', 'Piper Cub' UNION ALL
SELECT 'Higgins', 'B-52 Bomber' UNION ALL
SELECT 'Higgins', 'F-14 Fighter' UNION ALL
SELECT 'Higgins', 'Piper Cub' UNION ALL
SELECT 'Jones', 'B-52 Bomber' UNION ALL
SELECT 'Jones', 'F-14 Fighter' UNION ALL
SELECT 'Smith', 'B-1 Bomber' UNION ALL
SELECT 'Smith', 'B-52 Bomber' UNION ALL
SELECT 'Smith', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'B-1 Bomber' UNION ALL
SELECT 'Wilson', 'B-52 Bomber' UNION ALL
SELECT 'Wilson', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'F-17 Fighter' ;
INSERT INTO Hangar (plane)
SELECT 'B-1 Bomber' UNION ALL
SELECT 'B-52 Bomber' UNION ALL
SELECT 'F-14 Fighter' ;
SELECT PS1.pilot,
COUNT(PS1.plane) total_skills,
T.total_planes
FROM PilotSkills AS PS1, Hangar AS H1,
(SELECT COUNT(plane) FROM Hangar) AS T(total_planes)
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot, T.total_planes ;

> The SQL script to create the tables and insert data into them is at:
> http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
That's not up to the task in my opinion, which is why I've not used it.
No alternate keys and mostly nullable columns. IDENTITY should not be
the only key of any table and it's very hard to write and verify
queries against such a schema.
David Portas
SQL Server MVP
--

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 nulls and addition

Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together:

Col A Col B Col C

Row1 1 1 2

Row2 2 3 5

Here is the query

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint, Testint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

insert

into @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

SELECT

player_name,min(BuyIn)as BuyIn,min(TopUp)as TopUps,min(ReBuy)as ReBuy,min(Winnings)as Winnings,min(Events)as Events,min(test)as test

FROM

@.t

GROUP

BY player_name

ORDER

BY BuyInDESC

--ORDER BY TOPUPS DESC

END

THis is where I attempt to add the coloms but I get a null result

insertinto @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

any help would be great.

You could us the ISNULL fucntion:

insertinto @.t(player_name, test)

select

player_name,(ISNULL(TopUp, 0)+ ISNULL(Rebuy, 0))as Test

from

@.t|||

Hi the is null removes the nulls but I am still unable to add the coloums together, instead of null I get 0 in the test col. I am able to add topup +topup or Buyin + Buyin and I get the result but when I try to add the different cols its null or 0 if I use your suggestion.

any idea?

|||How about using COALESCE instead of ISNULL?|||

Hi. I hav two ideas about that.

1. Declare the column 'test' as a calculated column:

declare @.table TABLE
(player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test AS (TopUp + ReBuy))

2. If you, for example SELECT the table for one player use this: "SELECT * FROM table WHERE player_name = 'player1'" and get somethiong like this:

player_name TopUp ReBuy

player1 5 NULL

player1 NULL 3

So, if you sum each row its equal to TopUp + NULL and ReBUy + NULL.

May be you need in the final select this:

SELECT (TopUP + ReBuy) FROM

(SELECT SUM(TopUP) as TopUp, SUM(ReBuy) as ReBuy FROM @.t) As t

or the other option could be to insert the first time, an after that update the rows for the player.

|||

This is most excellent, thanking you exactly what I was looking for: here is my working query with your suggestion. Thanks again a great help!!!

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

--insert into @.t (player_name, test)

--select

--player_name, (ISNULL(TopUp, 0) + ISNULL(Rebuy, 0)) as Test

--from @.t

Select

*,(TopUps+ ReBuy+ BuyIn)as Cost,(winnings-(TopUps+ ReBuy+ BuyIn))as Profit

FROM

(SELECT player_name,(ISNULL(min(BuyIn),0))as BuyIn,(ISNULL(min(TopUp),0))as TopUps,(ISNULL(min(ReBuy),0))as ReBuy,min(Winnings)as Winnings,min(Events)as Events

FROM

@.tGROUPBY player_name)as t

GROUP

BY player_name, buyin,topUps, Rebuy, winnings, events

Order

by Profitdesc

Help with query

I am looking for some suggestions as I am a little stuck trying a create a
view that will give me the correct results. Basically I need to produce a
view where document amounts do not balance to 0 and to do this I am using
the ApplyTo column. It also needs to calculate the remaining balance of a
document
CREATE TABLE [dbo].[Invoices_Payments] (
[type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocNo] [int] NULL ,
[ApplyTo] [int] NULL ,
[Amount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,50)
Using the above example, the query should return the following results
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Any help would be much appreciated.
ThanksIt seems that you want to display only one document for each set that
does not balance to zero. That's not difficult but I'm not clear just
which document of the set you want to display. For example, the
following will return the highest numbered doc in each case:
SELECT type, docno, applyto, amount
FROM dbo.Invoices_Payments AS P
WHERE docno = (SELECT MAX(docno)
FROM dbo.Invoices_Payments
WHERE applyto = P.applyto
HAVING SUM(amount)<>0)
Also, what is the primary key? Apparently you don't have one. Shouldn't
ApplyTo be declared as a foreign key?
--
David Portas
SQL Server MVP
--|||I think this may be closer to what you wanted, although perhaps there
was a typo in your data: Credit: 50 instead of Credit: -50.
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
David Portas
SQL Server MVP
--|||David
Thanks for the suggestion, I am going to run it to see if it works with the
data I have here.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks David this has really helped. I just have another question for you..
is it possible to exclude the grouping on the apply to number when it equals
0. For example
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1012,1013,100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1013,1013,-100)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1014,1013,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1015,1015,250)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Inv',1016,1016,175)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1017,1016,-50)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Credit',1018,0,-60)
INSERT INTO [dbo].[Invoices_Payments] VALUES ('Payment',1019,0,-65)
Returns the following records
Credit, 1014, 1013, -60
Inv, 1015, 1015, 250
Inv, 1016, 1016, 125
Credit, 1018, 0 -60
Payment, 1019,0 -65
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114102359.681224.83190@.z14g2000cwz.googlegroups.com...
>I think this may be closer to what you wanted, although perhaps there
> was a typo in your data: Credit: 50 instead of Credit: -50.
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
>
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
>Thanks David this has really helped. I just have another question for you..
>is it possible to exclude the grouping on the apply to number when it equals
>0. For example
(snip)
Hi Sarah,
You could adapt David's code, like this:
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
Thanks.. can I get this working in a view? Apparently you can't use UNION
in a view?
Is there anyway around this?
Thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:lp1j619m1rf6grkapt32uj177d1lqeetju@.4ax.com...
> On Fri, 22 Apr 2005 17:11:45 +0100, Sarah Kingswell wrote:
>>Thanks David this has really helped. I just have another question for
>>you..
>>is it possible to exclude the grouping on the apply to number when it
>>equals
>>0. For example
> (snip)
> Hi Sarah,
> You could adapt David's code, like this:
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes, you can use UNION in a view. Maybe it's just that the GUI you are
using prevents you doing this? Try creating the view in Query Analyzer:
CREATE VIEW foo
AS
SELECT P.type, P.docno, P.applyto, Q.amount
FROM dbo.Invoices_Payments AS P,
(SELECT MAX(docno) AS docno,
SUM(amount) AS amount
FROM dbo.Invoices_Payments
GROUP BY applyto
HAVING SUM(amount)<>0) AS Q
WHERE P.docno = Q.docno
AND P.applyto <> 0
UNION ALL
SELECT P.type, P.docno, P.applyto, P.amount
FROM dbo.Invoices_Payments AS P
WHERE P.applyto = 0
If you mean that you can't create an INDEXED view then you are correct.
I don't think it will be possible to create an indexed view for this
because you can't avoid a self-join or subquery.
--
David Portas
SQL Server MVP
--|||Thank a million.. I never thought to try creating the view in the analyzer.
It works a treat!
Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114420218.812239.309580@.z14g2000cwz.googlegroups.com...
> Yes, you can use UNION in a view. Maybe it's just that the GUI you are
> using prevents you doing this? Try creating the view in Query Analyzer:
> CREATE VIEW foo
> AS
> SELECT P.type, P.docno, P.applyto, Q.amount
> FROM dbo.Invoices_Payments AS P,
> (SELECT MAX(docno) AS docno,
> SUM(amount) AS amount
> FROM dbo.Invoices_Payments
> GROUP BY applyto
> HAVING SUM(amount)<>0) AS Q
> WHERE P.docno = Q.docno
> AND P.applyto <> 0
> UNION ALL
> SELECT P.type, P.docno, P.applyto, P.amount
> FROM dbo.Invoices_Payments AS P
> WHERE P.applyto = 0
> If you mean that you can't create an INDEXED view then you are correct.
> I don't think it will be possible to create an indexed view for this
> because you can't avoid a self-join or subquery.
> --
> David Portas
> SQL Server MVP
> --
>

Help with query

Hello,

I have a problem with a query i'm trying to create.

I have a site that provides courses and at the end of each lesson the
students take a test with multiple choice answers. All that
information is stored in a results table that has the following
structure:

Course_id (the id of the course that relates to a Courses table)
Module_id (the id of the module or lesson that relates to a
Modules table)
User_id (the id of the user that relates to a User table)
Total (the total score of the test)

The problem is that I'm trying to create a report that gives the
scores the students had in each test of each course but sometimes they
haven't taken all the tests and I wanted the report to reflect that.

For example, for now I can make the following report:

Course: VB 6.0
User: Paul Smith
Test 1: 20
Test 2: 30
Test 3: 50
Test 5: 40
Test 7: 30
User: Judy Clarkson
Test 1: 30
Test 2: 20

But I would like to show all the tests even the ones not taken yet,
for example:

Course VB 6.0
User Paul Smith
Test 1: 20
Test 2: 30
Test 3: 50
Test 4: --
Test 5: 40
Test 6: --
Test 7: --
User: Judy Clarkson
Test 1: 30
Test 2: 20
Test 3: --
Test 4: --
Test 5: --
Test 6: --
Test 7: --

Can someone help me please ?
Thanks,

JoaoThe best way to get help for a problem like this is to post the DDL (CREATE
TABLE statements), a few rows of sample data (as INSERT statements) and show
your required result.

Unfortunately you've missed out some relevant information. It's not clear to
me what all the keys are. It looks like you could have a many-to-many
relationship between Courses and Modules (otherwise why would you have both
columns in the Results table?) but you haven't explained how we know which
Course relates to which Module. For this example I will assume that there is
a CourseModules table that relates the two.

Here's my assumed DDL:

CREATE TABLE Users (user_id INTEGER PRIMARY KEY, user_name VARCHAR(30) NOT
NULL UNIQUE)

CREATE TABLE Courses (course_id INTEGER PRIMARY KEY)

CREATE TABLE Modules (module_id INTEGER PRIMARY KEY)

CREATE TABLE CourseModules (course_id INTEGER REFERENCES Courses
(course_id), module_id INTEGER REFERENCES Modules (module_id), PRIMARY KEY
(course_id,module_id))

CREATE TABLE CourseResults (course_id INTEGER NOT NULL REFERENCES Courses
(course_id), module_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
total_score INTEGER NOT NULL, FOREIGN KEY (course_id, module_id) REFERENCES
CourseModules (course_id,module_id), PRIMARY KEY
(course_id,module_id,user_id))

Here's the query I think you need.

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
JOIN (SELECT DISTINCT user_id, course_id
FROM CourseResults) AS N
ON M.course_id = N.course_id
JOIN Users AS U
ON U.user_id = N.user_id
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Notice that this returns all modules for any course for which a user has
completed at least 1 module. If you want to include *all* courses for every
user:

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
CROSS JOIN Users AS U
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Hope this helps.

--
David Portas
SQL Server MVP
--|||The best way to get help for a problem like this is to post the DDL (CREATE
TABLE statements), a few rows of sample data (as INSERT statements) and show
your required result.

Unfortunately you've missed out some relevant information. It's not clear to
me what all the keys are. It looks like you could have a many-to-many
relationship between Courses and Modules (otherwise why would you have both
columns in the Results table?) but you haven't explained how we know which
Course relates to which Module. For this example I will assume that there is
a CourseModules table that relates the two.

Here's my assumed DDL:

CREATE TABLE Users (user_id INTEGER PRIMARY KEY, user_name VARCHAR(30) NOT
NULL UNIQUE)

CREATE TABLE Courses (course_id INTEGER PRIMARY KEY)

CREATE TABLE Modules (module_id INTEGER PRIMARY KEY)

CREATE TABLE CourseModules (course_id INTEGER REFERENCES Courses
(course_id), module_id INTEGER REFERENCES Modules (module_id), PRIMARY KEY
(course_id,module_id))

CREATE TABLE CourseResults (course_id INTEGER NOT NULL REFERENCES Courses
(course_id), module_id INTEGER NOT NULL, user_id INTEGER NOT NULL,
total_score INTEGER NOT NULL, FOREIGN KEY (course_id, module_id) REFERENCES
CourseModules (course_id,module_id), PRIMARY KEY
(course_id,module_id,user_id))

Here's the query I think you need.

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
JOIN (SELECT DISTINCT user_id, course_id
FROM CourseResults) AS N
ON M.course_id = N.course_id
JOIN Users AS U
ON U.user_id = N.user_id
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Notice that this returns all modules for any course for which a user has
completed at least 1 module. If you want to include *all* courses for every
user:

SELECT U.user_name, M.course_id, M.module_id, R.total_score
FROM CourseModules AS M
CROSS JOIN Users AS U
LEFT JOIN CourseResults AS R
ON M.course_id = R.course_id
AND M.module_id = R.module_id
AND U.user_id = R.user_id
ORDER BY U.user_name, M.course_id, M.module_id

Hope this helps.

--
David Portas
SQL Server MVP
--|||Hi David,

Sorry about the CourseModule table. I mistakenly did not referenced it.

And thanks for your reply. It's exactly what I was looking for.