difference between dates concatenated together

Question

Picture paints a thousand words. So the highlighted row (which is easy to work out) is correct.

But what I want to do on the other rows, is concatenate the months e.g. if we refer to row 2 which has 4 months between the start and end date: "1015 1115 1215 0116"

Any suggestions on how this could be achieved?

SQL Server version is 2012.

For clarification:

For the purpose of this question, we need to concentrate on the two columns - StartDate and EndDate. "NumberOfColumns" is a derived column created by me which shows the total number of months from Start to End. You can ignore "PaymentHolidaysTaken"

The final column on the helper screenshot and what I am trying to achieve is this - Display MMYY for each of the months and years from StartDate to EndDate (with a space seperator). As mentioned above, row 2 is a great example of several months and years and I would like this value to be "1015 1115 1215 0116". Row 4 would need to be "0916 1016"

Does this clarify?

I cannot accept - Dynamic SQL or Cursors. Company coding standards.

Please provide 1) Your attempt to solve the problem 2) DDL for the table 3) Data contained in the table 4) Expected output. — Igor, Jul 16 '18 at 15:37
[try this approach](https://www.sqlservercentral.com/Forums/Topic494640-149-1.aspx) and we have a related question on SO, which does it for dates not for periods but the principle is the same: https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788 — Cee McSharpface, Jul 16 '18 at 15:45
You need to go back and talk to whoever thought this up and get some clarification. If StartDate is 1/31 & EndDate is 3/31, does that mean you want "0131 0231 0331" even though 0231 is invalid? — Terry Carmen, Jul 16 '18 at 15:46
Igor - for purposes of this example (given the table has many columns), we can call it Select StartDate, EndDate From Table. There are about 2 million rows in the table. I've revised my question above to hopefully explain expected output — Raymondo, Jul 17 '18 at 06:47
Terry - Hopefully my revised explanation helps. I require MMYY repeated for each of the months from Start Date to End Date. — Raymondo, Jul 17 '18 at 06:49
dlatikay - My client is paid a lot of money by their client to ultimately provide a CSV monthly data feed. One of the many fields is the one I am trying to get working above. The client paying £££ wants MMYY and not MMDD — Raymondo, Jul 17 '18 at 06:51

Shnugo · Accepted Answer · 2018-07-17T07:20:11.390

Picture paints a thousand words

No! Don't post pictures of data or code!

Your question is not at all clear, even with the picture. And - looking at my attempt to answer - you see, that I had to type in a test scenario. This was your job actually... For the next question please provide a working stand-alone scenario and add the expected output. Add the code you tried yourself and some explanation. This is worth a thousand words, pictures are annoying...

As told already: The question is not very clear, so this might be wrong for you, but my magic crystal ball tells me, that you are looking for this:

DECLARE @tbl TABLE(D1 DATE, D2 DATE);

INSERT INTO @tbl VALUES
 ({d'2016-02-01'},{d'2016-03-31'})
,({d'2015-10-01'},{d'2016-01-31'})  --Your Row 2
,({d'2016-09-01'},{d'2016-10-31'})  --Your Row 4
,({d'2015-12-01'},{d'2015-12-31'})
,({d'2018-04-01'},{d'2018-06-30'})
,({d'2018-04-04'},{d'2018-06-30'})  --Day index != 1, but smaller in D1
,({d'2018-04-04'},{d'2018-06-03'}); --Day index is smaller in D2

SELECT t.*
      ,A.CountOfMonths
      ,(
         SELECT TOP(A.CountOfMonths) CONCAT(' '
                                           ,FORMAT(DATEADD(MONTH,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,D1),'MMyy')
                                           ) 
         FROM master..spt_values
         FOR XML PATH('')
       )
FROM @tbl t
CROSS APPLY (SELECT DATEDIFF(MONTH,D1,D2)
                    + CASE WHEN DAY(D1)<DAY(D2) THEN 1 ELSE 0 END) A(CountOfMonths);

The result

D1          D2         CoM   
2016-02-01  2016-03-31  2    0216 0316
2015-10-01  2016-01-31  4    1015 1115 1215 0116
2016-09-01  2016-10-31  2    0916 1016
2015-12-01  2015-12-31  1    1215
2018-04-01  2018-06-30  3    0418 0518 0618
2018-04-04  2018-06-30  3    0418 0518 0618
2018-04-04  2018-06-03  2    0418 0518

The query will use some calculation to find the "NumberOfMonths" between the two dates. This value will be used to get the TOP() rows of a table. I use master..spt_values which is just a table with many rows. We are not interested in these values, just in a result set with the correct count of rows. On this set I apply ROW_NUMBER to get a running number (1,2 or 1,2,3,4 and so on). This running number is added in months to D1. The final output is done by FORMAT().

The row with the 4th of April shows, that your first date is not always the first of the month. So I added a case where the day's index of the second month is smaller and I use a CASE to add one more month in "normal" situations. Might be, that you want to change this to <=... I don't know what you want to happen if the day's index is the same in D1 and D2...

If this does not solve your problem please use my scenario, add explanatory data and use the edit option of your question to make a good question out of it...

Many thanks Shnugo, your solution worked. Apologies for any confusion. I'd had a busy day and rushed my question before leaving the office to get home to my child. It's an excellent solution — Raymondo, Jul 17 '18 at 07:59

difference between dates concatenated together

1 Answers1