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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.06 seconds. Powered By: Snitz Forums 2000