Link to home
Start Free TrialLog in
Avatar of JohnJMA
JohnJMAFlag for United States of America

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
Avatar of Sharath S
Sharath S
Flag of United States of America image

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

Open in new window

Avatar of JohnJMA

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,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
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'

Open in new window

Avatar of JohnJMA

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

Open in new window

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

Open in new window

Avatar of JohnJMA

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.
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

Open in new window

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
...

Open in new window

Avatar of JohnJMA

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?
Yes, that looks good.
Avatar of JohnJMA

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.
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.
Avatar of JohnJMA

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
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JohnJMA

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,
Avatar of JohnJMA

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.ItemType) AS ssItemType
       ,ISNULL(ss.SPLIT,t.SplitQnty) 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

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

Open in new window