If you want a couple of good books on DAX, get Rob Collie's book (see http://www.powerpivotpro.com). He's great at breaking things down and explaining them. And you can download...

Did you try something like

SWITCH([Field],BLANK(),...)

?

BLANK() in DAX is like T-SQL NULL. (Not the same, though, I don't think...)

Oh, inherited mess... my favorite! Here's a quick example of using a window function.

SELECT pmts.ID

, pmts.Amount

, pmts.[Status]

, SUM([Amount]) OVER (ORDER BY ID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW)...

What are N1...N7? Are they the number of days to add to a date?

Maybe this article will help... should be required reading.

Forum Etiquette: How to post data/code on a forum...

Can't see the image, but generally speaking, when you want "all possible combinations", you need a CROSS JOIN between two tables... If you had a table of Courses and...

I'm not sure it matters if the table has data or not...

If you run some TSQL to append the data to the Archive table, and then run another that truncates...

Like this?

CREATE TABLE TABLE1

(IDint,

XDATEdate );

CREATE TABLE TABLE2

(XDATEdate );

GO

insert into TABLE1 (ID, XDATE) values

(100,'20150505'),

(100,'20150506'),

(100,'20150507'),

(222,'20150505'),

(222,'20150506');

insert into TABLE2 (XDATE) values

('20150505'),

('20150506'),

('20150507'),

('20150508');

SELECT t1.id, t2.xdate as t2date

FROM table2 t2

LEFT JOIN table1 t1 on t1.xdate=t2.xdate;

Why not...

If it's for a single stored procedure, wouldn't you just use WITH OPTION RECOMPILE?

At first I thought using DelimitedSplit8K would work for this... but I guess I didn't read the fine print carefully enough.

That will happen at 1:30 in the morning sometimes!

Here's a good article to read that you can adapt to answer your question:

http://dba.stackexchange.com/questions/86415/retrieving-n-rows-per-group

At least then you can give it a try and report back if you have problems. Helping...

Is this close?

-- create a cursor to loop over this result set

/* create a cursor to loop through each table... */

DECLARE @CmdLine AS NVARCHAR(200);

DECLARE @CmdCursor AS CURSOR;

SET @CmdCursor = CURSOR...

After taking Jeff's advice...

maybe this works?

SELECT Country

, AccountNum

, PolicyNum As CurrentPolicy

, LAG(PolicyNum,1) OVER (PARTITION BY Country, AccountNum ORDER BY PolicyEffDt) AS PrevPolicy

FROM @PolicyRelationship

ORDER BY Country

,AccountNum;

Nothing to it

SELECT * FROM #TEMP

ORDER BY NEWID();

NEWID() generates a random number for each record, so sorting by it causes a "random" sort.

This would be a much clearer question if you included sample data (create table scripts, insert scripts) and expected results.

I could rewrite the code this way...

SELECT personID

FROM #gradPerson AS g

WHERE NOT EXISTS (SELECT TC.PersonID

FROM TranscriptCourse AS tc

WHERE tc.PersonID = g.PersonID

Are you saying you don't understand...