query question

I saw a query in which there was a expression
SELECT
case when credit_flag&1 = 1 then 1 else 0 end as credit_default_flag1,
case when credit_flag&2 = 2 then 1 else 0 end as credit_default_flag2,
case when credit_flag&4 = 4 then 1 else 0 end as credit_default_flag3
FROM ...
What happens when we do column & 1, column & 2
THanks
Sanjay& is the bit-wise AND operator in T-SQL. So credit_flag &
1 will return 1 on the first bit only when the first bit
from the right (of credit_flag) is set to 1. And
credit_flag & 4 will return 1 on the third bit (thus
decimal 4) only when the third bit from the right (of
credit_flag) is set to 1.
Linchi
>--Original Message--
>I saw a query in which there was a expression
>SELECT
>case when credit_flag&1 = 1 then 1 else 0 end as
credit_default_flag1,
>case when credit_flag&2 = 2 then 1 else 0 end as
credit_default_flag2,
>case when credit_flag&4 = 4 then 1 else 0 end as
credit_default_flag3
>FROM ...
>
>What happens when we do column & 1, column & 2
>THanks
>Sanjay
>.
>|||HI Linch
Do you have some white paper or document which would explain more about these bit-wise operation
I think you explained what the result would be but i am not clear how bit-wise operations work at first plac
Thanks|||The bit-wise AND operator (also OR and XOR) is supported
in most, if not all, programming languages. You can pick
up any programming tutorial book and find information on
the bit-wise operations. You can also find the info in the
SQL Server Books Online.
Linchi
>--Original Message--
>HI Linchi
>Do you have some white paper or document which would
explain more about these bit-wise operations
>I think you explained what the result would be but i am
not clear how bit-wise operations work at first place
>Thanks
>.
>

Query question

Hi,
I am trying to use two databases in one query... and here is the simple
query I have created but it it throwing an error:
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '@.db1'.
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE @.db1
USE @.db2
SELECT * from authors
SELECT * from customers
What is that I am doing wrong... I went through SQL manual but no
help..
HJThe line number is wrong...
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.db1'.|||Oh by the way I am a complete novice to SQL and SQL server 2000 so
please exuse my stupid question...|||You can't have a variable for the USE command, quite simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147809433.886525.234400@.i39g2000cwa.googlegroups.com...
> Hi,
> I am trying to use two databases in one query... and here is the simple
> query I have created but it it throwing an error:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '@.db1'.
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE @.db1
> USE @.db2
> SELECT * from authors
> SELECT * from customers
> What is that I am doing wrong... I went through SQL manual but no
> help..
> HJ
>|||got ya.. so how about something like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
set @.db1 = 'PUBS'
set @.db2 = 'NORTHWIND'
USE PUBS
USE NORTHWIND
SELECT * from @.db1.dbo.authors
SELECT * from @.db2.dbo.customers|||Same problem. You cannot have a variable as the database qualifier in a SELECT statement.
Perhaps you can explain what you want to achieve in the end, and you might get some suggestions...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hitesh Joshi" <hitesh287@.gmail.com> wrote in message
news:1147810701.999538.273200@.38g2000cwa.googlegroups.com...
> got ya.. so how about something like this...
> Declare @.db1 varchar(40)
> Declare @.db2 varchar(40)
> set @.db1 = 'PUBS'
> set @.db2 = 'NORTHWIND'
> USE PUBS
> USE NORTHWIND
> SELECT * from @.db1.dbo.authors
> SELECT * from @.db2.dbo.customers
>|||Here is what I want to do... I want to use two different tables from
two different databases on the same server. I could always write full
names of the tables and databases in my script but as I am a novice, I
thought there must be someway to put table_names and db_names in a
variable...
After doing some research and copy & paste... I came with something
like this...
Declare @.db1 varchar(40)
Declare @.db2 varchar(40)
Declare @.tbl_name1 varchar (40)
Declare @.tbl_name2 varchar (40)
select @.db1 = 'PUBS'
select @.db2 = 'NORTHWIND'
select @.tbl_name1 = 'authors'
select @.tbl_name2 = 'customers'
/*USE PUBS
USE NORTHWIND */
EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
That worked but still do not understand that EXEC () thingy...
anyother way to accomplish this?
Thank you
HJ|||On 16 May 2006 13:37:04 -0700, Hitesh Joshi wrote:
(snip)
>EXEC ('SELECT * from ' + @.db1 + '..' + @.tbl_name1)
>EXEC ('SELECT * from ' + @.db2 + '..' + @.tbl_name2)
>That worked but still do not understand that EXEC () thingy...
>anyother way to accomplish this?
Hi HJ,
http://www.sommarskog.se/dynamic_sql.html
--
Hugo Kornelis, SQL Server MVPsql

Query question

Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
--
Paul G
Software engineer.The CASE statement is there to include or exclude that condition in the
query; however, it is a lousy implementation. You are obviously using
dynamic sql inside of a stored procedure. Other than a convenient place to
put it, dyanmical sql inside a proc reduces the effectiveness of using
stored procedure.
The '%' before and after the passed in parameter are wildcard characters
that allow any string as a substitute. So, any string plus parameter plus
any string becomes the search condition. You are gauranteed to do a table
scan or clustered index scan as that criteria could never be supported by an
index.
As a better solution, try something more like this:
SELECT Col1, Col2, ..., Coln
FROM Tab1 JOIN Tab2
ON Tab1.Key1 = Tab2.Key1
AND Tab1.Key1 = Tab2.Key2
...
AND Tab1.Keyn = Tab2.Keyn
...
...
JOIN Tabn
ON ...
WHERE criterion1 AND criterion2 ... AND criterionN
AND (@.stringvar IS NULL
OR TabX.ColX LIKE (@.stringvar + '%')
)
This is executed directly. There is no need for a variable nor the use of
the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highly
selectable. The query execution plan can be reused.
Hope this helps.
Sincerely,
Anthony Thomas
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
--
Paul G
Software engineer.|||Hi thanks for the response. It did seem like there would be a better way
other than the dynamic sql. Someone had suggested to me from this newsgroup
to use it so I went that route. No official SQL training so just learning by
trail and error.
"AnthonyThomas" wrote:
> The CASE statement is there to include or exclude that condition in the
> query; however, it is a lousy implementation. You are obviously using
> dynamic sql inside of a stored procedure. Other than a convenient place to
> put it, dyanmical sql inside a proc reduces the effectiveness of using
> stored procedure.
> The '%' before and after the passed in parameter are wildcard characters
> that allow any string as a substitute. So, any string plus parameter plus
> any string becomes the search condition. You are gauranteed to do a table
> scan or clustered index scan as that criteria could never be supported by an
> index.
> As a better solution, try something more like this:
> SELECT Col1, Col2, ..., Coln
> FROM Tab1 JOIN Tab2
> ON Tab1.Key1 = Tab2.Key1
> AND Tab1.Key1 = Tab2.Key2
> ...
> AND Tab1.Keyn = Tab2.Keyn
> ...
> ...
> JOIN Tabn
> ON ...
> WHERE criterion1 AND criterion2 ... AND criterionN
> AND (@.stringvar IS NULL
> OR TabX.ColX LIKE (@.stringvar + '%')
> )
> This is executed directly. There is no need for a variable nor the use of
> the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highly
> selectable. The query execution plan can be reused.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
> Hi I have a large stored procedure that performs several queries
> conditionally on a database depending on values that are passed into the
> procedure. Anyhow I am using a string search and have set up
> SELECT @.stringvar='%' + @.stringvar + '%'
> then have
> case when @.stringvar IS NOT NULL then
> 'AND (table.field LIKE @.stringvar)'
> ELSE ''
> It works fine just could not remember why I needed to have the
> SELECT @.stringvar='%' + @.stringvar + '%' statement.
> thanks,
> --
> Paul G
> Software engineer.
>
>

query question

I have shopping cart and the items are stored in a
StoreCart table. I'm trying to write a query that
returns the items in the cart in certain way but I don't
know how to do it. Say for example that I have the
following items:
Product Qty
-- --
Toy_1 3
Toy_2 1
Toy_3 2
I want to write a query that will return the item the
number of times that the Qty field has.
Product
--
Toy_1
Toy_1
Toy_1
Toy_2
Toy_3
Toy_3
How can I do this?
TIA,
Vic"Vic" <vduran@.specpro-inc.com> wrote in message
news:0e4201c53fcc$350cb270$a501280a@.phx.gbl...
>I have shopping cart and the items are stored in a
> StoreCart table. I'm trying to write a query that
> returns the items in the cart in certain way but I don't
> know how to do it. Say for example that I have the
> following items:
> Product Qty
> -- --
> Toy_1 3
> Toy_2 1
> Toy_3 2
> I want to write a query that will return the item the
> number of times that the Qty field has.
> Product
> --
> Toy_1
> Toy_1
> Toy_1
> Toy_2
> Toy_3
> Toy_3
>
create table N(i int primary key)
go
set nocount on
declare @.i int
set @.i = 0
while @.i < 10000
begin
insert into N(i) values (@.i)
set @.i = @.i + 1
end
go
create table cart(product varchar(50) not null, Qty int not null, primary
key (Product,qty))
insert into cart(product,qty)values('Toy_1',3)
insert into cart(product,qty)values('Toy_2',1)
insert into cart(product,qty)values('Toy_3',2)
go
select product
from
cart
join N on n.i < cart.qty
order by product
David

query question

Hi I have 3 tables and am wondering if there is a way to do this. I would
like a qerry that returns data for a data item but will return only the # of
records for the event and not the destination. For example if the query
returns data for Data ID 4
I would want to see as the results.
Data name event destination
report recieved office
report intransit frontdesk
Even though there are 3 destinations I would like to only list the first 2
and base the number of records returned on the number of events. Thanks.
table1 event
******************************************
*pri key Count * foriegn key Data ID * event *
* 1 * 3 * arrived *
* 2 * 4 * recieved *
* 3 * 4 * intransit *
******************************************
table2 data item
*****************************
* Pri key Data ID * Data name *
* 3 * email *
* 4 * report *
*****************************
table3- destination-note no prim key
**********************************
* Data ID * destination *
* 4 * office *
* 4 * frontdesk *
* 4 * mailroom *
**********************************
--
Paul G
Software engineer.Hi Paul
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
and example data in a usable format, also posting the expected output from
the data provided would be helpful e.g.
CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
INSERT INTO [event] ( [Count], [Data ID], [event] )
SELECT 1, 3, 'arrived'
UNION ALL SELECT 2, 4, 'recieved'
UNION ALL SELECT 3, 4, 'intransit'
CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
INSERT INTO [data item] ( [Data ID], [Data name] )
SELECT 3, 'email'
UNION ALL SELECT 4, 'report'
CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
INSERT INTO destination ( [Data ID], destination )
SELECT 4, 'office'
UNION ALL SELECT 4, 'frontdesk'
UNION ALL SELECT 4, 'mailroom'
There is no way to easily distinguish your destinations e.g
SELECT d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
report intransit mailroom
report recieved office
report recieved mailroom
report recieved frontdesk
(6 row(s) affected)
Limiting this to top 2
SELECT TOP 2 d.[Data name], e.[Event], f.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN destination f ON f.[Data Id] = e.[Data Id]
ORDER BY e.[Count] DESC
Returns
Data name Event destination
-- -- --
report intransit office
report intransit frontdesk
(2 row(s) affected)
You could give destination alphabetical rank such as
SELECT f.[Data ID], f.destination,
( SELECT COUNT(*) FROM destination b WHERE f..destination <
b.destination )+1 AS RANK
FROM destination f
This may then allow you to pick different destinations by joining to the
event count (you may have to rank these to get similar number ranges.
SELECT TOP 2 d.[Data name], e.[Event], g.destination
FROM [Data Item] d
JOIN [Event] e ON d.[Data Id] = e.[Data Id]
JOIN ( SELECT f.[Data ID],
f.destination,
( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
AS RANK
FROM destination f
) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
ORDER BY e.[Count] DESC
Data name Event destination
-- -- --
report intransit frontdesk
report recieved mailroom
(2 row(s) affected)
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> Hi I have 3 tables and am wondering if there is a way to do this. I would
> like a qerry that returns data for a data item but will return only the #
> of
> records for the event and not the destination. For example if the query
> returns data for Data ID 4
> I would want to see as the results.
> Data name event destination
> report recieved office
> report intransit frontdesk
> Even though there are 3 destinations I would like to only list the first 2
> and base the number of records returned on the number of events. Thanks.
> table1 event
> ******************************************
> *pri key Count * foriegn key Data ID * event *
> * 1 * 3 * arrived
> *
> * 2 * 4 * recieved
> *
> * 3 * 4 * intransit
> *
> ******************************************
> table2 data item
> *****************************
> * Pri key Data ID * Data name *
> * 3 * email *
> * 4 * report *
> *****************************
> table3- destination-note no prim key
> **********************************
> * Data ID * destination *
> * 4 * office *
> * 4 * frontdesk *
> * 4 * mailroom *
> **********************************
>
> --
> Paul G
> Software engineer.|||hi thanks for the response. Does seem quite useful to use DDL, will do this
in the future. Unfortunately I will net be able to use a constant for the #
of records returned as the number of records returned for each data item will
not be the destinations but the number of events for each data item. Guess I
will probably use 2 queries the first one getting the # of events for each
data item and the second returning the data name, event and destination and
using the TOP or ROWCOUNT to limit the # of records returned. Thanks again,
Paul.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi Paul
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> and example data in a usable format, also posting the expected output from
> the data provided would be helpful e.g.
> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> INSERT INTO [event] ( [Count], [Data ID], [event] )
> SELECT 1, 3, 'arrived'
> UNION ALL SELECT 2, 4, 'recieved'
> UNION ALL SELECT 3, 4, 'intransit'
> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> INSERT INTO [data item] ( [Data ID], [Data name] )
> SELECT 3, 'email'
> UNION ALL SELECT 4, 'report'
>
> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> INSERT INTO destination ( [Data ID], destination )
> SELECT 4, 'office'
> UNION ALL SELECT 4, 'frontdesk'
> UNION ALL SELECT 4, 'mailroom'
>
> There is no way to easily distinguish your destinations e.g
> SELECT d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> report intransit mailroom
> report recieved office
> report recieved mailroom
> report recieved frontdesk
> (6 row(s) affected)
>
> Limiting this to top 2
> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN destination f ON f.[Data Id] = e.[Data Id]
> ORDER BY e.[Count] DESC
> Returns
> Data name Event destination
> -- -- --
> report intransit office
> report intransit frontdesk
> (2 row(s) affected)
> You could give destination alphabetical rank such as
>
> SELECT f.[Data ID], f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> b.destination )+1 AS RANK
> FROM destination f
> This may then allow you to pick different destinations by joining to the
> event count (you may have to rank these to get similar number ranges.
> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> FROM [Data Item] d
> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> JOIN ( SELECT f.[Data ID],
> f.destination,
> ( SELECT COUNT(*) FROM destination b WHERE f.destination < b.destination )+1
> AS RANK
> FROM destination f
> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> ORDER BY e.[Count] DESC
> Data name Event destination
> -- -- --
> report intransit frontdesk
> report recieved mailroom
> (2 row(s) affected)
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> > Hi I have 3 tables and am wondering if there is a way to do this. I would
> > like a qerry that returns data for a data item but will return only the #
> > of
> > records for the event and not the destination. For example if the query
> > returns data for Data ID 4
> > I would want to see as the results.
> > Data name event destination
> > report recieved office
> > report intransit frontdesk
> >
> > Even though there are 3 destinations I would like to only list the first 2
> > and base the number of records returned on the number of events. Thanks.
> >
> > table1 event
> > ******************************************
> > *pri key Count * foriegn key Data ID * event *
> > * 1 * 3 * arrived
> > *
> >
> > * 2 * 4 * recieved
> > *
> >
> > * 3 * 4 * intransit
> > *
> >
> > ******************************************
> > table2 data item
> > *****************************
> > * Pri key Data ID * Data name *
> > * 3 * email *
> >
> > * 4 * report *
> >
> > *****************************
> > table3- destination-note no prim key
> > **********************************
> > * Data ID * destination *
> > * 4 * office *
> >
> > * 4 * frontdesk *
> > * 4 * mailroom *
> >
> > **********************************
> >
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>|||Hi
SQL 2005 allows top to take a variable, but that may not be any use to
yourself. You may be able to use a having clause if you can formulate the
number of rows required and then use the ranking as the tested value.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> hi thanks for the response. Does seem quite useful to use DDL, will do
> this
> in the future. Unfortunately I will net be able to use a constant for the
> #
> of records returned as the number of records returned for each data item
> will
> not be the destinations but the number of events for each data item.
> Guess I
> will probably use 2 queries the first one getting the # of events for each
> data item and the second returning the data name, event and destination
> and
> using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> again,
> Paul.
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
>> Hi Paul
>> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
>> and example data in a usable format, also posting the expected output
>> from
>> the data provided would be helpful e.g.
>> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
>> INSERT INTO [event] ( [Count], [Data ID], [event] )
>> SELECT 1, 3, 'arrived'
>> UNION ALL SELECT 2, 4, 'recieved'
>> UNION ALL SELECT 3, 4, 'intransit'
>> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
>> INSERT INTO [data item] ( [Data ID], [Data name] )
>> SELECT 3, 'email'
>> UNION ALL SELECT 4, 'report'
>>
>> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
>> INSERT INTO destination ( [Data ID], destination )
>> SELECT 4, 'office'
>> UNION ALL SELECT 4, 'frontdesk'
>> UNION ALL SELECT 4, 'mailroom'
>>
>> There is no way to easily distinguish your destinations e.g
>> SELECT d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> report intransit mailroom
>> report recieved office
>> report recieved mailroom
>> report recieved frontdesk
>> (6 row(s) affected)
>>
>> Limiting this to top 2
>> SELECT TOP 2 d.[Data name], e.[Event], f.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN destination f ON f.[Data Id] = e.[Data Id]
>> ORDER BY e.[Count] DESC
>> Returns
>> Data name Event destination
>> -- -- --
>> report intransit office
>> report intransit frontdesk
>> (2 row(s) affected)
>> You could give destination alphabetical rank such as
>>
>> SELECT f.[Data ID], f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
>> b.destination )+1 AS RANK
>> FROM destination f
>> This may then allow you to pick different destinations by joining to the
>> event count (you may have to rank these to get similar number ranges.
>> SELECT TOP 2 d.[Data name], e.[Event], g.destination
>> FROM [Data Item] d
>> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
>> JOIN ( SELECT f.[Data ID],
>> f.destination,
>> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
>> b.destination )+1
>> AS RANK
>> FROM destination f
>> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
>> ORDER BY e.[Count] DESC
>> Data name Event destination
>> -- -- --
>> report intransit frontdesk
>> report recieved mailroom
>> (2 row(s) affected)
>> John
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
>> > Hi I have 3 tables and am wondering if there is a way to do this. I
>> > would
>> > like a qerry that returns data for a data item but will return only the
>> > #
>> > of
>> > records for the event and not the destination. For example if the
>> > query
>> > returns data for Data ID 4
>> > I would want to see as the results.
>> > Data name event destination
>> > report recieved office
>> > report intransit frontdesk
>> >
>> > Even though there are 3 destinations I would like to only list the
>> > first 2
>> > and base the number of records returned on the number of events.
>> > Thanks.
>> >
>> > table1 event
>> > ******************************************
>> > *pri key Count * foriegn key Data ID * event *
>> > * 1 * 3 * arrived
>> > *
>> >
>> > * 2 * 4 * recieved
>> > *
>> >
>> > * 3 * 4 * intransit
>> > *
>> >
>> > ******************************************
>> > table2 data item
>> > *****************************
>> > * Pri key Data ID * Data name *
>> > * 3 * email *
>> >
>> > * 4 * report *
>> >
>> > *****************************
>> > table3- destination-note no prim key
>> > **********************************
>> > * Data ID * destination *
>> > * 4 * office *
>> >
>> > * 4 * frontdesk *
>> > * 4 * mailroom *
>> >
>> > **********************************
>> >
>> >
>> >
>> > --
>> > Paul G
>> > Software engineer.
>>|||ok thanks for the additional information. I guess I could use the ROWCOUNT
which does take a variable. Hopefully we will upgrade to SQL 2005 but it
will be awhile.
--
Paul G
Software engineer.
"John Bell" wrote:
> Hi
> SQL 2005 allows top to take a variable, but that may not be any use to
> yourself. You may be able to use a having clause if you can formulate the
> number of rows required and then use the ranking as the tested value.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:0881ACE2-979C-425A-B53B-3627D46A6A05@.microsoft.com...
> > hi thanks for the response. Does seem quite useful to use DDL, will do
> > this
> > in the future. Unfortunately I will net be able to use a constant for the
> > #
> > of records returned as the number of records returned for each data item
> > will
> > not be the destinations but the number of events for each data item.
> > Guess I
> > will probably use 2 queries the first one getting the # of events for each
> > data item and the second returning the data name, event and destination
> > and
> > using the TOP or ROWCOUNT to limit the # of records returned. Thanks
> > again,
> > Paul.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> >> Hi Paul
> >>
> >> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL
> >> and example data in a usable format, also posting the expected output
> >> from
> >> the data provided would be helpful e.g.
> >> CREATE TABLE [event] ( [Count] int, [Data ID] int, [event] varchar(30) )
> >>
> >> INSERT INTO [event] ( [Count], [Data ID], [event] )
> >>
> >> SELECT 1, 3, 'arrived'
> >>
> >> UNION ALL SELECT 2, 4, 'recieved'
> >>
> >> UNION ALL SELECT 3, 4, 'intransit'
> >>
> >> CREATE TABLE [data item] ( [Data ID] int, [Data name] varchar(30) )
> >>
> >> INSERT INTO [data item] ( [Data ID], [Data name] )
> >>
> >> SELECT 3, 'email'
> >>
> >> UNION ALL SELECT 4, 'report'
> >>
> >>
> >> CREATE TABLE destination ( [Data ID] int, destination varchar(30) )
> >>
> >> INSERT INTO destination ( [Data ID], destination )
> >>
> >> SELECT 4, 'office'
> >>
> >> UNION ALL SELECT 4, 'frontdesk'
> >>
> >> UNION ALL SELECT 4, 'mailroom'
> >>
> >>
> >> There is no way to easily distinguish your destinations e.g
> >>
> >> SELECT d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> report intransit mailroom
> >>
> >> report recieved office
> >>
> >> report recieved mailroom
> >>
> >> report recieved frontdesk
> >>
> >> (6 row(s) affected)
> >>
> >>
> >>
> >> Limiting this to top 2
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], f.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN destination f ON f.[Data Id] = e.[Data Id]
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Returns
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit office
> >>
> >> report intransit frontdesk
> >>
> >> (2 row(s) affected)
> >>
> >> You could give destination alphabetical rank such as
> >>
> >>
> >>
> >> SELECT f.[Data ID], f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f..destination <
> >> b.destination )+1 AS RANK
> >>
> >> FROM destination f
> >>
> >> This may then allow you to pick different destinations by joining to the
> >> event count (you may have to rank these to get similar number ranges.
> >>
> >> SELECT TOP 2 d.[Data name], e.[Event], g.destination
> >>
> >> FROM [Data Item] d
> >>
> >> JOIN [Event] e ON d.[Data Id] = e.[Data Id]
> >>
> >> JOIN ( SELECT f.[Data ID],
> >>
> >> f.destination,
> >>
> >> ( SELECT COUNT(*) FROM destination b WHERE f.destination <
> >> b.destination )+1
> >> AS RANK
> >>
> >> FROM destination f
> >>
> >> ) g ON g.[Data Id] = e.[Data Id] AND e.[count] = g.rank
> >>
> >> ORDER BY e.[Count] DESC
> >>
> >> Data name Event destination
> >>
> >> -- -- --
> >>
> >> report intransit frontdesk
> >>
> >> report recieved mailroom
> >>
> >> (2 row(s) affected)
> >>
> >> John
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:7FC06E5C-8E29-4F97-9268-F7E9A6878C03@.microsoft.com...
> >> > Hi I have 3 tables and am wondering if there is a way to do this. I
> >> > would
> >> > like a qerry that returns data for a data item but will return only the
> >> > #
> >> > of
> >> > records for the event and not the destination. For example if the
> >> > query
> >> > returns data for Data ID 4
> >> > I would want to see as the results.
> >> > Data name event destination
> >> > report recieved office
> >> > report intransit frontdesk
> >> >
> >> > Even though there are 3 destinations I would like to only list the
> >> > first 2
> >> > and base the number of records returned on the number of events.
> >> > Thanks.
> >> >
> >> > table1 event
> >> > ******************************************
> >> > *pri key Count * foriegn key Data ID * event *
> >> > * 1 * 3 * arrived
> >> > *
> >> >
> >> > * 2 * 4 * recieved
> >> > *
> >> >
> >> > * 3 * 4 * intransit
> >> > *
> >> >
> >> > ******************************************
> >> > table2 data item
> >> > *****************************
> >> > * Pri key Data ID * Data name *
> >> > * 3 * email *
> >> >
> >> > * 4 * report *
> >> >
> >> > *****************************
> >> > table3- destination-note no prim key
> >> > **********************************
> >> > * Data ID * destination *
> >> > * 4 * office *
> >> >
> >> > * 4 * frontdesk *
> >> > * 4 * mailroom *
> >> >
> >> > **********************************
> >> >
> >> >
> >> >
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>

Query question

Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>

Query Question

I have a question that I will illustrate with the script below. As you can
see there two tables, airlines and routes. Each airline has a customer_rank
that indicates the preferred airline of the customer ie 1 is most preferred.
Each route has a name, cost and duration.
What I would like to do is write a query that returns one row for each route
with the columns : airline, cost and duration. I would like the airline
column to be based on the preferred carrier, irrespective of the cost and
duration.
Can someone please stop my head spinning?
Thanks, Tad
CREATE TABLE [dbo].[Airlines] (
[Airline] [char] (10) PRIMARY KEY,
[Customer_Rank] [tinyint] NOT NULL
)
CREATE TABLE [dbo].[Routes] (
[ID] [int] IDENTITY (1, 1) PRIMARY KEY,
[Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
[Route] [char] (10) NOT NULL ,
[Cost] [numeric](18, 0) NOT NULL ,
[Duration] [numeric](18, 0) NOT NULL
)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('BOAC',3)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('TWA',2)
INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
VALUES('United',1)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('BOAC', 'A2B', 150, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('TWA', 'A2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('BOAC', 'B2C', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('United', 'A2B', 200, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('United', 'D2E', 300, 3)
INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
VALUES('BOAC', 'B2D', 300, 3)first of all you want to know which airline is the preferred carrier
for each route, so you have to inner join the two tables and find the
airline with the lowest (if highest substitute min by max) rank:
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
with this query you get the the ranking of the preferred airline per
route. This output has to be inner joined with the airlines and routes
to get the desired result
select *
from airlines a,
routes r,
(
select route, min(customer_rank) rank
from routes r, airlines a
where a.airline=r.airline
group by route
) p
where a.customer_rank = p.rank
and r.route = p.route
and a.airline = r.airline
if you want to avoid an inner table (the stuff between (...)p) you
could create a view, but you will learn that in your next class.|||Hi
Thanks for the DDL and Example data, showing your expected results would
also be nice. You can either use a subquery in the where clause or a derived
table such as
SELECT A.[Customer_Rank],
R.[ID], R.[Airline], R.[Route], R.[Cost], R.[Duration]
FROM [dbo].[Routes] R
JOIN [dbo].[Airlines] A ON A.[Airline] = R.[Airline]
JOIN ( SELECT MIN(E.[Customer_Rank]) AS [Customer_Rank], D.[Route]
FROM [dbo].[Routes] D
JOIN [dbo].[Airlines] E ON D.[Airline] = E.[Airline]
GROUP BY D.[Route] ) F ON F.[Customer_Rank] = A.[Customer_Rank] AND
F.[Route] = R.[Route]
ORDER BY R.[Route], A.[Customer_Rank]
John
"Tadwick" wrote:
> I have a question that I will illustrate with the script below. As you can
> see there two tables, airlines and routes. Each airline has a customer_rank
> that indicates the preferred airline of the customer ie 1 is most preferred.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each route
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2D', 300, 3)|||Nite4Hawks and John,
You are both awesome. I am amazed to get responses with two different
techniques in such a short time. Are there pros and cons of the correlated
subquery vs derived table methods?
Thanks again, Tad
"Tadwick" wrote:
> I have a question that I will illustrate with the script below. As you can
> see there two tables, airlines and routes. Each airline has a customer_rank
> that indicates the preferred airline of the customer ie 1 is most preferred.
> Each route has a name, cost and duration.
> What I would like to do is write a query that returns one row for each route
> with the columns : airline, cost and duration. I would like the airline
> column to be based on the preferred carrier, irrespective of the cost and
> duration.
> Can someone please stop my head spinning?
> Thanks, Tad
>
> CREATE TABLE [dbo].[Airlines] (
> [Airline] [char] (10) PRIMARY KEY,
> [Customer_Rank] [tinyint] NOT NULL
> )
> CREATE TABLE [dbo].[Routes] (
> [ID] [int] IDENTITY (1, 1) PRIMARY KEY,
> [Airline] [char] (10) NOT NULL REFERENCES Airlines(Airline),
> [Route] [char] (10) NOT NULL ,
> [Cost] [numeric](18, 0) NOT NULL ,
> [Duration] [numeric](18, 0) NOT NULL
> )
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('BOAC',3)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('TWA',2)
> INSERT INTO [dbo].[Airlines]([Airline], [Customer_Rank])
> VALUES('United',1)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'A2B', 150, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('TWA', 'A2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2C', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'A2B', 200, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('United', 'D2E', 300, 3)
> INSERT INTO dbo.[Routes]([Airline], [Route], [Cost], [Duration])
> VALUES('BOAC', 'B2D', 300, 3)sql