I need to make a report of items to 6 months ago

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

I need to make a report of items to 6 months ago

Author

Topic

kuroneko
Starting Member

5 Posts

Posted - 2008-12-15 : 11:55:35

here the query.........

SELECT T2.ItemCode as 'Referencia', T2.ItemName as ' Descripcion', cast(round(T3.OnHand, 0)as int)as 'Existencia', cast(round(T3.IsCommited,0)as int)as 'Reservado', T3.WhsCode as 'Bodega'
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode
WHERE T2.ItemCode not in (Select T1.ItemCode From INV1 T1
INNER JOIN OINV T0 ON T0.DocEntry = T1.DocEntry
Where T1.DocDate between DATEADD(m,6,GETDATE()) and GetDate())
AND T3.OnHand > 0
GROUP BY T2.ItemCode, T2.ItemName, T3.OnHand, T3.IsCommited, T3.WhsCode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 11:58:14

quote:
Originally posted by kuroneko

here the query.........

it should be -6 as you want 6 months back

SELECT T2.ItemCode as 'Referencia', T2.ItemName as ' Descripcion', cast(round(T3.OnHand, 0)as int)as 'Existencia', cast(round(T3.IsCommited,0)as int)as 'Reservado', T3.WhsCode as 'Bodega'
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode
WHERE T2.ItemCode not in (Select T1.ItemCode From INV1 T1
INNER JOIN OINV T0 ON T0.DocEntry = T1.DocEntry
Where T1.DocDate between DATEADD(m,-6,GETDATE()) and GetDate())
AND T3.OnHand > 0
GROUP BY T2.ItemCode, T2.ItemName, T3.OnHand, T3.IsCommited, T3.WhsCode

kuroneko
Starting Member

5 Posts

Posted - 2008-12-15 : 12:24:31

does not work.....the screen goes blank, does not show any result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 12:29:09

are you sure you've related records in all the tables to which you're joining (we dont have access to your system so cant understand how your data is) probably you could post some sample data and illustrate what you want giving sample output

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

kuroneko
Starting Member

5 Posts

Posted - 2008-12-15 : 12:44:01

Do you have an email address to explain what is happening to me?....if not a lot trouble

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 12:46:42

just post question here providing supporting data as per link, then somebody will be able to help you out

kuroneko
Starting Member

5 Posts

Posted - 2008-12-15 : 12:48:50

ok....I need help with this query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 13:24:38

which query? post some data and explain your reqmnt rather than giving query

kuroneko
Starting Member

5 Posts

Posted - 2008-12-15 : 13:41:03

I need to make a report of items that have more inventory to 0 and which had not been moving 6 months ago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 23:31:45

quote:
Originally posted by kuroneko

I need to make a report of items that have more inventory to 0 and which had not been moving 6 months ago

how will we know what are your table fields and how your data would be. did you even had a look at link i posted.whats preventing you from posting sample data as requested? if you want more help, post question in desired format. please understand we cant read your mind neither can see your system.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources