|
Pom Grewal
Starting Member
United Kingdom
14 Posts |
 Posted - 01/26/2012 : 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 |
|
Dates in results not matching query
