Advice on SQL statement please.

I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. How would I use my qry to accomplish this task.
I appreciate the help.
Here's my qry:
Select GroupType, Sum(SumRevAmt) as Last12Mnths
from MyQRY
Where Period = '200006'
Group by GroupType
Type Last12Mnths_200006 Prior12Mnths_199906
Airlines 1234.50 '
Concessions 73854.00 '
etc......Russell Verdun wrote:
> I have a query that generates the dataset below, based on the year being
> filtered I get the sum of an amount Group By the type. What I would like t
o
> do is use the exact qry using a differnet date, to generate a third column
> called Prior12Mnths. How would I use my qry to accomplish this task.
> I appreciate the help.
> Here's my qry:
> Select GroupType, Sum(SumRevAmt) as Last12Mnths
> from MyQRY
> Where Period = '200006'
> Group by GroupType
>
>
> Type Last12Mnths_200006 Prior12Mnths_199906
> Airlines 1234.50 '?
?
> Concessions 73854.00 '
> etc......
>
>
It's not clear from your example what this "other date" would be, so
I'll use a different example. Say I have a table containing
transactions, and each transaction consists of an account, a transaction
date, and an amount:
SELECT
account,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
ELSE 0) AS Last12Months,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
THEN amount ELSE 0) AS Prev12Months
FROM table
GROUP BY account
Is that enough to get you started?|||Tracy McKibben wrote:
> Russell Verdun wrote:
> It's not clear from your example what this "other date" would be, so
> I'll use a different example. Say I have a table containing
> transactions, and each transaction consists of an account, a transaction
> date, and an amount:
> SELECT
> account,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
> ELSE 0) AS Last12Months,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
> THEN amount ELSE 0) AS Prev12Months
> FROM table
> GROUP BY account
> Is that enough to get you started?
>
Sorry, those CASE statements are missing ENDs...|||Hi Russell,
I believe you could do something like this:
Select GroupType, Sum(CASE Period=200006 THEN SumRevAmt ELSE 0 END) as
Last12Mnths, Sum(CASE Period=199906 THEN SumRevAmt ELSE 0 END) as
Prior12Mnths
from MyQRY
Where Period = '200006' or Period = '199906'
Group by GroupType
I Dont know if thats the best way, but that is what first comes to
mind.
Paul T.|||>> I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is also helpful if the data elements have good
names.
CREATE TABLE Revenues -- guess at meaningful name
(grp_type INTEGER NOT NULL
REFERENCES GroupTypes(grp_type),
rev_amt DECIMAL(12,2) NOT NULL,
rev_date DATETIME NOT NULL PRIMARY KEY);
In the vague pseudo-code you posted, only some kind of vague date can
be a key
The best trick for this kind of summary is to build a reporting range
table
CREATE TABLE ReportRanges
(range_name CHAR() NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (range_name, start_date));
INSERT INTO ReportRanges
VALUES ('2006-06: Prior12' , '2005-06-01', '2006-06-31' );
INSERT INTO ReportRanges
VALUES ('2006-06: ytd' , '2006-01-01', '2006-06-31' );
SELECT grp_type,
SUM (CASE WHEN R.range_name = '2006-06: ytd'
THEN rev_amt ELSE 0.00 END) AS ytd,
SUM (CASE WHEN R.range_name = '2006-06: Prior12'
THEN rev_amt ELSE 0.00 END) AS Prior12,
etc.
FROM Revenues
GROUP BY grp_type;
Adjust the table as needed.

Advice on Simple Structured Sql Query (Search)

Hi All,
I have created a well structured cross relationship db.. To extract all
the details out is not a problem but i am trying to now include a
simple search to filter results.. Where is the best place to build the
query, within my .Net web app or within a sp itself..
My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
(JobID, Title, Keywords) and then another table that lists the
locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
What i am then trying to do is include a keyword search and a location
search together, i need to make it boolean as well and split keywords
up and then search within these to make sure they are in the
locations.. In my head it seemed fairly approchable but cant seem to
grasp it and cant find any decent tutorials.. Could you please point me
in the right direction: Do i need to be using dynamic sql', Should i
be doing most of the work within .net
A typical search would be for:
Keywords: IT,Graduate
Within Location: 12,35,48
I can do one or the other but not combined.. I also dont know how to
split up the keywords to make them two seperate keywords..
If anyone can point me in the right direction as this is my last
hurdle.. On this project at least :)Have a look at
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ponyoo" <anthonykallay@.hotmail.com> wrote in message
news:1133857887.366250.221060@.g14g2000cwa.googlegroups.com...
> Hi All,
> I have created a well structured cross relationship db.. To extract all
> the details out is not a problem but i am trying to now include a
> simple search to filter results.. Where is the best place to build the
> query, within my .Net web app or within a sp itself..
> My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
> (JobID, Title, Keywords) and then another table that lists the
> locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
> What i am then trying to do is include a keyword search and a location
> search together, i need to make it boolean as well and split keywords
> up and then search within these to make sure they are in the
> locations.. In my head it seemed fairly approchable but cant seem to
> grasp it and cant find any decent tutorials.. Could you please point me
> in the right direction: Do i need to be using dynamic sql', Should i
> be doing most of the work within .net
> A typical search would be for:
> Keywords: IT,Graduate
> Within Location: 12,35,48
> I can do one or the other but not combined.. I also dont know how to
> split up the keywords to make them two seperate keywords..
> If anyone can point me in the right direction as this is my last
> hurdle.. On this project at least :)
>

advice on query

Hi,
my question is - how do I get just the best price from this query:
item is a shopping list
prod is the product list
price is the list of prices, some product have multiple prices for different
quantities
eg Prod price minquant
6 3.53 1
6 3.22 30
6 2.95 100
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
gives
20 1 1 Kistenspanners K-10 4.5
18 5 1 De-Sta-Co #311 22.42
21 6 35 Spring 3.53
21 6 35 Spring 3.22
21 6 35 Spring 2.95
I just want the price relevant to the quantity - eg in eaxample above, 35
Springs qualifies for >30 price of 3.22.
Appreciate any help!
thansk,
NEILwell, i didn't have time to recreate the tables and data you listed
without the DDL, but i think you need a subquery. try adding something
like this to your WHERE clause, and you should get only the most
appropriate quantity:
AND (item.iQuant > (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))
for a whole query of:
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
AND (item.iQuant > (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))
hope this helps
jason|||actually, it should probably look more like this:
AND (price.minquant = (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))|||You can try this:
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
and minquant = (select max(minquant)
from mms_tblProductPriceList price2
where price2.minquant < item.iQuant
prod.ProductID = price2.ProductID)
Perayu
"Neil Jarman" wrote:

> Hi,
> my question is - how do I get just the best price from this query:
> item is a shopping list
> prod is the product list
> price is the list of prices, some product have multiple prices for differe
nt
> quantities
> eg Prod price minquant
> 6 3.53 1
> 6 3.22 30
> 6 2.95 100
> SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
> prod.sShortDesc, price.cPrice
> FROM mms_tblOrderItemList item INNER JOIN
> mms_tblProductList prod ON item.ProductID =
> prod.ProductID INNER JOIN
> mms_tblProductPriceList price ON prod.ProductID =
> price.ProductID
> WHERE (item.OrderHeadID = 9)
> gives
> 20 1 1 Kistenspanners K-10 4.5
> 18 5 1 De-Sta-Co #311 22.42
> 21 6 35 Spring 3.53
> 21 6 35 Spring 3.22
> 21 6 35 Spring 2.95
> I just want the price relevant to the quantity - eg in eaxample above, 35
> Springs qualifies for >30 price of 3.22.
> Appreciate any help!
> thansk,
> NEIL
>
>|||Thank you both sooooo much - I was really stuck with that one!!!
NEIL
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:dekp74$kvk$1$8300dec7@.news.demon.co.uk...
> Hi,
> my question is - how do I get just the best price from this query:
> item is a shopping list
> prod is the product list
> price is the list of prices, some product have multiple prices for
> different quantities
> eg Prod price minquant
> 6 3.53 1
> 6 3.22 30
> 6 2.95 100
> SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
> prod.sShortDesc, price.cPrice
> FROM mms_tblOrderItemList item INNER JOIN
> mms_tblProductList prod ON item.ProductID =
> prod.ProductID INNER JOIN
> mms_tblProductPriceList price ON prod.ProductID =
> price.ProductID
> WHERE (item.OrderHeadID = 9)
> gives
> 20 1 1 Kistenspanners K-10 4.5
> 18 5 1 De-Sta-Co #311 22.42
> 21 6 35 Spring 3.53
> 21 6 35 Spring 3.22
> 21 6 35 Spring 2.95
> I just want the price relevant to the quantity - eg in eaxample above, 35
> Springs qualifies for >30 price of 3.22.
> Appreciate any help!
> thansk,
> NEIL
>|||my pleasure, glad it helped

Advice on query

Hi,

Quite possibly this is easy but my brain is bazango'd right now and for the life of me I can't think of a good way to get the following...

I have a table with 4 columns

ID (int)
User (varchar(200))
Timestamp (datetime)
Note (text)

The ID, User, Timestamp make up the primary key and nulls are not permitted in any column

I'm trying to select the top 3 most recent rows [using timestamp] for each ID.

anyone?

thanks in advance
Mac

Hi,

SELECT *

FROM

(

SELECT

[id], [user],[timestamp], --you should not use reserved words for column names

RANK() OVER (PARTITION BY id,user,timestamp ORDER BY Timestamp DESC) AS Ranked

) Subquery

WHERE Ranked <=3

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Mac:

Maybe something like this?

set nocount on

declare @.mockUp table
( ID integer not null,
[USER] varchar (200) not null,
Timestamp datetime not null,
Note text not null
)

insert into @.mockUp values (1, 'Mugambo', '10/1/6', 'Note #1')
insert into @.mockUp values (2, 'Mugambo', '10/2/6', 'Note #2')
insert into @.mockUp values (3, 'Mugambo', '10/5/6', 'Note #3')
insert into @.mockUp values (4, 'Mugambo', '10/4/6', 'Note #4')
insert into @.mockUp values (5, 'Mugambo', '10/8/6', 'Note #5')

insert into @.mockUp values (11, 'Herman Munster', '10/1/6', 'Note #1')
insert into @.mockUp values (12, 'Herman Munster', '10/2/6', 'Note #2')
insert into @.mockUp values (13, 'Herman Munster', '10/3/6', 'Note #3')
insert into @.mockUp values (14, 'Herman Munster', '10/3/6', 'Note #4')
insert into @.mockUp values (15, 'Herman Munster', '10/5/6', 'Note #5')

select [user],
id,
Timestamp,
Note
from (
select id,
[user],
Timestamp,
rank ()
over (partition by [user] order by timestamp, id)
as Seq,
Note
from @.mockUp
) x
where seq <= 3

-- --
-- SQL Server 2005 SAMPLE OUTPUT:
-- --

-- Herman Munster 11 2006-10-01 00:00:00.000 Note #1
-- Herman Munster 12 2006-10-02 00:00:00.000 Note #2
-- Herman Munster 13 2006-10-03 00:00:00.000 Note #3
-- Mugambo 1 2006-10-01 00:00:00.000 Note #1
-- Mugambo 2 2006-10-02 00:00:00.000 Note #2
-- Mugambo 4 2006-10-04 00:00:00.000 Note #4

select x.ID,
x.[User],
x.Timestamp,
y.note
from (
select a.id,
a.[user],
a.Timestamp,
count(*) as Seq
from @.mockUp a
inner join @.mockUp b
on a.[user] = b.[user]
and ( a.timestamp > b.timestamp or
a.timestamp = b.timestamp and
a.id >= b.id
)
group by a.id, a.[user], a.Timestamp
) x
inner join @.mockUp y
on x.seq <= 3
and x.id = y.id
order by x.[user], x.seq

-- --
-- SQL Server 2000 SAMPLE OUTPUT:
-- --

-- Herman Munster 11 2006-10-01 00:00:00.000 Note #1
-- Herman Munster 12 2006-10-02 00:00:00.000 Note #2
-- Herman Munster 13 2006-10-03 00:00:00.000 Note #3
-- Mugambo 1 2006-10-01 00:00:00.000 Note #1
-- Mugambo 2 2006-10-02 00:00:00.000 Note #2
-- Mugambo 4 2006-10-04 00:00:00.000 Note #4

|||I definitely agree with Jens' solution!|||Jens, why didn't you used a TOP / ORDER BY solution ? does the "ranking over" performs better ?|||

thank you both, exactly what I was needing.

|||

Hi,

That should be worth a try for the original poster:

SELECT *

FROM SomeTable ST
WHERE Exists

(

SELECT
TOP 3 [id], [user],[timestamp], --you should not use reserved words for column names
From SomeTable Subquery
Where
Subquery.[id] = ST.[id] AND
Subquery.[user] = ST.[user] AND
Subquery.[timestamp]
= ST.[timestamp]

)

I don′t know if you meant TOP 3 in the upper query, that would not take the effect to get back the first three OF EVERY ID.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very much
Hi
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John
|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment is
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>

Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very muchHi
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment i
s
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>

Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very muchHi
"elisacarli" wrote:
> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment is
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:
> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !
Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:

> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>
|||thanks, it was helpfull !

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !SHAPE is not a TSQL language element. If I'm not mistaken, it is implemented
in ADO. I have a
feeling that you will get better assistance if you post to a ADO group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<doar123@.gmail.com> wrote in message news:1137102448.006011.123840@.g43g2000cwa.googlegroups
.com...
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:

> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !

Advanced Sql-Shape Query - Help

Hi,

This is my basic sql shape query:
------------------
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
------------------

With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.

It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.

My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !

I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).

I hope that my question is clear enough. thanks !(doar123@.gmail.com) writes:
> This is my basic sql shape query:
> ------------------
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ------------------
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !

If I get this right you want this in the SHAPE part:

SELECT * FROM tbl1
WHERE EXISTS (SELECT * FROM tbl2 WHERE tbl2.field = tbl1.fiedl)

--
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|||I didn't know i can use the exists query - I will check it out and get
back to report.

thanks for your help !|||Well, It works like a charm !!!!

thank you !!!

Advanced SQL script Editor

All,
Who and how writes very long scripts in T-SQL? Do we have anything more
convenient than M$ SQL Query Analyzer? I finally started writing my scripts
in WinWord because of the terrible despair. It at least allows me to use
formatting, color selection, block separation, etc. Then I can copy/paste
this script into SQL Query Analyzer to check if it's correct. I realize that
it's crazy, but the Visual Studio IDE, regarding to the T-SQL scripts, is a
simple plain editor, I can't hide blocks, I can't use colors, nothing that I
can do for C#. The long script finally becomes a mess, terrible plain text
unreadable or very hardly readable. I'm talking about the very long scripts
including some general logic, variables, counters, etc., the length of such
a script can be a few thousand lines of the formatted SQL code.
Any advice?
Just D.> a script can be a few thousand lines of the formatted SQL code.
Well that is your fist and biggest mistake. TSQL is not a programming
language and should not be long and drawn out. At the very least break it
down into several manageable stored procedures. This is optimized for SET
based processing of data which should not take 1000 lines of code or more.
Andrew J. Kelly SQL MVP
"Just D." <no@.spam.please> wrote in message
news:LtHXe.131841$Ji4.30207@.fed1read03...
> All,
> Who and how writes very long scripts in T-SQL? Do we have anything more
> convenient than M$ SQL Query Analyzer? I finally started writing my
> scripts in WinWord because of the terrible despair. It at least allows me
> to use formatting, color selection, block separation, etc. Then I can
> copy/paste this script into SQL Query Analyzer to check if it's correct. I
> realize that it's crazy, but the Visual Studio IDE, regarding to the T-SQL
> scripts, is a simple plain editor, I can't hide blocks, I can't use
> colors, nothing that I can do for C#. The long script finally becomes a
> mess, terrible plain text unreadable or very hardly readable. I'm talking
> about the very long scripts including some general logic, variables,
> counters, etc., the length of such a script can be a few thousand lines of
> the formatted SQL code.
> Any advice?
> Just D.
>|||Ok,
Let's ask how to write a 1000-line T-SQL code in the most convenient way.
You suppose that I'd better call the SP from this SP or Trigger using EXEC
etc.? And how fast it should run? Another one bottle neck is the parameters
that in case of EXEC SP should be stored somewhere and sent to this SP, then
something returned back. It will not increase the speed of the script
itself.
Just D.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ehG$XDXvFHA.724@.TK2MSFTNGP14.phx.gbl...
> Well that is your fist and biggest mistake. TSQL is not a programming
> language and should not be long and drawn out. At the very least break it
> down into several manageable stored procedures. This is optimized for
> SET based processing of data which should not take 1000 lines of code or
> more.
> --
> Andrew J. Kelly SQL MVP
>
> "Just D." <no@.spam.please> wrote in message
> news:LtHXe.131841$Ji4.30207@.fed1read03...
>|||Just,
Stored procedures are faster. The reduce the overall network traffic for
each call and optimize the execution for subsequent calls on the SQL Server.
HTH
Jerry
"Just D." <no@.spam.please> wrote in message
news:XZHXe.131843$Ji4.74359@.fed1read03...
> Ok,
> Let's ask how to write a 1000-line T-SQL code in the most convenient way.
> You suppose that I'd better call the SP from this SP or Trigger using EXEC
> etc.? And how fast it should run? Another one bottle neck is the
> parameters that in case of EXEC SP should be stored somewhere and sent to
> this SP, then something returned back. It will not increase the speed of
> the script itself.
> Just D.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ehG$XDXvFHA.724@.TK2MSFTNGP14.phx.gbl...
>|||Maybe I didn't explain correctly, the script should work itself as a SP or a
Trigger, so it will be compiled and optimized.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyfR%23QXvFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Just,
> Stored procedures are faster. The reduce the overall network traffic for
> each call and optimize the execution for subsequent calls on the SQL
> Server.
> HTH
> Jerry
> "Just D." <no@.spam.please> wrote in message
> news:XZHXe.131843$Ji4.74359@.fed1read03...
>|||I guess the first question that comes to my mind is what are you trying
to do that requires such a lengthy script? As Andrew pointed out,
T-SQL is optimized for set-based solutions (ie., doing a lot of things
at one time) as opposed to a procedural programming language. It
sounds as if (based on your short description) you're trying to write a
step-by-step operation. In most cases, there is another way, but
without knowing what you are attempting to do, that's a difficult call.
As far as your first question, I don't know of any better editor than
query analyzer. But I do think that having a master procedure call a
series of sub-procedures is one method of breaking your code up into
easily editable sections.
So, what are you trying to do?
Stu|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:553583
This task has already been working as a SP in a scheduled job every n
minutes and survived for longer than a year. I just need to rewrite it
because the format of the database is modified and it should be reflected in
the local trigger itself. What exactly..? Just a sync script that uploads
the data from one db to another db. Could be simple if the format of these
DBs was equal, but not in this case, I need to transfer different types of
data converting then and the calls themselves to be accepted by the target
database. The best way to go for us was using a scheduled job. I would not
like a system service doing these things or some external app, it's my way
to go, suppose not discussible here. But we have already forgotten the first
question. And I suppose that if there is no ideas it's better to skip the
question instead of starting this discussion in a different direction.
Thanks,
Just D.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1127173889.928153.17340@.z14g2000cwz.googlegroups.com...
>I guess the first question that comes to my mind is what are you trying
> to do that requires such a lengthy script? As Andrew pointed out,
> T-SQL is optimized for set-based solutions (ie., doing a lot of things
> at one time) as opposed to a procedural programming language. It
> sounds as if (based on your short description) you're trying to write a
> step-by-step operation. In most cases, there is another way, but
> without knowing what you are attempting to do, that's a difficult call.
> As far as your first question, I don't know of any better editor than
> query analyzer. But I do think that having a master procedure call a
> series of sub-procedures is one method of breaking your code up into
> easily editable sections.
> So, what are you trying to do?
> Stu
>|||Not to keep beating a dead horse, but have you considered DTS for the
basis of your job? It's much more flexible for data transformation (as
you describe), and it's usually faster than running a stored procedure
to do complex transformations.
I don't think that anyone has forgotten your first question, btw; it's
just that most of us have a belief that you should use the right tool
for the job. In most cases, T-SQL is not the best choice for complex
procedural transformations; there are exceptions, but they should
remain exceptions.
I'll get off my soapbox now; I'm not trying to lecture you, but I
didn't want you to walk away thinking that your question went
unanswered. It may be that it's not the best approach.
Stu|||You know, maybe you're right, but the solution was written so far ago and
still exists and even works. Maybe Sybase ASA or ASE with its built-in
synchronization is much better, I know enough about it, but sometimes we're
not choosing or even taking a tool, we're just using it. Yes, I got a
Licensed box with ASA9 from Sybase that they presented me for the critical
bug that I found in their mobile engine, I'm still sure that Sybase is the
best appropriate database for what I'm doing now, but keeping the point and
being in this newsgroup where Sybase definitely is off-topic, it's better to
skip that as well. Finally we're not responsible for the clients whose data
I'm syncing now. And this is a problem, not so huge, but solvable using
different approaches, that I'm doing right now.
Thanks,
Just D.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1127176774.800960.61880@.f14g2000cwb.googlegroups.com...
> Not to keep beating a dead horse, but have you considered DTS for the
> basis of your job? It's much more flexible for data transformation (as
> you describe), and it's usually faster than running a stored procedure
> to do complex transformations.
> I don't think that anyone has forgotten your first question, btw; it's
> just that most of us have a belief that you should use the right tool
> for the job. In most cases, T-SQL is not the best choice for complex
> procedural transformations; there are exceptions, but they should
> remain exceptions.
> I'll get off my soapbox now; I'm not trying to lecture you, but I
> didn't want you to walk away thinking that your question went
> unanswered. It may be that it's not the best approach.
> Stu
>|||This link might help; certainly on topic : - )
[url]http://www.dba.com/[/url]
"Just D." <no@.spam.please> wrote in message
news:jhJXe.131850$Ji4.111606@.fed1read03...
> You know, maybe you're right, but the solution was written so far ago and
> still exists and even works. Maybe Sybase ASA or ASE with its built-in
> synchronization is much better, I know enough about it, but sometimes
> we're not choosing or even taking a tool, we're just using it. Yes, I got
> a Licensed box with ASA9 from Sybase that they presented me for the
> critical bug that I found in their mobile engine, I'm still sure that
> Sybase is the best appropriate database for what I'm doing now, but
> keeping the point and being in this newsgroup where Sybase definitely is
> off-topic, it's better to skip that as well. Finally we're not responsible
> for the clients whose data I'm syncing now. And this is a problem, not so
> huge, but solvable using different approaches, that I'm doing right now.
> Thanks,
> Just D.
>
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1127176774.800960.61880@.f14g2000cwb.googlegroups.com...
>

Advanced sort

I need some help writing this sql query.
I have three columns in my table
let call them
tOrderA,tOrderB,tContent
tOrderA,tOrderB are random integers from 1-10 000, tContent is a text field
so my table looks something like this
|tOrderA|tOrderB|tContent|
1 27 my
9 3 asfdf
152 16 sdfsd
18 22 dfdf
182 14 dfdsfs
tOrderA is my primary sort with levels (ie.1-100,101-200,201-300)
tOrderB is my secondary sort.
when the sort is applied the table should look like this.
|tOrderA|tOrderB|tContent|
9 3 asfdf
18 22 dfdf
1 27 my
182 14 dfdsfs
152 16 sdfsd
How would I write this query?
select tContent from tableA order by tOrderA ASC [range
1-100,101-200,201-300...] and tOrderB ASC
Thanks,
Aaronselect tOrderA, tOrderB, tContent
from tableA
order by (tOrderA + 1) / 100, tOrderB
"Aaron" wrote:

> I need some help writing this sql query.
> I have three columns in my table
> let call them
> tOrderA,tOrderB,tContent
> tOrderA,tOrderB are random integers from 1-10 000, tContent is a text fiel
d
> so my table looks something like this
> |tOrderA|tOrderB|tContent|
> 1 27 my
> 9 3 asfdf
> 152 16 sdfsd
> 18 22 dfdf
> 182 14 dfdsfs
>
> tOrderA is my primary sort with levels (ie.1-100,101-200,201-300)
> tOrderB is my secondary sort.
> when the sort is applied the table should look like this.
> |tOrderA|tOrderB|tContent|
> 9 3 asfdf
> 18 22 dfdf
> 1 27 my
> 182 14 dfdsfs
> 152 16 sdfsd
> How would I write this query?
> select tContent from tableA order by tOrderA ASC [range
> 1-100,101-200,201-300...] and tOrderB ASC
>
> Thanks,
> Aaron
>
>|||You mean:
select tOrderA, tOrderB, tContent
from tableA
order by (tOrderA - 1) / 100, tOrderB
^^^^^
With your code 99 and 100 will be in the [101 - 200] bracket.
Jacco Schalkwijk
SQL Server MVP
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:B77956D1-7821-4B24-B0A2-E898FA3B3F48@.microsoft.com...
> select tOrderA, tOrderB, tContent
> from tableA
> order by (tOrderA + 1) / 100, tOrderB
> "Aaron" wrote:
>|||Right ! Thanks! would that be the infamous "off by 2" error '
"Jacco Schalkwijk" wrote:

> You mean:
> select tOrderA, tOrderB, tContent
> from tableA
> order by (tOrderA - 1) / 100, tOrderB
> ^^^^^
> With your code 99 and 100 will be in the [101 - 200] bracket.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:B77956D1-7821-4B24-B0A2-E898FA3B3F48@.microsoft.com...
>
>

Advanced Query Question

What I am trying to accomplish is to return a resultset that would look
like this:
Company.Name Table1.Column Table2.Column Table3.Column
Company A y y y
Company B n y n
Company C n n y
Company D n n n
I am using 4 tables to do this (Company, Table1, Table2, Table3). What
makes this somewhat complex is that I need to filter on a specific date
range (ie. 5/1/2006 - 5/31/2006) and display a company even if nothing
is available for the date range entered (Company D). Each Table has
its own date column. So I would need to apply my date criteria to
Table1.Date, Table2.Date, and Table3.Date.
In my first attempt I was using an Outer Join to display all company
names even if nothing was available in the three columns. That didn't
seem to work because then Company D wouldn't be included in the output.
The primary/foreign key relationship on the tables is the CompanyId.
The primary CompanyId being in the Company table and the foreign
CompanyId being in Tables 1,2, & 3.
How can I write this SQL statement in order to get the above results?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***"JP" <jp@.nospam.com> wrote in message
news:OJoiMuahGHA.4368@.TK2MSFTNGP03.phx.gbl...
> What I am trying to accomplish is to return a resultset that would look
> like this:
> Company.Name Table1.Column Table2.Column Table3.Column
> Company A y y y
> Company B n y n
> Company C n n y
> Company D n n n
<snip>
CREATE TABLE #Company (CompanyID INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL)
CREATE TABLE #Table1 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table1 ADD
CONSTRAINT FK_Table1_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table2 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table2 ADD
CONSTRAINT FK_Table2_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table3 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table3 ADD
CONSTRAINT FK_Table3_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
INSERT INTO #Company (CompanyID, [Name])
SELECT 1, 'Microsoft'
UNION SELECT 2, 'Hooters'
UNION SELECT 3, 'Ca$h Munny Records'
UNION SELECT 4, 'Widgets LLC.'
UNION SELECT 5, 'Five Inc'
INSERT INTO #Table1 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-01-01'
UNION SELECT 1, 2, '2006-05-01'
UNION SELECT 3, 1, '2005-01-01'
UNION SELECT 5, 1, '2006-05-15'
INSERT INTO #Table2 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-31'
UNION SELECT 1, 2, '2006-01-01'
UNION SELECT 2, 1, '2005-04-01'
UNION SELECT 4, 1, '2006-05-15'
INSERT INTO #Table3 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-02'
UNION SELECT 2, 1, '2006-05-04'
UNION SELECT 3, 1, '2006-05-09'
UNION SELECT 4, 1, '2006-05-15'
UNION SELECT 5, 1, '2006-05-14'
UNION SELECT 2, 2, '2001-01-01'
SELECT DISTINCT c.CompanyID, c.[Name],
CASE WHEN t1.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table1_Column],
CASE WHEN t2.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table2_Column],
CASE WHEN t3.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table3_Column]
FROM #Company c
LEFT OUTER JOIN #Table1 t1
ON c.CompanyID = t1.CompanyID
AND (t1.[Date] >= '2006-05-01' AND t1.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table2 t2
ON c.CompanyID = t2.CompanyID
AND (t2.[Date] >= '2006-05-01' AND t2.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table3 t3
ON c.CompanyID = t3.CompanyID
AND (t3.[Date] >= '2006-05-01' AND t3.[Date] < '2006-06-01')
DROP TABLE #Table3
DROP TABLE #Table2
DROP TABLE #Table1
DROP TABLE #Company|||Thank you very much Mike!
*** Sent via Developersdex http://www.examnotes.net ***

Advanced query question

Hi
I have a tre table setup in order to support dynamic attributs of
users:
user table:
| *userid* | *username*
| 1 | joe
| 2 | james
attributetype table:
| *attributetypeid* | *attributename* |
| 1 | height
| 2 | weight
userattribute table:
| *userattributeid* | *userid* | attributetypeid* |*text*
| 1 | 1 | 1 | 80
| 2 | 1 | 2 | 180
| 1 | 2 | 1 | 90
| 2 | 2 | 2 | 160
(the userid and attributetypeid are fk to the user and attributetype
tables.)
Now i would like to create a sql statement that returns something like:
| *userid* | *username* | *height* | *weight*
| 1 | joe | 80 | 180
| 1 | joe | 90 | 160
The should support that I add an additional attributetype, say "IQ"
and then it should return something like:
| *userid* | *username* | *height* | *weight* | *IQ*
| 1 | joe | 80 | 180 | null
| 1 | joe | 90 | 160 | null
I hope someone knows the answer to this or can at least provide
a different solution.
Regards,
Rasmus Oudal EdbergSelect UAT.UserId, U.UserName
, Min(Case When UAT.AttributeTypeId = 1 Then UAT.Text End) As Height
, Min(Case When UAT.AttributeTypeId = 2 Then UAT.Text End) As Weight
From Users As U
Join UserAttributes As UAT
On U.UserId = UAT.UserId
Group By UAT.UserId, U.UserName
When You add AttributeTypeId = 3 for IQ you would need to adjust the query l
ike
so:
Select UAT.UserId, U.UserName
, Min(Case When UAT.AttributeTypeId = 1 Then UAT.Text End) As Height
, Min(Case When UAT.AttributeTypeId = 2 Then UAT.Text End) As Weight
, Min(Case When UAT.AttributeTypeId = 3 Then UAT.Text End) As IQ
From Users As U
Join UserAttributes As UAT
On U.UserId = UAT.UserId
Group By UAT.UserId, U.UserName
There is no way to dynamically add columns to this crosstab type result with
out
using dynamic SQL or an outside reporting tool.
Thomas
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a tre table setup in order to support dynamic attributs of
> users:
> user table:
> | *userid* | *username*
> | 1 | joe
> | 2 | james
> attributetype table:
> | *attributetypeid* | *attributename* |
> | 1 | height
> | 2 | weight
> userattribute table:
> | *userattributeid* | *userid* | attributetypeid* |*text*
> | 1 | 1 | 1 | 80
> | 2 | 1 | 2 | 180
> | 1 | 2 | 1 | 90
> | 2 | 2 | 2 | 160
> (the userid and attributetypeid are fk to the user and attributetype table
s.)
> Now i would like to create a sql statement that returns something like:
> | *userid* | *username* | *height* | *weight*
> | 1 | joe | 80 | 180
> | 1 | joe | 90 | 160
> The should support that I add an additional attributetype, say "IQ"
> and then it should return something like:
> | *userid* | *username* | *height* | *weight* | *IQ*
> | 1 | joe | 80 | 180 | null
> | 1 | joe | 90 | 160 | null
> I hope someone knows the answer to this or can at least provide
> a different solution.
>
> Regards,
> Rasmus Oudal Edberg
>|||what you're looking for is xtab.
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...b;EN-US;q175574
Dynamic Crosstab Queries
http://www.sqlmag.com/Articles/Inde...ArticleID=15608
xp_ags_crosstabExtended Stored Procedure
http://www.ag-software.com/xp_ags_crosstab.asp
and our RAC crosstab and pivoting utility
http://www.rac4sql.net
-oj
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a tre table setup in order to support dynamic attributs of
> users:
> user table:
> | *userid* | *username*
> | 1 | joe
> | 2 | james
> attributetype table:
> | *attributetypeid* | *attributename* |
> | 1 | height
> | 2 | weight
> userattribute table:
> | *userattributeid* | *userid* | attributetypeid* |*text*
> | 1 | 1 | 1 | 80
> | 2 | 1 | 2 | 180
> | 1 | 2 | 1 | 90
> | 2 | 2 | 2 | 160
> (the userid and attributetypeid are fk to the user and attributetype
> tables.)
> Now i would like to create a sql statement that returns something like:
> | *userid* | *username* | *height* | *weight*
> | 1 | joe | 80 | 180
> | 1 | joe | 90 | 160
> The should support that I add an additional attributetype, say "IQ"
> and then it should return something like:
> | *userid* | *username* | *height* | *weight* | *IQ*
> | 1 | joe | 80 | 180 | null
> | 1 | joe | 90 | 160 | null
> I hope someone knows the answer to this or can at least provide
> a different solution.
>
> Regards,
> Rasmus Oudal Edberg
>|||You are mixing dat and metadata in the schema. This is a serious
design flaw. Get a book on basic data modeling before you go any
further.|||On Fri, 29 Apr 2005 17:38:49 -0700, --CELKO-- wrote:

> You are mixing dat and metadata in the schema. This is a serious
> design flaw. Get a book on basic data modeling before you go any
> further.
Sounds like a correct observation to me.
However going through some of my DB books has
not given me an answer to an alternative datamodel
that would support the dynamic attributes I described
in my problem.
If however someone could point me in the right
direction that would help me a lot.
/Rasmus|||Hi oj,
Could you give me an example of this query using your rac4sql.
/Rasmus
"oj" <nospam_ojngo@.home.com> wrote in message
news:eNUOtYQTFHA.2996@.TK2MSFTNGP15.phx.gbl...
> what you're looking for is xtab.
> HOW TO: Rotate a Table in SQL Server
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Dynamic Crosstab Queries
> http://www.sqlmag.com/Articles/Inde...ArticleID=15608
> xp_ags_crosstabExtended Stored Procedure
> http://www.ag-software.com/xp_ags_crosstab.asp
> and our RAC crosstab and pivoting utility
> http://www.rac4sql.net
> --
> -oj
>
> "Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
> news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Here is an example.
create table #users(userid int, username sysname)
insert #users select 1,'joe' union all select 2,'james'
create table #attributetypes(attributetypeid int,attributename sysname)
insert #attributetypes select 1,'height' union all select 2,'weight'
create table #userattributes(userattributeid int,userid int,attributetypeid
int,[text] int)
insert #userattributes select 1,1,1,80 union all select 2,1,2,180 union all
select 3,2,1,90 union all select 4,2,2,160
go
exec rac
@.transform='max(ua.[text])',
@.rows='u.userid & u.username',
@.pvtcol='a.attributename',
@.from='#userattributes ua join #users u on ua.userid=u.userid join
#attributetypes a on ua.attributetypeid=a.attributetypeid'
,@.shell='n'
-- ,@.row_totals='n'
-- ,@.grand_totals='n'
-- ,@.printagg='n'
,@.defaults1='y'
go
insert #attributetypes select 3,'iq'
insert #userattributes select 5,1,3,100 union all select 6,2,3,129
go
exec rac
@.transform='max(ua.[text])',
@.rows='u.userid & u.username',
@.pvtcol='a.attributename',
@.from='#userattributes ua join #users u on ua.userid=u.userid join
#attributetypes a on ua.attributetypeid=a.attributetypeid'
,@.shell='n'
-- ,@.row_totals='n'
-- ,@.grand_totals='n'
-- ,@.printagg='n'
,@.defaults1='y'
go
drop table #userattributes,#users,#attributetypes
go
--RESULT--
userid username height weight
-- -- -- --
1 joe 80 180
2 james 90 160
(1 row(s) affected)
(2 row(s) affected)
userid username height iq weight
-- -- -- -- --
1 joe 80 100 180
2 james 90 129 160
-oj
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:%23OKxKIdTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi oj,
> Could you give me an example of this query using your rac4sql.
> /Rasmus
>|||>> an alternative data model that would support the an alternative
datamodel that would support the dynamic attributes I described in my
problem. <<
There are no "dynamic attributes" in a properly designed RDBMS. The
idea of data modeling is that you know what the world looks like before
you build the model. You enforce the rules, define the entities and
their attributes. You do not let an unknown user, present or future,
create anything in the model on the fly. Your users are users, not
magicians that can make elephants fall out of the sky.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114959059.283784.264640@.g14g2000cwa.googlegroups.com...
> Your users are users, not
> magicians that can make elephants fall out of the sky.
>
Which would have scared the heck out of Hannibal as well as the Romans, BTW.

advanced query help needed

I have 2 tables
mvm_campi
id_camp dsc_cam
1 desc1
2 desc2
3 desc3
mvm_prova
id_prova id_camp desc
1 2 x
2 2 y
3 2 z
3 1 k
Is there a way to get an SQL query result as following
idcamp(1) idcamp(2)
desc1 desc2
idprova(1) null x
2 null y
3 k zTry,
select
a.id_prova,
min(case when b.dsc_cam = 'desc1' then a.col_desc end) as desc1,
min(case when b.dsc_cam = 'desc2' then a.col_desc end) as desc2
from
mvm_prova as a
inner join
mvm_campi as b
on a.id_camp = b.id_camp
group by
a.id_prova
order by
a.id_prova
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"Denis" wrote:

> I have 2 tables
> mvm_campi
> id_camp dsc_cam
> 1 desc1
> 2 desc2
> 3 desc3
> mvm_prova
> id_prova id_camp desc
> 1 2 x
> 2 2 y
> 3 2 z
> 3 1 k
> Is there a way to get an SQL query result as following
> idcamp(1) idcamp(2)
> desc1 desc2
> idprova(1) null x
> 2 null y
> 3 k z
>
>
>|||Your query doesn't work because
it has 'desc1' an 'desc2' static, but dsc_cam of table mvm_campi is dynamic
(inserted by the user)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> ha scritto nel
messaggio news:CE3D53F3-4664-4739-96EF-70552A14A037@.microsoft.com...
> Try,
> select
> a.id_prova,
> min(case when b.dsc_cam = 'desc1' then a.col_desc end) as desc1,
> min(case when b.dsc_cam = 'desc2' then a.col_desc end) as desc2
> from
> mvm_prova as a
> inner join
> mvm_campi as b
> on a.id_camp = b.id_camp
> group by
> a.id_prova
> order by
> a.id_prova
> go
> HOW TO: Rotate a Table in SQL Server
> http://support.microsoft.com/defaul...574&Product=sql
>
> AMB
>
> "Denis" wrote:
>
>|||Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Denis" wrote:

> Your query doesn't work because
> it has 'desc1' an 'desc2' static, but dsc_cam of table mvm_campi is dynami
c
> (inserted by the user)
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> ha scritto nel
> messaggio news:CE3D53F3-4664-4739-96EF-70552A14A037@.microsoft.com...
>
>|||Do you have something against making things (ie. dynamic pivoting) as simple
as
possible for the user? :)
Check out the RAC utility for S2k.
www.rac4sql.net
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:DFA21DFD-A83E-453B-8311-9497C5D333E4@.microsoft.com...
> Dynamic Crosstab Queries
> http://www.windowsitpro.com/SQLServ...5608/15608.html
> Dynamic Cross-Tabs/Pivot Tables
> http://www.sqlteam.com/item.asp?ItemID=2955
>
> AMB
> "Denis" wrote:
>

Advanced Query

I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName

so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'.

I would imagine I would need a Declare @.Status nvarchar(15) -- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.

Add this as a column:

CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status

And remove AND (ev.personalScore IS NULL) from the WHERE clause.

Like:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ev.PersonalScore IS NULL THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName

Of course, that assumes that every employee can only have 0 or 1 records in the employeeeval table. If you want to test if they have 0 or >0 records, then it would be like this:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,CASE WHEN ISNULL(EvalCount,0)=0 THEN 'Not Started' ELSE 'In Progress' END AS Status

FROM Employee e

LEFT OUTER JOIN (SELECT COUNT(*) EvalCount,EmployeeID FROM EmployeeEval GROUP BY EmployeeID) ev ON e.EmployeeID = ev.EmployeeID

LEFT OUTER JOIN [User] u ON u.Department = e.DeptID

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @.CompanyID) AND (e.FacilityID = @.FacilityID) ORDER BY e.DeptID, e.LastName

|||

Gah, I think I finally understand what it is that you want. Questions I have:

Can there be more than 1 row in the EmployeeEval table for an employee?

It seems like someone (The employee) is starting an eval request, and you enter a row into the EmployeeEval table (With approved=0). The supervisor gets notified and they mark approved=1 when they pick up the request for eval. When the eval is completed, you then remove the row from EmployeeEval? Is that correct?

Can there ever be more than 1 row per employee in the eval table? What happens if an unapproved eval is in the table and they request again? Do you deny the request and not make another row? What if it's in progress (approved=1)? Deny and don't enter another row as well?

|||

thanks, but I had to place the approved column in place of the personalscore so it looks like this:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

As you stated above they can only have 0 or 1 records that's why I went w/the first one. Thanks for the help.

|||Everytime I get one thing working people keep requesting things I just don't have the experience to complete. I now need the ability w/the forementioned query this: I need to count all employees in each dept and if they match the # in the employeeeval table place in a simple text that says Reviews for Dept. 100% complete.

I've tried working ways of using the Count statement, but I'm lost again.|||

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

I'm starting to get a wee bit confused on what exactly you want back from the query. I'm having serious doubts that the above query is actually going to return what you want it to, but.. To add your last request you can do it two ways.

Add these to your FROM clause:

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)

Then add this to your columns:

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete

if you want the % to be accurate, or the following if you want it to 100% or some other message if not 100%:

CASE WHEN c1.cnt=c2.cnt THEN 'Reviews for Dept. 100% complete' ELSE 'Not done yet!' END As DeptComplete

And you should end up with something like:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100,0) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||

The query is being used to create a dataset, which in turn is used to populate a crystal report. The users, however keep wanting new requests. So I'm forced to do this through my Dataset.

I really don't need a percentage brought back, I just need to know when the #'s match in the employeeeval table and employee table, and say either incomplete or complete.

When I ran your query it tells me 3 errors--

Server: Msg 174, Level 15, State 1, Line 3
The floor function requires 1 arguments.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'c1'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'c2'.

|||

Try:

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||

nope now I'm getting the following:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Employee.DeptID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

In the above however, I tried removing e.deptid and still got the same error. Where is it pulling Employee.DeptID from?

|||

Doh! My bad.

SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status,

'Reviews for Dept. ' + CAST(FLOOR(c1.cnt/c2.cnt*100) as varchar(3)) + 'complete.' AS DeptComplete

FROM Employee e
LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeGROUP BY DeptID) c1 ON (c1.DeptID=e.DeptID)

LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID)GROUP BY DeptID) c2 ON (c2.DeptID=e.DeptID)

WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

|||Thanks for being so patient with me, I have a few me questions. Can you break down a couple of things for me. What does the Cast(Floor portion of the code do? As well what does (SELECT COUNT(*) cnt,DeptID FROM Employee do? <--for this what does the cnt, DeptID do ? I imagine cnt is like a variable for C1 or C2 but I'm a little lost on it.

Another thing I would like to be able to do--If the number of employees in a specific dept = the number of employees in the Employeeeval table I would like to return something like --Reviews are 100% complete for this dept. I don't need the employee name or anything like what I brought back in the SELECT statement.--For this I realize I'll probably need a new procedure. So I'll work on that and if I need help I'm sure I can find help on here.|||

The CAST(FLOOR(c1/c2*100) AS varchar(3)) is two steps, the first FLOOR(c1/c2*100) takes c1 divides it by c2, multiplies it by 100, and then floor says to remove any fraction, always rounding down. (99.9 becomes 99, etc). Then once we have an integer (0-100), we then say take that integer and make it a varchar(3) which is a variable length string up to 3 bytes/characters long.

As for (SELECT COUNT(*) cnt,DeptID FROM Employee ...
I take the count of records where the DeptID is the same and create a derived table from the result. Specifically, I am calling the count of records 'cnt'. DeptID is the second column taken from Employee.

Think of it this way... If you do:

SELECT COUNT(*),DeptID FROM Employee GROUP BY DeptID and your employee table looks like:

DeptId/{other columns}
1/
1
2
2
3
3
3

Then the result of the query would be:
COUNT(*)/DeptID
2/1
2/2
3/3

Instead of allowing the first column name be "COUNT(*)" since it has no other real name, COUNT(*) cnt says that I want the count, but call the column 'cnt'.

That make any sense?

|||

Yes--I just wish I could totally grasp T-SQL--So I'm trying to do the portion of SQL I asked in my last post--is there a way to incorporate all depts in this? so I still show only employees who have ev.PersonalScore is NULL but show the All Departments --Kinda like this:

Dept 1 - Supervisor 1 - EmpName - EmployeeID - Status- DeptStatus

But if the dept has all employees w/value in PersonalScore and the counts match -- put Dept Status = 'Dept has all reviews complete' --See I get lost as to where I put this to try and attempt to make it work.

|||Ok so I got what I wanted for just the Dept. but I would like to incorporate it into my other query so I don't have to run a subreport--b/c that is a pain.

select distinct d.[description],
Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
from department d LEFT OUTER JOIN [user] u ON d.deptid = u.department
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)
ORDER BY d.[description]

I just wouldn't know how to place this in my SELECT for the previous query b/c I need to use the c1 & c2 with d.DeptID instead of Employee|||

Sure... I can help you, but since I'm not totally sure on what you want out of it, let me explain this part.

Take the last query you have, and run that in management studio, or query analyzer, etc. See the results you get back? You can call it a resultset, or you can call it a table. Technically, I believe it should be called a resultset, which is like a table, but it's ordered. In any case, remove the ORDER BY from it, and you should see that the results don't really change, except that now it's in no particular order anymore.

If you now think of that as being a "table" you can now select from, aggregate, join etc, this is how you can make some very complex queries possible. I can see how you might want to use something like this in different queries. You have two options now. You can take that whole query and put parenthesis around it, and use it just about anywhere you can use a table if you give it an alias. For example...

SELECT myalias.description, myalias.status
FROM (your query here) myalias
WHERE ...

You can also use it in joins like...

SELECT myalias.description,myalias.status
FROM (your query here) myalias
JOIN (some other table) myalias2 ON (some condition)
WHERE ...

You can also make your query a view, which basically saves it so you can reuse it easily like...

CREATE VIEW DepartmentStatusView AS
(your query here)

Then you can use it almost anywhere:
SELECT *
FROM DepartmentStatusView
WHERE ...

However, to be truly useful, you will need to include the DeptID in your query, since that is what you would most likely be using in a join condition, so lets go ahead and build something like that:

CREATE VIEW vw_DepartmentStatus AS
select d.*,Case when c1.cnt = c2.cnt then 'Reviews are complete' else 'Reviews still in Progress' END AS Status
FROM department d
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM Employee GROUP BY DeptID) c1 ON (c1.DeptID=d.DeptID)
LEFT JOIN (SELECT COUNT(*) cnt,DeptID FROM EmployeeEval JOIN Employee ON (EmployeeEval.EmployeeID=Employee.EmployeeID) GROUP BY DeptID) c2 ON (c2.DeptID=d.DeptID)

Now, anytime you want the department status, you can simply join in vw_DepartmentStatus using the DeptID for the ON clause, and you can reference it. Infact, you can simply think of vw_DepartmentStatus as the departments table with an extra "Status" column at the end if that helps.

If you want to add the "Status" on any query that you had before, so long as you were using Department, you can simply change your reference from Department to vw_DepartmentStatus and now you can reference the Status column like going from:

SELECT (some fields)
FROM departments d
JOIN (some stuff)
WHERE (some conditions)

to:

SELECT (some fields),d.status
FROM vw_DepartmentStatus d
JOIN (some stuff)
WHERE (some conditions)

Note the only change was to add d.status as a column you wanted to return and the change from departments to vw_DepartmentStatus

Advanced Index Statistics Query

I have noticed that running UPDATE STATISTICS without specifying any sampling
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pro.../qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:

> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pro.../qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>
|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:

Advanced Index Statistics Query

I have noticed that running UPDATE STATISTICS without specifying any samplin
g
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UP
DATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/pr...5/qrystats.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampli
ng
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBC
C
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hen
ce
> lessening the usefulness of the statistics. The values for EQ_ROWS are al
so
> much less accurate, which I suppose you'd expect with a smaller sample siz
e.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:

> DBREINDEX will build the statistics based on all rows, i.e., no sampling.
UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/pr...5/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE
option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...[vbcol=seagreen]
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>