SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Dates in results not matching query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pom Grewal
Starting Member

United Kingdom
14 Posts

Posted - 01/26/2012 :  07:10:00  Show Profile  Reply with Quote
Hi Im having difficulting seeing where I am going wrong with my query. Can some please correct it or show me how to?

The results should show me task dates in 2011, but I get dates going back to 2008? Why is this!?!?

Thanks in advance....

select t1.[Document No_]as "Order Number",
REPLACE(REPLACE(t1.[Invoice],'0','No'),'1','Yes') as [Invoice],
REPLACE(REPLACE(t1.[Invoiced],'0','No'),'1','Yes') as [Invoiced],
REPLACE(REPLACE(t1.[Posted],'0','No'),'1','Yes') as [Posted],
t1.[Sub Contract No_],
t1.[Business-with No_],
t1.[Invoice Layout Code],
t1.[Qty_ to Dispose],
t1.[Contract No_],
t1.[Contract Line No_],
t1.[Business with Name],
t2.[Task-at Name] as "Task At Name (Header)",
REPLACE(REPLACE(REPLACE(t1.[Posting Type],'0',''),'1','Purchase'),'2','Sales') AS [Posting Type],
t1.[Post-with No_],
t1.[Invoice-with No_],
REPLACE(REPLACE(t1.[Type],'0',''),'1','Service') as [Type],
t1.[No_],
t1.[Description],
t1.[Int_ Material Catalog]as "WEEE Stream",
t1.[Quantity],
t1.[Unit of Measure],
'£' + CONVERT(varchar, t1.[Unit Price], 1) AS [Unit Price],
'£' + convert(varchar,t1.[Amount]) as [Amount],
'£' + convert(varchar,t1.[Amount Including VAT]) as [Amount Including VAT],
t1.[External Bill of Delivery No_],
CONVERT(varchar,t2.[Task Date],111) as "Task Date",
CONVERT(varchar,t2.[Order Date],111) as "Order Date",
left (convert (varchar,t2.[Task Date],120),7) as [Month]
from DHL.dbo.[DHL WEEE$Waste Mgt_ Header Archive] t2
inner join DHL.dbo.[DHL WEEE$Waste Mgt_ Line Archive] t1
on t2.[No_] = t1.[Document No_]and t1.[Version No_] = t2.[Version No_]
where t1.[Version No_] = (SELECT MAX([Version No_]) FROM DHL.dbo.[DHL WEEE$Waste Mgt_ Header Archive] WHERE [No_] = t1.[No_] AND [Version No_] <> 1)
and t1.[Invoice-with No_] = 'TF-000002'
and t2.[Task Date] between '2011-01-01' and '2011-12-31'
or t1.[No_] = 'B2C-601'
or t1.[No_] = 'B2C-603'
and t1.[Document No_] like 'BO-%'
Order By t2.[Task Date] ASC

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 01/26/2012 :  07:29:07  Show Profile  Reply with Quote
You are missing ( ) brackets for your OR statement.

and t2.[Task Date] between '2011-01-01' and '2011-12-31'
or t1.[No_] = 'B2C-601'
or t1.[No_] = 'B2C-603'
and t1.[Document No_] like 'BO-%'

Not sure what logic you want here. Do you really want dates OR t1.[No_] is either of those values? I am guessing not as you do not want dates outside of 2011.

Let us know the precedence of your statements and we an help.

Edited by - RickD on 01/26/2012 07:30:37
Go to Top of Page

Pom Grewal
Starting Member

United Kingdom
14 Posts

Posted - 01/26/2012 :  08:11:01  Show Profile  Reply with Quote
I need the results to show all values in 2011 where the No. is B2C-601 or B2C-602 (it could be either of these as these are service numbers) where the document number starts with a BO-. Hope I have explained this well enough?
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 01/26/2012 :  10:13:59  Show Profile  Reply with Quote
Change your OR's to AND's.

and t2.[Task Date] between '2011-01-01' and '2011-12-31'
and t1.[No_] IN ('B2C-601','B2C-603')
and t1.[Document No_] like 'BO-%'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000