Query on comparing products

mjv · September 1, 2018, 1:34pm

Team,

Need your help for my analysis.

Attached scenario is the price comparison between three products Apple, Orange and Mango are available in my product table date wise

There is a price difference between previous day and the current day and the same is captured as Plus and Minus for each product day wise

Now the requirement is given below,

Need to compare each day's "Sign" of every product with the 2 other product and find out which pattern suits my requirement

Eg., Apple --> Orange

Current date's "Sign" needs to be compared with the previous date's "Sign"

Orange - 03-Aug-18 - "Minus", has to be compared with Apple 02-Aug-18 - "Minus"

As per my requirement orange is matched with apple, 3rd aug Minus sign is matched with 2nd Aug Minus sign,4rd aug Plus sign is in 3nd Aug Plus sign,5th aug Plus sign is in 4th Aug Plus sign.

Like wise need to compare each product with the other two
Apple --> Orange Signs
Apple --> Mango Signs
Orange --> Apple Signs
Orange --> Mango Signs
Mango --> Apple signs
Mango --> Orange signs

I wanted the above example to be written in a SQL Query where the query will output my requirement.

thanks
Jay Product%20Data

harishgg1 · September 1, 2018, 2:19pm

hi

please provide data script

like this

data script

USE harish_sample 

go 

DROP TABLE har_temp 

go 

CREATE TABLE har_temp 
  ( 
     product        VARCHAR(100), 
     price          INT, 
     date_123       DATE, 
     difference_123 INT NULL, 
     sign_123       VARCHAR(100) NULL 
  ) 

go 

INSERT INTO har_temp 
SELECT 'Apple', 
       10, 
       '01-AUG-2018', 
       NULL, 
       NULL 

go

bitsmed · September 1, 2018, 5:20pm

Please show us the output you'd like to see

harishgg1 · September 2, 2018, 6:54am

Hi

I have come up with the create data script

Create Data Script

USE harish_sample 

go 

DROP TABLE har_temp 

go 

CREATE TABLE har_temp 
  ( 
     product        VARCHAR(100) NULL, 
     price          DECIMAL(10, 1) NULL, 
     date_123       DATE NULL, 
     difference_123 DECIMAL(10, 1) NULL, 
     sign_123       VARCHAR(100) NULL 
  ) 

go 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Apple', 
       10, 
       '01-Aug-18', 
       NULL, 
       NULL 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Apple', 
       9, 
       '02-Aug-18', 
       -1, 
       '	Minus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Apple', 
       10, 
       '03-Aug-18', 
       1, 
       '	Plus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Apple', 
       8.5, 
       '04-Aug-18', 
       -1.5, 
       '	Minus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Apple', 
       10, 
       '05-Aug-18', 
       1.5, 
       '	Plus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Orange', 
       4, 
       '01-Aug-18', 
       NULL, 
       NULL 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Orange', 
       4.5, 
       '02-Aug-18', 
       0.5, 
       '	Plus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Orange', 
       3.5, 
       '03-Aug-18', 
       -1, 
       '	Minus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Orange', 
       6, 
       '04-Aug-18', 
       2.5, 
       '	Plus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Orange', 
       5.2, 
       '05-Aug-18', 
       -0.8, 
       '	Minus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Mango', 
       6.5, 
       '01-Aug-18', 
       NULL, 
       NULL 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Mango', 
       7, 
       '02-Aug-18', 
       0.5, 
       '	Plus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Mango', 
       8.4, 
       '03-Aug-18', 
       1.4, 
       '	Plus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Mango', 
       6.5, 
       '04-Aug-18', 
       -1.9, 
       '	Minus' 

INSERT INTO har_temp 
            (product, 
             price, 
             date_123, 
             difference_123, 
             sign_123) 
SELECT 'Mango', 
       5.5, 
       '05-Aug-18', 
       -1, 
       '	Minus'

harishgg1 · September 2, 2018, 7:22am

hi

I have come up with SQL Query

SQL Script

SELECT a.*, 
       b.* 
FROM   (SELECT * 
        FROM   har_temp) a 
       JOIN (SELECT * 
             FROM   har_temp) b 
         ON a.sign_123 = b.sign_123 
            AND Dateadd(day, -1, a.date_123) = b.date_123

harishgg1 · September 2, 2018, 7:24am

Hi Using my SQL Query

I got the following OUTPUT
is this what you want ???

mjv · September 2, 2018, 8:54am

harishgg1, Output

Many thanks for your quick response. The output that you have put is very near my need. But can you fine tune the script to get an output as i attached. I have excel file with details but I cannot upload it , so i am attaching a screen print.

Explanation : Base product is the product that needs to be used for comparison.
Matched Product1 is the product that matched with base product. In our case Apple and Orange matched so there are 5 rows and matched rows are 3. But when you see Orange / Mango and Mango / Apple the count is less than 3.

I am expecting an output like this, I really appreciate your help.

Jay

harishgg1 · September 2, 2018, 9:51am

Hi

Here is my query

New Query

SELECT a.product        AS baseproduct, 
       b.product        AS matchedproduct, 
       a.baseproductcount, 
       Count(b.product) AS MatchedCount 
FROM   (SELECT aa.product, 
               aa.price, 
               aa.date_123, 
               aa.difference_123, 
               aa.sign_123, 
               bb.baseproductcount 
        FROM   har_temp aa 
               JOIN (SELECT product, 
                            Count(*) AS baseproductcount 
                     FROM   har_temp 
                     GROUP  BY product) bb 
                 ON aa.product = bb.product) a 
       JOIN (SELECT * 
             FROM   har_temp) b 
         ON a.sign_123 = b.sign_123 
            AND Dateadd(day, -1, a.date_123) = b.date_123 
GROUP  BY a.product, 
          b.product, 
          a.baseproductcount 
ORDER  BY a.product, 
          Count(b.product) DESC

harishgg1 · September 2, 2018, 9:53am

Hi

Please find my OUTPUT

is this OK ? only difference i see between your and mine output
is
3 Mango Mango 5 2

bitsmed · September 2, 2018, 10:06am

Alternative

create table #fruit(
   product varchar(10)
  ,price decimal(10,2)
  ,[date] date
  ,[difference] decimal(10,2)
  ,[sign] varchar(10)
);

insert into #fruit values
   ('Apple',10,'2018-08-01',null,null)
  ,('Apple',9,'2018-08-02',-1,'Minus')
  ,('Apple',10,'2018-08-03',1,'Plus')
  ,('Apple',8.5,'2018-08-04',-1.5,'Minus')
  ,('Apple',10,'2018-08-05',1.5,'Plus')
  ,('Orange',4,'2018-08-01',null,null)
  ,('Orange',4.5,'2018-08-02',0.5,'Plus')
  ,('Orange',3.5,'2018-08-03',-1,'Minus')
  ,('Orange',6,'2018-08-04',2.5,'Plus')
  ,('Orange',5.2,'2018-08-05',-0.8,'Minus')
  ,('Mango',6.5,'2018-08-01',null,null)
  ,('Mango',7,'2018-08-02',0.5,'Plus')
  ,('Mango',8.4,'2018-08-03',1.4,'Plus')
  ,('Mango',6.5,'2018-08-04',-1.9,'Minus')
  ,('Mango',5.5,'2018-08-05',-1,'Minus')
;

select a.product
      ,b.product
      ,avg(a.[n]) as [base total count]
      ,count(*) as [matched count]
  from (select product
              ,[date]
              ,[sign]
              ,count(*) over(partition by product) as n
          from #fruit
       ) as a
       inner join #fruit as b
               on b.product!=a.product
              and b.[date]=dateadd(day,1,a.[date])
              and b.[sign]=a.[sign]
 group by a.product
         ,b.product
 order by a.product
         ,[matched count] desc
;

drop table #fruit;

mjv · September 3, 2018, 10:54am

Yes it works. Many thanks.

The example I shared having dates in sequence order, I may not have date in sequence order in some cases, example for Sat and Sunday there will not be any rows. For below data the output does not fetch our earlier results. I see that the query is using dateadd is added with 1 , is there a way to alter the query to get output even when the date is not in sequence and with sequence also.

insert into fruit values
('Apple',10,'2018-08-01',null,null)
,('Apple',9,'2018-08-02',-1,'Minus')
,('Apple',10,'2018-08-03',1,'Plus')
,('Apple',8.5,'2018-08-07',-1.5,'Minus')
,('Apple',10,'2018-08-08',1.5,'Plus')
,('Orange',4,'2018-08-01',null,null)
,('Orange',4.5,'2018-08-02',0.5,'Plus')
,('Orange',3.5,'2018-08-03',-1,'Minus')
,('Orange',6,'2018-08-07',2.5,'Plus')
,('Orange',5.2,'2018-08-08',-0.8,'Minus')
,('Mango',6.5,'2018-08-01',null,null)
,('Mango',7,'2018-08-02',0.5,'Plus')
,('Mango',8.4,'2018-08-03',1.4,'Plus')
,('Mango',6.5,'2018-08-07',-1.9,'Minus')
,('Mango',5.5,'2018-08-05',-1,'Minus')
;

Great job well done.

harishgg1 · September 3, 2018, 11:53am

When the date is not in sequence .. we should create SAMPLE data first where DATE is not in sequence

Please provide data set where date not in sequence

I am thinking its possible

Thanks

mjv · September 3, 2018, 12:23pm

Data with not sequence is in my reply post, its above, any how I will post again. Its 1st 2nd 3rd and jumped to 7th and 8th in the date column

insert into fruit values
('Apple',10,'2018-08-01',null,null)
,('Apple',9,'2018-08-02',-1,'Minus')
,('Apple',10,'2018-08-03',1,'Plus')
,('Apple',8.5,'2018-08-07',-1.5,'Minus')
,('Apple',10,'2018-08-08',1.5,'Plus')
,('Orange',4,'2018-08-01',null,null)
,('Orange',4.5,'2018-08-02',0.5,'Plus')
,('Orange',3.5,'2018-08-03',-1,'Minus')
,('Orange',6,'2018-08-07',2.5,'Plus')
,('Orange',5.2,'2018-08-08',-0.8,'Minus')
,('Mango',6.5,'2018-08-01',null,null)
,('Mango',7,'2018-08-02',0.5,'Plus')
,('Mango',8.4,'2018-08-03',1.4,'Plus')
,('Mango',6.5,'2018-08-07',-1.9,'Minus')
,('Mango',5.5,'2018-08-05',-1,'Minus')
;

harishgg1 · September 3, 2018, 2:28pm

hi

I tried

Here is my SQL

SQL

SELECT a.baseproduct, 
       a.basesign, 
       Count(a.matchproduct) 
FROM   (SELECT DISTINCT a.product AS baseproduct, 
                        a.[sign]  AS basesign, 
                        b.product AS matchproduct, 
                        b.[sign]  AS matchsign 
        FROM   fruit a 
               CROSS JOIN fruit b) a 
WHERE  a.basesign IS NOT NULL 
       AND a.matchsign IS NOT NULL 
       AND a.baseproduct <> a.matchproduct 
       AND a.basesign = a.matchsign 
GROUP  BY a.baseproduct, 
          a.basesign

Here is the result
Is this what you are looking for ????

mjv · September 3, 2018, 2:47pm

No, the output has to be same as the first result. I mean it has to have four columns. Like base product, matchproduct, product count, matched count.

The result columns has to same for dates that are in sequence and dates that are not in sequence.

Hope this helps. Thanks for the great help.

Jay

harishgg1 · September 3, 2018, 3:02pm

Hi jay

If we use date in getting ..1
If we don't use date in getting..2

Result s will be different for 1 and 2

Why don't we chat live ..
So that we understand each other

bitsmed · September 3, 2018, 3:08pm

Try this:

with cte
  as (select product
            ,[date]
            ,[sign]
            ,count(*) over(partition by product) as n
            ,row_number() over(partition by product order by [date]) as rn
        from #fruit
     )
select a.product
      ,b.product
      ,avg(a.[n]) as [base total count]
      ,count(*) as [matched count]
  from cte as a
       inner join cte as b
               on b.product!=a.product
              and b.rn=a.rn+1
              and b.[sign]=a.[sign]
 group by a.product
         ,b.product
 order by a.product
         ,[matched count] desc
;

harishgg1 · September 3, 2018, 3:43pm

I think depends on data

I will look at what how
bitsMed did

mjv · September 4, 2018, 6:46am

cte

CTE get me the same output. Let me explore the CTE. Thanks for the help.

Jay

mjv · September 4, 2018, 6:47am

Take a look at the CTE....... it works well