Optimize Cursor

Question

Optimize Cursor

SSCarpal Tunnel

Points: 4473

February 21, 2020 at 9:55 pm

I inherited this script that has a cursor and I know this query can be optimized by not using a cursor but can't figure out how to optimize it. Can someone help?

DECLARE @GroupNbr INT
SET @GroupNbr = 1
--2) Find out how many groups need to get processed
DECLARE @MaxGroupNbr INT
SELECT @MaxGroupNbr= 
       (SELECT MAX(groupnbr) 
       FROM    tmpDeal01 
       )
PRINT 'Starting Group Number: ' + CAST(@GroupNbr AS VARCHAR(10)) + ' of ' + CAST(@MaxGroupNbr AS VARCHAR(10)) + ' total groups. '
WHILE (@GroupNbr <= @MaxGroupNbr)
--3) Start to process the first group number
BEGIN
        --4) Start with the first row number for the group
        DECLARE @RowNbr INT
        SET @RowNbr = 1
        --5) Find the maximum row numbers in group.  This will be the number of loops per group number
        DECLARE @MaxGroupRowNbr INT
        SELECT @MaxGroupRowNbr=COUNT(*) 
        FROM   tmpDeal01 
        WHERE  GroupNbr=@GroupNbr
        PRINT 'This group ' + CAST(@GroupNbr AS VARCHAR(10)) + ' has a maximum of ' + CAST(@MaxGroupRowNbr AS VARCHAR(10)) + ' Rows'
        WHILE (@RowNbr <= @MaxGroupRowNbr)
        BEGIN
                UPDATE A
                SET        a.FromDate = 
                           CASE 
                                      WHEN a.Price_Paid = b.Price_Paid 
                                      THEN b.FromDate 
                                      ELSE a.RcvDt 
                           END
                FROM       tmpDeal01            AS A 
                           INNER JOIN tmpDeal01 AS B 
                           ON         a.RowNbr   = b.RowNbr +1
                           AND        a.GroupNbr = b.GroupNbr
                WHERE      a.GroupNbr            = @GroupNbr
                AND        a.RowNbr              = @RowNbr
                PRINT @RowNbr
                SET @RowNbr =@RowNbr + 1
        END
        SET @GroupNbr =@GroupNbr + 1
END
GO

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

What's the structure of tmpDeal01? And where does it get its data? There's not enough detail here to figure out the problem, I don't think. Got some sample data - you know, CREATE TABLE & INSERT scripts?

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

This looks like a recursive update, so it's best to go row by row (or, if you are willing, use Moden's "special" update method using a specific clustered index structure).

But, I would think that at least you could avoid a separate loop for each group, like so:

--4) 
DECLARE @RowNbr INT
SET @RowNbr = 1
--5) Find the maximum number of rows in *any* group.  This will be the total number of loops required.
DECLARE @MaxRowNbr INT
SELECT @MaxRowNbr = MAX(RowCount)
FROM (
    SELECT GroupNbr, COUNT(*) AS RowCount
    FROM   tmpDeal01 
    GROUP BY GroupNbr
) AS derived
PRINT 'The largest row number in any group is ' + 
    CAST(@MaxRowNbr AS VARCHAR(10)) + '.'
WHILE (@RowNbr <= @MaxRowNbr)
BEGIN
        UPDATE A
        SET        a.FromDate = 
                    CASE 
                                WHEN a.Price_Paid = b.Price_Paid 
                                THEN b.FromDate 
                                ELSE a.RcvDt 
                    END
        FROM       tmpDeal01            AS A 
                    INNER JOIN tmpDeal01 AS B 
                    ON         a.RowNbr   = b.RowNbr +1
                    AND        a.GroupNbr = b.GroupNbr
        WHERE      a.RowNbr               = @RowNbr
        PRINT @RowNbr
        SET @RowNbr =@RowNbr + 1
END /*WHILE*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

no need for a loop as far as I can see, but a sub select required to ensure we do get the correct date set

UPDATE A
SET  a.FromDate = new.new_FromDate
FROM tmpDeal01 AS A 
inner join (select a.RowNbr
                 , a.GroupNbr
                 , CASE 
                   WHEN a.Price_Paid = b.Price_Paid 
                    THEN b.FromDate 
                   ELSE a.RcvDt 
                   END as new_FromDate
            FROM tmpDeal01 AS A 
            INNER JOIN tmpDeal01 AS B 
                ON  a.RowNbr   = b.RowNbr +1
                AND a.GroupNbr = b.GroupNbr
           ) new
           ON  new.RowNbr   = a.RowNbr
           AND new.GroupNbr = a.GroupNbr

This reply was modified 4 years, 9 months ago by frederico_fonseca.
This reply was modified 4 years, 9 months ago by frederico_fonseca.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Scott's answer looks correct, the UPDATE is potentially updating the FromDate with the FromDate of a row it has previously just updated (which also could have been updated with the value in a row that has just been updated).

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

You could also do it all in a single statement with a recursive CTE:

;WITH rCTE AS
(
    SELECT a.RcvDt,
            a.FromDate,
            a.GroupNbr,
            a.RowNbr,
            a.Price_Paid 
        FROM tmpDeal01 a
        CROSS APPLY (SELECT NULL x
                       FROM tmpDeal01 b 
                      WHERE b.GroupNbr = a.GroupNbr
                     HAVING MIN(b.RowNbr) = a.RowNbr) b
      UNION ALL
     SELECT a.RcvDt,
            CASE WHEN a.Price_Paid = rCTE.Price_Paid THEN rCTE.FromDate
                 ELSE a.RcvDt
            END,
            a.GroupNbr,
            a.RowNbr,
            a.Price_Paid 
       FROM tmpDeal01 a
      INNER JOIN rCTE
              ON rCTE.GroupNbr = a.GroupNbr
             AND rCTE.RowNbr = a.RowNbr - 1
)
UPDATE a
   SET a.FromDate = rCTE.FromDate
  FROM tmpDeal01 a
 INNER JOIN rCTE
         ON rCTE.GroupNbr = a.GroupNbr
        AND rCTE.RowNbr = a.RowNbr

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

@GrassHopper,

Since you're using SQL Server 2016, this can probably be easily accomplished with LEAD/LAG. Help us help you... post the content of the tmpDeal01 table in a readily consumable format. Please see the first link in my signature line below for one way to do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 7

This is a sample of the data:

--===== If the test table already exists, drop it
     IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
         DROP TABLE #mytable
--===== Create the test table with 
 CREATE TABLE #mytable 
        (
        GroupNbr INT NOT NULL,
        FromDate DATETIME NULL,
ToDate DATETIME NULL, 
        Vndnbr varchar(50) NULL,
        ItemNbr varchar(50) NULL,
        ItemDesc varchar(50) NULL, 
RcvDt Date NULL,
Price_Paid Numeric(12,2) NULL,
RowNbr bigint NULL 
        )
--===== Setup any special required conditions especially where dates are concerned
    SET DATEFORMAT DMY

Insert Into #mytable (GroupNbr,
        FromDate,
ToDate, 
        Vndnbr,
        ItemNbr,
        ItemDesc, 
RcvDt,
Price_Paid,
RowNbr) 
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2016-12-05','52.26','1' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2016-12-06','52.26','2' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2016-12-13','52.26','3' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2016-12-20','52.26','4' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2016-12-28','52.26','5' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2016-12-30','52.26','6' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-01-05','52.26','7' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-01-10','52.26','8' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-01-12','52.26','9' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-01-17','52.26','10' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-01-31','52.26','11' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-02-02','52.26','12' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-02-16','52.26','13' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-02-23','52.26','14' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-03-02','52.26','15' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-03-09','52.26','16' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-03-16','52.26','17' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-03-30','52.26','18' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-04-03','52.26','19' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-04-10','52.26','20' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-04-13','52.26','21' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-04-27','52.26','22' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-05-01','52.26','23' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-05-04','52.26','24' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-05-09','52.26','25' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-05-16','52.26','26' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-05-22','52.26','27' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-05-25','52.26','28' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-05-31','52.26','29' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-06-08','52.26','30' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-06-12','52.26','31' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-06-15','52.26','32' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-06-22','52.26','33' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-06-24','52.26','34' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-06-29','52.26','35' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-07-05','52.26','36' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-07-13','52.26','37' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-07-18','52.26','38' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-07-24','52.26','39' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-07-31','52.26','40' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-08-10','52.26','41' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-08-15','52.26','42' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-08-22','52.26','43' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-08-24','52.26','44' UNION ALL
SELECT '272','Dec  5 2016 12:00AM','Sep  5 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-09-05','52.26','45' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-09-08','54.36','46' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-09-12','54.36','47' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-09-18','54.36','48' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-09-19','54.36','49' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-09-25','54.36','50' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-09-28','54.36','51' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-10-03','54.36','52' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-10-17','54.36','53' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-10-19','54.36','54' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-10-26','54.36','55' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-10-31','54.36','56' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-11-06','54.36','57' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-11-07','54.36','58' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-11-09','54.36','59' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-11-14','54.36','60' UNION ALL
SELECT '272','Sep  8 2017 12:00AM','Nov 20 2017 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-11-20','54.36','61' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-12-12','53.33','62' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-12-19','53.33','63' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-12-23','53.33','64' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2017-12-27','53.33','65' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-01-12','53.33','66' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-01-18','53.33','67' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-01-23','53.33','68' UNION ALL
SELECT '272','Dec 12 2017 12:00AM','Feb  5 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-02-05','53.33','69' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-02-13','37.25','70' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-02-19','37.25','71' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-02-22','37.25','72' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-03-01','37.25','73' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-03-08','37.25','74' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-03-15','37.25','75' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-03-20','37.25','76' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-03-26','37.25','77' UNION ALL
SELECT '466','Feb  2 2017 12:00AM','Feb 23 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-02-02','4.06','1' UNION ALL
SELECT '466','Feb  2 2017 12:00AM','Feb 23 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-02-06','4.06','2' UNION ALL
SELECT '466','Feb  2 2017 12:00AM','Feb 23 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-02-09','4.06','3' UNION ALL
SELECT '466','Feb  2 2017 12:00AM','Feb 23 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-02-20','4.06','4' UNION ALL
SELECT '466','Feb  2 2017 12:00AM','Feb 23 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-02-23','4.06','5' UNION ALL
SELECT '466','Feb 28 2017 12:00AM','Mar 21 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-02-28','9.37','6' UNION ALL
SELECT '466','Feb 28 2017 12:00AM','Mar 21 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-03-06','9.37','7' UNION ALL
SELECT '466','Feb 28 2017 12:00AM','Mar 21 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-03-14','9.37','8' UNION ALL
SELECT '466','Feb 28 2017 12:00AM','Mar 21 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-03-16','9.37','9' UNION ALL
SELECT '466','Feb 28 2017 12:00AM','Mar 21 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-03-21','9.37','10' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-03-27','4.06','11' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-04-03','4.06','12' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-04-10','4.06','13' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-04-11','4.06','14' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-04-15','4.06','15' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-04-24','4.06','16' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-04-27','4.06','17' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-05-02','4.06','18' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-05-05','4.06','19' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-05-16','4.06','20' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-05-18','4.06','21' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-05-26','4.06','22' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-05-27','4.06','23' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-06-02','4.06','24' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-06-06','4.06','25' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-06-08','4.06','26' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-06-13','4.06','27' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-06-21','4.06','28' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-07-07','4.06','29' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-07-10','4.06','30' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-07-14','4.06','31' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-07-19','4.06','32' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-07-21','4.06','33' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-03','4.06','34' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-05','4.06','35' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-10','4.06','36' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-15','4.06','37' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-19','4.06','38' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-25','4.06','39' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-28','4.06','40' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-08-29','4.06','41' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-01','4.06','42' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-05','4.06','43' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-08','4.06','44' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-12','4.06','45' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-18','4.06','46' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-19','4.06','47' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-25','4.06','48' UNION ALL
SELECT '466','Mar 27 2017 12:00AM','Sep 28 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-09-28','4.06','49' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-02','3.82','50' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-03','3.82','51' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-05','3.82','52' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-10','3.82','53' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-16','3.82','54' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-19','3.82','55' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-23','3.82','56' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-24','3.82','57' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-10-31','3.82','58' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-11-07','3.82','59' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-11-08','3.82','60' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-11-09','3.82','61' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-11-10','3.82','62' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-11-11','3.82','63' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-11-24','3.82','64' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-11-29','3.82','65' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-12-04','3.82','66' UNION ALL
SELECT '466','Oct  2 2017 12:00AM','Dec  5 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-12-05','3.82','67' UNION ALL
SELECT '466','Dec 19 2017 12:00AM','Dec 19 2017 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-12-19','9.37','68' UNION ALL
SELECT '466','Dec 25 2017 12:00AM','Mar 15 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-12-25','3.76','69' UNION ALL
SELECT '466','Dec 25 2017 12:00AM','Mar 15 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-12-28','3.76','70' UNION ALL
SELECT '466','Dec 25 2017 12:00AM','Mar 15 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2017-12-30','3.76','71' UNION ALL
SELECT '466','Dec 25 2017 12:00AM','Mar 15 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-01-08','3.76','72' UNION ALL
SELECT '466','Dec 25 2017 12:00AM','Mar 15 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-01-27','3.76','73' UNION ALL
SELECT '466','Dec 25 2017 12:00AM','Mar 15 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-03-15','3.76','74' UNION ALL
SELECT '466','Mar 20 2018 12:00AM','Mar 20 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-03-20','130.89','75' UNION ALL
SELECT '466','Apr 13 2018 12:00AM','Apr 13 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-04-13','130.82','76' UNION ALL
SELECT '466','Apr 26 2018 12:00AM','Apr 26 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-04-26','3.76','77' UNION ALL
SELECT '466','May  1 2018 12:00AM','May  1 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-05-01','7.92','78' UNION ALL
SELECT '466','May  5 2018 12:00AM','Jun 27 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-05-05','3.76','79' UNION ALL
SELECT '466','May  5 2018 12:00AM','Jun 27 2018 12:00AM','0000132','0001029','ETOPOSIDE 20 MG/ML 5MLVIAL','2018-06-07','3.76','80' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','ADM SET, BLOOD Y TYPE','2017-01-30','125.76','1' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','ADM SET, BLOOD Y TYPE','2017-05-01','125.76','2' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','ADM SET, BLOOD Y TYPE','2017-06-22','125.76','3' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','ADM SET, BLOOD Y TYPE','2017-08-18','125.76','4' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','ADM SET, BLOOD Y TYPE','2017-11-10','125.76','5' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','SET ADM 79IN 170UM 10 GTT 39.4','2018-01-12','125.76','6' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','SET ADM 79IN 170UM 10 GTT 39.4','2018-03-13','125.76','7' UNION ALL
SELECT '3','Jan 30 2017 12:00AM','May 22 2018 12:00AM','0000037','0000015','SET ADM 79IN 170UM 10 GTT 39.4','2018-05-22','125.76','8' UNION ALL
SELECT '3','Aug  8 2018 12:00AM','Aug  8 2018 12:00AM','0000037','0000015','SET ADM 79IN 170UM 10 GTT 39.4','2018-08-08','129.12','9' UNION ALL
SELECT '11','Dec 23 2016 12:00AM','Jun 12 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2016-12-23','433.94','1' UNION ALL
SELECT '11','Dec 23 2016 12:00AM','Jun 12 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-01-20','433.94','2' UNION ALL
SELECT '11','Dec 23 2016 12:00AM','Jun 12 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-02-22','433.94','3' UNION ALL
SELECT '11','Dec 23 2016 12:00AM','Jun 12 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-03-23','433.94','4' UNION ALL
SELECT '11','Dec 23 2016 12:00AM','Jun 12 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-04-26','433.94','5' UNION ALL
SELECT '11','Dec 23 2016 12:00AM','Jun 12 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-05-24','433.94','6' UNION ALL
SELECT '11','Dec 23 2016 12:00AM','Jun 12 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-06-12','433.94','7' UNION ALL
SELECT '11','Jul 13 2017 12:00AM','Aug 17 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-07-13','440.00','8' UNION ALL
SELECT '11','Jul 13 2017 12:00AM','Aug 17 2017 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-08-17','440.00','9' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-10-11','412.00','10' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-11-16','412.00','11' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET, CATHETERIZATION RADIAL','2017-12-15','412.00','12' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-01-21','412.00','13' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-02-20','412.00','14' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-03-30','412.00','15' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-04-20','412.00','16' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-05-23','412.00','17' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-06-26','412.00','18' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-07-18','412.00','19' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-08-15','412.00','20' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-09-17','412.00','21' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-10-11','412.00','22' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-11-15','412.00','23' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2018-12-11','412.00','24' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2019-01-21','412.00','25' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2019-03-05','412.00','26' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2019-04-12','412.00','27' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2019-05-14','412.00','28' UNION ALL
SELECT '11','Oct 11 2017 12:00AM','Jun 26 2019 12:00AM','0000055','0002466','SET CATH 1.75IN 20GA 22GA .018','2019-06-26','412.00','29' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-01-08','59.38','1' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-04-10','59.38','2' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-04-30','59.38','3' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-05-16','59.38','4' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-05-27','59.38','5' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-09-08','59.38','6' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-10-20','59.38','7' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-11-09','59.38','8' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR AESCULAP CNTNR','2017-11-13','59.38','9' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2018-02-17','59.38','10' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2018-05-06','59.38','11' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2018-07-06','59.38','12' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2018-09-07','59.38','13' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2018-09-27','59.38','14' UNION ALL
SELECT '42','Jan  8 2017 12:00AM','Nov 24 2018 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2018-11-24','59.38','15' UNION ALL
SELECT '42','Feb 11 2019 12:00AM','Aug 26 2019 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2019-02-11','57.82','16' UNION ALL
SELECT '42','Feb 11 2019 12:00AM','Aug 26 2019 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2019-04-15','57.82','17' UNION ALL
SELECT '42','Feb 11 2019 12:00AM','Aug 26 2019 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2019-06-03','57.82','18' UNION ALL
SELECT '42','Feb 11 2019 12:00AM','Aug 26 2019 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2019-07-12','57.82','19' UNION ALL
SELECT '42','Feb 11 2019 12:00AM','Aug 26 2019 12:00AM','0000062','0053330','FLTR STRL CNTNR 7.5IN STRCTNR','2019-08-26','57.82','20' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-04-02','37.25','78' UNION ALL
SELECT '272','Feb 13 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-04-03','37.25','79' UNION ALL
SELECT '272','May 14 2018 12:00AM','Jun 26 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-05-14','37.23','80' UNION ALL
SELECT '272','May 14 2018 12:00AM','Jun 26 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-05-17','37.23','81' UNION ALL
SELECT '272','May 14 2018 12:00AM','Jun 26 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-05-24','37.23','82' UNION ALL
SELECT '272','May 14 2018 12:00AM','Jun 26 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-06-05','37.23','83' UNION ALL
SELECT '272','May 14 2018 12:00AM','Jun 26 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-06-21','37.23','84' UNION ALL
SELECT '272','May 14 2018 12:00AM','Jun 26 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-06-26','37.23','85' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-07-06','50.29','86' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-07-12','50.29','87' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-07-19','50.29','88' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-07-23','50.29','89' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-07-30','50.29','90' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-08-02','50.29','91' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-08-07','50.29','92' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-08-09','50.29','93' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-08-14','50.29','94' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-08-20','50.29','95' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-08-27','50.29','96' UNION ALL
SELECT '272','Jul  6 2018 12:00AM','Aug 28 2018 12:00AM','0000132','0000514','CHLORDIAZEPOXIDE HCL 25 MG CAP','2018-08-28','50.29','97' UNION ALL
SELECT '275','Mar 27 2017 12:00AM','May 15 2017 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2017-03-27','47.63','1' UNION ALL
SELECT '275','Mar 27 2017 12:00AM','May 15 2017 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2017-05-15','47.63','2' UNION ALL
SELECT '275','Jul 20 2017 12:00AM','Jul 25 2017 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2017-07-20','45.01','3' UNION ALL
SELECT '275','Jul 20 2017 12:00AM','Jul 25 2017 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2017-07-21','45.01','4' UNION ALL
SELECT '275','Jul 20 2017 12:00AM','Jul 25 2017 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2017-07-25','45.01','5' UNION ALL
SELECT '275','Jun 26 2018 12:00AM','Jun 26 2018 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2018-06-26','48.27','6' UNION ALL
SELECT '275','Jul 12 2018 12:00AM','Jul 12 2018 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2018-07-12','51.08','7' UNION ALL
SELECT '275','Jul 23 2018 12:00AM','Jul 23 2018 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2018-07-23','40.72','8' UNION ALL
SELECT '275','Jul 23 2018 12:00AM','Jul 23 2018 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2018-07-23','51.08','9' UNION ALL
SELECT '275','Jul 27 2018 12:00AM','Aug 23 2018 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2018-07-27','40.72','10' UNION ALL
SELECT '275','Jul 27 2018 12:00AM','Aug 23 2018 12:00AM','0000132','0000519','CHLOROTHIAZIDE SOD 500 MG VIAL','2018-08-23','40.72','11' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2016-12-16','1.81','1' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2016-12-23','1.81','2' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-01-04','1.81','3' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-01-06','1.81','4' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-01-25','1.81','5' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-02-08','1.81','6' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-03-10','1.81','7' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-03-24','1.81','8' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-04-14','1.81','9' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-04-17','1.81','10' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-05-12','1.81','11' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-05-26','1.81','12' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-06-05','1.81','13' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-06-21','1.81','14' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-07-14','1.81','15' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-07-24','1.81','16' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-08-02','1.81','17' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-08-05','1.81','18' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-08-18','1.81','19' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-09-01','1.81','20' UNION ALL
SELECT '277','Dec 16 2016 12:00AM','Sep 20 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-09-20','1.81','21' UNION ALL
SELECT '277','Nov 22 2017 12:00AM','Nov 22 2017 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-11-22','1.94','22' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2017-12-29','1.78','23' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-01-12','1.78','24' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-02-02','1.78','25' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-02-23','1.78','26' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-02-28','1.78','27' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-03-16','1.78','28' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-03-28','1.78','29' UNION ALL
SELECT '277','Dec 29 2017 12:00AM','Apr 13 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-04-13','1.78','30' UNION ALL
SELECT '277','Apr 25 2018 12:00AM','Apr 25 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-04-25','1.91','31' UNION ALL
SELECT '277','May 18 2018 12:00AM','Jun  1 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-05-18','1.78','32' UNION ALL
SELECT '277','May 18 2018 12:00AM','Jun  1 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-06-01','1.78','33' UNION ALL
SELECT '277','Jun 13 2018 12:00AM','Aug 27 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-06-13','1.91','34' UNION ALL
SELECT '277','Jun 13 2018 12:00AM','Aug 27 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-06-27','1.91','35' UNION ALL
SELECT '277','Jun 13 2018 12:00AM','Aug 27 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-07-07','1.91','36' UNION ALL
SELECT '277','Jun 13 2018 12:00AM','Aug 27 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-07-20','1.91','37' UNION ALL
SELECT '277','Jun 13 2018 12:00AM','Aug 27 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-07-27','1.91','38' UNION ALL
SELECT '277','Jun 13 2018 12:00AM','Aug 27 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-08-08','1.91','39' UNION ALL
SELECT '277','Jun 13 2018 12:00AM','Aug 27 2018 12:00AM','0000132','0000524','CHLORHEXIDINE GLUC 120 ML BOT','2018-08-27','1.91','40' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','CHROMAGEN FE FUM,VITC,VITB12','2016-12-08','82.02','1' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','CHROMAGEN FE FUM,VITC,VITB12','2016-12-13','82.02','2' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','CHROMAGEN FE FUM,VITC,VITB12','2017-02-17','82.02','3' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','CHROMAGEN FE FUM,VITC,VITB12','2017-03-30','82.02','4' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','CHROMAGEN FE FUM,VITC,VITB12','2017-04-11','82.02','5' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','CHROMAGEN FE FUM,VITC,VITB12','2017-05-09','82.02','6' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2017-06-27','82.02','7' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2017-07-25','82.02','8' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2017-08-30','82.02','9' UNION ALL
SELECT '280','Dec  8 2016 12:00AM','Oct 17 2017 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2017-10-17','82.02','10' UNION ALL
SELECT '280','Dec  4 2017 12:00AM','Jan 11 2018 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2017-12-04','80.48','11' UNION ALL
SELECT '280','Dec  4 2017 12:00AM','Jan 11 2018 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2018-01-11','80.48','12' UNION ALL
SELECT '280','Feb 20 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2018-02-20','67.73','13' UNION ALL
SELECT '280','Feb 20 2018 12:00AM','Apr  3 2018 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2018-04-03','67.73','14' UNION ALL
SELECT '280','May 29 2018 12:00AM','May 29 2018 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2018-05-29','67.69','15' UNION ALL
SELECT '280','Jul  3 2018 12:00AM','Aug 21 2018 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2018-07-03','81.32','16' UNION ALL
SELECT '280','Jul  3 2018 12:00AM','Aug 21 2018 12:00AM','0000132','0000536','IRON AG/C/B12/CA/SUCC TAB','2018-08-21','81.32','17' UNION ALL
SELECT '281','Dec 16 2016 12:00AM','May  3 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2016-12-16','27.72','1' UNION ALL
SELECT '281','Dec 16 2016 12:00AM','May  3 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-01-24','27.72','2' UNION ALL
SELECT '281','Dec 16 2016 12:00AM','May  3 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-02-23','27.72','3' UNION ALL
SELECT '281','Dec 16 2016 12:00AM','May  3 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-04-07','27.72','4' UNION ALL
SELECT '281','Dec 16 2016 12:00AM','May  3 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-05-03','27.72','5' UNION ALL
SELECT '281','Jun 23 2017 12:00AM','Jul 19 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-06-23','51.59','6' UNION ALL
SELECT '281','Jun 23 2017 12:00AM','Jul 19 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-07-05','51.59','7' UNION ALL
SELECT '281','Jun 23 2017 12:00AM','Jul 19 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-07-19','51.59','8' UNION ALL
SELECT '281','Sep  8 2017 12:00AM','Oct  6 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-09-08','27.72','9' UNION ALL
SELECT '281','Sep  8 2017 12:00AM','Oct  6 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-10-06','27.72','10' UNION ALL
SELECT '281','Nov 22 2017 12:00AM','Dec 12 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-11-22','46.39','11' UNION ALL
SELECT '281','Nov 22 2017 12:00AM','Dec 12 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-12-04','46.39','12' UNION ALL
SELECT '281','Nov 22 2017 12:00AM','Dec 12 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-12-05','46.39','13' UNION ALL
SELECT '281','Nov 22 2017 12:00AM','Dec 12 2017 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2017-12-12','46.39','14' UNION ALL
SELECT '281','Jan 10 2018 12:00AM','Jan 26 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-01-10','45.69','15' UNION ALL
SELECT '281','Jan 10 2018 12:00AM','Jan 26 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-01-26','45.69','16' UNION ALL
SELECT '281','Feb 12 2018 12:00AM','Feb 20 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-02-12','51.71','17' UNION ALL
SELECT '281','Feb 12 2018 12:00AM','Feb 20 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-02-20','51.71','18' UNION ALL
SELECT '281','Mar 15 2018 12:00AM','Mar 15 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-03-15','45.69','19' UNION ALL
SELECT '281','Apr 24 2018 12:00AM','Jun 19 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-04-24','44.11','20' UNION ALL
SELECT '281','Apr 24 2018 12:00AM','Jun 19 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-06-19','44.11','21' UNION ALL
SELECT '281','Aug  2 2018 12:00AM','Aug 30 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-08-02','51.71','22' UNION ALL
SELECT '281','Aug  2 2018 12:00AM','Aug 30 2018 12:00AM','0000132','0000547','CIPROFLOXACIN 200MG IN D5W 200','2018-08-30','51.71','23' UNION ALL
SELECT '283','Dec  9 2016 12:00AM','Dec 19 2016 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2016-12-09','40.51','1' UNION ALL
SELECT '283','Dec  9 2016 12:00AM','Dec 19 2016 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2016-12-19','40.51','2' UNION ALL
SELECT '283','Dec 23 2016 12:00AM','Jan  5 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2016-12-23','40.50','3' UNION ALL
SELECT '283','Dec 23 2016 12:00AM','Jan  5 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2016-12-29','40.50','4' UNION ALL
SELECT '283','Dec 23 2016 12:00AM','Jan  5 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-02','40.50','5' UNION ALL
SELECT '283','Dec 23 2016 12:00AM','Jan  5 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-05','40.50','6' UNION ALL
SELECT '283','Jan 11 2017 12:00AM','Jan 16 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-11','63.58','7' UNION ALL
SELECT '283','Jan 11 2017 12:00AM','Jan 16 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-16','63.58','8' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-18','40.50','9' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-20','40.50','10' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-23','40.50','11' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-27','40.50','12' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-01-31','40.50','13' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-02-03','40.50','14' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-02-10','40.50','15' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-02-15','40.50','16' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-02-27','40.50','17' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-01','40.50','18' UNION ALL
SELECT '283','Jan 18 2017 12:00AM','Mar  7 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-07','40.50','19' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-14','46.84','20' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-18','46.84','21' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-21','46.84','22' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-23','46.84','23' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-24','46.84','24' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-27','46.84','25' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-03-31','46.84','26' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-04-06','46.84','27' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-04-13','46.84','28' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-04-17','46.84','29' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-04-21','46.84','30' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-05-02','46.84','31' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-05-04','46.84','32' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-05-05','46.84','33' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-05-15','46.84','34' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-05-24','46.84','35' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-05-25','46.84','36' UNION ALL
SELECT '283','Mar 14 2017 12:00AM','Jun  2 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-06-02','46.84','37' UNION ALL
SELECT '283','Jun  9 2017 12:00AM','Jun  9 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-06-09','52.04','38' UNION ALL
SELECT '283','Jun 15 2017 12:00AM','Jun 16 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-06-15','46.84','39' UNION ALL
SELECT '283','Jun 15 2017 12:00AM','Jun 16 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-06-16','46.84','40' UNION ALL
SELECT '283','Jun 29 2017 12:00AM','Jul  3 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-06-29','52.04','41' UNION ALL
SELECT '283','Jun 29 2017 12:00AM','Jul  3 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-07-03','52.04','42' UNION ALL
SELECT '283','Jul 14 2017 12:00AM','Dec  8 2017 12:00AM','0000132','0000551','CIPROFLOXACIN 400MG IN D5W 400','2017-07-14','46.84','43' UNION ALL
SELECT '72','Jan  9 2017 12:00AM','Apr 11 2017 12:00AM','0000130','0056019','OXYGEN GRAB N GO','2017-01-09','2.89','1' UNION ALL
SELECT '72','Jan  9 2017 12:00AM','Apr 11 2017 12:00AM','0000130','0056019','OXYGEN GRAB N GO','2017-04-11','2.89','2' UNION ALL
SELECT '72','Jul 23 2018 12:00AM','Oct 10 2018 12:00AM','0000130','0056019','CYL MED GAS GRN/WHT GRNGO','2018-07-23','2.96','3' UNION ALL
SELECT '72','Jul 23 2018 12:00AM','Oct 10 2018 12:00AM','0000130','0056019','CYL MED GAS GRN/WHT GRNGO','2018-08-10','2.96','4' UNION ALL
SELECT '72','Jul 23 2018 12:00AM','Oct 10 2018 12:00AM','0000130','0056019','CYL MED GAS GRN/WHT GRNGO','2018-10-10','2.96','5' UNION ALL
SELECT '75','Dec 16 2016 12:00AM','Dec 16 2016 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2016-12-16','799.80','1' UNION ALL
SELECT '75','Dec 16 2016 12:00AM','Dec 16 2016 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2016-12-16','1238.98','2' UNION ALL
SELECT '75','Dec 16 2016 12:00AM','Dec 16 2016 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2016-12-16','2747.98','3' UNION ALL
SELECT '75','Jan  9 2017 12:00AM','Jan  9 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-01-09','31.16','4' UNION ALL
SELECT '75','Jan  9 2017 12:00AM','Jan  9 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-01-09','882.95','5' UNION ALL
SELECT '75','Jan  9 2017 12:00AM','Jan  9 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-01-09','2602.61','6' UNION ALL
SELECT '75','Feb  2 2017 12:00AM','Feb  2 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-02-02','31.16','7' UNION ALL
SELECT '75','Feb  2 2017 12:00AM','Feb  2 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-02-02','882.95','8' UNION ALL
SELECT '75','Feb  2 2017 12:00AM','Feb  2 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-02-02','2203.75','9' UNION ALL
SELECT '75','Mar 15 2017 12:00AM','Mar 15 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-03-15','29.49','10' UNION ALL
SELECT '75','Mar 15 2017 12:00AM','Mar 15 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-03-15','882.95','11' UNION ALL
SELECT '75','Mar 15 2017 12:00AM','Mar 15 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-03-15','1898.48','12' UNION ALL
SELECT '75','Mar 15 2017 12:00AM','Mar 15 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-03-15','2218.45','13' UNION ALL
SELECT '75','Apr 11 2017 12:00AM','Apr 11 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-04-11','31.16','14' UNION ALL
SELECT '75','Apr 11 2017 12:00AM','Apr 11 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-04-11','882.95','15' UNION ALL
SELECT '75','Apr 11 2017 12:00AM','Apr 11 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-04-11','1834.29','16' UNION ALL
SELECT '75','Apr 11 2017 12:00AM','Apr 11 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-04-11','2067.04','17' UNION ALL
SELECT '75','May 10 2017 12:00AM','May 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-05-10','30.60','18' UNION ALL
SELECT '75','May 10 2017 12:00AM','May 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-05-10','450.00','19' UNION ALL
SELECT '75','May 10 2017 12:00AM','May 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-05-10','739.14','20' UNION ALL
SELECT '75','May 10 2017 12:00AM','May 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-05-10','882.95','21' UNION ALL
SELECT '75','May 10 2017 12:00AM','May 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-05-10','1527.55','22' UNION ALL
SELECT '75','May 10 2017 12:00AM','May 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-05-10','2464.58','23' UNION ALL
SELECT '75','Jun 14 2017 12:00AM','Jun 14 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-06-14','31.16','24' UNION ALL
SELECT '75','Jun 14 2017 12:00AM','Jun 14 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-06-14','882.95','25' UNION ALL
SELECT '75','Jun 14 2017 12:00AM','Jun 14 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-06-14','979.73','26' UNION ALL
SELECT '75','Jun 14 2017 12:00AM','Jun 14 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-06-14','2376.72','27' UNION ALL
SELECT '75','Jul  7 2017 12:00AM','Jul  7 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-07-07','30.60','28' UNION ALL
SELECT '75','Jul  7 2017 12:00AM','Jul  7 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-07-07','836.65','29' UNION ALL
SELECT '75','Jul  7 2017 12:00AM','Jul  7 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-07-07','882.95','30' UNION ALL
SELECT '75','Jul  7 2017 12:00AM','Jul  7 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-07-07','1596.15','31' UNION ALL
SELECT '75','Aug  3 2017 12:00AM','Aug  3 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-08-03','31.16','32' UNION ALL
SELECT '75','Aug  3 2017 12:00AM','Aug  3 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-08-03','882.95','33' UNION ALL
SELECT '75','Aug  3 2017 12:00AM','Aug  3 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-08-03','2086.15','34' UNION ALL
SELECT '75','Aug  3 2017 12:00AM','Aug  3 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-08-03','2389.95','35' UNION ALL
SELECT '75','Sep 11 2017 12:00AM','Sep 11 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-09-11','31.16','36' UNION ALL
SELECT '75','Sep 11 2017 12:00AM','Sep 11 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-09-11','882.95','37' UNION ALL
SELECT '75','Sep 11 2017 12:00AM','Sep 11 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-09-11','1990.11','38' UNION ALL
SELECT '75','Oct 10 2017 12:00AM','Oct 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-10-10','1486.39','39' UNION ALL
SELECT '75','Oct 10 2017 12:00AM','Oct 10 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-10-10','1602.52','40' UNION ALL
SELECT '75','Nov  9 2017 12:00AM','Nov  9 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-11-09','31.16','41' UNION ALL
SELECT '75','Nov  9 2017 12:00AM','Nov  9 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-11-09','882.95','42' UNION ALL
SELECT '75','Nov  9 2017 12:00AM','Nov  9 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-11-09','1225.71','43' UNION ALL
SELECT '75','Nov  9 2017 12:00AM','Nov  9 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-11-09','2025.88','44' UNION ALL
SELECT '75','Dec 13 2017 12:00AM','Dec 13 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-12-13','31.40','45' UNION ALL
SELECT '75','Dec 13 2017 12:00AM','Dec 13 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-12-13','882.95','46' UNION ALL
SELECT '75','Dec 13 2017 12:00AM','Dec 13 2017 12:00AM','0000130','0056023','BULK LIQUID OXYGEN','2017-12-13','1273.24','47'

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

You've already been supplied with some queries. Have you tried them?

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 9

When I run Scott's code, I get this error msg?

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'RowCount'.

Msg 156, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'RowCount'.

Jonathan's code gives me this error:

--Msg 530, Level 16, State 1, Line 34

--The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Frederico's code ran and I am checking the results now.

thanks! all for your help. I provided the data as a few people asked for data.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 10

GrassHopper wrote:

When I run Scott's code, I get this error msg?
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'RowCount'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'RowCount'.
Jonathan's code gives me this error:
--Msg 530, Level 16, State 1, Line 34
--The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Frederico's code ran and I am checking the results now.
thanks! all for your help. I provided the data as a few people asked for data.

To stop the error in Scott's code put RowCount in square brackets:

SELECT @MaxRowNbr = MAX([RowCount])
  FROM (SELECT GroupNbr, COUNT(*) AS [RowCount]
         FROM #mytable 
        GROUP BY GroupNbr) AS derived

Add this to the end of the SQL to stop the max recursion error:

OPTION (MAXRECURSION 0)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 11

GrassHopper wrote:

When I run Scott's code, I get this error msg?
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'RowCount'.

Reserved word, just use a different name. I try to write my code in the general pattern of the code given, even though I hate camel-case-style variables.

SELECT @MaxRowNbr = MAX(row_count)
FROM (
    SELECT GroupNbr, COUNT(*) AS row_count
    FROM tmpDeal01 
    GROUP BY GroupNbr
) AS derived

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".