JohnJMA
asked on
How to select one of possibly 3 records in a query
Hello,
I have been trying to work on this solution for some time and I just cant figure it out. I will try to explain it as bes as possible. I am doing a number of sql SELECTS and building a temporary table which is working well. In my final select the betwen the temporary table (TempTable) and another table (RSTTable) it is possible to get up to 3 records returned for one record in my temporary table. The RSTTable can return 3 records because of all the data fields other than one of the fields called Type which holds an R, S or T value. Depending on the value of the Type field and the combination of records available the selectedrecord is as follows:
if all three types of records exist (R, S, and T) select the S record
if an R and T record exist select the T record
if an S and T record exist then select the S record
if an R and S record exist then select the S record
if only 1 record exist with any Type select that one reocrd (which the query will only return one records)
So if only one record exist that is not a problem it is when multiple records are returned. This is done with the TempTable having 1,000's of records at a time.
I hope my explination makes sense.
Thanks in advance for all the help.
John
I have been trying to work on this solution for some time and I just cant figure it out. I will try to explain it as bes as possible. I am doing a number of sql SELECTS and building a temporary table which is working well. In my final select the betwen the temporary table (TempTable) and another table (RSTTable) it is possible to get up to 3 records returned for one record in my temporary table. The RSTTable can return 3 records because of all the data fields other than one of the fields called Type which holds an R, S or T value. Depending on the value of the Type field and the combination of records available the selectedrecord is as follows:
if all three types of records exist (R, S, and T) select the S record
if an R and T record exist select the T record
if an S and T record exist then select the S record
if an R and S record exist then select the S record
if only 1 record exist with any Type select that one reocrd (which the query will only return one records)
So if only one record exist that is not a problem it is when multiple records are returned. This is done with the TempTable having 1,000's of records at a time.
I hope my explination makes sense.
Thanks in advance for all the help.
John
Can you post your query so far?
I assume col_1,col_2 are the join condition between TempTable and RSTTable. You can try like this.
SELECT *
FROM TempTable AS t1
JOIN (SELECT *,
ROW_NUMBER()
OVER(PARTITION BY col_1,col_2 ORDER BY SortOrder) rn
FROM (SELECT *,
CASE
WHEN TYPE = 'S' THEN 1
WHEN 'T' THEN 2
WHEN 'S' THEN 3
ELSE 4
END SortOrder
FROM RSTTable) t1
WHERE rn = 1) AS t2
ON t1.col_1 = t2.col_1
AND t1.col_2 = t2.col_2
ASKER
Sharath, here is what I have so far. The first part builds the temp file and the second query takes the one record from the temp file and returns 1 to 3 records from SQLSCSPrice table. From the SQLSCSPrice table I need to select one record, based on above criteria if more than one record is returned in the resulted query. Also, in the queries I am forcing some values to keep the amount of records returned to make it easier to figure out. in the MDTemp table there could be 1,000's of records and in the SQLSCSPrice table there are currently 400,000 and growing.
-------------------------- ---------- ----
-- Build MD Temp table
-------------------------- ---------- ----
INSERT INTO MDTemp
(StoreId, UPC, MovementDate, ActualMovement,DollarsGene rated)
SELECT t.StoreId, t.UPCNbr, t.TransDate
,t.Qnty, t.SoldPrice
--,SUM(t.Qnty) AS Qnty, SUM(t.SoldPrice) AS SoldPrice
FROM dbo.TLogDtl t
WHERE t.storeid = 92
AND t.transdate = '3/11/11'
AND t.UPCNbr = 1780042903
AND t.SoldPrice <> 0
--GROUP BY t.StoreId, t.UPCNbr, t.TransDate
SELECT * FROM mdtemp
-------------------------- ---------- ----
-- End Build MD Temp table
-------------------------- ---------- ----
SELECT t.StoreId, t.UPC, t.MovementDate
,s.Zone
,sv.VendorNumber
,ss.BeginDate, ss.EndDate, ss.zone, ss.RType
FROM MDTemp t
JOIN PSPCorp.dbo.Store s ON t.StoreId = s.StoreId
JOIN PSPCorp.dbo.StoreVendor sv ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv ON t.UPC = pv.UPC AND sv.VendorNumber = pv.VendorNumber
JOIN PSPCorp.dbo.SQLSCSPrice ss ON t.UPC = ss.UPC AND sv.VendorNumber = ss.VendorNumber
WHERE ss.zone = 2
AND '3/11/11' BETWEEN ss.BeginDate AND ss.EndDate
--------------------------
-- Build MD Temp table
--------------------------
INSERT INTO MDTemp
(StoreId, UPC, MovementDate, ActualMovement,DollarsGene
SELECT t.StoreId, t.UPCNbr, t.TransDate
,t.Qnty, t.SoldPrice
--,SUM(t.Qnty) AS Qnty, SUM(t.SoldPrice) AS SoldPrice
FROM dbo.TLogDtl t
WHERE t.storeid = 92
AND t.transdate = '3/11/11'
AND t.UPCNbr = 1780042903
AND t.SoldPrice <> 0
--GROUP BY t.StoreId, t.UPCNbr, t.TransDate
SELECT * FROM mdtemp
--------------------------
-- End Build MD Temp table
--------------------------
SELECT t.StoreId, t.UPC, t.MovementDate
,s.Zone
,sv.VendorNumber
,ss.BeginDate, ss.EndDate, ss.zone, ss.RType
FROM MDTemp t
JOIN PSPCorp.dbo.Store s ON t.StoreId = s.StoreId
JOIN PSPCorp.dbo.StoreVendor sv ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv ON t.UPC = pv.UPC AND sv.VendorNumber = pv.VendorNumber
JOIN PSPCorp.dbo.SQLSCSPrice ss ON t.UPC = ss.UPC AND sv.VendorNumber = ss.VendorNumber
WHERE ss.zone = 2
AND '3/11/11' BETWEEN ss.BeginDate AND ss.EndDate
Try this:
----------------------------------------
-- Build MD Temp table
----------------------------------------
INSERT INTO MDTemp
(StoreId, UPC, MovementDate, ActualMovement,DollarsGenerated)
SELECT t.StoreId, t.UPCNbr, t.TransDate
,t.Qnty, t.SoldPrice
--,SUM(t.Qnty) AS Qnty, SUM(t.SoldPrice) AS SoldPrice
FROM dbo.TLogDtl t
WHERE t.storeid = 92
AND t.transdate = '3/11/11'
AND t.UPCNbr = 1780042903
AND t.SoldPrice <> 0
--GROUP BY t.StoreId, t.UPCNbr, t.TransDate
SELECT * FROM mdtemp
----------------------------------------
-- End Build MD Temp table
----------------------------------------
SELECT t.StoreId, t.UPC, t.MovementDate
,s.Zone
,sv.VendorNumber
,ss.BeginDate, ss.EndDate, ss.zone, ss.RType
INTO #Results
FROM MDTemp t
JOIN PSPCorp.dbo.Store s ON t.StoreId = s.StoreId
JOIN PSPCorp.dbo.StoreVendor sv ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv ON t.UPC = pv.UPC AND sv.VendorNumber = pv.VendorNumber
JOIN PSPCorp.dbo.SQLSCSPrice ss ON t.UPC = ss.UPC AND sv.VendorNumber = ss.VendorNumber
WHERE ss.zone = 2
AND '3/11/11' BETWEEN ss.BeginDate AND ss.EndDate
-- SELECT the correct record
declare @RTypes varchar(10)
set @types = (select ''+RType from #Results for xml path(''))
if len(@RTypes) = 1
select * from #Results
else if @RTypes like '%S%'
select * from #Results where RType = 'S'
else if @RTypes like '%R%'
select * from #Results where RType = 'R'
ASKER
Sharath, I think I understand what you are getting at. One question or comment to make sure I understand is where you have rn = 1 that gives the first Row_Number() no matter how manyrows are returned and where you have WHEN 'S' THEN 3 is possibly a typo and you meant 'R' and if all I have is three options then I don't need the ELSE or I could replace the ...THEN 3 with ELSE 3? Is this correct.
You can try this, in place of your second query.
;WITH cte1
AS (SELECT *,
CASE RType
WHEN 'S' THEN 1
WHEN 'T' THEN 2
WHEN 'R' THEN 3
ELSE 4
END SortOrder
FROM PSPCorp.dbo.SQLSCSPrice
WHERE ZONE = 2
AND '3/11/11' BETWEEN BeginDate AND EndDate),
cte2
AS (SELECT *,
ROW_NUMBER()
OVER(PARTITION BY UPC,VendorNumber ORDER BY SortOrder) rn
FROM cte1),
cte3
AS (SELECT *
FROM cte2
WHERE rn = 1)
SELECT t.StoreId,
t.UPC,
t.MovementDate,
s.ZONE,
sv.VendorNumber,
ss.BeginDate,
ss.EndDate,
ss.ZONE,
ss.RType
FROM MDTemp t
JOIN PSPCorp.dbo.Store s
ON t.StoreId = s.StoreId
JOIN PSPCorp.dbo.StoreVendor sv
ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv
ON t.UPC = pv.UPC
AND sv.VendorNumber = pv.VendorNumber
JOIN cte3 ss
ON t.UPC = ss.UPC
AND sv.VendorNumber = ss.VendorNumber
You got what I am trying to do. Thats correct. and that was a typo in my first post to have S multiple times in CASE condiiton.
Alternatively (based on Sharath_123 idea):
SELECT top 1 t.StoreId, t.UPC, t.MovementDate
,s.Zone
,sv.VendorNumber
,ss.BeginDate, ss.EndDate, ss.zone, ss.RType
FROM MDTemp t
JOIN PSPCorp.dbo.Store s ON t.StoreId = s.StoreId
JOIN PSPCorp.dbo.StoreVendor sv ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv ON t.UPC = pv.UPC AND sv.VendorNumber = pv.VendorNumber
JOIN PSPCorp.dbo.SQLSCSPrice ss ON t.UPC = ss.UPC AND sv.VendorNumber = ss.VendorNumber
WHERE ss.zone = 2
AND '3/11/11' BETWEEN ss.BeginDate AND ss.EndDate
ORDER BY CASE ss.RType
WHEN 'S' THEN 1
WHEN 'T' THEN 2
WHEN 'R' THEN 3
ELSE 4
END
ASKER
Hello Sharath & wdosanjos, Both solutions work. Two other questions arise. The first is if I want the Zone and Dates to be variables in the Select statements within the cte how would I do that? Especially since Zone would come from the Store table or the MDTemp table but it has to match
all of the critera and the date would come from the MDTemp MovementDate field.
The second question is how would you go about updating the VendorNumber and the RegSaleTPR (equal to the RType value)?
Thanks for all the hlep.
all of the critera and the date would come from the MDTemp MovementDate field.
The second question is how would you go about updating the VendorNumber and the RegSaleTPR (equal to the RType value)?
Thanks for all the hlep.
The update part would be something like this (I'm assuming the update is on the StoreVendor table):
UPDATE PSPCorp.dbo.StoreVendor
SET RegSaleTPR = q.RType
FROM PSPCorp.dbo.StoreVendor vendor
INNER JOIN (
SELECT top 1 t.StoreId, t.UPC, t.MovementDate
,s.Zone
,sv.VendorNumber
,ss.BeginDate, ss.EndDate, ss.zone, ss.RType
FROM MDTemp t
JOIN PSPCorp.dbo.Store s ON t.StoreId = s.StoreId
JOIN PSPCorp.dbo.StoreVendor sv ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv ON t.UPC = pv.UPC AND sv.VendorNumber = pv.VendorNumber
JOIN PSPCorp.dbo.SQLSCSPrice ss ON t.UPC = ss.UPC AND sv.VendorNumber = ss.VendorNumber
WHERE ss.zone = 2
AND '3/11/11' BETWEEN ss.BeginDate AND ss.EndDate
ORDER BY CASE ss.RType
WHEN 'S' THEN 1
WHEN 'T' THEN 2
WHEN 'R' THEN 3
ELSE 4
END) q ON q.VendorNumber = vendor.VendorNumber
Regarding the variables, you can set them as follows:
declare @zone int
declare @date datetime
set @zone = 2
set @date = '3/11/11'
...
WHERE ss.zone = @zone
AND @date BETWEEN ss.BeginDate AND ss.EndDate
...
ASKER
wdosanjos, the variables are actually field values from other tables that are being used in the current script so for example if I wanted to use t.MovementDate rather than @date I would just write it as:
WHERE ss.zone = s.Zone -- rather than @zone
AND t.MovementDate BETWEEN ss.BeginDate AND ss.EndDate
Correct?
WHERE ss.zone = s.Zone -- rather than @zone
AND t.MovementDate BETWEEN ss.BeginDate AND ss.EndDate
Correct?
Yes, that looks good.
ASKER
wdosanjos, I wass also wondering if you could explain how the code that you wrote works:
declare @RTypes varchar(10)
set @Rtypes = (select ''+RType from #Results for xml path(''))
if len(@RTypes) = 1
select * from #Results
else if @RTypes like '%S%'
select * from #Results where RType = 'S'
else if @RTypes like '%T%'
select * from #Results where RType = 'T'
else if @RTypes like '%R%'
select * from #Results where RType = 'R'
Thanks.
declare @RTypes varchar(10)
set @Rtypes = (select ''+RType from #Results for xml path(''))
if len(@RTypes) = 1
select * from #Results
else if @RTypes like '%S%'
select * from #Results where RType = 'S'
else if @RTypes like '%T%'
select * from #Results where RType = 'T'
else if @RTypes like '%R%'
select * from #Results where RType = 'R'
Thanks.
35168062 is a better solution than 35168347. Any way, 35168347 sets @RTypes to a string containing the RTypes on the result set. So if the result set contains 'S' and 'T', @RTypes will contain 'ST'. Then the if statements check the presence of a particular type in order to return the correct result set.
ASKER
Sharath, in the solution you provided me works great. I am trying to make the value of zone equate to a value (parameter) rather than just a number and the same about the MovementDate value to be a variable as well ('3/11/11' between startdate and enddate). The problem I am having is that the Zone comes from the store table (s.Zone) file and the movementdate (variable to replace the '3/11/11' date) comes from the MDTemp table (t.MovementDate). When I replace those two hardcoded values with varables SQL gives me a "s.Zone" could not be bound error, same thing with t.MovementDate, Any ideas on how I can work around this?
Thanks,
John
Thanks,
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath, Thanks for the help. On the above solution I am only getting on row returned for each row in MDTemp even if there are multiple UPC's for the same Store and MovementDate so I am not returning all of the data but selecing the correct data for the zone and where movementdate "between" a date range. On your previous solution (just rearranged differently than the above) I was able to get the correct number of records but the zone and movementdate had to be hard coded. I am working on trying to get the best of both, the correct number of records returned with using the zone and movementdate as avariables.
Shararth what do you think of this? What if I built the CTE file for all zones and all dates and then in the select statement I use the where clause to select for a specific zone and movmentdate. Similar to your first answer by just moveing the zone and movementdate from the cte section to the selecting of records section? Does this make sense?
Thanks,
Shararth what do you think of this? What if I built the CTE file for all zones and all dates and then in the select statement I use the where clause to select for a specific zone and movmentdate. Similar to your first answer by just moveing the zone and movementdate from the cte section to the selecting of records section? Does this make sense?
Thanks,
ASKER
Sharath, here is the solution I came up with. I did add the Zone field to the MDTEmp table. I think this works. If a record from the MDTemp (t) does not meet the criteria in SQLSCSPrice (s) (t.MovementDate between s.BeginDate and s.EndDate and t.Zone = s.Zone and t.UPC = s.UPC and t.StoreId = s.StoreId) then it is a valid record. All of the others that are in MDTemp that do not match that criteria are Non-Approved Vendors so they will get processed in a different way. I can select the non approved vendors by joining MDTemp to the #CTETemp and taking where null is equal to #CTETemp.
Here is the code I came up with does it look valid to you?
;WITH cte1
AS (SELECT *,
CASE RType
WHEN 'S' THEN 1
WHEN 'T' THEN 2
WHEN 'R' THEN 3
ELSE 4
END SortOrder
FROM PSPCorp.dbo.SQLSCSPrice ),
--WHERE ZONE = 2
-- AND '1/2/11' BETWEEN BeginDate AND EndDate),
cte2
AS (SELECT *,
ROW_NUMBER()
OVER(PARTITION BY UPC,VendorNumber, Zone ORDER BY SortOrder) rn
FROM cte1),
cte3
AS (SELECT *
FROM cte2
WHERE rn = 1)
SELECT t.StoreId
,t.UPC
,t.MovementDate
,t.ZONE
,t.ItemType as tItemType
,t.Retail as tRetail
,t.ActualMovement, t.DollarsGenerated, t.SplitQnty, t.SplitPrice
,s.ZONE AS sZone
,ss.BeginDate
,ss.EndDate
,ss.ZONE AS ssZone
,ss.RType
,ss.VendorNumber
,ss.CaseQty, ss.CaseCost, ISNULL(ss.ItemType,t.ItemT ype) AS ssItemType
,ISNULL(ss.SPLIT,t.SplitQn ty) AS ssSplit, ISNULL(ss.Retail, t.Retail) AS ssRetail
INTO #CTETemp
FROM MDTemp t
JOIN PSPCorp.dbo.StoreInfo s
ON t.StoreId = s.StoreId3
JOIN PSPCorp.dbo.StoreVendor sv
ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv
ON t.UPC = pv.UPC
AND sv.VendorNumber = pv.VendorNumber
JOIN cte3 ss
ON t.UPC = ss.UPC
AND sv.VendorNumber = ss.VendorNumber
WHERE t.ZONE = ss.ZONE
AND t.MovementDate BETWEEN ss.BeginDate AND ss.EndDate
Here is the code I came up with does it look valid to you?
;WITH cte1
AS (SELECT *,
CASE RType
WHEN 'S' THEN 1
WHEN 'T' THEN 2
WHEN 'R' THEN 3
ELSE 4
END SortOrder
FROM PSPCorp.dbo.SQLSCSPrice ),
--WHERE ZONE = 2
-- AND '1/2/11' BETWEEN BeginDate AND EndDate),
cte2
AS (SELECT *,
ROW_NUMBER()
OVER(PARTITION BY UPC,VendorNumber, Zone ORDER BY SortOrder) rn
FROM cte1),
cte3
AS (SELECT *
FROM cte2
WHERE rn = 1)
SELECT t.StoreId
,t.UPC
,t.MovementDate
,t.ZONE
,t.ItemType as tItemType
,t.Retail as tRetail
,t.ActualMovement, t.DollarsGenerated, t.SplitQnty, t.SplitPrice
,s.ZONE AS sZone
,ss.BeginDate
,ss.EndDate
,ss.ZONE AS ssZone
,ss.RType
,ss.VendorNumber
,ss.CaseQty, ss.CaseCost, ISNULL(ss.ItemType,t.ItemT
,ISNULL(ss.SPLIT,t.SplitQn
INTO #CTETemp
FROM MDTemp t
JOIN PSPCorp.dbo.StoreInfo s
ON t.StoreId = s.StoreId3
JOIN PSPCorp.dbo.StoreVendor sv
ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv
ON t.UPC = pv.UPC
AND sv.VendorNumber = pv.VendorNumber
JOIN cte3 ss
ON t.UPC = ss.UPC
AND sv.VendorNumber = ss.VendorNumber
WHERE t.ZONE = ss.ZONE
AND t.MovementDate BETWEEN ss.BeginDate AND ss.EndDate
No, you can include as many columns as you want in the PARTITION BY clause. Can you run this query and give me the result. Also give me the expected result.
SELECT t.StoreId,
t.UPC,
t.MovementDate,
s.ZONE,
sv.VendorNumber,
ss.BeginDate,
ss.EndDate,
ss.RType,
CASE ss.RType
WHEN 'S' THEN 1
WHEN 'T' THEN 2
WHEN 'R' THEN 3
ELSE 4
END SortOrder
FROM MDTemp t
JOIN PSPCorp.dbo.Store s
ON t.StoreId = s.StoreId
JOIN PSPCorp.dbo.StoreVendor sv
ON t.StoreId = sv.StoreId
JOIN PSPCorp.dbo.ProductVendor pv
ON t.UPC = pv.UPC
AND sv.VendorNumber = pv.VendorNumber
JOIN PSPCorp.dbo.SQLSCSPrice ss
ON t.UPC = ss.UPC
AND sv.VendorNumber = ss.VendorNumber
WHERE t.MovementDate BETWEEN ss.BeginDate AND ss.EndDate