sql server need help for the below query

Question

dear frnds please help me in sql server TABLE 1 Name DATE Total_Calls Productive_Calls TLSD KK 28-Mar-17 0 0 0 RK 29-Mar-17 45 31 111 MK 30-Mar-17 50 30 97 GK 31-Mar-17 45 34 105 KK 1-Apr-17 46 30 114 RK 2-Apr-17 0 0 0 MK 3-Apr-17 50 32 112 GK 4-Apr-17 53 40 164 RK 5-Apr-17 50 12 33 I want summary in sql Name MTD YTD Total_Calls Productive_Calls TLSD Total_Calls Productive_Calls TLSD GK 53 40 164 98 74 269 KK 46 30 114 46 30 114 MK 50 32 112 100 62 209 RK 50 12 33 95 43 144

Manish Kumar · Answer

I have understand only for MTD for YTD please explain the summary.Mean while you can try below query for MTD part. declare @tbl table (name varchar(10) datecol date col1 int col2 int col3 int ) insert into @tbl select 'KK''28-Mar-17'000 union all select 'RK''29-Mar-17'4531111 union all select 'MK''30-Mar-17'503097 union all select 'GK''31-Mar-17'4534105 union all select 'KK''1-Apr-17'4630114 union all select 'RK''2-Apr-17'000 union all select 'MK''3-Apr-17'5032112 union all select 'GK''4-Apr-17'5340164 union all select 'RK''5-Apr-17'501233 select tbl.nametbl2.col1tbl2.col2tbl2.col3 from (select namemax(datecol) as coldate from @tbl group by name ) tbl INNER join (select namedatecolcol1col2col3 from @tbl) tbl2 on tbl.name=tbl2.name and tbl.coldate=tbl2.datecol order by tbl.name

Manish Kumar · Answer

Run below query for your expected output. declare @tbl table (name varchar(10) datecol date col1 int col2 int col3 int ) insert into @tbl select 'KK''28-Mar-17'000 union all select 'RK''29-Mar-17'4531111 union all select 'MK''30-Mar-17'503097 union all select 'GK''31-Mar-17'4534105 union all select 'KK''1-Apr-17'4630114 union all select 'RK''2-Apr-17'000 union all select 'MK''3-Apr-17'5032112 union all select 'GK''4-Apr-17'5340164 union all select 'RK''5-Apr-17'501233 --select namesum(col1)sum(col2)sum(col3) from @tbl --group by name select tbl.nametbl2.col1tbl2.col2tbl2.col3tbl3.ytdcol1tbl3.ytdcol2tbl3.ytdcol3 from (select namemax(datecol) as coldate from @tbl group by name ) tbl INNER join (select namedatecolcol1col2col3 from @tbl) tbl2 on tbl.name=tbl2.name and tbl.coldate=tbl2.datecol inner join (select namesum(col1) ytdcol1sum(col2)ytdcol2sum(col3) ytdcol3 from @tbl group by name) tbl3 on tbl.name=tbl3.name order by tbl.name https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/

Kumaran P · Answer

thanks manish for ur reply i want also YTD -means like example 1st jan to 15 Apr for the date column Name YTD Total_Calls Productive_Calls TLSD GK 98 74 269 KK 46 30 114 MK 100 62 209 RK 95 43 144

Kumaran P · Answer

gu's Please help any one

dear frnds

please help me in sql server

TABLE 1
Name	DATE	Total_Calls	Productive_Calls	TLSD
KK	28-Mar-17	0	0	0
RK	29-Mar-17	45	31	111
MK	30-Mar-17	50	30	97
GK	31-Mar-17	45	34	105
KK	1-Apr-17	46	30	114
RK	2-Apr-17	0	0	0
MK	3-Apr-17	50	32	112
GK	4-Apr-17	53	40	164
RK	5-Apr-17	50	12	33

I want summary in sql
Name	MTD			YTD
Name	Total_Calls	Productive_Calls	TLSD	Total_Calls	Productive_Calls	TLSD
GK	53	40	164	98	74	269
KK	46	30	114	46	30	114
MK	50	32	112	100	62	209
RK	50	12	33	95	43	144