Reply to thread

Message

<blockquote data-quote="0cean" data-source="post: 22634389" data-attributes="member: 562898">Machan, mata query ekakin adala year eke adala mase ki denekge birthdays thiyanawada kiyala hoyaganna ona. Birthdays e mase nathnam 0 result eke ennna ona.Example, 2015, jan, 20kage birthdays thiyanawa. oya wage. (year, month, count / 2015, jan, 20 OR 2015, 1, 20) Birthdays adala mase nathnam 0 enna ona.Example 2017, Feb, 0 kiyala enna ona. (or 2017, 2, 0)mama group by clause ekak dala month tikata count eka ena widiyat hada gaththa. eth awula thiyenne birthdays nathi masa wala 0 enne naha.kohomada bn meka hariyatama hada ganne...?danata mama hdapu query eka... (MSSQL)[CODE]select year(birthday) as 'Year', month(birthday) as 'Month', count(birthday) as 'Count'from mytablegroup by month(birthday), year(birthday) order by year(birthday)[/CODE]Result eka...[CODE]2015&nbsp; &nbsp; 1&nbsp; &nbsp; 332015&nbsp; &nbsp; 2&nbsp; &nbsp; 352015&nbsp; &nbsp; 3&nbsp; &nbsp; 292015&nbsp; &nbsp; 5&nbsp; &nbsp; 262015&nbsp; &nbsp; 6&nbsp; &nbsp; 452015&nbsp; &nbsp; 7&nbsp; &nbsp; 422015&nbsp; &nbsp; 8&nbsp; &nbsp; 472015&nbsp; &nbsp; 9&nbsp; &nbsp; 292015&nbsp; &nbsp; 10&nbsp; &nbsp; 342015&nbsp; &nbsp; 11&nbsp; &nbsp; 412015&nbsp; &nbsp; 12&nbsp; &nbsp; 372016&nbsp; &nbsp; 1&nbsp; &nbsp; 292016&nbsp; &nbsp; 2&nbsp; &nbsp; 322016&nbsp; &nbsp; 3&nbsp; &nbsp; 312017&nbsp; &nbsp; 1&nbsp; &nbsp; 342017&nbsp; &nbsp; 2&nbsp; &nbsp; 242017&nbsp; &nbsp; 3&nbsp; &nbsp; 302017&nbsp; &nbsp; 5&nbsp; &nbsp; 292017&nbsp; &nbsp; 6&nbsp; &nbsp; 332017&nbsp; &nbsp; 7&nbsp; &nbsp; 322017&nbsp; &nbsp; 8&nbsp; &nbsp; 392017&nbsp; &nbsp; 9&nbsp; &nbsp; 372017&nbsp; &nbsp; 10&nbsp; &nbsp; 292017&nbsp; &nbsp; 11&nbsp; &nbsp; 332017&nbsp; &nbsp; 12&nbsp; &nbsp; 4[/CODE]Othana result eke 2015.4 mase bithdays nathi nisa eka result eke naha. <img src="/styles/default/xenforo/smilies/default/sad.gif"&nbsp; class="smilie" loading="lazy" alt=":(" title="Sad&nbsp; &nbsp; :(" data-shortname=":(" />table eke structure eka... [id (int), name (nvarchar), birthday (date)][CODE]id, name, birthday01, saman, 2015.01.0202, nimal, 2016.02.0203, amal, 2017.05.0204, kamal, 2015.09.0205, nayana, 2015.08.02[/CODE]SQL (MSSQL) danna kenek innawanam help ekak denna. Thanks.-------------------------Sample table[CODE]declare @mytable table(id int, stname char(10), birthday date);insert into @mytable values (1, 'saman', '2015.01.02')insert into @mytable values (2, 'nimal', '2016.02.02')insert into @mytable values (3, 'amal', '2017.05.02')insert into @mytable values (4, 'kamal', '2015.09.02')insert into @mytable values (5, 'nayana', '2015.08.02')select * from&nbsp; @mytable t1;[/CODE][CODE]declare @mytable table(id int, stname char(10), birthday date);insert into @mytable values (1, 'saman', '2015.01.02')insert into @mytable values (2, 'nimal', '2016.02.02')insert into @mytable values (3, 'amal', '2017.05.02')insert into @mytable values (4, 'kamal', '2015.09.02')insert into @mytable values (5, 'nayana', '2015.08.02')select year(birthday) as 'Year', month(birthday) as 'Month', count(birthday) as 'Count'from @mytablegroup by month(birthday), year(birthday) order by year(birthday)[/CODE]--------------------------------------------------Hari machan.Thanks okkotama.wade goda.Answer 01Answer 02[CODE]declare @mytable table(id int, stname char(10), birthday date);insert into @mytable values (1, 'saman', '2015.01.02')insert into @mytable values (2, 'nimal', '2016.02.02')insert into @mytable values (3, 'amal', '2017.05.02')insert into @mytable values (4, 'kamal', '2015.09.02')&nbsp; &nbsp; insert into @mytable values (5, 'nayana', '2015.08.02')select x.*, DateName( month , DateAdd( month , x.month , -1 ) ) as 'MonthName', coalesce( y.count,0) as 'Count'from (SELECT distinct year(birthday) year,a.*FROM @mytable ,( select 1 'month'unionselect 2 'month'unionselect 3 'month'unionselect 4 'month'unionselect 5 'month'unionselect 6 'month'unionselect 7 'month'unionselect 8 'month'unionselect 9 'month'unionselect 10 'month'unionselect 11 'month'unionselect 12 'month') a) x left outer join (SELECT year(birthday) year , month (birthday) month ,count(*) count FROM @mytablegroup by year(birthday) ,month (birthday)) yon x.year=y.year and x.month=y.monthorder by x.year, x.month[/CODE]Result [CODE]2015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; January&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; February&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; March&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; April&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; May&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; June&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; July&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; August&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; September&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 12015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; October&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; November&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; December&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; January&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; February&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; March&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; April&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; May&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; June&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; July&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; August&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; September&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; October&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; November&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; December&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; January&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; February&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; March&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; April&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; May&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; June&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; July&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; August&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9&nbsp; &nbsp; September&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; October&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; November&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp; &nbsp; December&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0[/CODE]</blockquote>

[QUOTE="0cean, post: 22634389, member: 562898"] Machan, mata query ekakin adala year eke adala mase ki denekge birthdays thiyanawada kiyala hoyaganna ona. Birthdays e mase nathnam 0 result eke ennna ona. Example, 2015, jan, 20kage birthdays thiyanawa. oya wage. (year, month, count / 2015, jan, 20 OR 2015, 1, 20) Birthdays adala mase nathnam 0 enna ona. Example 2017, Feb, 0 kiyala enna ona. (or 2017, 2, 0) mama group by clause ekak dala month tikata count eka ena widiyat hada gaththa. eth awula thiyenne birthdays nathi masa wala 0 enne naha. kohomada bn meka hariyatama hada ganne...? danata mama hdapu query eka... (MSSQL) [CODE] select year(birthday) as 'Year', month(birthday) as 'Month', count(birthday) as 'Count' from mytable group by month(birthday), year(birthday) order by year(birthday) [/CODE] Result eka... [CODE] 2015 1 33 2015 2 35 2015 3 29 2015 5 26 2015 6 45 2015 7 42 2015 8 47 2015 9 29 2015 10 34 2015 11 41 2015 12 37 2016 1 29 2016 2 32 2016 3 31 2017 1 34 2017 2 24 2017 3 30 2017 5 29 2017 6 33 2017 7 32 2017 8 39 2017 9 37 2017 10 29 2017 11 33 2017 12 4 [/CODE] Othana result eke 2015.4 mase bithdays nathi nisa eka result eke naha. :( table eke structure eka... [id (int), name (nvarchar), birthday (date)] [CODE] id, name, birthday 01, saman, 2015.01.02 02, nimal, 2016.02.02 03, amal, 2017.05.02 04, kamal, 2015.09.02 05, nayana, 2015.08.02 [/CODE] SQL (MSSQL) danna kenek innawanam help ekak denna. Thanks. ------------------------- Sample table [CODE] declare @mytable table(id int, stname char(10), birthday date); insert into @mytable values (1, 'saman', '2015.01.02') insert into @mytable values (2, 'nimal', '2016.02.02') insert into @mytable values (3, 'amal', '2017.05.02') insert into @mytable values (4, 'kamal', '2015.09.02') insert into @mytable values (5, 'nayana', '2015.08.02') select * from @mytable t1; [/CODE] [CODE] declare @mytable table(id int, stname char(10), birthday date); insert into @mytable values (1, 'saman', '2015.01.02') insert into @mytable values (2, 'nimal', '2016.02.02') insert into @mytable values (3, 'amal', '2017.05.02') insert into @mytable values (4, 'kamal', '2015.09.02') insert into @mytable values (5, 'nayana', '2015.08.02') select year(birthday) as 'Year', month(birthday) as 'Month', count(birthday) as 'Count' from @mytable group by month(birthday), year(birthday) order by year(birthday) [/CODE] -------------------------------------------------- [B]Hari machan. Thanks okkotama. wade goda.[/B] [U]Answer 01[/U] [U]Answer 02[/U] [CODE]declare @mytable table(id int, stname char(10), birthday date); insert into @mytable values (1, 'saman', '2015.01.02') insert into @mytable values (2, 'nimal', '2016.02.02') insert into @mytable values (3, 'amal', '2017.05.02') insert into @mytable values (4, 'kamal', '2015.09.02') insert into @mytable values (5, 'nayana', '2015.08.02') select x.*, DateName( month , DateAdd( month , x.month , -1 ) ) as 'MonthName', coalesce( y.count,0) as 'Count' from (SELECT distinct year(birthday) year,a.* FROM @mytable , ( select 1 'month' union select 2 'month' union select 3 'month' union select 4 'month' union select 5 'month' union select 6 'month' union select 7 'month' union select 8 'month' union select 9 'month' union select 10 'month' union select 11 'month' union select 12 'month' ) a) x left outer join ( SELECT year(birthday) year , month (birthday) month ,count(*) count FROM @mytable group by year(birthday) ,month (birthday)) y on x.year=y.year and x.month=y.month order by x.year, x.month[/CODE] Result [CODE] 2015 1 January 1 2015 2 February 0 2015 3 March 0 2015 4 April 0 2015 5 May 0 2015 6 June 0 2015 7 July 0 2015 8 August 1 2015 9 September 1 2015 10 October 0 2015 11 November 0 2015 12 December 0 2016 1 January 0 2016 2 February 1 2016 3 March 0 2016 4 April 0 2016 5 May 0 2016 6 June 0 2016 7 July 0 2016 8 August 0 2016 9 September 0 2016 10 October 0 2016 11 November 0 2016 12 December 0 2017 1 January 0 2017 2 February 0 2017 3 March 0 2017 4 April 0 2017 5 May 1 2017 6 June 0 2017 7 July 0 2017 8 August 0 2017 9 September 0 2017 10 October 0 2017 11 November 0 2017 12 December 0 [/CODE] [/QUOTE]

Verification

Hath warak paha keeyada? (hatha wadikireema paha)

Search

Latest ads

Reply to thread