query help

ok, I am rather a novice when it comes to SQL queries, so please bear with
me.
I will only be running this table on one table.
I have about 10 columns in teh table
just to make things easy to understand, I will try to make this cleaar and
straight forward:
I have table A and I run the following query on it:
Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
clmB is formated in SQL as DateTime
anyway, so I have several rows returned to me.
For example, it will read:
Shipped, 7/14/2007, 82465,8202007
Shipped,7/16/2007,123456,8202007
on-hand,7/14/2007,82465,
on-hand,7/13/2007,12345,
now, if you noticed, the first and forth items are the same, except the 3rd
item says on-hand instead of shipped.
What I am looking for is a query that would NOT include the items that say
ON-HAND, if the other items are teh same.
Meaning that in my example, it would have only listed items 1,2,4
also, when a row is set as on-hand, there are a couple of cells that will
not be populated.
oy, I hope I made it easy enough to understand.
thanks in advanceThis may give you some ideas to get you started.
SELECT colA, colB, colC,
CASE WHEN colA <> 'on-hand'
THEN colD
ELSE NULL
END
FROM TheTable as A
WHERE clmA <> 'on-hand'
OR NOT EXISTS
(SELECT * FROM TheTable as B
WHERE A.clmB = B.clmB
AND A.clmC = B.clmC
AND B.clmA <> 'on-hand')
Roy Harvey
Beacon Falls, CT
On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>ok, I am rather a novice when it comes to SQL queries, so please bear with
>me.
>I will only be running this table on one table.
>I have about 10 columns in teh table
>just to make things easy to understand, I will try to make this cleaar and
>straight forward:
>I have table A and I run the following query on it:
>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>clmB is formated in SQL as DateTime
>anyway, so I have several rows returned to me.
>For example, it will read:
>Shipped, 7/14/2007, 82465,8202007
>Shipped,7/16/2007,123456,8202007
>on-hand,7/14/2007,82465,
>on-hand,7/13/2007,12345,
>
>now, if you noticed, the first and forth items are the same, except the 3rd
>item says on-hand instead of shipped.
>What I am looking for is a query that would NOT include the items that say
>ON-HAND, if the other items are teh same.
>Meaning that in my example, it would have only listed items 1,2,4
>also, when a row is set as on-hand, there are a couple of cells that will
>not be populated.
>oy, I hope I made it easy enough to understand.
>thanks in advance
>|||WOW!!
That was fast, I will give it a shot. Thank you!!
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||hmmmm
oy, this is kicking my rump here.
haven't gotten it to work, but to try to make it a little more clear...
lets say I have 2 rows, they have almost teh exact same info. The only
difference is that when colA says ON-HAND colD, colE and colF will be null
if colA says SHIPPED all teh other cols will be the same Excpet colD, colE
and colF will not be null
This table gets it's data by reading a txt file once a day with shipment
status info. Normally, I would have it update an exsisting record as the
STATUS (colA) changed from on-hand top shipped. BUT I can't do that becsue
the powers that be, want to be able to see teh records of when it was
on-hand and then when it shipped.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||OK, I think I got it to work, but now I can't get my DATE clause in there to
work.
clmB is a date, and I want everything from 8/8/2007 and newer
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:krimc3dfu0c6fipivj3v679bt49u0vfkht@.4ax.com...
> This may give you some ideas to get you started.
> SELECT colA, colB, colC,
> CASE WHEN colA <> 'on-hand'
> THEN colD
> ELSE NULL
> END
> FROM TheTable as A
> WHERE clmA <> 'on-hand'
> OR NOT EXISTS
> (SELECT * FROM TheTable as B
> WHERE A.clmB = B.clmB
> AND A.clmC = B.clmC
> AND B.clmA <> 'on-hand')
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Aug 2007 13:19:56 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>ok, I am rather a novice when it comes to SQL queries, so please bear with
>>me.
>>I will only be running this table on one table.
>>I have about 10 columns in teh table
>>just to make things easy to understand, I will try to make this cleaar and
>>straight forward:
>>I have table A and I run the following query on it:
>>Select clmA, clmB, clmC, clmD from tableA where clmB >= "7/10/2007'
>>clmB is formated in SQL as DateTime
>>anyway, so I have several rows returned to me.
>>For example, it will read:
>>Shipped, 7/14/2007, 82465,8202007
>>Shipped,7/16/2007,123456,8202007
>>on-hand,7/14/2007,82465,
>>on-hand,7/13/2007,12345,
>>
>>now, if you noticed, the first and forth items are the same, except the
>>3rd
>>item says on-hand instead of shipped.
>>What I am looking for is a query that would NOT include the items that say
>>ON-HAND, if the other items are teh same.
>>Meaning that in my example, it would have only listed items 1,2,4
>>also, when a row is set as on-hand, there are a couple of cells that will
>>not be populated.
>>oy, I hope I made it easy enough to understand.
>>thanks in advance|||On Tue, 21 Aug 2007 14:03:34 -0700, "Johnfli" <john@.ivhs.us> wrote:
>OK, I think I got it to work, but now I can't get my DATE clause in there to
>work.
>clmB is a date, and I want everything from 8/8/2007 and newer
Hopefully clmB is of the datetime or smalldatetime datatype, in which
case I would expect this to work:
WHERE clmB >= '20070808'
If clmB is a string life is not so simple.
WHERE CONVERT(datetime,clmB) >= '20070808'
The really unfortunate part of that approach is that it can not take
advantage of an index on clmB, as it might if clmB were a datetime
column. Also, if any rows have data in clmB that can NOT be converted
to datetime it will fail. The workaround for THAT might be something
like:
WHERE CASE WHEN ISDATE(clmB) = 1
THEN CONVERT(datetime,clmB)
ELSE NULL
END >= '20070808'
Roy Harvey
Beacon Falls, CT|||as luck would have it, clmB is formated as DateTime.
The query I am running is:
SELECT Status, RecDate, Container, [PO Number], Shipment, Style,
Cartons, Qty, OSDNote, OutTrailer, ShipDate, SentEDI,
CASE WHEN status <> 'ON-hand' THEN sentedi ELSE NULL
END AS Expr1
FROM jcrew A
WHERE (Status <> 'ON-hand') AND (RecDate >= '8/10/2007') OR
(NOT EXISTS
(SELECT *
FROM jcrew AS B
WHERE a.[po number] = B.[po number] AND
a.shipment = B.shipment AND a.style = B.style AND a.cartons = B.cartons AND
a.qty = B.qty AND
b.status <> 'on-hand'))
****Please note that in the CASE statement, SQL adds the AS Expr1 on it's
own. *******
RecDate, is the clomun name that I substituted as clmB.
But when I run the query, it returns all dates, not just the ones 8/10/2007
and newer.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:c3qmc3pb1nckesfjlhg5coaqofckvfc7og@.4ax.com...
> On Tue, 21 Aug 2007 14:03:34 -0700, "Johnfli" <john@.ivhs.us> wrote:
>>OK, I think I got it to work, but now I can't get my DATE clause in there
>>to
>>work.
>>clmB is a date, and I want everything from 8/8/2007 and newer
> Hopefully clmB is of the datetime or smalldatetime datatype, in which
> case I would expect this to work:
> WHERE clmB >= '20070808'
> If clmB is a string life is not so simple.
> WHERE CONVERT(datetime,clmB) >= '20070808'
> The really unfortunate part of that approach is that it can not take
> advantage of an index on clmB, as it might if clmB were a datetime
> column. Also, if any rows have data in clmB that can NOT be converted
> to datetime it will fail. The workaround for THAT might be something
> like:
> WHERE CASE WHEN ISDATE(clmB) = 1
> THEN CONVERT(datetime,clmB)
> ELSE NULL
> END >= '20070808'
> Roy Harvey
> Beacon Falls, CT|||On Wed, 22 Aug 2007 09:11:06 -0700, "Johnfli" <john@.ivhs.us> wrote:
>as luck would have it, clmB is formated as DateTime.
DATETIME is not a format, it is a data type.
>The query I am running is:
>SELECT Status, RecDate, Container, [PO Number], Shipment, Style,
>Cartons, Qty, OSDNote, OutTrailer, ShipDate, SentEDI,
> CASE WHEN status <> 'ON-hand' THEN sentedi ELSE NULL
>END AS Expr1
>FROM jcrew A
>WHERE (Status <> 'ON-hand') AND (RecDate >= '8/10/2007') OR
> (NOT EXISTS
> (SELECT *
> FROM jcrew AS B
> WHERE a.[po number] = B.[po number] AND
>a.shipment = B.shipment AND a.style = B.style AND a.cartons = B.cartons AND
>a.qty = B.qty AND
> b.status <> 'on-hand'))
>****Please note that in the CASE statement, SQL adds the AS Expr1 on it's
>own. *******
Some front-end tool adds the AS Expr1, not SQL Server.
And now I see the problem with filter on date: the OR test. I don't
know what was intended, but I think the problem is that the OR test
for the NOT EXISTS is an alternative to both the Status test and the
RecDate test. If the NOT EXISTS test passes, the row passes. When
you have AND mixed with OR, always use (parentheses) to make clear
what works with what.
Roy Harvey
Beacon Falls, CT