February 2, 2010 at 8:46 am
declare @StartDate as datetime
declare @EndDate as datetime
declare @ProductGroupCd as varchar(20)
SET @StartDate=convert(datetime, '1/30/2010')
SET @EndDate=convert(datetime, '1/30/2010')
SET @ProductGroupCd='04'
select * from
MSCSQL.CentralIntake.dbo.Referrals R
INNER JOINMSCSQL.CentralIntake.dbo.ReferralLineItems RLI
ON RLI.ReferralId = R.ReferralId
INNER JOIN MSCSQL.CentralIntake.dbo.QALineItems QALI
ON QALI.ReferralLineItemId = RLI.ReferralLineItemId
LEFT OUTER JOIN MSCSQL.CentralIntake.dbo.referrallineitem_prepo_po RP
on (RP.referrallineitemid=RLI.referrallineitemid and RP.PrePOId is Null and RP.POID is not Null and RP.POLine is Not Null)
LEFT OUTER JOIN MSCSQL.fstrkdb_3.dbo.Poheader PH
on PH.PO_number=RP.POid
LEFT OUTER JOIN MSCSQL.fstrkdb_3.dbo.Vendor V
on V.Vendor_number=PH.Vendor_number
LEFT OUTER join MSCSQL.CentralIntake.dbo.PurchaseOrders PO
onR.ReferralId = PO.ReferralId
LEFT OUTER join MSCSQL.CentralIntake.dbo.PurchaseOrderLineItems PL
on ( RLI.ReferralLineItemId = PL.ReferralLineItemId AND PO.poid = PL.POId)
LEFT OUTER JOINMSCSQL.CentralIntake.dbo.VendorSites VS
on PO.Vendornumber = VS.Vendor_number
where
(Cast(Convert(varchar, RLI.StartDate, 101) as datetime) between @StartDate AND @EndDate)
AND (
RLI.ProductNumber NOT in (SELECT [Product_number]FROM [dbMSCReports].[dbo].[tbl_Discarded_Products])
)
AND(QALineItemId =
(COALESCE((select max(QA2.[QALineItemId]) as maxnum from MSCSQL.CentralIntake.dbo.QALineItems QA2
Where QA2.ReferralLineItemId = QALI.ReferralLineItemId), 0)))
AND ((PH.PO_DATE IS NOT NULL AND LEN(PH.PO_DATE) <> 0) OR
(PO.createddate IS NOT NULL AND LEN(PO.createddate) <> 0 AND rtrim(PO.POStatus) != 'Closed' ) )
AND (QALI.QAStatus in ('NEW','PENDING'))
AND ( RLI.ParentReferralLineItemID is NULL )
February 2, 2010 at 9:32 am
sanjanajax,
See the following MS KB:
http://support.microsoft.com/kb/936223
http://support.microsoft.com/kb/915074
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply