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.
Author |
Topic |
runnerpaul
Starting Member
24 Posts |
Posted - 2013-09-04 : 07:54:37
|
Hi,I wish to change my query(see below) so that when the CUSTPACKINGSLIPTRANS table is joined, only the first record for each PACKINGSLIPID is selected. Is this possible? A unique identifying field in this table is RECID.CheersPaulSELECT TOP (100) PERCENT dbo.CUSTPACKINGSLIPTRANS.DIMENSION2_ [Load Type], dbo.PURCHLINE.DIMENSION3_ AS [Cost Centre], CASE WHEN Month(dbo.PURCHLINE.DELIVERYDATE) <= 2 THEN year(dbo.PURCHLINE.DELIVERYDATE) - 1 WHEN Month(dbo.PURCHLINE.DELIVERYDATE) >= 4 THEN year(dbo.PURCHLINE.DELIVERYDATE) WHEN Month(dbo.PURCHLINE.DELIVERYDATE) = 3 AND year(dbo.PURCHLINE.DELIVERYDATE) <= 2011 THEN year(dbo.PURCHLINE.DELIVERYDATE) ELSE year(dbo.PURCHLINE.DELIVERYDATE) - 1 END AS [Fin Year], CASE WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) <= 2 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 10 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2011 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) >= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 2 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) = 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) = 3 THEN 13 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2013 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) <= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 9 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) >= 4 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 3 END AS Period, CASE dbo.PURCHTABLE.PURCHASETYPE WHEN '0' THEN 'Journal' WHEN '1' THEN 'Quotation' WHEN '2' THEN 'Subscription' WHEN '3' THEN 'Purchase Order' WHEN '4' THEN 'Returned Item' WHEN '5' THEN 'Blanket Order' END AS [Order Type], dbo.PURCHTABLE.PURCHID AS [Purchase Order], dbo.PURCHTABLE.PROJID AS Project, CONVERT(varchar(12), dbo.PURCHTABLE.CREATEDDATE, 111) AS [Trans Date], dbo.PURCHTABLE.VENDORREF AS [Vendor Requistion], CASE dbo.PURCHTABLE.PURCHSTATUS WHEN '0' THEN 'None' WHEN '1' THEN 'Open Order' WHEN '2' THEN 'Received' WHEN '3' THEN 'Invoiced' WHEN '4' THEN 'Canceled' END AS [Order Status], dbo.PURCHTABLE.CURRENCYCODE AS [Order Currency], dbo.PURCHTABLE.ORDERACCOUNT AS [Vendor Account], dbo.PURCHTABLE.INVOICEACCOUNT AS [Invoice Account], dbo.VENDTABLE.NAME AS [Vendor Name], DIMENSIONS_1.DESCRIPTION AS [Product Group], dbo.INVENTTABLE.ITEMGROUPID AS [Item Group], dbo.INVENTTABLE.SECTION, dbo.PURCHLINE.ITEMID AS [Stock Code], dbo.INVENTTABLE.ITEMNAME AS [Stock Desc], dbo.PURCHLINE.PURCHUNIT AS [Unit of Purchase], dbo.PURCHLINE.PURCHQTY AS [Qty Ordered], dbo.PURCHLINE.REMAINPURCHPHYSICAL AS [Qty Remaining], dbo.INVENTTABLE.NETWEIGHT AS [Item Weight], dbo.PURCHLINE.PURCHPRICE AS [Unit Price], dbo.PURCHLINE.LINEPERCENT AS [Dicount %], dbo.PURCHLINE.LINEDISC AS [Discount Amt], dbo.PURCHLINE.LINEAMOUNT AS [Net Amount(Pos)], CASE dbo.PURCHLINE.BLOCKED WHEN '0' THEN 'No' WHEN '1' THEN 'Yes' END AS [Order Line Stopped?], dbo.PURCHLINE.DIMENSION AS [Site/Location], dbo.PURCHLINE.DIMENSION4_ AS Department, dbo.PURCHLINE.DIMENSION6_ AS [Job Number], dbo.INVENTDIM.INVENTLOCATIONID AS Warehouse, dbo.EMPLTABLE.NAME AS [Sales Person], CONVERT(varchar(12), dbo.PURCHLINE.DELIVERYDATE, 111) AS [Delivery Date], dbo.INVENTPOSTING.LEDGERACCOUNTID AS [GL Code], dbo.LEDGERTABLE.ACCOUNTNAME AS [GL Name], dbo.INVENTTABLEMODULE.PRICE AS [Standard Cost Price], dbo.PROJVISITLOADS.LOADWEIGHT AS [Load Weight]FROM dbo.VENDTABLE INNER JOIN dbo.PURCHLINE INNER JOIN dbo.INVENTTABLE ON dbo.PURCHLINE.ITEMID = dbo.INVENTTABLE.ITEMID INNER JOIN dbo.PURCHTABLE ON dbo.PURCHLINE.PURCHID = dbo.PURCHTABLE.PURCHID AND dbo.PURCHLINE.DATAAREAID = dbo.PURCHTABLE.DATAAREAID ON dbo.VENDTABLE.ACCOUNTNUM = dbo.PURCHTABLE.ORDERACCOUNT INNER JOIN dbo.INVENTPOSTING ON dbo.INVENTTABLE.ITEMGROUPID = dbo.INVENTPOSTING.ITEMRELATION INNER JOIN dbo.LEDGERTABLE ON dbo.INVENTPOSTING.LEDGERACCOUNTID = dbo.LEDGERTABLE.ACCOUNTNUM INNER JOIN dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID LEFT OUTER JOIN dbo.DIMENSIONS AS DIMENSIONS_1 ON dbo.PURCHLINE.DIMENSION2_ = DIMENSIONS_1.NUM LEFT OUTER JOIN dbo.EMPLTABLE ON dbo.PURCHTABLE.PURCHPLACER = dbo.EMPLTABLE.EMPLID LEFT OUTER JOIN dbo.INVENTDIM ON dbo.PURCHLINE.INVENTDIMID = dbo.INVENTDIM.INVENTDIMID LEFT OUTER JOIN dbo.PROJVISITLOADS ON dbo.PURCHTABLE.PURCHID = dbo.PROJVISITLOADS.HAULAGEPO INNER JOIN dbo.CUSTPACKINGSLIPTRANS ON dbo.PROJVISITLOADS.PACKINGSLIPID = dbo.CUSTPACKINGSLIPTRANS.PACKINGSLIPID INNER JOIN dbo.CUSTPACKINGSLIPJOUR ON dbo.PROJVISITLOADS.PACKINGSLIPID = dbo.CUSTPACKINGSLIPJOUR.PACKINGSLIPIDWHERE (dbo.EMPLTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTDIM.DATAAREAID = N'agp') AND (dbo.PURCHTABLE.DATAAREAID = N'agp') AND (dbo.PURCHLINE.DATAAREAID = N'agp') AND (DIMENSIONS_1.DATAAREAID = N'agp') AND (DIMENSIONS_1.DIMENSIONCODE = 1) AND (dbo.VENDTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTPOSTING.DATAAREAID = N'agp') AND (dbo.INVENTPOSTING.INVENTACCOUNTTYPE = 0) AND (dbo.LEDGERTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLEMODULE.MODULETYPE = 0) AND (dbo.INVENTTABLEMODULE.DATAAREAID = N'vuk') AND (dbo.CUSTPACKINGSLIPTRANS.DATAAREAID = N'agp') AND (dbo.CUSTPACKINGSLIPJOUR.DATAAREAID = N'agp') |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-09-04 : 08:30:35
|
[code]SELECT * FROM (SELECT <Your Columns List what ever in your above query> , ROW_NUMBER() OVER(PARTITION BY PACKINGSLIPID ORDER BY ColumnNameOnwhichBasisYouWantFirstRow ) RN FROM ........................... ) TempWHERE Temp.RN=1[/code]--Chandu |
|
|
runnerpaul
Starting Member
24 Posts |
Posted - 2013-09-05 : 03:59:33
|
Perhaps what I was looking to do was wrong. The query now only returns 5060 records. That's the same number of records that CUSTPACKINGSLIPTRANS gives me when I search for the first record for each PACKINGSLIPID.Perhaps the way I'm going about this is incorrect. Let me try to explain what I'm trying to do.Below is my original query:SELECT TOP (100) PERCENT dbo.PURCHLINE.DIMENSION3_ AS [Cost Centre], CASE WHEN Month(dbo.PURCHLINE.DELIVERYDATE) <= 2 THEN year(dbo.PURCHLINE.DELIVERYDATE) - 1 WHEN Month(dbo.PURCHLINE.DELIVERYDATE) >= 4 THEN year(dbo.PURCHLINE.DELIVERYDATE) WHEN Month(dbo.PURCHLINE.DELIVERYDATE) = 3 AND year(dbo.PURCHLINE.DELIVERYDATE) <= 2011 THEN year(dbo.PURCHLINE.DELIVERYDATE) ELSE year(dbo.PURCHLINE.DELIVERYDATE) - 1 END AS [Fin Year], CASE WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) <= 2 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 10 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) <= 2011 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) >= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 2 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) = 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) = 3 THEN 13 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2013 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) <= 3 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) + 9 WHEN YEAR(dbo.PURCHLINE.DELIVERYDATE) >= 2012 AND MONTH(dbo.PURCHLINE.DELIVERYDATE) >= 4 THEN MONTH(dbo.PURCHLINE.DELIVERYDATE) - 3 END AS Period, CASE dbo.PURCHTABLE.PURCHASETYPE WHEN '0' THEN 'Journal' WHEN '1' THEN 'Quotation' WHEN '2' THEN 'Subscription' WHEN '3' THEN 'Purchase Order' WHEN '4' THEN 'Returned Item' WHEN '5' THEN 'Blanket Order' END AS [Order Type], dbo.PURCHTABLE.PURCHID AS [Purchase Order], dbo.PURCHTABLE.PROJID AS Project, CONVERT(varchar(12), dbo.PURCHTABLE.CREATEDDATE, 111) AS [Trans Date], dbo.PURCHTABLE.VENDORREF AS [Vendor Requistion], CASE dbo.PURCHTABLE.PURCHSTATUS WHEN '0' THEN 'None' WHEN '1' THEN 'Open Order' WHEN '2' THEN 'Received' WHEN '3' THEN 'Invoiced' WHEN '4' THEN 'Canceled' END AS [Order Status], dbo.PURCHTABLE.CURRENCYCODE AS [Order Currency], dbo.PURCHTABLE.ORDERACCOUNT AS [Vendor Account], dbo.PURCHTABLE.INVOICEACCOUNT AS [Invoice Account], dbo.VENDTABLE.NAME AS [Vendor Name], DIMENSIONS_1.DESCRIPTION AS [Product Group], dbo.INVENTTABLE.ITEMGROUPID AS [Item Group], dbo.INVENTTABLE.SECTION, dbo.PURCHLINE.ITEMID AS [Stock Code], dbo.INVENTTABLE.ITEMNAME AS [Stock Desc], dbo.PURCHLINE.PURCHUNIT AS [Unit of Purchase], dbo.PURCHLINE.PURCHQTY AS [Qty Ordered], dbo.PURCHLINE.REMAINPURCHPHYSICAL AS [Qty Remaining], dbo.INVENTTABLE.NETWEIGHT AS [Item Weight], dbo.PURCHLINE.PURCHPRICE AS [Unit Price], dbo.PURCHLINE.LINEPERCENT AS [Dicount %], dbo.PURCHLINE.LINEDISC AS [Discount Amt], dbo.PURCHLINE.LINEAMOUNT AS [Net Amount(Pos)], CASE dbo.PURCHLINE.BLOCKED WHEN '0' THEN 'No' WHEN '1' THEN 'Yes' END AS [Order Line Stopped?], dbo.PURCHLINE.DIMENSION AS [Site/Location], dbo.PURCHLINE.DIMENSION4_ AS Department, dbo.PURCHLINE.DIMENSION6_ AS [Job Number], dbo.INVENTDIM.INVENTLOCATIONID AS Warehouse, dbo.EMPLTABLE.NAME AS [Sales Person], CONVERT(varchar(12), dbo.PURCHLINE.DELIVERYDATE, 111) AS [Delivery Date], dbo.INVENTPOSTING.LEDGERACCOUNTID AS [GL Code], dbo.LEDGERTABLE.ACCOUNTNAME AS [GL Name], dbo.INVENTTABLEMODULE.PRICE AS [Standard Cost Price], dbo.PROJVISITLOADS.LOADWEIGHT AS [Load Weight]FROM dbo.VENDTABLE INNER JOIN dbo.PURCHLINE INNER JOIN dbo.INVENTTABLE ON dbo.PURCHLINE.ITEMID = dbo.INVENTTABLE.ITEMID INNER JOIN dbo.PURCHTABLE ON dbo.PURCHLINE.PURCHID = dbo.PURCHTABLE.PURCHID AND dbo.PURCHLINE.DATAAREAID = dbo.PURCHTABLE.DATAAREAID ON dbo.VENDTABLE.ACCOUNTNUM = dbo.PURCHTABLE.ORDERACCOUNT INNER JOIN dbo.INVENTPOSTING ON dbo.INVENTTABLE.ITEMGROUPID = dbo.INVENTPOSTING.ITEMRELATION INNER JOIN dbo.LEDGERTABLE ON dbo.INVENTPOSTING.LEDGERACCOUNTID = dbo.LEDGERTABLE.ACCOUNTNUM INNER JOIN dbo.INVENTTABLEMODULE ON dbo.INVENTTABLE.ITEMID = dbo.INVENTTABLEMODULE.ITEMID LEFT OUTER JOIN dbo.DIMENSIONS AS DIMENSIONS_1 ON dbo.PURCHLINE.DIMENSION2_ = DIMENSIONS_1.NUM LEFT OUTER JOIN dbo.EMPLTABLE ON dbo.PURCHTABLE.PURCHPLACER = dbo.EMPLTABLE.EMPLID LEFT OUTER JOIN dbo.INVENTDIM ON dbo.PURCHLINE.INVENTDIMID = dbo.INVENTDIM.INVENTDIMID LEFT OUTER JOIN dbo.PROJVISITLOADS ON dbo.PURCHTABLE.PURCHID = dbo.PROJVISITLOADS.HAULAGEPOWHERE (dbo.EMPLTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTDIM.DATAAREAID = N'agp') AND (dbo.PURCHTABLE.DATAAREAID = N'agp') AND (dbo.PURCHLINE.DATAAREAID = N'agp') AND (DIMENSIONS_1.DATAAREAID = N'agp') AND (DIMENSIONS_1.DIMENSIONCODE = 1) AND (dbo.VENDTABLE.DATAAREAID = N'vuk') AND (dbo.INVENTPOSTING.DATAAREAID = N'agp') AND (dbo.INVENTPOSTING.INVENTACCOUNTTYPE = 0) AND (dbo.LEDGERTABLE.DATAAREAID = N'val') AND (dbo.INVENTTABLEMODULE.MODULETYPE = 0) AND (dbo.INVENTTABLEMODULE.DATAAREAID = N'vuk') When I run it I get just over 71000 rows.I now want to add a a column from a different table (dbo.CUSTPACKINGSLIPTRANS.DIMENSION2_). If I add this as an INNER JOIN my query returns over 86000. I tried different join types but the row counts were still out. I'm looking to get the same number of rows returned but just have the extra column. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-09-06 : 01:43:35
|
What Bandi said will work to make sure you only pick one row. The fact you're getting more than one says you're not doing that ROW_NUMBER() thing. If you don't have at least one row in the CUSTPACKINGSLIPTRANS table for each row in in the source table then you will lose rows and you'll have to do an outer join and deal with NULLS where the row is missing.And drop the 100 percent - pointless. |
|
|
|
|
|
|
|