date field not following the where

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

date field not following the where

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2011-08-12 : 10:11:24

Hey guys, hope you can help me out with this. What i am trying to do is see the last date that an item was moved into certain locations. But that items must also still exist there. The problem is when i run the query, the date that shows is that time anything was done with that item, not just the last date it was put into the specified locations. I know it is probably something simple i am missing but i can not figure it out. It seems as if i am missing a sub query of some sorts as the where statement is not being passed on to the date field. Any help would be great

select locationmaster.code as Location, itemmaster.itemno as ItemNO, inventorymaster.qty QTY,
(select MAX(at.date)as lastdate) from inventorymaster
inner join itemmaster on
inventorymaster.itemid=itemmaster.id
inner join locationmaster on
locationmaster.id=inventorymaster.locationid
inner join accountingtransaction at
on at.itemno=itemmaster.itemno
where locationmaster.code= 'recvreturn'
or locationmaster.code= 'recvtech'
or locationmaster.code='receiving'
and inventorymaster.qty <> 0
group by locationmaster.code, itemmaster.itemno, inventorymaster.qty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-12 : 10:24:59

perhaps a missing parenthesis () here


where (locationmaster.code= 'recvreturn'
or locationmaster.code= 'recvtech'
or locationmaster.code='receiving' )
and inventorymaster.qty <> 0

KH
[spoiler]Time is always against us[/spoiler]

Dmh188
Starting Member

37 Posts

Posted - 2011-08-12 : 10:28:43

khtan,

Thank you for responding, but unfortunately that did not work either.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-12 : 10:30:18

can't really help you much further unless you can explain the schema and post the full query. What you have posted seems like partial

Also please post some sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-12 : 14:37:50

Every SQL forum OR newsgroup expects that you will post DDL, sample data AND clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys AND should have DRI, constraints, AND all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling AND use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

The vague DML: you did post is a disaster. Data elements change names from table to table, full of vague generic names or reserved words like “date” and “code” and other things. I also love seeing the term “Master” in RDBMS – that belongs in network DB and old file system. However did this mess has no idea how to do a data model. I will try to fix it:

SELECT L.location_id, I.item_nbr, INV.onhand_qty
MAX(AT.screwup_date) AS last_screwup_date
FROM Inventory AS INV,
Items AS I,
Locations AS L
AccountingTransactions AS AT
WHERE INV.item_id = I.item_id
AND L.location_id = INV.location_id
AND AT.item_nbr = I.item_nbr
AND L.location_id IN ('recv_return', 'recv_tech', 'receiving')
AND INV.onhand_qty > 0
GROUP BYL.location_id, I.item_nbr, INV.onhand_qty;

>> What I am trying to do is see the last date that an item was moved into certain locations. But that items must also still exist there. The problem is when I run the query, the date that shows is that time anything was done with that item, not just the last date it was put into the specified locations. I know it is probably something simple I am missing but I can not figure it out. It seems as if I am missing a sub query of some sorts as the where statement is not being passed on to the date field [sic: fields are not columns]. Any help would be great <<

How do you think we can answer this with any DDL? I se that Items are different from Inventory, so what is in this Inventory? A move has to have a source and a destination location, with a time on it. But where is that information?

try again, with something usable.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources