June 14, 2012 at 5:45 pm
Table a
recordIDDate LineIDROWNUM
6731357/1/2011 0:00NULL1
673403745/4/2010 0:0012
6734037410/20/2009 0:0013
33334316212/23/2011 0:0011
333314939/12/2011 0:0012
333314937/27/2011 0:0013
table b
FROMIDTOIDFROMDATETODATE
4037431710/1/2011 0:0012/31/2099
14934316210/1/2011 0:0012/31/2099
The logic here is, I have to insert a new records to table a for record 673, if the rownum = 1 and ID = table b.40374. (How do I join here ?????)
Similarly, have insert a new record for record 3333, if the rownum = 1 and ID = table b.43162 (which works if I join to table b on FROMID and Ls.Date < = table b.FromDate, but the able doesn’t work If I do so..)
select * from (
SELECT reocrd, ID, Date, LINEID,
ROW_NUMBER() OVER (PARTITION BY record ORDER BY Date DESC, LINEID DESC) ROWNUM
FROM table LS
WHERE EXISTS (
select 'x'
from table a
where a.id in (SELECT DISTINCT FROM table b)
and a.record = LS.record
and record in (673, 3333)
) A
WHERE LS.Date < '10/1/2011' -- This should be from the table b. HOw do I join to this table, since only column is ID. IF I join on FROMID, thn I don't get records for 3333 as well.
June 14, 2012 at 6:48 pm
Couple of issues here:
1) You did not post DDL nor data in readily consumable form, but as I'm feeling charitable this morning, I have done that for you.
2) Your expected results aren't real clear. You could have done better by showing us the rows existing in TableA after the INSERT was complete.
Setup data:
DECLARE @TableA TABLE
(record INT, ID INT, [Date] DATETIME, LineID INT, ROWNUM INT)
INSERT INTO @TableA
SELECT 673, 135, '2011-07-01', NULL, 1
UNION ALL SELECT 673, 40374, '2010-05-04', 1, 2
UNION ALL SELECT 673, 40374, '2009-10-20', 1, 3
UNION ALL SELECT 3333, 43162, '2011-12-23', 1, 1
UNION ALL SELECT 3333, 1493, '2011-09-12', 1, 2
UNION ALL SELECT 3333, 1493, '2011-07-27', 1, 3
Nonetheless, I'll venture a guess that you're looking to do something like this:
;WITH TableB (FROMID, TOID, FROMDATE, TODATE) AS (
SELECT 40374, 317, '2011-10-01', '2099-12-31'
UNION ALL SELECT 1493, 43162, '2011-10-01', '2099-12-31')
INSERT INTO @TableA
SELECT a.record, ID=FROMID, [Date]=MAX(FROMDATE), LineID=1, 1+MAX(a.ROWNUM)
FROM TableB b
INNER JOIN @TableA a
ON a.ID = b.FROMID
GROUP BY a.record, FROMID
SELECT * FROM @TableA
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 1:46 am
If you are checking for the existance of data in one table comared to another and acting on the result.
You could try using the "MERGE" statement
IF MATCHED in SOURCE
IF MATCHED in TARGET
IF NOT MATCHED in SOURCE
IF NOT MATCHED in TARGET
etc....
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply