Here is a different approach using a zero-based tally table. I've used a CTE to create a tally table, but you can skip that if you already have a...

I set up some sample data (only one subscriber) and come up with the following:

WITH Subscriptions AS (

SELECT *

FROM (

VALUES

(1, CAST('2014-01-01' AS DATE), CAST('2014-03-01' AS DATE))

,(1, '2014-02-01', '2014-05-01')

,(1, '2014-04-01', '2014-06-01')

,(1,...

Yes it is, but in doing so, your field tag now contains integer and character data, so you will have troubles shredding it later.

Select

ca.doc

From Main

Left Join Docs on...

The easy way to do this is to create the results that you are looking for and then globally replace all of the single quotes with two single quotes before...

If you are inserting col4 into col1, don't you want to use (col6 + col4)?

Drew

I thought that response was brilliant when I saw that it...

I believe...

No, it's still a breaking change.

SELECT *

FROM (

VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),

('2015-12-31', '2015-01-01'),

('2016-12-31', '2016-01-01')

) AS mc(TerminationDate, EffectiveDate)

ORDER BY ISNULL(mc.TerminationDate, mc.EffectiveDate) DESC

SELECT *

FROM (

VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),

('2015-12-31', '2015-01-01'),

('2016-12-31',...

You're confusing the RETURN status with the RESULT SET. Since @Results is an integer, it CANNOT contain two separate numbers. The RETURN status can be specifically...

You need to be more specific. We need to know what the value...

Jacob Wilkins (2/4/2016)

---

drew.allen (2/4/2016)

---

Jacob Wilkins (2/4/2016)

---

Unary plus and negative: https://msdn.microsoft.com/en-us/library/ms174362.aspx

Cheers!

EDIT: I must say that it is still strange that the unary plus works with string data, since the documentation specifies...

Hugo Kornelis (2/5/2016)

---

I didn't go over all your modifications, but this one just struck my eye.

I think you intended this to be:

ORDER BY MC.TerminationDate DESC,...

Jacob Wilkins (2/4/2016)

---

Unary plus and negative: https://msdn.microsoft.com/en-us/library/ms174362.aspx

Cheers!

EDIT: I must say that it is still strange that the unary plus works with string data, since the documentation specifies numeric data. However,...

+ can be either a binary or unary operator. The first one is the binary operator and the second is the unary operator.

Drew

This should give you what you need:

WITH Combined AS(

SELECT MachineID, InstanceKey, TimeKey, RevisionID, rowversion, Account, SchemaClass

FROM TABLE_A

UNION ALL

SELECT MachineID, InstanceKey, TimeKey, RevisionID, rowversion, Account, SchemaClass

FROM TABLE_B

)

, Ordered AS (

SELECT MachineID,...