Dates in results not matching query

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

SQL Server 2000 Forums

Transact-SQL (2000)

Dates in results not matching query

Author

Topic

Pom Grewal
Starting Member

14 Posts

Posted - 2012-01-26 : 07:10:00

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

3608 Posts

Posted - 2012-01-26 : 07:29:07

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.

Pom Grewal
Starting Member

14 Posts

Posted - 2012-01-26 : 08:11:01

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?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-01-26 : 10:13:59

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-%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources