CODEWITHSUNDEEP
sqlsql-server-2008-r2intersect
crodie
crodie

Reputation: 112

Store intersect result in table

i'm having this query to get the doubles in a table, but on a different date:

SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.prijs
FROM dbo.Acf_LstPrijzen acf
WHERE HstDate = '2013-12-01'
INTERSECT
SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.Prijs
FROM dbo.Acf_LstPrijzen acf
WHERE HstDate = (SELECT MAX(acf2.hstDate) 
                         FROM dbo.Acf_LstPrijzen acf2
                         WHERE acf2.HstDate < '2013-12-01'
                         AND acf.id = acf2.id
                         GROUP BY id)

My question is: How can I insert this result in another table? Is it possible?

Upvotes: 1

Views: 1098

Answers (2)

Justin
Justin

Reputation: 9724

I would do something like this:

INSERT INTO TableName (column1,column2,column3,column4,column5,column6)
SELECT t.ID , t.ID2 , t.CNK , t.Omsch , t.Groep , t.prijs
FROM(
SELECT  t.ID , t.ID2 , t.CNK , t.Omsch , t.Groep , t.prijs
FROM dbo.t_LstPrijzen t
WHERE HstDate = '2013-12-01'
INTERSECT
SELECT  t1.ID , t1.ID2 , t1.CNK , t1.Omsch , t1.Groep , t1.Prijs,
FROM    (SELECT  ID, ID2, CNK , Omsch , Groep , Prijs,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY HstDate DESC) AS rnk
FROM dbo.t_LstPrijzen 
WHERE HstDate < '2013-12-01') t1
WHERE t1.rnk = 1 ) t

Upvotes: 2

Kishore
Kishore

Reputation: 846

Try this...

insert into #T

    select * from
    (
    SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.prijs
    FROM dbo.Acf_LstPrijzen acf
    WHERE HstDate = '2013-12-01'
    INTERSECT
    SELECT  acf.ID , acf.ID2 , acf.CNK , acf.Omsch , acf.Groep , acf.Prijs
    FROM dbo.Acf_LstPrijzen acf
    WHERE HstDate = (SELECT MAX(acf2.hstDate) 
                             FROM dbo.Acf_LstPrijzen acf2
                             WHERE acf2.HstDate < '2013-12-01'
                             AND acf.id = acf2.id
                             GROUP BY id)
    )

Upvotes: 3

Related Questions