CODEWITHSUNDEEP
sqlsql-server
apomene
apomene

Reputation: 14389

Generate Report from SQL Query

I have the following table:

 Code  State  Site  Date
    ----------
    x1      2      s1     d1
    x2      2      s1     d2
    x3      2      s1     d3
    x1      3      s2     d4     
    x2      3      s1     d5
    x3      3      s1     d6
    x4      2      s2     d7
    x5      2      s2     d8
    x3      2      s1     d9
    ----------
(here d1<d2....<d7)

My goal is to make a report (new table) for every Code:

For the above table the result would be:

 Code    Date
    ----------   
    x2          d2
    x3          d3      
    x2          d5
    x3          d6  
    ----------

What I tried was:

   Select Code,Date,Site from Transactions where State='2' and  Site in 
(Select Site from Transactions where State='3')

But this query isn't enough because for the given table it returns:

 Code    Date
    ----------   
    x2          d2
    x3          d3      
    x2          d5
    x3          d6 
    x3          d9 
    ----------

Which isn't what I want excatly since here in date d9 hasnt a pairing with state 3 so that d9< of that pair...

Hope all this make sense.

If they do is there a SQL query to achieve my purpose?

Upvotes: 0

Views: 309

Answers (1)

TYY
TYY

Reputation: 2716

For the set you gave this should work, although since you did not mention what happens in terms of multiple dates for a specific state I did not answer that question. Long but it works

declare @WhatEverYourTableNameIs Table
(
    Code varchar(2),
    State int,
    Site VarChar(2),
    DateGotten Date
)

Insert into @WhatEverYourTableNameIs
Values
('x1',2,'s1','2014-1-1'),
('x2',2,'s1','2014-1-2'),
('x3',2,'s1','2014-1-3'),
('x1',3,'s2','2014-1-4'),
('x2',3,'s1','2014-1-5'),
('x3',3,'s1','2014-1-6'),
('x4',2,'s2','2014-1-7'),
('x5',2,'s2','2014-1-8'),
('x3',2,'s1','2014-1-9')


SELECT * into #MyTemp
FROM
(
    SELECT Code, [State],Site [Site],DateGotten
    FROM @WhatEverYourTableNameIs
    GROUP BY Code, [State], Site, DateGotten
) a

SELECT *
FROM
(
     SELECT DISTINCT a.Code, a.State, a.Site, a.DateGotten
     FROM #MyTemp a 
     JOIN ( 
                  SELECT * 
                  FROM #MyTemp 
                  WHERE [State] =3
              ) b ON a.Code = b.Code and a.Site = b.Site
     WHERE a.[State] = 2 and a.DateGotten < b.DateGotten
     UNION
     SELECT DISTINCT b.Code, b.State, b.Site, b.DateGotten 
     FROM #MyTemp a 
     JOIN ( 
                 SELECT * 
                 FROM #MyTemp 
                 WHERE [State] =3
              ) b on a.Code = b.Code and a.Site = b.Site
    WHERE b.[State] = 3 and a.DateGotten < b.DateGotten
) a
order by a.DateGotten

drop table #MyTemp

>Code   State   Site    DateGotten

>x2 2   s1  2014-01-02

>x3 2   s1  2014-01-03

>x2 3   s1  2014-01-05

>x3 3   s1  2014-01-06

Upvotes: 2

Related Questions