Solved

How to select one of possibly 3 records in a query

Posted on 2011-03-17
20
167 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:JohnJMA
  • 8
  • 6
  • 6
20 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35160597
Can you post your query so far?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35160625
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

0
 

Author Comment

by:JohnJMA
ID: 35160652
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
      
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35160730
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

0
 

Author Comment

by:JohnJMA
ID: 35160747
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.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35160780
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

0
 
LVL 40

Expert Comment

by:Sharath
ID: 35160789
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.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35160836
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

0
 

Author Comment

by:JohnJMA
ID: 35167939
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.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35168062
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

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 23

Expert Comment

by:wdosanjos
ID: 35168155
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

0
 

Author Comment

by:JohnJMA
ID: 35168295
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?
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35168341
Yes, that looks good.
0
 

Author Comment

by:JohnJMA
ID: 35168347
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.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35182488
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.
0
 

Author Comment

by:JohnJMA
ID: 35184275
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
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35184372
try this.
;WITH cte1 
     AS (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), 
     cte2 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY ZONE,UPC,VendorNumber ORDER BY SortOrder) rn 
           FROM cte1) 
SELECT * 
  FROM cte2 
 WHERE rn = 1

Open in new window

0
 

Author Comment

by:JohnJMA
ID: 35189259
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,
0
 

Author Comment

by:JohnJMA
ID: 35189793
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

0
 
LVL 40

Expert Comment

by:Sharath
ID: 35196128
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

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now