Help with SQL Query

Given the following two tables below, I need help in writing a query that would retreive only 3 or less distinct values of BDesc from tbB table for every row found in tbA.

for example:

I expect result to be:

Aid Bdesc
100 1st Desc for 100
100 2nd Desc for 100
100 3rd Desc for 100
200 1st Desc for 200
200 2nd Desc for 200
200 3rd Desc for 200
300 1st Desc for 300
300 2nd Desc for 300
300 3rd Desc for 300
400 1st Desc for 400
500 1st Desc for 500
500 3rd Desc for 500

The tables are:

use tempdb

go

set nocount on

if exists (select name from sysobjects where name = 'TbA')

Drop table TbA

Create Table TbA ( Aid int )

Insert into TbA values(100)

Insert into TbA values(200)

Insert into TbA values(300)

Insert into TbA values(400)

--select * from TbA

if exists (select name from sysobjects where name = 'TbB')

Drop table TbB

Create Table TbB ( Bid int , BDesc varchar(50) )

INSERT INTO TbB Values(100, '1st Desc for 100')

INSERT INTO TbB Values(100, '2nd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(200, '2nd Desc for 200')

INSERT INTO TbB Values(200, '3rd Desc for 200')

INSERT INTO TbB Values(200, '4th Desc for 200')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(300, '1st Desc for 300')

INSERT INTO TbB Values(300, '2nd Desc for 300')

INSERT INTO TbB Values(300, '3rd Desc for 300')

INSERT INTO TbB Values(300, '4th Desc for 300')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '3rd Desc for 500')

--select * from TbB

Thanks for your help with this...

Here ya go

Code Snippet

selectdistinct TbB.*

from(selectdistinct aid from TbA)as TbA

innerjoin TbB

on TbA.Aid = TbB.Bid

and TbB.BDesc in

(selectdistincttop 3 BDesc from TbB where Bid = TbA.Aid orderby BDesc)

|||Thanks|||

How about if I wanted to get the result like this:

ColA ColB

100 1st Descfor 100, 2nd Descfor 100, 3rd Descfor 100

200 1st Descfor 200, 2nd Descfor 200, 3rd Descfor 200

300 1st Descfor 300, 2nd Descfor 300, 3rd Descfor 300

400 1st Descfor 400

500 1st Descfor 500, 3rd Descfor 500

|||hi, you can try using a udf

CREATE FUNCTION dbo.GetBDesc
(
@.AID int
)
RETURNS varchar(800)
AS
BEGIN
DECLARE @.BDesc varchar(100)
SET @.BDesc = ''
SELECT
@.BDesc = @.BDesc + BDesc + ','
FROM (SELECT DISTINCT TOP 3 * FROM TbB a WHERE a.BID = @.AID) b
WHERE BID = @.AID
ORDER BY
BDesc

IF @.BDesc <> '' SET @.BDesc = LEFT(@.BDesc, LEN(@.BDesc) - 1)

RETURN @.BDesc

END

GO

select *
, dbo.GetBDesc(AID)
from tba|||

If you use SQL Server 2005 you dont need a function...

Here the sample,

Code Snippet

Create Table #TableA(

Aid int );

Insert into #TableA values(100)

Insert into #TableA values(200)

Insert into #TableA values(300)

Insert into #TableA values(400)

Insert into #TableA values(500)

Create Table #TableB(

Bid int

,BDesc varchar(50)

)

INSERT INTO #TableB Values(100, '1st Desc for 100')

INSERT INTO #TableB Values(100, '2nd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(200, '2nd Desc for 200')

INSERT INTO #TableB Values(200, '3rd Desc for 200')

INSERT INTO #TableB Values(200, '4th Desc for 200')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(300, '1st Desc for 300')

INSERT INTO #TableB Values(300, '2nd Desc for 300')

INSERT INTO #TableB Values(300, '3rd Desc for 300')

INSERT INTO #TableB Values(300, '4th Desc for 300')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '3rd Desc for 500')

;With DistinctData

as

(

Select Distinct A.Aid,B.BDesc from #TableA A Join #TableB B On A.Aid =B.Bid

),

RowData

as

(

Select Aid,Bdesc,Row_Number() Over(Partition By Aid Order By BDesc) RowID From DistinctData

)

/*

Select

Aid,

BDesc

From

RowData

Where

RowID <=3

*/

Select Distinct

Aid

,Substring((Select ',' + BDesc as [text()] From RowData Sub Where Sub.Aid=Main.Aid And Sub.RowId<=3 For XML Path(''), Elements),2,8000) as Descs

From

RowData Main

|||MG,

How about these two queries (both require SQL 2005)

select a.aid, b.bdesc
from tbA a
cross apply
(select distinct top (3) bdesc from tbB b where b.bid = a.aid) b
;

select a.aid as ColA, stuff((select distinct top (3) ', ' + bdesc from tbB b where b.bid = a.aid order by 1 for xml path('')),1,2,'') as ColB
from tbA a
;

The second one puts them into a single column for you.

Rob|||I should have mentioned that this is for SQL 2000 and for an OLTP environment. The procedure processes approx. 20,000 rows and right now it's using cursor logic which is slowing things down, so I was looking for ways to use set based processing. The function idea is good, but again its going to be row by row processing.|||

The function approach should be a significant improvement over any cursor processing.

Is there something we're not understanding about what you want to accomplish?

|||

Hi Rhamille Golimlim,

There is an issue when using "order by" during an aggregate concatenation query.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

http://support.microsoft.com/default.aspx/kb/287515

AMB

|||thanks for the tip hunchback. would it still show a different execution plan if we put the order by inside the subquery?|||

Hi Rhamille Golimlim,

If you put the "order by" clause inside the derived table, then how are you going to be sure that the result is sorted if the only way to asure a sorted resultset is using the "order by" clause in the statement that pull the data?. It is like sorting inside a view and not using "order by" clause when you pull from the view.

Concatenating row values in T-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB

|||hi hunchback,

cool, would the xml path approach be the best work around for this scenario? or are there other alternatives or tsql hacks?

/rhamille

Help with SQL if statement and adding fields together

I have a query that I need a hand on. I am trying to add togther some
fiends based on values of another.

What I would like to add a billing total by saying more or less the
following:

SELECT labor_hours, labor_cost, expidite_fee, flat_rate,
include_repair_cost, include_cal, include_flat_rate, include_parts,
cur_bill,
(labor_hours * labor_cost) AS labor_total,
(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0)) AS cal_total,
(
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS parts_total,
(
(labor_hours * labor_cost) + expidite_fee + flat_rate +
ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0) +
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS actual_total,
(
expidite_fee
IF include_repair_cost = 1
+ (labor_hours * labor_cost)
IF include_flat_rate = 1
+ flat_rate
IF include_cal = 1
+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
BY dateCAL DESC),0)
IF include_parts = 1
+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS billing_total
FROM view_inventory
WHERE orderID=79559

I know the IF part is whacked, that's where I need the help. Is this
type of thing even possible? Or even efficent? Is it wise to subquery
for totals (not like I have a choice based on the application
requirements)? help.On 18 Mar 2005 07:56:07 -0800, Rob Kopp wrote:
(snip)
>(
>expidite_fee
>IF include_repair_cost = 1
>+ (labor_hours * labor_cost)
>IF include_flat_rate = 1
>+ flat_rate
>IF include_cal = 1
>+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
>BY dateCAL DESC),0)
>IF include_parts = 1
>+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
>repair_partsID WHERE orderID=79559),0) +
>ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
>misc_part_assocID WHERE orderID=79559),0)
>) AS billing_total
>FROM view_inventory
>WHERE orderID=79559
>I know the IF part is whacked, that's where I need the help. Is this
>type of thing even possible? Or even efficent?

Hi Rob,

You'll need to use CASE:

(
expidite_fee +
CASE WHEN include_repair_cost = 1
THEN (labor_hours * labor_cost)
ELSE 0 END +
CASE WHEN include_flat_rate = 1
THEN flat_rate
ELSE 0 END +
CASE WHEN include_cal = 1
THEN ISNULL((subquery cal_cost), 0)
ELSE 0 END +
CASE WHEN include_parts = 1
THEN ISNULL((subquery gptotal), 0) +
ISNULL((subquery gnptotal), 0)
ELSE 0 END
) AS billing_total

> Is it wise to subquery
>for totals (not like I have a choice based on the application
>requirements)? help.

Well, you can do some things to speed up the query.

Since you use the same subquery in two places, you could use a derived
table. Like this:

SELECT a, b, c, a + b + c AS GrandTotal
FROM (SELECT complicated_expression AS a,
complicated_expression AS b,
complicated expression AS c
FROM YourTable
WHERE ...) AS x

Another possibility is to use a join between your inventory table and
derived tables where the grouping has already been done:

SELECT ...,
gptotal,
...,
complicated expression using gptotal,
...
FROM view_inventory AS vi
LEFT OUTER JOIN (SELECT orderID,
SUM((qty * cost) + premium_charge) AS gptotal
FROM repair_partsID
GROUP BY orderID) AS a
ON a.orderID = vi.orderID
LEFT OUTER JOIN (...) AS b
ON b.orderID = vi.orderID
(etc)
WHERE vi.orderID = 79559

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You are the man, Hugo. I bow to your majesty.sql

Help with sproc with one parameter that can contain multiple values

I am trying to get the following procedure to work and I am getting hung up
on the @.strClaim parameter, this could be either 1 or more claim numbers
for one terminal number. I want to be able to get all the claim detail
information for, say, terminal # 1222222abc that are in claims 521, 522,
523, 530.
I don't know how to handle the @.strClaim so that the procedure will for all
claim numbers in that list.
Any help appreciated.
TIA
Nancy
Create Procedure usp_GetClaims
(@.strClaim as Char(10),
@.strTerminal as Char(30))
as
Select X_CLAIMS_NO,X_TERMINAL_NUMBER
from
dbo.X_HCFA_CLAIM
where
Cast(X_CLAIMS_NO as char(10)) IN @.strClaim
AND
X_TERMINAL_NUMBER = @.strTerminal
exec usp_GetClaims '574, 573', 'RMFAHESSSXYHLLLX'To pass a CSV list as a VARCHAR(n) parameter, you will have to use something
different. For various alternatives, refer to:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Thanks, I think I found what I needed. Great site too!
Nancy
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OVgTMF1oFHA.3316@.tk2msftngp13.phx.gbl...
> To pass a CSV list as a VARCHAR(n) parameter, you will have to use
> something different. For various alternatives, refer to:
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Anith
>

Help with SP or Function

I was trying to write this MDX but I am getting errors so maybe I should
create a stored procedure against my CUBE.
I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.
can someone provide an example?
My cube provides the total revenue for any date. I thought I could take the
avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg
'
avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg
'
and get a value but I am getting an error message.What error message? Where?
ML
http://milambda.blogspot.com/

Help with sorting strings...

I'm trying to search a database and get a list of results of
the latest values which are of type 'STRING'. How would I do it?

For instance, I've got a dataset like the one below.

Col 1 Col 2 Col 3
---------------
Dog Blue 11a
Dog Blue 11b
Cat Blue 14
Cat Red 21a
Cat Red 21b
Fish Yellow 31
Shark Black 12a
Shark Purple 21

I only want it to return the ones with the highest 'Col 3' value, so it returns something like.

Col 1 Col 2 Col 3
----------------
Dog Blue 11b
Cat Red 21b
Fish Yellow 31
Shark Purple 21

I've tried something like this:

SELECT
table.col1,
table.col2,
table.col3
FROM
table
WHERE
1 > (
SELECT
COUNT(DISTINCT table.col3)
FROM
table tab
WHERE
tab.col3 > table.col3
)

However I get the ERR: An aggregate may not appear in the WHERE clause
unless it is in a subquery contained in a HAVING clause or select
list, and the column being aggregated is an outer reference.I don't what your backend database is but this should help.

SELECT t.col1
, t.col2
, t.col3
FROM tablex t
, (SELECT tablex.col1
, max (tablex.col3) col3
FROM tablex
group by col1) g
WHERE t.col1 = g.col1
AND t.col3 = g.col3
;

or using ANSI joins

SELECT t.col1
, t.col2
, t.col3
FROM tablex t
JOIN (SELECT tablex.col1
, max (tablex.col3) col3
FROM tablex
group by col1) g
ON t.col1 = g.col1
AND t.col3 = g.col3
;|||Thanks for your help gannet.

help with sort

I have a column which has values as
1
2
2.1
3
4
2.1.1
2.1.1.1
2.1.2
2.1.3
2.2
2.1.4
2.3
2.99
2.99.1
2.1.5
2.99.1.1
2.1.6
2.100
2.100.1
2.101
2.102
2.1.7
and i need to get this sorted as
1
2
2.1
2.1.1
2.1.1.1
2.1.2
2.1.3
2.1.4
2.1.5
2.1.6
2.1.7
2.2
2.99
2.99.1
2.99.1.1
2.100
2.100.1
2.101
2.102
Is this comething doable thru sql. any help will be greatly appreciated.
Thanks
Message posted via http://www.webservertalk.com>> Is this comething doable thru sql.
Are these IP addresses or something? Is it always less than or equal to 4
digits in the string? If such sorting is a business requirement, you might
want to consider a better schema. You post somewhat exemplifies the
complexity behind queries involving tables with multiple values crammed in a
single column.
One quick & dirty way is to do:
SELECT col
FROM tbl
ORDER BY CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 4 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 3 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 2 ) AS INT ),
CAST( PARSENAME( col + REPLICATE( '.0', 3 - ( LEN( col ) -
LEN( REPLACE( col, '.', '') ) ) ), 1 ) AS INT );
You can find details about PARSENAME and REPLICATE functions in SQL Server
Books Online. The idea is a extract each component ( as identified by a
dot ) of the string and then sort them accordingly. The type conversion to
INT is to avoid character based sorting which is not what is specified in
your sample results.
Anith

Help with SELECT query

My SELECT query returns a data set, one column of which contains a set
of values corresponding to the same date. I.e. for each date in column
"Date", I have a set of numbers in column "Numbers". However, I am
only interested in getting the largest value in column "Numbers"
corresponding to each value in column "Date". How do I do that?
Thanks,

Marcomdi00@.hotmail.com (Marco) wrote in news:e5f1d809.0411190312.c9f8b07
@.posting.google.com:

> My SELECT query returns a data set, one column of which contains a set
> of values corresponding to the same date. I.e. for each date in column
> "Date", I have a set of numbers in column "Numbers". However, I am
> only interested in getting the largest value in column "Numbers"
> corresponding to each value in column "Date". How do I do that?
> Thanks,
> Marco

SELECT "Date", MAX("Numbers")AS "Largest number"
FROM sometable
GROUP BY "Date"|||Marco wrote:

> My SELECT query returns a data set, one column of which contains a set
> of values corresponding to the same date. I.e. for each date in column
> "Date", I have a set of numbers in column "Numbers". However, I am
> only interested in getting the largest value in column "Numbers"
> corresponding to each value in column "Date". How do I do that?
> Thanks,
> Marco

Look up MAX and GROUP BY.

Help with select

Hi
I have a table with year and date values
create table year_mon
(year char (6),
mon char(2)
)
insert into year_mon values ('2003',12)
insert into year_mon values ('2004',12)
insert into year_mon values ('2005',12)
insert into year_mon values ('2003',3)
insert into year_mon values ('2003',6)
insert into year_mon values ('2003',9)
insert into year_mon values ('2004',3)
insert into year_mon values ('2004',6)
How can I insert a 0 value before the mon where mon is 3,6 or 9
select year, mon from year_mon
go
gives me
2003 12
2004 12
2005 12
2003 3 --> would like the values to be displayed as 2003 03
2003 6 --> would like the values to be displayed as 2003 06
2003 9 --> would like the values to be displayed as 2003 09
etc ...
AHi,
Try the below statement
select year, right(('0'+ltrim(rtrim(mon))),2) from year_mon
Thanks
Hari
SQL Server MVP
"ajmister" <ajmister@.optonline.net> wrote in message
news:%23hNQxFwWFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>|||Try,
update year_mon
set mon = '0' + ltrim(cast(month as int))
where len(month) = 1;
AMB
"ajmister" wrote:

> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>
>|||Hi
There are several ways, this is one
select year, right('0'+rtrim(MON),2) from year_mon
go
John
"ajmister" wrote:

> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>
>|||Sorry.
select [year], right('0' + ltrim(cast(month as int)), 2) as [month]
from year_mon
AMB
"Alejandro Mesa" wrote:
> Try,
> update year_mon
> set mon = '0' + ltrim(cast(month as int))
> where len(month) = 1;
>
> AMB
> "ajmister" wrote:
>|||Thank you all. I was able to get the output using
select year,
right("0" + convert(varchar(2),mon) as mon.
from year_mon
Aj
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BAA3A38D-E203-4D5D-B39E-C97330F83F84@.microsoft.com...
> Sorry.
> select [year], right('0' + ltrim(cast(month as int)), 2) as [month]
> from year_mon
>
> AMB
> "Alejandro Mesa" wrote:
>|||You have missed the point of SQL.
The language has temporal data types, so you use them for temporal
data. Look up the concept of proper domains for data. This is not
COBOL any more; we do not use strings and numerics for this. The
second fundamental thing that you missed is that time is always modeled
as durations. The third thing is that in a tiered architecture display
and formatting are never done in the database, but belongs in the front
end.
CREATE TABLE MonthlyCalendar
(year_month CHAR (7) NOT NULL PRIMARY KEY,
month_start_date DATETIME NOT NULL,
month_end_date DATETIME NOT NULL
CHECK (month_start_date < month_end_date));|||> The
> second fundamental thing that you missed is that time is always modeled
> as durations.
I disagree. According to Snodgrass (Developing Time-Oriented Database
Applications in SQL), time data types include Instance, Interval and Period.
His
storage could be commensurate with an Interval (e.g. March 2005, May 2004).
Granted, even intervals of this nature can be stored using standard DateTime
data types.

> The third thing is that in a tiered architecture display
> and formatting are never done in the database, but belongs in the front
> end.
Agreed. The reporting engine should be doing the formatting.
Thomassql

HELP with Running value totals

HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)

Help with primary keys

I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.

If I understand the problem correctly one way to do this is to use a FULL JOIN; maybe something like:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select coalesce (aKey, bKey) as [Key],
case when aKey is null then 'Table B' else 'Table A'
end as sourceTable
from @.tableA
full join @.tableB
on aKey = bKey
where aKey is null
or bKey is null
order by coalesce (aKey, bKey)

/*
Key sourceTable
-- --
2 Table A
4 Table B
5 Table A
7 Table B
*/

Now that I think about it, a better way to do this is probably to do this differently; hang on and I'll get you a better method. This might perform a little better:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select 'TableA' as SourceTable,
aKey as [Key]
from @.tableA a
where not exists
( select 0 from @.tableB b
where aKey = bKey
)
union all
select 'TableB' as SourceTable,
bKey as [Key]
from @.tableB a
where not exists
( select 0 from @.tableA b
where aKey = bKey
)
order by [Key]

/*
SourceTable Key
-- --
TableA 2
TableB 4
TableA 5
TableB 7
*/

|||

If I understand you correctly, you want to find rows in TableA that do not exist in TableB, and conversely, rows in TableB that do not exist in TableA.

Code Snippet

SELECT
'TableA',
PKColumn
FROM TABLEA
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableB
)
UNION

SELECT
'TableB',
PKColumn
FROM TABLEB
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableA
)

If you are using SQL 2005, you could use a EXCEPT JOIN.

|||As usual, Arnie makes good points. Be aware of the EXCEPT join. In many cases it will be slower than the query Arnie put together. I try to avoid using the EXCEPT join.|||

Our (Kent's and mine) suggested solutions are virtually identical -since you are seeking PKeys, there are no duplicates -so DISTINCT is not necessary. And the query processor has to read the entire index anyway, so EXISTS and NOT IN have the same effect for this circumstance.

And if you are using SQL 2005, you could use a FULL OUTER JOIN. Example:

Code Snippet

SET NOCOUNT ON

DECLARE @.TableA table
( PKColumn int )

DECLARE @.Tableb table
( PKColumn int )

INSERT INTO @.TableA VALUES ( 1 )
INSERT INTO @.TableA VALUES ( 2 )
INSERT INTO @.TableA VALUES ( 3 )
INSERT INTO @.TableA VALUES ( 4 )
INSERT INTO @.TableA VALUES ( 5 )

INSERT INTO @.TableB VALUES ( 3 )
INSERT INTO @.TableB VALUES ( 4 )
INSERT INTO @.TableB VALUES ( 5 )
INSERT INTO @.TableB VALUES ( 6 )
INSERT INTO @.TableB VALUES ( 7 )

SELECT
'TableA' = a.PKColumn,
'TableB' = b.PkColumn
FROM @.TableA a
FULL OUTER JOIN @.TableB b
ON a.PKColumn = b.PKColumn
WHERE ( a.PKColumn IS NULL
OR b.PKColumn IS NULL
)
ORDER BY ( isnull( a.PKColumn, 0 ) + isnull( b.PKColumn, 0 ))

TableA TableB
-- --
1 NULL
2 NULL
NULL 6
NULL 7

IF this is a regular process, you might wish to check which of the possiblities is most efficient. (I'm betting on the FOJ.)

Help with Outer join

How can I join 3 tables that represent many-many realtions, in a way
that returns all the values of one table and an extra column of
true/false or null or some way to to show that the record exists or
doesnt exist in the link table.
This is so that when I pass a paramter of an author ID I can create an
array of checkboxes form the record set that are either ticked or not
ticked.
E.g for Pubs, output something like this:
author exists title
1 False 1
1 True 2
Thanks
hals_leftUSE Pubs
SELECT A.au_id, T.title,
CASE WHEN U.title_id IS NOT NULL
THEN 'Y' ELSE 'N' END AS exist
FROM Authors AS A
JOIN Titles AS T
ON A.au_id = '267-41-2394'
LEFT JOIN TitleAuthor AS U
ON U.au_id = A.au_id
AND U.title_id = T.title_id
David Portas
SQL Server MVP
--|||use pubs
Go
SELECT A.au_id, CASE WHEN EXISTS(SELECT 1 FROM TitleAuthor T
WHERE T.Au_id= A.Au_id) Then 1 Else 0 End as Exist
FROM Authors A
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:c04136bd.0502280242.5c8a6a27@.posting.google.com...
> How can I join 3 tables that represent many-many realtions, in a way
> that returns all the values of one table and an extra column of
> true/false or null or some way to to show that the record exists or
> doesnt exist in the link table.
> This is so that when I pass a paramter of an author ID I can create an
> array of checkboxes form the record set that are either ticked or not
> ticked.
> E.g for Pubs, output something like this:
> author exists title
> 1 False 1
> 1 True 2
> Thanks
> hals_left

Help with modifying my function in order to seek out and return specific values

I'll try to make this simple, but first, a few requests & info for you:

1) Do not ask the "why", just help me with my question, how to change my SQL

2) I will try to explain this in as simple terms and with enough info that I think is needed for this post

3) Do not tell me this should be done more easily in an OOP language..I have already realized this but, I don’t have time to turn back now.I want to use what I have that works, and simply modify it for now to get the results I need with your help

4) I am not using SQL 2005 so I cannot put in nice regex functions.I realized now, I should have used C# for this *** but oh well, too late to turn back, time is of the essence

5) Yes, it’s the stupidest thing, we have ProductIDs in a memo field and we’re now fixing this by inserting these ProductIDs returned back into a bridge table.I was not on this team, I would never design a table like this in the first place, but…with that said, I am assigned to fix the data.So please refrain from any remarks to that…I hear ya!I hate it too.

6) Do not assume that I know SQL well.I do know pretty advanced, but not this extreme, this is getting crazy with all these PATINDEX, CHARINDEX, and in combination/use with SUBSTRING.I am first and foremost a C# programmer, not a SQL guru by all means.

First off, the existing function works great.It takes in a ProductDescription that has ProductIDs embedded in it (again, yes, very stupid) and then this function returns a nice comma deilimited string of ProductIDs like this:

106761,106763,106791,105813

My goal, and what’s hard for me:

1) I realized I need to change this function to return ProductIDs based on product type: WAV vs. MP3

2) I need help with modifying my existing function above, changing the Substring, PatIndex, or whatever so that it checks the current ProductDescription, looks for some strings that tell you whether it’s a WAV or ProductID, and rips out WAV or MP3 productIDs specifically.

Other Information you need:

There are 2 posssible types of @.ProductDescription formats that could be passed to this function.

The # of ProductIDs vary and this function already handles that problem

Example 1:

‘These music tracks invoke the spirit<br><br><span class='product-name-no-link'>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>’

Example 2:

‘Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings.<br><br><b>Styles:</b> Dramatic,Reflective,Somber/dark<br><br><b>If you like this track, you can save more than <span style='color=#ff0000'>70%</span> by purchasing it on the following albums:</b><br><a href="ProductInfo.aspx?ProductID=106758">Hip-Hop / Urban</a><br><a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a><br><br>Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.’

Conclusion

1) So basically I assume the best way to do this would be to somehow check on the following strings in order to determine what ProductIDs to bring back in the @.result:

‘MP3</a>’

‘WAV</a>’

‘for the WAV version of this track’

‘for the MP3 version of this track’

Existing Script:

ALTER FUNCTION [dbo].[GetProductChildIDs] (

@.ProductDescription varchar(5500),

@.FileFormatvarchar(3)

)

RETURNS varchar(1000)

AS

BEGIN

DECLARE@.Location varchar(50),

@.Result varchar(1000)

SET @.Result = ''

SET @.Location = PATINDEX('%ProductID=%',@.ProductDescription)+10

WHILE @.Location > 10

BEGIN

SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription,PATINDEX('%ProductID=%',@.ProductDescription)+10, (CHARINDEX('"',@.ProductDescription,PATINDEX('%ProductID=%',@.ProductDescription)) - (PATINDEX('%ProductID=%',@.ProductDescription)+10))) + ','

SET @.ProductDescription = RIGHT(@.ProductDescription,LEN(@.ProductDescription) - @.Location)

SET @.Location = PATINDEX('%ProductID=%',@.ProductDescription)+10

END

-- Return the comma delimited string of child ProductIDs for Currrent ProductID

-- and get rid of the last comma

return substring(@.Result,1,len(@.Result)-1)

END

My start, a mock-up:

Here’s a mock-up of trying to start this.

ALTER FUNCTION [dbo].[GetProductChildIDs] (

@.ProductDescription varchar(5500),

@.FileFormatvarchar(3)

)

RETURNS varchar(1000)

AS

BEGIN

DECLARE@.Location varchar(50),

@.Result varchar(1000)

SET @.Result = ''

If @.FileFormat = 'MP3'

BEGIN

WHILE @.Location > 10

BEGIN

Get and set only the ProductIDs for MP3

END

-- Return the comma delimited string of MP3 ProductIDs

return substring(@.Result,1,len(@.Result)-1)

END

If @.FileFormat = 'WAV'

BEGIN

WHILE @.Location > 10

BEGIN

Get and set only the ProductIDs for WAV

END

-- Return the comma delimited string of WAV ProductIDs

return substring(@.Result,1,len(@.Result)-1)

END

END

Looking at the first example first...

I think if you run some sort of string split function (like Itzik's at http://www.sql.co.il/books/insidetsql2005/source_code/TechEd%202006%20Israel%20-%20Advanced%20T-SQL%20Techniques.txt - don't worry, you don't need SQL2005 for it, just be prepared to make yourself an auxiliary table of numbers), then you could split your string on "<BR>" to break it into the lines for each song.

Then you could split those lines on "ProductID=", to get something that looks like:

1.1 - 01. American Plains <a

href="ProductInfo.aspx?
1.2 - 105234">MP3</a> | <a href="ProductInfo.aspx?
1.3 - 105235">WAV</a>

2.1 - 02. Sultry Summer Night <a href="ProductInfo.aspx?
2.2 - 105236">MP3</a> | <a href="ProductInfo.aspx?
2.3 - 105237">WAV</a>

3.1 - 03. Ocean Skyline <a href="ProductInfo.aspx?
3.2 - 105238">MP3</a> | <a href="ProductInfo.aspx?
3.3 - 105239">WAV</a>

etc.

So then you should be able to quite easily pull out the song title, ID and type.

My code is here... the stuff in the comment at the start sets it up, and then the rest is to pull the data out. I haven't tried to do the second format... but I'm sure you can look at what I'm doing here and adapt it accordingly.

/*
create table dbo.Nums (n int primary key)
while (select count(*) from nums) < 1000
insert into nums select n + (select count(*) from nums) from nums

create table largetext (id int identity(1,1) primary key, largetext nvarchar(3000))
insert into largetext values (
'‘These music tracks invoke the spirit<br><br><span class=''product-name-no-link''>You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>’')

select * from largetext
*/

declare @.rowseparator nvarchar(100)
set @.rowseparator = N'<br>'

SELECT
l.id,
(n - LEN(REPLACE(LEFT(l.largetext, n), @.rowseparator, ''))) / len(@.rowseparator) + 1 AS pos,
SUBSTRING(l.largetext, n,
CHARINDEX(@.rowseparator, l.largetext + @.rowseparator, n) - n)
AS val
into #lines
FROM dbo.largetext l JOIN dbo.Nums
ON n <= LEN(l.largetext)
AND SUBSTRING(@.rowseparator + l.largetext, n, len(@.rowseparator)) = @.rowseparator

set @.rowseparator = N'ProductID='

SELECT
l.id,
l.pos as lineid,
(n - LEN(REPLACE(LEFT(l.val, n), @.rowseparator, ''))) / len(@.rowseparator) + 1 AS pos,
SUBSTRING(l.val, n,
CHARINDEX(@.rowseparator, l.val + @.rowseparator, n) - n)
AS val
into #lines2
FROM #lines l JOIN dbo.Nums
ON n <= LEN(l.val)
AND SUBSTRING(@.rowseparator + l.val, n, len(@.rowseparator)) = @.rowseparator
where l.val like '%' + @.rowseparator + '%'

select left(titles.val,charindex(N'<a href',titles.val)-2) as title,
left(pid.val,charindex(N'"',pid.val)-1) as productid,
substring(pid.val,charindex(N'>',pid.val)+1,charindex(N'</a>',pid.val)-charindex(N'>',pid.val)-1) as producttype
from #lines2 titles
join
#lines2 pid
on titles.lineid = pid.lineid
and titles.pos = 1
and pid.pos > 1

drop table #lines
drop table #lines2|||

Rob, thank you, and of course I will work on this and take maybe a different approach. I just get head spins sometimes with combining the Substring, Charindex, and Patindex together when it's getting this complex.

Thanks a lot, I'll try it from here and also make sure I understand it going forward.

Also, checked out your blog, nice! I see you're from Austrailia. My experience with people from Britain and Austrailia is that they are very pleasant people. I wish in general, programmers in the US could be more like you...instead of ego trips like I see every day here for the past 10 years in my profession with many of them.

cheers!

|||The principle behind this approach is to join it to a table of numbers. That way, you can easily analyse what's in each character. If it's not the start of the separator, then you can tell the system to ignore it.

It's very different to the way you'd do it in C#, because it's taking a set-based approach to the problem - not stepping through it bit by bit. If you insert extra rows into that largetext table, you'll see that it doesn't really take much longer.

And please don't call me 'Bob'... No-one calls me that.

Rob|||Thank you for explaining. I corrected the Rob.

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~Steven
I guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&[2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&[2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/20
05]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]
),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~StevenI guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&
1;2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&
1;2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/
2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[
2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[
2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>

Help with joining/selecting values to show

I am editing a pre-existing view.

This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.

I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.

So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.

Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.

Example:

for group X of clients...
Deposit 1 due on oct 1: $20
Deposit 2 due on oct 15: $30
Deposit 3 due on nov 15: $40
Deposit 4 due on nov 30: $50

for group Y of clients...
Deposit 1 due on Oct 30: $200
Deposit 2 due on Nov 30: $300
Deposit 3 due on Dec 30: $400

So when if I execute the view today (Nov 7th) each client from group X
should have:
Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)

Group Y should have:
Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)

And so on.J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.
>
I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.
>
So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.

Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.

From what you have said, it could be something like this:

LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate

Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.

Quote:

Originally Posted by

Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.

I did not cover the percentage thing, since that was just too unclear
to me how it works.

If you want more accurate assistance, I would suggest that you post:

o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hey there, sorry about the lack of details.

<< o CREATE TABLE statement for your deposits table>>
CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the
field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT NULL DEFAULT 1 --(typeid 0 or 1 means amount is a
dollar amount. typeid = 2 means amount is a percentage amount)
PRIMARY KEY(QuoteDueDateID)
)

<<, and a table that represents the view, including the essential
columns only.>>
The result from the view is pretty big but the important columns are
QuoteID (int) - the quote they belong to -, BillingPrice (money) -the
total they have to pay - and payments (money) - how much they've paid
so far -

<< INSERT statements with sample data.>>
INSERT INTO datQuotationDueDates VALUES (4, 1, '10/1/2006', 15, 2)
this is a deposit due on October 1st for 15% of their total billing
price for all clients in quote 4

INSERT INTO datQuotationDueDates VALUES (4, 1, '11/1/2006', 100, 1)
this is a deposit due on November 1st for $100 for all clients in quote
4

INSERT INTO datQuotationDueDates VALUES (4, 1, '12/1/2006', 100, 1)
this is a deposit due on December 1st for $100 for all clients in quote
4

INSERT INTO datQuotationDueDates VALUES (4, 1, '1/1/2007', 100, 1)
this is a deposit due on January 1st for $100 for all clients in quote
4

INSERT INTO datQuotationDueDates VALUES (10, 1, '11/1/2006', 20, 2)
this is a deposit due on November 1st for 20% of their total for all
clients in quote 10

INSERT INTO datQuotationDueDates VALUES (10, 1, '11/15/2006', 10, 2)
this is a deposit due on November 15th for 10% of their total for all
clients in quote 10

INSERT INTO datQuotationDueDates VALUES (10, 1, '12/1/2006', 300, 1)
this is a deposit due on December 1st for $300 for all clients in quote
10

<<The desired result given the sample.>>
let's say, for argument's sake that the view only brings back the
columns I mentioned above: Quote, billingprice and payments along with
client name

4 | 1000.00 | 250.00 | john smith
4 | 1000.00 | 150.00 | jane doe
10 | 2000.00 | 400.00 | jack jones
10 | 2000.00 | 0.00 | james james

now, what I'm looking for is this:
- Quote 4 has 2 deposits that should have been paid already (Oct 1st -
15% of their total - and Nov 1st - $100 -) and the next one is due on
Dec 1st for another $100

- Quote 10 has 1 deposit that should have been paid already (Nov 1st -
20% of their total -) and the next one is due on Dec 1st for another
$300

john smith has paid his deposits in full
jane doe has only paid $150 of $250 that she should have paid
jack jones paid his first deposit in full
james james has paid nothing

The result should be (QuoteID, billingPrice, Payments, Name,
NextDueDate, TotalDue: total due is (the total of deposits due by
NextDueDate) - (payments))

4 | 1000.00 | 250.00 | john smith | 12/1/2006 | 100.00
4 | 1000.00 | 150.00 | jane doe | 12/1/2006 | 200.00
10 | 2000.00 | 400.00 | jack jones | 12/1/2006 | 300.00
10 | 2000.00 | 0.00 | james james | 12/1/2006 | 700.00

<< Which version of SQL Server you are using.>>
SQL Server 2000

Erland Sommarskog wrote:

Quote:

Originally Posted by

J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.

I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.

So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.

>
Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.
>
From what you have said, it could be something like this:
>
LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate
>
Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.
>

Quote:

Originally Posted by

Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.

>
I did not cover the percentage thing, since that was just too unclear
to me how it works.
>
If you want more accurate assistance, I would suggest that you post:
>
o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Well, this is apparently solved.

There probably was simpler or more optimized way of doing it but I'm
just starting to use SQL server for tasks like this one. My solution
was this:

LEFT JOIN
(SELECT DISTINCT QuoteID,
(SELECT TOP 1 DueDate FROM datQuotationDueDates WHERE DateTypeID = 1
and DueDate GETDATE() and QuoteID = dQDD.QuoteID ORDER BY DueDate) as
NextDueDate
,(SELECT SUM(CASE WHEN (TypeID = 1 OR TypeID = 0) THEN Amount END )
FROM datQuotationDueDates WHERE DateTypeID = 1 AND QuoteID =
dQDD.QuoteID AND DueDate <= (SELECT TOP 1 DueDate FROM
datQuotationDueDates WHERE DateTypeID = 1 and DueDate GETDATE() AND
QuoteID = dQDD.QuoteID ORDER BY DueDate) ) AS Dollars
,(SELECT SUM(CASE WHEN TypeID = 2 THEN Amount END ) FROM
datQuotationDueDates WHERE DateTypeID = 1 AND QuoteID = dQDD.QuoteID
AND DueDate <= (SELECT TOP 1 DueDate FROM datQuotationDueDates WHERE
DateTypeID = 1 AND DueDate GETDATE() AND QuoteID = dQDD.QuoteID ORDER
BY DueDate) ) AS Percentages
FROM datQuotationDueDates dQDD) AS NextDD on NextDD.QuoteID =
vwClients.QuoteID

this way I bring the next due date, a column with to total of dollar
amounts and the total of percentages. Then I print out the date and use
amount and percentages along with billingprice and payments to
calculate the amount due for the next due date

I'd still be very interested in hearing how any of you would have done
it

J wrote:

Quote:

Originally Posted by

Hey there, sorry about the lack of details.
>
<< o CREATE TABLE statement for your deposits table>>
CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the
field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT NULL DEFAULT 1 --(typeid 0 or 1 means amount is a
dollar amount. typeid = 2 means amount is a percentage amount)
PRIMARY KEY(QuoteDueDateID)
)
>
<<, and a table that represents the view, including the essential
columns only.>>
The result from the view is pretty big but the important columns are
QuoteID (int) - the quote they belong to -, BillingPrice (money) -the
total they have to pay - and payments (money) - how much they've paid
so far -
>
>
<< INSERT statements with sample data.>>
INSERT INTO datQuotationDueDates VALUES (4, 1, '10/1/2006', 15, 2)
this is a deposit due on October 1st for 15% of their total billing
price for all clients in quote 4
>
INSERT INTO datQuotationDueDates VALUES (4, 1, '11/1/2006', 100, 1)
this is a deposit due on November 1st for $100 for all clients in quote
4
>
INSERT INTO datQuotationDueDates VALUES (4, 1, '12/1/2006', 100, 1)
this is a deposit due on December 1st for $100 for all clients in quote
4
>
INSERT INTO datQuotationDueDates VALUES (4, 1, '1/1/2007', 100, 1)
this is a deposit due on January 1st for $100 for all clients in quote
4
>
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/1/2006', 20, 2)
this is a deposit due on November 1st for 20% of their total for all
clients in quote 10
>
INSERT INTO datQuotationDueDates VALUES (10, 1, '11/15/2006', 10, 2)
this is a deposit due on November 15th for 10% of their total for all
clients in quote 10
>
INSERT INTO datQuotationDueDates VALUES (10, 1, '12/1/2006', 300, 1)
this is a deposit due on December 1st for $300 for all clients in quote
10
>
<<The desired result given the sample.>>
let's say, for argument's sake that the view only brings back the
columns I mentioned above: Quote, billingprice and payments along with
client name
>
4 | 1000.00 | 250.00 | john smith
4 | 1000.00 | 150.00 | jane doe
10 | 2000.00 | 400.00 | jack jones
10 | 2000.00 | 0.00 | james james
>
now, what I'm looking for is this:
- Quote 4 has 2 deposits that should have been paid already (Oct 1st -
15% of their total - and Nov 1st - $100 -) and the next one is due on
Dec 1st for another $100
>
- Quote 10 has 1 deposit that should have been paid already (Nov 1st -
20% of their total -) and the next one is due on Dec 1st for another
$300
>
john smith has paid his deposits in full
jane doe has only paid $150 of $250 that she should have paid
jack jones paid his first deposit in full
james james has paid nothing
>
The result should be (QuoteID, billingPrice, Payments, Name,
NextDueDate, TotalDue: total due is (the total of deposits due by
NextDueDate) - (payments))
>
4 | 1000.00 | 250.00 | john smith | 12/1/2006 | 100.00
4 | 1000.00 | 150.00 | jane doe | 12/1/2006 | 200.00
10 | 2000.00 | 400.00 | jack jones | 12/1/2006 | 300.00
10 | 2000.00 | 0.00 | james james | 12/1/2006 | 700.00
>
<< Which version of SQL Server you are using.>>
SQL Server 2000
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.
>
I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next deposit due and the total of all
payments that they will have made by the next due date.
>
So this is how things are. I join the table and it obviously brings
multiple records for each record (one for each matching one in the new
table). I need, instead, to be able to make out what due date I should
print (the first one that is GETDATE()?) and the total of deposits up
to that date.

Obviously no one here can say whether getdate() is right for you.
That depends on the business requirements.

From what you have said, it could be something like this:

LEFT JOIN (SELECT a.clientid, a.duedate, a.amt,
totalamt = (SELECT SUM(c.amt)
FROM deposits c
WHERE c.clientid = a.clientid
AND c.duedate <= b.duedate)
FROM deposits a
JOIN (SELECT clientid, MIN(duedate)
FROM deposits
GROUP BY clientid) AS b ON a.clientid = b.clientid
AND a.duedate = b.duedate

Here I am asuming that (clienid, duedate) is a key in your table,
but that is of course a plain guess. Hopefully this can serve as
a starting point for you endeavour.

Quote:

Originally Posted by

Now, payments can be either dollar amounts or percentages of another
amount in the view. So if it's an amount I add it, if it's a % I
calculate the amount and add it.

I did not cover the percentage thing, since that was just too unclear
to me how it works.

If you want more accurate assistance, I would suggest that you post:

o CREATE TABLE statement for your deposits table, and a table that
represents the view, including the essential columns only.
o INSERT statements with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||J (julian.solis@.gmail.com) writes:

Quote:

Originally Posted by

Hey there, sorry about the lack of details.
>...
><<, and a table that represents the view, including the essential
columns only.>>
The result from the view is pretty big but the important columns are
QuoteID (int) - the quote they belong to -, BillingPrice (money) -the
total they have to pay - and payments (money) - how much they've paid
so far -

Thanks for the table and sample data. Below is my query (together with
a table I composed for the clients.) For quote 10, I got different
results that you had as the desired. Looking at the sample data, my
result seemed OK, but I may have misunderstood something about the
business rules. Whether this query performs better than yours, I don't
know. Only testing can tell.

One note about the table datQuotationDueDates: I would guess that (QuoteID, DueDate) is unique. But in such case that should be the primary key, and
the column QuoteDueDateID does not really serve any purpose.

Here is the script:

CREATE TABLE datQuotationDueDates(
QuoteDueDateIDintNOT NULL IDENTITY,
QuoteIDintNOT NULL, --clients are assigned to quotes so this is the field I'll be using for the join
DateTypeIDintNOT NULL, --type of payment (datetypeid = 1 is deposit)
DueDatedatetimeNOT NULL, --date when the payment is due
AmountfloatNULL, --amount for the payment
TypeIDintNOT NULL DEFAULT 1 --(typeid 0 or 1 means amount is a dollar amount. typeid = 2 means amount is a percentage amount)
PRIMARY KEY(QuoteDueDateID)
)
INSERT INTO datQuotationDueDates VALUES (4, 1, '10/1/2006', 15, 2)
--this is a deposit due on October 1st for 15% of their total billing price for all clients in quote 4

INSERT INTO datQuotationDueDates VALUES (4, 1, '11/1/2006', 100, 1)
-- this is a deposit due on November 1st for $100 for all clients in quote 4

INSERT INTO datQuotationDueDates VALUES (4, 1, '12/1/2006', 100, 1)
-- this is a deposit due on December 1st for $100 for all clients in quote 4

INSERT INTO datQuotationDueDates VALUES (4, 1, '1/1/2007', 100, 1)
-- this is a deposit due on January 1st for $100 for all clients in quote 4

INSERT INTO datQuotationDueDates VALUES (10, 1, '11/1/2006', 20, 2)
-- this is a deposit due on November 1st for 20% of their total for all clients in quote 10

INSERT INTO datQuotationDueDates VALUES (10, 1, '11/15/2006', 10, 2)
-- this is a deposit due on November 15th for 10% of their total for all clients in quote 10

INSERT INTO datQuotationDueDates VALUES (10, 1, '12/1/2006', 300, 1)
-- this is a deposit due on December 1st for $300 for all clients in quote 10
go
CREATE TABLE clients (quoteid smallint not null,
billprice float not null,
payments float not null,
name varchar(20) not null)
go
insert clients values(4, 1000.00, 250.00, 'john smith')
insert clients values(4, 1000.00 , 150.00, 'jane doe')
insert clients values(10, 2000.00, 400.00, 'jack jones')
insert clients values(10 , 2000.00, 0.00, 'james james')
go
SELECT c.quoteid, c.billprice, c.payments, c.name,
due.DueDate,
due.Amount + due.Perc * c.billprice / 100 - c.payments
FROM clients c
JOIN (SELECT a.QuoteID, DueDate = MAX(a.DueDate),
Amount = SUM(CASE TypeID WHEN 1 THEN Amount ELSE 0 END),
Perc = SUM(CASE TypeID WHEN 2 THEN Amount ELSE 0 END)
FROM datQuotationDueDates a
JOIN (SELECT QuoteID, DueDate = MIN(DueDate)
FROM datQuotationDueDates
WHERE DueDate getdate()
GROUP BY QuoteID) AS b
ON a.QuoteID = b.QuoteID
AND a.DueDate <= b.DueDate
GROUP BY a.QuoteID) AS due
ON due.QuoteID = c.quoteid

go
DROP TABLE datQuotationDueDates
drop table clients

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with Join

H
I have two tables
Create table company
( firm char(32),
p_name char(40),
proj_code char(8),
proj_start_dt datetime
)
go
insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
go
Create table employee
( firm char(32),
p_name char(40),
proj_end_dt datetime
)
go
insert into employee values ("ABC","John Doe", "Nov 20 2003")
insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
go
I need to extract data for p_name with proj_start_dt > proj_end_dt
my query is not working
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns all values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC John Doe
DJOE Nov 12 2001 12:00AM
ABC Jane Doe
JANEDOE Oct 26 2000 12:00AM
ABC Bruce Smith
BRUCES Mar 1 2002 12:00AM
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c,
employee e
where c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
returns no values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
Could someone please help.
AjmisterThanks for posting the DDL and sample data. Could you show and explain
exactly what result you want. As far as I can see there are no rows where
Proj_start_dt is greater than Proj_end_dt for any given P_name.
David Portas
SQL Server MVP
--|||try this...
select c.firm,
c.p_name,
c.proj_code,
c.proj_start_dt
from company c
left join employee e
on c.p_name=e.p_name
where c.proj_start_dt > e.proj_end_dt
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Did you try it? :-)
David Portas
SQL Server MVP
--|||Sorry abt my previous post, that was wrong.
here is the answer,
the company table has the proj_start_dt = Apr 15 2004 which is greater than
the proj_end date.But the value of p_name in the company table is Lisa Cox
which is not there in the employee table.So when you do a join you will not
get the result.
"ajmister" wrote:

> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>
>|||Your first join is returning all rows because you are doing an outer join
which is telling SQL you want to return all row from the set on the left
(company) even if they don't match any rows in the set on the right
(employee). If you run the following command you can see you are not
returning any values from the employee table:
select c.*, e.*
from company c
left join employee e
on c.p_name=e.p_name
and c.proj_start_dt > e.proj_end_dt
go
Your second query seems to be doing what you want, and you are not getting
any rows returned because the are no company.proj_start_dt's that are > the
employee.proj_end_dt when the company.p_name and employee.p_name are equal.
Hope this helps explain why you are getting the results you posted.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ajmister" <ajmister@.optonline.net> wrote in message
news:etG3OUXCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> H
> I have two tables
> Create table company
> ( firm char(32),
> p_name char(40),
> proj_code char(8),
> proj_start_dt datetime
> )
> go
> insert into company values ("ABC","John Doe", "DJOE","Nov 12 2001")
> insert into company values ("ABC","Jane Doe", "JANEDOE","Oct 26 2000")
> insert into company values ("ABC","Bruce Smith", "BRUCES","Mar 01 2002")
> insert into company values ("ABC","David Smith", "SDAVID","Nov 12 2003")
> insert into company values ("ABC","Lisa Cox", "LCOX","Apr 15 2004")
> go
>
> Create table employee
> ( firm char(32),
> p_name char(40),
> proj_end_dt datetime
> )
> go
> insert into employee values ("ABC","John Doe", "Nov 20 2003")
> insert into employee values ("ABC","Jane Doe", "Dec 26 2002")
> insert into employee values ("ABC","Bruce Smith","Apr 01 2003")
> go
> I need to extract data for p_name with proj_start_dt > proj_end_dt
> my query is not working
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c
> left join employee e
> on c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns all values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> ABC John Doe
> DJOE Nov 12 2001 12:00AM
> ABC Jane Doe
> JANEDOE Oct 26 2000 12:00AM
> ABC Bruce Smith
> BRUCES Mar 1 2002 12:00AM
> ABC David Smith
> SDAVID Nov 12 2003 12:00AM
> ABC Lisa Cox
> LCOX Apr 15 2004 12:00AM
>
> select c.firm,
> c.p_name,
> c.proj_code,
> c.proj_start_dt
> from company c,
> employee e
> where c.p_name=e.p_name
> and c.proj_start_dt > e.proj_end_dt
> go
> returns no values
> firm p_name
> proj_code proj_start_dt
> -- ---
--
> -- --
> Could someone please help.
>
> Ajmister
>|||Thank you for a quick response, sorry I want to extract all Proj_start_dt
that do not have a proj_end_dt
Thanx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:taednVcaFv9azpzfRVn-sg@.giganews.com...
> Thanks for posting the DDL and sample data. Could you show and explain
> exactly what result you want. As far as I can see there are no rows where
> Proj_start_dt is greater than Proj_end_dt for any given P_name.
> --
> David Portas
> SQL Server MVP
> --
>|||Yes, but it did not return any values
firm p_name
proj_code proj_start_dt
-- --- --
-- --
I would the query to return proj_start_dt whihc do not have proj_end_dt
(sorry about the previous error)
example
firm p_name
proj_code proj_start_dt
-- --- --
-- --
ABC David Smith
SDAVID Nov 12 2003 12:00AM
ABC Lisa Cox
LCOX Apr 15 2004 12:00AM
Thank you
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:yt6dnXskg5uRy5zfRVn-vg@.giganews.com...
> Did you try it? :-)
> --
> David Portas
> SQL Server MVP
> --
>|||Try this:
SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
FROM company AS C
LEFT JOIN employee AS E
ON C.p_name = E.p_name
AND C.proj_start_dt < E.proj_end_dt
WHERE C.proj_start_dt IS NOT NULL
AND E.proj_end_dt IS NULL
David Portas
SQL Server MVP
--|||Thank you sir. That gave me the correct output.
Ajmister
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OIydnb5UiOmFGZzfRVn-iA@.giganews.com...
> Try this:
> SELECT C.firm, C.p_name, C.proj_code, C.proj_start_dt
> FROM company AS C
> LEFT JOIN employee AS E
> ON C.p_name = E.p_name
> AND C.proj_start_dt < E.proj_end_dt
> WHERE C.proj_start_dt IS NOT NULL
> AND E.proj_end_dt IS NULL
> --
> David Portas
> SQL Server MVP
> --
>