here i am selecting many col. but i want only
x.PurchaseOrderNumber
to be distinct.
i have used
SELECT disctinct(x.PurchaseOrderNumber), x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,
x.POStatus,
x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost
but it does not serve the purpose
i have even used group by
x.PurchaseOrderNumber
but it for that i have use aggregated function which i don't want to use !!
SELECT x.PurchaseOrderNumber, x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,
x.POStatus,
x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost
FROM wsm_View_PODashboardList AS X
INNER JOIN dbo.wsm_WorkOrder_PurchaseOrder wp ON wp.PurchaseOrderNumber = X.PurchaseOrderNumber
INNER JOIN dbo.wsm_WorkOrderSchedule wo ON wo.WONumber = wp.WONumber
where X.POStatus in ('Created','Authorised','Sent','Matched')
and wo.WOStatus in ('New Request','Delayed','Scheduled','Work In Progress')
and wo.EstCompletionDate <= getdate ()
and wo.siteid in (select wsm_ContactSite.siteid from wsm_ContactSite where wsm_ContactSite.contactid = @ContactID) ORDER BY X.DateRaised DESC
What I have tried:
i have used
SELECT disctinct(x.PurchaseOrderNumber), x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,
x.POStatus,
x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost
but it does not serve the purpose
i have even used group by
x.PurchaseOrderNumber
but it for that i have use aggregated function which i don't want to use !!