query help

Hi,
I want to bring up the following data for groups:
Blue, Red, Green and Yellow.
I have the following view in sql 7 which brings up the data i want, How can
i bring up the data for the other groups within the same query.
SELECT DISTINCT
salesorders.srep, SUM(salesitems.sprice) AS Expr1,
delv.dtaxd
FROM dbo.salesorders INNER JOIN
dbo.salesitems ON
dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
dbo.delvitems ON
dbo.salesorders.son = dbo.delvitems.dord AND
dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
dbo.delv ON
dbo.delvitems.delvnoa = dbo.delv.delvno
WHERE (dbo.salesorders.srep = 'blue') AND
(dbo.delv.dedate > CONVERT(DATETIME,
'2008-02-01 00:00:00', 102))
GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
I then need to call this query witin MS Access and use it to output to a
Data sheet.
Thanks
Mohammad
A little more info.
The data i want will look like this as an example:
Team price Date
==== ==== ===
Blue 500 01/01/2008
Green 600 04/02/2008
Yellow 2000 01/02/2008
"mahmad" wrote:

> Hi,
> I want to bring up the following data for groups:
> Blue, Red, Green and Yellow.
> I have the following view in sql 7 which brings up the data i want, How can
> i bring up the data for the other groups within the same query.
> SELECT DISTINCT
> salesorders.srep, SUM(salesitems.sprice) AS Expr1,
> delv.dtaxd
> FROM dbo.salesorders INNER JOIN
> dbo.salesitems ON
> dbo.salesorders.son = dbo.salesitems.sona INNER JOIN
> dbo.delvitems ON
> dbo.salesorders.son = dbo.delvitems.dord AND
> dbo.salesitems.sonitem = dbo.delvitems.ditem INNER JOIN
> dbo.delv ON
> dbo.delvitems.delvnoa = dbo.delv.delvno
> WHERE (dbo.salesorders.srep = 'blue') AND
> (dbo.delv.dedate > CONVERT(DATETIME,
> '2008-02-01 00:00:00', 102))
> GROUP BY dbo.salesorders.srep, dbo.delv.dtaxd
> I then need to call this query witin MS Access and use it to output to a
> Data sheet.
> Thanks
> Mohammad

Query Help

Hi All,
Hoping you might be able to help with this one.
Say I have this layout:
Tbl_People
Person_ID, Name, Address, City, State, Zip.
Tbl_Response
Response_ID, Person_ID, Q_ID (integer)
LU_Questions
Q_ID, Q_Label
So basically I have a lookup table with a bunch of question IDs. What I'm
really looking to get is data in this format:
Person_ID Q_Label1 QLabel2 QLabel3
5 Yes Yes
No
6 No Yes
Yes
Where I have one column for each record in LU_Questions. If a record exists
in Tbl_Response with that Person_ID and Q_ID, then it will return Yes, if
not, then No.
Any idea how I can do this?
James
Can you post sample data to be tested?
"James" <minorkeys@.gmail.com> wrote in message
news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> Hoping you might be able to help with this one.
> Say I have this layout:
> Tbl_People
> Person_ID, Name, Address, City, State, Zip.
> Tbl_Response
> Response_ID, Person_ID, Q_ID (integer)
> LU_Questions
> Q_ID, Q_Label
>
> So basically I have a lookup table with a bunch of question IDs. What I'm
> really looking to get is data in this format:
> Person_ID Q_Label1 QLabel2 QLabel3
> 5 Yes Yes No
> 6 No Yes Yes
> Where I have one column for each record in LU_Questions. If a record
> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
> Yes, if not, then No.
> Any idea how I can do this?
>
|||I'm not sure what format you'd be looking for it in, but something like:
Tbl_People:
Person_ID Name Address City
1 Bob 123 Street Madison
2 John 321 Ave Johnstown
Tbl_Response:
Person_ID Q_ID
1 1
1 2
2 1
2 2
2 4
Tbl_Questions:
Q_ID Q_Label
1 Golf
2 Camping
3 Hiking
4 Fishing
So ultimately I want:
Name OtherMetaData Golf Camping Hiking Fishing
Bob street/city/etc Yes Yes No No
John street/city/etc Yes Yes No Yes
Make sense? But it would need to be dynamic as Tbl_Questions grows.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
> James
> Can you post sample data to be tested?
>
>
> "James" <minorkeys@.gmail.com> wrote in message
> news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>
|||On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <u...@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>
>
>
>
>
>
>
>
> - Show quoted text -
select
tp.name
, tp.other
, tq.Q_Label
, tr.golf
, tr.camping,
, tr.hiking
, tr.fishing
from tbl_people tp
join (
select person_id
,Q_id
,case when Q_id =1 then 'yes' else 'no' end golf
,case when Q_id =2 then 'yes' else 'no' end Camping
,case when Q_id =3 then 'yes' else 'no' end Hiking
,case when Q_id =4 then 'yes' else 'no' end Fishing
from tbl_response
)as tr on tp.Person_id =tr.Person_id
|||James
> I'm not sure what format you'd be looking for it in, but something like:
That what I meant
create table Tbl_People (Person_ID int, Name varchar(10)
,Address varchar(20), City varchar(20))
insert into Tbl_People values (1,'Bob','123 Street','Madison')
insert into Tbl_People values (2,'John','321 Ave',' Johnstown')
create table Tbl_Response (Person_ID int,Q_ID int)
insert into Tbl_Response values (1,1)
insert into Tbl_Response values (1,2)
insert into Tbl_Response values (2,1)
insert into Tbl_Response values (2,2)
insert into Tbl_Response values (2,4)
create table Tbl_Questions (Q_ID int,Q_Label varchar(10))
insert into Tbl_Questions values (1,'Golf')
insert into Tbl_Questions values (2,'Camping')
insert into Tbl_Questions values (3,'Hiking')
insert into Tbl_Questions values (4,'Fishing')
select Name ,Address, City,max(case when Q_Label ='Golf' then 'yes' else
'no' end) as Golf,
max(case when Q_Label ='Camping' then 'yes' else
'no' end) as Camping,
max(case when Q_Label ='Hiking' then 'yes' else
'no' end) as Hiking,
max(case when Q_Label ='Fishing' then 'yes' else
'no' end) as Fishing
from Tbl_People tp join Tbl_Response tr
on tp.Person_ID=tr.Person_ID
join Tbl_Questions tq on tq.Q_ID=tr.Q_ID
group by Name ,Address, City
"James" <minorkeys@.gmail.com> wrote in message
news:%23Tx7Y6nsHHA.4916@.TK2MSFTNGP04.phx.gbl...
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>
|||Thank you both. This is pretty close and I may end up having to work with
this.
Is there any way to make the column names dynamic, instead of hardcoded?
Either way thank you immensely for your help.
"schal" <shivaramchalla@.gmail.com> wrote in message
news:1182264456.599338.38270@.w5g2000hsg.googlegrou ps.com...
> On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
> select
> tp.name
> , tp.other
> , tq.Q_Label
> , tr.golf
> , tr.camping,
> , tr.hiking
> , tr.fishing
> from tbl_people tp
> join (
> select person_id
> ,Q_id
> ,case when Q_id =1 then 'yes' else 'no' end golf
> ,case when Q_id =2 then 'yes' else 'no' end Camping
> ,case when Q_id =3 then 'yes' else 'no' end Hiking
> ,case when Q_id =4 then 'yes' else 'no' end Fishing
> from tbl_response
> )as tr on tp.Person_id =tr.Person_id
>
sql

Query Help

Hi all,
I have one ‘tall’ table that records the following on a regular basis:
STATE SERIAL# DATE
====== ======== ========
VA Z32WE12 12/31/2003
CA QWEFD1 05/04/2005
VA Z32WE13 01/01/2003
CA QWEFD2 05/05/2005
TX POISD21 05/03/2005
TX POISD21 05/04/2005
TX POISD21 05/05/2005
We are tracking the serial number for each state and would like to report on
the current and previous serial number for each state. Can someone please
help me with building the query in order to get the following:
StateCurrent Serial#SincePrevious Serial#
CAQWEFD205/05/2005QWEFD1
VAZ32WE1301/01/2003Z32WE12
TXPOISD21 05/03/2005 Never Changed
Thanks in advance,
-Appreciator
It seems to be a flaw in the data:

> VA Z32WE12 12/31/2003
> VA Z32WE13 01/01/2003
Did you mean "12/31/2002" for serial# "Z32WE12"?
try:
use northwind
go
create table t1 (
state char(2) not null,
serial# varchar(10) not null,
[date] datetime,
)
go
insert into t1 values('VA','Z32WE12', '12/31/2002')
insert into t1 values('CA','QWEFD1' , '05/04/2005')
insert into t1 values('VA','Z32WE13', '01/01/2003')
insert into t1 values('CA','QWEFD2' , '05/05/2005')
insert into t1 values('TX','POISD21', '05/03/2005')
insert into t1 values('TX','POISD21', '05/04/2005')
insert into t1 values('TX','POISD21', '05/05/2005')
go
create view v1
as
select state, serial#, max([date]) as [date] from t1 group by state, serial#
go
create view v2
as
select
a.state,
a.serial# as current_serial#,
a.[date] as since,
isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
previous_serail#
from
v1 as a
left join
v1 as b
on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
where c.state = a.state and c.[date] > b.[date])
go
select
*
from
v2 as a
where
previous_serail# != 'have_not_changed_since'
or (previous_serail# = 'have_not_changed_since' and not exists(select *
from v2 as b where b.state = a.state and b.previous_serail# !=
'have_not_changed_since'))
order by
case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
a.state
go
drop view v2, v1
go
drop table t1
go
AMB
"URG" wrote:

> Hi all,
> I have one ‘tall’ table that records the following on a regular basis:
> STATE SERIAL# DATE
> ====== ======== ========
> VA Z32WE12 12/31/2003
> CA QWEFD1 05/04/2005
> VA Z32WE13 01/01/2003
> CA QWEFD2 05/05/2005
> TX POISD21 05/03/2005
> TX POISD21 05/04/2005
> TX POISD21 05/05/2005
> We are tracking the serial number for each state and would like to report on
> the current and previous serial number for each state. Can someone please
> help me with building the query in order to get the following:
> StateCurrent Serial#SincePrevious Serial#
> CAQWEFD205/05/2005QWEFD1
> VAZ32WE1301/01/2003Z32WE12
> TXPOISD21 05/03/2005 Never Changed
> Thanks in advance,
> -Appreciator
>
|||Hi Alejandro,
Thanks a bunch! That really works perfect..!!
Sorry about the flaw - I had typed in the sample data.
URG
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> It seems to be a flaw in the data:
>
> Did you mean "12/31/2002" for serial# "Z32WE12"?
> try:
> use northwind
> go
> create table t1 (
> state char(2) not null,
> serial# varchar(10) not null,
> [date] datetime,
> )
> go
> insert into t1 values('VA','Z32WE12', '12/31/2002')
> insert into t1 values('CA','QWEFD1' , '05/04/2005')
> insert into t1 values('VA','Z32WE13', '01/01/2003')
> insert into t1 values('CA','QWEFD2' , '05/05/2005')
> insert into t1 values('TX','POISD21', '05/03/2005')
> insert into t1 values('TX','POISD21', '05/04/2005')
> insert into t1 values('TX','POISD21', '05/05/2005')
> go
> create view v1
> as
> select state, serial#, max([date]) as [date] from t1 group by state, serial#
> go
> create view v2
> as
> select
> a.state,
> a.serial# as current_serial#,
> a.[date] as since,
> isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
> previous_serail#
> from
> v1 as a
> left join
> v1 as b
> on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
> where c.state = a.state and c.[date] > b.[date])
> go
> select
> *
> from
> v2 as a
> where
> previous_serail# != 'have_not_changed_since'
> or (previous_serail# = 'have_not_changed_since' and not exists(select *
> from v2 as b where b.state = a.state and b.previous_serail# !=
> 'have_not_changed_since'))
> order by
> case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
> a.state
> go
> drop view v2, v1
> go
> drop table t1
> go
>
> AMB
> "URG" wrote:

Query Help

Help, please!
I have 3 columns with numeric values. i want to write a query that returns
the following:
If all 3 columns are populated, then the middle value (not the average);
If one colunn has a null, then the lesser value,
If 2 are null, then the remaining value.
For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
the result to be "10". If ColumnB were null, then the result would be "5".
Making sense?
Is there a way to do this, without having to create a bazillion Case When
statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
... and so on).
Just wondering.
Thank you!
Kyra
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
Kyra,
I think the bazillion CASEs is the only way to go. Here's one attempt:
CREATE TABLE T1 (T1ID INT NOT NULL IDENTITY, Col1 int, Col2 int, Col3 int)
GO
INSERT T1 VALUES (NULL, NUll, 1)
INSERT T1 VALUES (NULL, 2, NULL)
INSERT T1 VALUES (3, NUll, NULL)
INSERT T1 VALUES (NULL, 4, 5)
INSERT T1 VALUES (6, NULL, 7)
INSERT T1 VALUES (8,9, NULL)
INSERT T1 VALUES (10,11,12)
GO
SELECT
CASE
WHEN Col1 IS NULL AND Col2 IS NULL THEN Col3
WHEN Col2 IS NULL AND Col2 IS NULL THEN Col1
WHEN Col1 IS NULL AND Col3 IS NULL THEN Col2
WHEN Col1 IS NULL AND Col2 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col2 < Col3 THEN Col2 Else Col3 END
WHEN Col2 IS NULL AND Col1 IS NOT NULL AND Col3 IS NOT NULL THEN
CASE WHEN Col1 < Col3 THEN Col1 Else Col3 END
WHEN Col3 IS NULL AND Col1 IS NOT NULL AND Col2 IS NOT NULL THEN
CASE WHEN Col1 < Col2 THEN Col1 Else Col2 END
WHEN Col1 <= Col2 AND Col2 <= Col3 THEN Col2
WHEN Col2 <= Col3 AND Col3 <= Col1 THEN Col3
WHEN Col3 <= Col1 AND Col1 <= Col2 THEN Col1
END
FROM T1
One thing your conditions left out: what if they're all NULL?
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:3147EC21-B5A3-4725-B156-6A40834E5561@.microsoft.com...
> Help, please!
> I have 3 columns with numeric values. i want to write a query that returns
> the following:
> If all 3 columns are populated, then the middle value (not the average);
> If one colunn has a null, then the lesser value,
> If 2 are null, then the remaining value.
> For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
> the result to be "10". If ColumnB were null, then the result would be "5".
> Making sense?
> Is there a way to do this, without having to create a bazillion Case When
> statements? (case when columnA > ColumnB and ColumnB < ColumnC then
> ColumnB
> else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else .
> .
> .. and so on).
> Just wondering.
> Thank you!
> Kyra
> --
> Financial Systems Analyst
> CCNA, MCSE, MCSA, MCDBA
|||Ysandre,
Here is an alternative to Ron's solution. In general, you
would have an easier time if all the values were in one
column..
SELECT
T1ID,
CASE cntC
WHEN 1 THEN maxC
WHEN 2 THEN minC
WHEN 3 THEN sumC - maxC - minC
END AS C
FROM (
SELECT
T1ID,
SUM(C) as sumC,
MIN(C) as minC,
MAX(C) as maxC,
COUNT(C) as cntC
FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T
GROUP BY T1ID
) T
or
SELECT T1ID, MIN(C) FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) T1
WHERE C IN (
SELECT TOP 2 C FROM (
SELECT T1ID, Col1 AS C FROM T1
UNION ALL
SELECT T1ID, Col2 FROM T1
UNION ALL
SELECT T1ID, Col3 FROM T1
) AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
If your table were in the form I used for the derived table above (one
value column instead of three), you could write
SELECT T1ID, MIN(C) FROM T1
WHERE C IN (
SELECT TOP 2 C FROM T1 AS T1x
WHERE T1x.T1ID = T1.T1ID
AND C IS NOT NULL
ORDER BY C DESC
)
GROUP BY T1ID
Steve Kass
Drew University
Ysandre wrote:

>Help, please!
>I have 3 columns with numeric values. i want to write a query that returns
>the following:
>If all 3 columns are populated, then the middle value (not the average);
>If one colunn has a null, then the lesser value,
>If 2 are null, then the remaining value.
>For example: if ColumnA = 5 and ColumnB = 10 and ColumnC = 12, then I want
>the result to be "10". If ColumnB were null, then the result would be "5".
>Making sense?
>Is there a way to do this, without having to create a bazillion Case When
>statements? (case when columnA > ColumnB and ColumnB < ColumnC then ColumnB
>else case when columA < ColumnB and ColumnB > ColumnC then ColumnB else . .
>.. and so on).
>Just wondering.
>Thank you!
>Kyra
>
>
|||Thank you Ron and Steve, that was very helpful!!!!
Thanks,
ysandre
"Steve Kass" wrote:

> Ysandre,
> Here is an alternative to Ron's solution. In general, you
> would have an easier time if all the values were in one
> column..
> SELECT
> T1ID,
> CASE cntC
> WHEN 1 THEN maxC
> WHEN 2 THEN minC
> WHEN 3 THEN sumC - maxC - minC
> END AS C
> FROM (
> SELECT
> T1ID,
> SUM(C) as sumC,
> MIN(C) as minC,
> MAX(C) as maxC,
> COUNT(C) as cntC
> FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T
> GROUP BY T1ID
> ) T
> or
> SELECT T1ID, MIN(C) FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) T1
> WHERE C IN (
> SELECT TOP 2 C FROM (
> SELECT T1ID, Col1 AS C FROM T1
> UNION ALL
> SELECT T1ID, Col2 FROM T1
> UNION ALL
> SELECT T1ID, Col3 FROM T1
> ) AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> If your table were in the form I used for the derived table above (one
> value column instead of three), you could write
> SELECT T1ID, MIN(C) FROM T1
> WHERE C IN (
> SELECT TOP 2 C FROM T1 AS T1x
> WHERE T1x.T1ID = T1.T1ID
> AND C IS NOT NULL
> ORDER BY C DESC
> )
> GROUP BY T1ID
> Steve Kass
> Drew University
>
> Ysandre wrote:
>

Query help

Here is what I am trying to do... I have a phone list of all our stores. It
contains managers home and cell numbers for emergencies. So the list is
about 44 stores and about 7 to 8 managers to each store...so that's about
350 or so numbers on this list... Now our store each have there own unique
IP address, ex. Store 2 would be 192.168.2.? So within the phone list I
have, for each store I have stored the Ip like 192.168.2 or 192.168.6 and
so on.. Well what I want to do is try to build a query that will only return
records that are similar to the users IP... so I have a hidden request for
the IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
variable in my query to compare their ip with what's in the table..
So the results would be like this...
My IP 192.168.6.15
I should see ONLY records with 192.168.6 as part of the IP.
Any suggestions? Ideas? not sure if this would be the place to post this,
but would appreciate any help.
You might want to try the LIKE operator, read more in Books Online.
Regards,
Eric Garza
AMIGE
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uRdrbBByEHA.1404@.TK2MSFTNGP11.phx.gbl...
> Here is what I am trying to do... I have a phone list of all our stores.
It
> contains managers home and cell numbers for emergencies. So the list is
> about 44 stores and about 7 to 8 managers to each store...so that's about
> 350 or so numbers on this list... Now our store each have there own unique
> IP address, ex. Store 2 would be 192.168.2.? So within the phone list I
> have, for each store I have stored the Ip like 192.168.2 or 192.168.6 and
> so on.. Well what I want to do is try to build a query that will only
return
> records that are similar to the users IP... so I have a hidden request for
> the IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> variable in my query to compare their ip with what's in the table..
> So the results would be like this...
> My IP 192.168.6.15
> I should see ONLY records with 192.168.6 as part of the IP.
> Any suggestions? Ideas? not sure if this would be the place to post this,
> but would appreciate any help.
>
|||Daniel_Cha wrote:
> Here is what I am trying to do... I have a phone list of all our
> stores. It contains managers home and cell numbers for emergencies.
> So the list is about 44 stores and about 7 to 8 managers to each
> store...so that's about 350 or so numbers on this list... Now our
> store each have there own unique IP address, ex. Store 2 would be
> 192.168.2.? So within the phone list I have, for each store I have
> stored the Ip like 192.168.2 or 192.168.6 and so on.. Well what I
> want to do is try to build a query that will only return records that
> are similar to the users IP... so I have a hidden request for the
> IP... Request.ServerVariables("REMOTE_ADDR") which I want to use as a
> variable in my query to compare their ip with what's in the table..
> So the results would be like this...
> My IP 192.168.6.15
> I should see ONLY records with 192.168.6 as part of the IP.
> Any suggestions? Ideas? not sure if this would be the place to post
> this, but would appreciate any help.
It sounds like your IP address is stored in character format, and that's
good. To select only those stores that match your specs, use:
Select store_information
From Stores
Where IP LIKE '192.168.6.%'
Or if you are using a parameter:
@.IP as varchar(12)
Set @.IP = @.IP + '%'
Select store_information
From Stores
Where IP LIKE @.IP
David Gugick
Imceda Software
www.imceda.com
|||ok, here is my sql statement...
SELECT *
FROM tbEmerStore, tbIpRange
WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange LIKE
'varIP'
ORDER BY Store
Variable is Name= varIP Run-Time Value =
Request.ServerVariable("REMOTE_ADDR")
what am i doing wrong?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Daniel_Cha wrote:
> It sounds like your IP address is stored in character format, and that's
> good. To select only those stores that match your specs, use:
> Select store_information
> From Stores
> Where IP LIKE '192.168.6.%'
> Or if you are using a parameter:
> @.IP as varchar(12)
> Set @.IP = @.IP + '%'
> Select store_information
> From Stores
> Where IP LIKE @.IP
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Daniel_Cha wrote:
> ok, here is my sql statement...
> SELECT *
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange
> LIKE 'varIP'
> ORDER BY Store
> Variable is Name= varIP Run-Time Value =
> Request.ServerVariable("REMOTE_ADDR")
> what am i doing wrong?
>
You are not using a variable. You are using the literal 'varIP'. 'varIP'
is not a variable.
Also, you should never use SELECT * in a production environment. It's
considered bad practice because you almost always end up returning more
columns than you really need. Even if you need them all, specify each
one in the query.
Review my example again.
David Gugick
Imceda Software
www.imceda.com
|||I'm assuming that the varIP is storing the full octets, what does the
tbIpRane.IpRange field data look like?
I'm also assuming you are sending ad hoc T-SQL to the server instead of
using a stored procedure. I'll also assume that you are only storing the
truncated octet in the database. If this is so, modify your query, thusly:
varSQL = "
SELECT *
FROM tbEmerStore AS es
INNER JOIN
tbIpRange AS ir
ON es.Store = ir.StoreNum
WHERE ir.IpRange = " & LEFT(varIP, 12) & "
ORDER BY es.Store
"
conn.Execute(varSQL)
This is probably not exact given the line breaks, but you should be able to
get the point.
To make life easier, I would strongly recommend that you use fixed-length,
zero-padded IP addresses and subnets; otherwise, you are going to have to do
some INSTR pattern matching to find the last "." octet seperator to determine
if the characters you need to truncate is 1, 2, or 3--that would suck.
Sincerely,
Anthony Thomas
"Daniel_Cha" wrote:

> ok, here is my sql statement...
> SELECT *
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND tbIpRange.IpRange LIKE
> 'varIP'
> ORDER BY Store
> Variable is Name= varIP Run-Time Value =
> Request.ServerVariable("REMOTE_ADDR")
> what am i doing wrong?
>
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23fMvpGByEHA.2540@.TK2MSFTNGP09.phx.gbl...
>
>
|||Ok, I what is stored in the database is the following:
Table = tbIpRange
Column Name Value
StoreNum 02 San Antonio
IPRange 192.168.2
Table = tbEmerStore
Column Name Value
Store 02 San Antonio
So I have one table that contains this information for each store, since
this will not change unless we add a new location to the company.
In the table that contains all the information there is a column that
contains the same StoreNum value and its called Store
So my select statement looks like this..
SELECT Store, StoreNum
FROM tbEmerStore, tbIpRange
WHERE tbEmerStore.Store = tbIpRange.StoreNum AND 'varIP' LIKE
tbIpRange.IpRange
my variable varIP has a run-time value of
Request.ServerVariable("REMOTE_ADDR") so that I can capture the users IP
and compare it to the table.
Im not all that great with queries and variables... so im not sure what im
doing wrong...and our SQL guru is out on vacation since he just had a little
boy this week.. so I dont have anyone to help me with this. So I want to
compare the REMOTE_ADDR to the IPRange column.. and if they are alike then
ONLY return the stores that have that IP Range.
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:89C657FA-CA4F-4C8C-BF08-3C9447F6C1B5@.microsoft.com...
> I'm assuming that the varIP is storing the full octets, what does the
> tbIpRane.IpRange field data look like?
> I'm also assuming you are sending ad hoc T-SQL to the server instead of
> using a stored procedure. I'll also assume that you are only storing the
> truncated octet in the database. If this is so, modify your query,
thusly:
> varSQL = "
> SELECT *
> FROM tbEmerStore AS es
> INNER JOIN
> tbIpRange AS ir
> ON es.Store = ir.StoreNum
> WHERE ir.IpRange = " & LEFT(varIP, 12) & "
> ORDER BY es.Store
> "
> conn.Execute(varSQL)
> This is probably not exact given the line breaks, but you should be able
to
> get the point.
> To make life easier, I would strongly recommend that you use fixed-length,
> zero-padded IP addresses and subnets; otherwise, you are going to have to
do
> some INSTR pattern matching to find the last "." octet seperator to
determine[vbcol=seagreen]
> if the characters you need to truncate is 1, 2, or 3--that would suck.
> Sincerely,
>
> Anthony Thomas
> "Daniel_Cha" wrote:
that[vbcol=seagreen]
a[vbcol=seagreen]
that's[vbcol=seagreen]
|||I don't mean to give you too much trouble but what are you, dense?
Three of us have given you examples now. Your problem is two-fold.
1. You can't quote your variable: 'varIP'
2. Your source is longer than your search. You'll have to truncate part of
your source.
Look at the examples again.
Sincerely,
Anthony Thomas

"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:%23zj3AaCyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Ok, I what is stored in the database is the following:
> Table = tbIpRange
> Column Name Value
> StoreNum 02 San Antonio
> IPRange 192.168.2
> Table = tbEmerStore
> Column Name Value
> Store 02 San Antonio
>
> So I have one table that contains this information for each store, since
> this will not change unless we add a new location to the company.
> In the table that contains all the information there is a column that
> contains the same StoreNum value and its called Store
> So my select statement looks like this..
> SELECT Store, StoreNum
> FROM tbEmerStore, tbIpRange
> WHERE tbEmerStore.Store = tbIpRange.StoreNum AND 'varIP' LIKE
> tbIpRange.IpRange
> my variable varIP has a run-time value of
> Request.ServerVariable("REMOTE_ADDR") so that I can capture the users IP
> and compare it to the table.
> Im not all that great with queries and variables... so im not sure what im
> doing wrong...and our SQL guru is out on vacation since he just had a
little
> boy this week.. so I dont have anyone to help me with this. So I want to
> compare the REMOTE_ADDR to the IPRange column.. and if they are alike
then[vbcol=seagreen]
> ONLY return the stores that have that IP Range.
>
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:89C657FA-CA4F-4C8C-BF08-3C9447F6C1B5@.microsoft.com...
the[vbcol=seagreen]
> thusly:
> to
fixed-length,[vbcol=seagreen]
to[vbcol=seagreen]
> do
> determine
LIKE[vbcol=seagreen]
emergencies.[vbcol=seagreen]
have[vbcol=seagreen]
> that
as[vbcol=seagreen]
> a
table..[vbcol=seagreen]
post
> that's
>

Query help

I am sure this is something totally simple that I am forgetting, but
it is driving me crazy!
I have a cross-reference table of items and attributes. So each row
of the table contains one item and one attribute. Most items have
many lines. I need a select statement (or statements) where I can get
back all the items that have 3 specific attributes. So it would be an
AND thing, not an OR thing. How do I do this?
Thanks,
Gala
Please post your DDL. Perhaps relational division will do it:
select
ItemID
from
MyTable
where
attribute in ('ATTR1', 'ATTR2', 'ATTR3')
group by
ItemID
having
count (*) = 3
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gala" <gala@.sonic.net> wrote in message
news:14dba7aa.0408231103.5df59f81@.posting.google.c om...
I am sure this is something totally simple that I am forgetting, but
it is driving me crazy!
I have a cross-reference table of items and attributes. So each row
of the table contains one item and one attribute. Most items have
many lines. I need a select statement (or statements) where I can get
back all the items that have 3 specific attributes. So it would be an
AND thing, not an OR thing. How do I do this?
Thanks,
Gala

query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can have multiple rows with the same checkdate. I am trying to query the two tables so I can get the sum of the earnings for a particular checkdate and the employees status at the time
of the check. Here is an example of the data and what I have written so far:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employeeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows for each ID each month will be painful.
How can I write this query so that the select will return the sum of earnings by employeeID on a specific checkdate and the employee status on that date. I thought of using a cursor, but I am not advanced enough to write one. I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!
I realize I should have posted this in data mining, but I don't want to double post
sql