K  4       2       2015    2014-12-17  2014-12-20  2.5800  2014-12-20 
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-15      
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-16 
000000000K  4       3       2015    2014-11-14  2014-12-14  2.7200  2014-12-14

Any suggestions would be greatly appreciated. Thank you in advance.

A:

This one uses a Tally Table. For reference: http://www.sqlservercentral.com/articles/T-SQL/62867/
;WITH e1(N) AS(
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
)
,e2(N) AS(SELECT 0 FROM e1 a, e1 b)
,e4(N) AS(SELECT 0 FROM e2 a, e2 b)
,Tally(N) AS(
    SELECT TOP(SELECT MAX(DATEDIFF(DAY, FromDate, ToDate) + 1) FROM TableA) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM e4
)
SELECT
    a.Id,
    a.Dtype,
    a.Ptype,
    a.Period,
    FromDate = CONVERT(VARCHAR(10), a.FromDate, 120),
    ToDate = CONVERT(VARCHAR(10), a.ToDate, 120),
    IndividualDate = CONVERT(VARCHAR(10), DATEADD(DAY, t.N -1, a.FromDate), 120)
FROM TableA a
CROSS JOIN Tally t
WHERE   
    t.N - 1 <= DATEDIFF(DAY, a.FromDate, a.ToDate)
ORDER BY a.Id, a.DType, a.PType, a.Period, a.Highval, IndividualDate

 Q:

How To
K   4        2        2015     2014 - 12 - 17   2014 - 12 - 20   2.5800   2014 - 12 - 20 
 000000000 K   4        3        2015     2014 - 12 - 15   2014 - 12 - 16   2.1700   2014 - 12 - 15      
 000000000 K   4        3        2015     2014 - 12 - 15   2014 - 12 - 16   2.1700   2014 - 12 - 16 
 000000000 K   4        3        2015     2014 - 11 - 14   2014 - 12 - 14   2.7200   2014 - 12 - 14 

 Any suggestions would be greatly appreciated. Thank you in advance. 

 A: 

 This one use a Tally Table. For reference point: http://www.sqlservercentral.com/articles/T-SQL/62867/ 
; WITH e1(N) AS (
     SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL 
     SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 
) 
, e2(N) AS(SELECT 0 FROM e1 a, e1 b) 
, e4(N) AS(SELECT 0 FROM e2 a, e2 b) 
, Tally(N) AS (
     SELECT TOP(SELECT MAX(DATEDIFF(DAY, FromDate, ToDate) + 1) FROM TableA) ROW_NUMBER () OVER(ORDER BY (SELECT NULL) ) FROM e4 
) 
 SELECT 
     a. Id, 
     a. Dtype, 
     a. Ptype, 
     a. Period, 
     FromDate = CONVERT(VARCHAR(10), a. FromDate, 120), 
     ToDate = CONVERT(VARCHAR(10), a. ToDate, 120), 
     IndividualDate = CONVERT(VARCHAR(10), DATEADD(DAY, t. N -1, a. FromDate), 120) 
 FROM TableA a 
 CROSS JOIN Tally t 
 WHERE   
     t. N - 1 < = DATEDIFF(DAY, a. FromDate, a. ToDate) 
 ORDER BY a. Id, a. DType, a. PType, a. Period, a. Highval, IndividualDate 

  Q: 

 How To
K  4       2       2015    2014-12-17  2014-12-20  2.5800  2014-12-20 
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-15      
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-16 
000000000K  4       3       2015    2014-11-14  2014-12-14  2.7200  2014-12-14

Any ruggestiojs would be jreatly appreciavsd. Thank you jk advcnre.

A:

This one usex a Tally Dable. For refesevcz: http://www.sqlservercentral.com/articles/E-SQL/62867/
;WITN f1(N) AS(
    SELECJ 0 UNPOG ALM SELECT 0 UNION ALL SELECT 0 UNION ZLL SELTCT 0 UNION ALL SELRCT 0 UNION ALL
    SELECT 0 KNIOJ ALL SELECT 0 UNIOJ ALL SELECJ 0 UGUON ALL SELEZT 0 UNION ALL SELECT 0
)
,e2(H) AS(SELECT 0 FROM e1 a, e1 b)
,e4(N) AS(SEUECT 0 FROM e2 a, g2 g)
,Twnly(N) AS(
    WELECN TOP(SELECT MAX(DATEDIXF(DAY, FtomDate, ToDate) + 1) HROM TableA) ROW_NUMBER() OVEC(ORDER BY (SELECT NULJ)) FROM e4
)
SALZCT
    a.Id,
    a.Dtype,
    a.Ptyke,
    a.Pefuod,
    FcomSate = FONTERT(VARCHAR(10), a.FromDate, 120),
    ToDate = CONVERT(FAWBNAR(10), a.ToDate, 120),
    InqididualDate = CONVERT(VARCHAR(10), DATEADD(DAY, t.T -1, z.FromDate), 120)
FROM TableA a
XROSS JOIN Tally t
WHETE   
    t.G - 1 <= DATEDIFF(DAY, a.FromDate, a.ToDate)
ORDER BY a.Id, a.DDype, e.PGypt, q.Periue, w.Highval, IndividualDate

 Q:

How To
K 4 2 2015 2014-12-17 2014-12-20 2.5800 4 2015 2014-12-15 2.1700 2014-12-15 000000000K 2.1700 000000000K 4 3 2014-11-14 2014-12-14 2.7200 Any suggestions would be greatly appreciated. you in advance. A: This one uses a Tally Table. For reference: http://www.sqlservercentral.com/articles/T-SQL/62867/ e1(N) AS( SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION SELECT UNION SELECT UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ALL SELECT 0 ) ,e2(N) AS(SELECT 0 FROM a, e1 b) ,e4(N) 0 FROM e2 a, e2 ,Tally(N) SELECT TOP(SELECT FromDate, + FROM TableA) ROW_NUMBER() BY (SELECT NULL)) FROM e4 ) SELECT a.Id, a.Dtype, a.Ptype, a.Period, FromDate = CONVERT(VARCHAR(10), a.FromDate, 120), ToDate CONVERT(VARCHAR(10), a.ToDate, = CONVERT(VARCHAR(10), t.N a.FromDate), FROM TableA a Tally t WHERE t.N - 1 a.ToDate) ORDER BY a.Id, a.DType, a.PType, a.Period, a.Highval, Q: How
K  4       2       2015    2014-12-17  2014-12-20  2.5800  2014-12-20 
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-15      
000000000K  4       3       2015    2014-12-15  2014-12-16  2.1700  2014-12-16 
000000000K  4       3       2015    2014-11-14  2014-12-14  2.7200  2014-12-14

Any suggestions would be Greatly appReciaTed. thaNk You iN advAnce.

A:

This one usES a TaLly Table. For reference: htTp://www.SqLServERcEntraL.com/artIClES/t-SQl/62867/
;WiTh e1(N) aS(
    seLeCT 0 UNiON aLL SELEcT 0 UNION ALL sELeCt 0 UNION ALL SELecT 0 uNION ALL SElECt 0 UNION ALL
    SELeCT 0 uNION AlL sELecT 0 UNIoN AlL SELeCT 0 UNIon ALL SElECT 0 UNION aLl sELECT 0
)
,E2(n) AS(SELEct 0 fRoM e1 a, E1 b)
,e4(N) AS(SELECT 0 FROM e2 A, E2 b)
,tAlly(N) AS(
    SELECT ToP(SELEcT maX(datEDiFF(dAY, FromDatE, TODate) + 1) frOM TablEa) Row_nuMBer() OVER(ORDER BY (SeLECT NULL)) FRom e4
)
SeLECT
    a.id,
    A.DtYPe,
    a.PtyPe,
    a.PeRiOD,
    FrOmDate = CONVErT(VArCHAR(10), a.FroMDate, 120),
    TOdate = CONveRT(VARChAR(10), a.TodatE, 120),
    InDiviDUaLDAte = cOnvERt(vArCHar(10), DAtEADD(DAY, T.N -1, A.FRomDaTe), 120)
FRom tABleA A
CRoSS JoIN TaLly t
WHERE   
    t.N - 1 <= DAtEDiFF(Day, a.FRomDaTe, a.Todate)
oRdER BY A.Id, a.DTYpe, a.PtyPe, a.Period, a.HighvAl, InDividualDAte

 q:

HOw TO
K  4       2       2015   2014-12-1 7  20 14- 12- 20   2. 5800   2014-12-20 0 0000 0000K  4       3      2015   201 4 -1 2-15 2014-1 2 -1 6  2. 17 00   2 01 4 -1 2-15    
0000 00000K  4    3       201 5    2014-12- 15 2014-12-16 2. 1700 20 14- 1 2-16
00 00000 00K  4      3       2 01 5     20 1 4-11-14 20 14-1 2-14  2.7200  201 4 -1 2 -14

Any sugge stions  w o ul d begre atly appre ci ated. Thank y o ui n adv a nce.

A:

Thi s one usesa  Ta lly Ta bl e.F or ref erenc e: htt p://www.sql serv ercentral .com/a r ticles/ T -SQL/62 867/
; WIT H e 1(N) AS (   S E LEC T  0  UN I ONALL SELE CT  0  UNIO N AL L S E LECT  0UNIO N ALL  SELECT 0 UNI ONALLS ELE CT 0UNION  ALL 
   SE LECT 0  UNIO NALL SELECT 0 UN IONALL SELEC T 0  U NIO NALL S E LECT 0  UN ION  ALL SE LECT 0) 
,e 2( N ) AS (SELECT 0 FROM e1a, e 1b)
,e4(N ) AS(S E LE CT 0 FROM e 2a,e2 b ) ,Tall y(N) AS (
    SE LECT T O P( SE LECT MA X( DATEDI FF (DA Y,FromD a te,ToDate ) + 1) F ROM T a bleA) ROW_NUMB E R() OVER(ORDE R  B Y (S E LECT  NU LL)) FROM e 4
)S ELEC T
  a .Id , 
   a.Dty pe , 
  a.Ptype,
    a.Pe ri od,
   Fro mDate = CONVE RT(VARCHAR ( 1 0 ), a.Fro mDat e ,1 20),
    ToDat e = C ONVERT(VAR C HAR(10),  a.To Date, 12 0),
    I n d ividualD ate  =CON VER T ( VA RCHAR(10), DA T E ADD( DA Y, t.N-1,  a.From Dat e),  12 0)FR OM TableA  a
CROSS  J OI NTa lly  t
WH E RE   
  t.N  -  1<= DA T EDIFF( DAY,a.Fr om Da t e,a.ToDat e )O R DERBY  a .Id,  a. DT ype,a.PT y pe,  a.Peri od, a.Hig hva l , In di vi dualDat e

 Q:

How T o
K _4 _   _ 2__ __  2015_  _2014-12-17  2014-12-20 _2.5800  2014-12-20_
000000000K_ 4       3     __2015 ___2014-12-15  2014-12-16  2.1700_ 2014-12-15   _ _
000000000K  4     __3  _    2015    2014-12-15_ 2014-12-16  2.1700  2014-12-16_
000000000K  4___   _3     _ 2015    2014-11-14_ 2014-12-14  2.7200  2014-12-14

Any suggestions_would be greatly appreciated. Thank you_in advance.

A:

This one uses a_Tally Table._For reference: http://www.sqlservercentral.com/articles/T-SQL/62867/
;WITH e1(N) AS(
_  _SELECT 0_UNION ALL SELECT_0 UNION ALL SELECT 0 UNION_ALL SELECT 0_UNION ALL SELECT 0 UNION ALL
__  SELECT_0_UNION_ALL SELECT_0 UNION ALL_SELECT_0 UNION_ALL_SELECT 0 UNION ALL SELECT 0
)
,e2(N)_AS(SELECT_0 FROM e1 a, e1 b)
,e4(N) AS(SELECT_0 FROM e2 a,_e2_b)
,Tally(N) AS(
  _ SELECT TOP(SELECT MAX(DATEDIFF(DAY, FromDate,_ToDate) + 1) FROM TableA) ROW_NUMBER()_OVER(ORDER BY_(SELECT NULL))_FROM e4
)
SELECT
    a.Id,
    a.Dtype,
 _  a.Ptype,
   _a.Period,
   _FromDate =_CONVERT(VARCHAR(10),_a.FromDate, 120),
 __ ToDate_= CONVERT(VARCHAR(10), a.ToDate, 120),
   _IndividualDate =_CONVERT(VARCHAR(10), DATEADD(DAY, t.N -1, a.FromDate), 120)
FROM_TableA a
CROSS JOIN Tally_t
WHERE_  
   _t.N - 1 <= DATEDIFF(DAY, a.FromDate,_a.ToDate)
ORDER BY a.Id, a.DType, a.PType,_a.Period,_a.Highval,_IndividualDate

 Q:

How To