Latest ads

Electronics
Vehicles
Property
Search

SQL danna aya enna....

0cean

0cean

Member
May 25, 2017
841
58
0
N/A
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)

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

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

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: 
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)

--------------------------------------------------

Hari machan.
Thanks okkotama.
wade goda.

Answer 01

deltamoon said:
මේක try කරලා බලපන්..


select x.*, coalesce( y.count,0) from
(SELECT distinct year(logDate) year,a.*
FROM tbl_log ,
( 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(logDate) year , month (logDate) month ,count(*) count FROM tbl_log
group by year(logDate) ,month (logDate)) y
on x.year=y.year and x.month=y.month
Click to expand...

Answer 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')
	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

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
 
Last edited:
  • Like
Reactions: sithija.h
123buddy

123buddy

Active member
  • Aug 15, 2007
    251
    54
    28
    wennappuwa
    SELECT DATEPART(yy, [birthday]) AS [Year],DATEPART(MM, [birthday]) AS [Month],Count([birthday]) AS [Count]
    FROM [dbo].[testt]
    group by DATEPART(yy, [birthday]),DATEPART(MM, [birthday])
     
    • Like
    Reactions: 0cean
    deltamoon

    deltamoon

    Active member
  • Mar 6, 2007
    246
    155
    43
    මේක try කරලා බලපන්..


    select x.*, coalesce( y.count,0) from
    (SELECT distinct year(logDate) year,a.*
    FROM tbl_log ,
    ( 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(logDate) year , month (logDate) month ,count(*) count FROM tbl_log
    group by year(logDate) ,month (logDate)) y
    on x.year=y.year and x.month=y.month
     
    • Like
    Reactions: 0cean
    0cean

    0cean

    Member
    May 25, 2017
    841
    58
    0
    N/A
    123buddy said:
    SELECT DATEPART(yy, [birthday]) AS [Year],DATEPART(MM, [birthday]) AS [Month],Count([birthday]) AS [Count]
    FROM [dbo].[testt]
    group by DATEPART(yy, [birthday]),DATEPART(MM, [birthday])
    Click to expand...

    ethakotath data nathi masa walata 0 kiyala enne na bn. :(


    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 DATEPART(yy, [birthday]) AS [Year],DATEPART(MM, [birthday]) AS [Month],Count([birthday]) AS [Count]
FROM @mytable
group by DATEPART(yy, [birthday]),DATEPART(MM, [birthday])
     
    kasun757

    kasun757

    Well-known member
  • Jan 12, 2011
    984
    155
    63
    .

    date nathi masa wala 0 wenna nam function ekek or sp ekek liyala date and month eka loop karala temp table ekakata insert karala eka view karanna
     
    • Like
    Reactions: 0cean
    cscuser

    cscuser

    Active member
  • May 24, 2017
    397
    57
    43
    Polonaruwa
    SELECT DATENAME(month,TA.birthday) + ' ' + CAST(DATEPART(year,TA.birthday) AS VARCHAR(4)),
    COUNT(1) Total
    FROM testt as TA
    GROUP BY DATENAME(month,TA.birthday) + ' ' + CAST(DATEPART(year,TA.birthday) AS VARCHAR(4))
     
    • Like
    Reactions: 0cean
    0cean

    0cean

    Member
    May 25, 2017
    841
    58
    0
    N/A
    deltamoon said:
    මේක try කරලා බලපන්..


    select x.*, coalesce( y.count,0) from
    (SELECT distinct year(logDate) year,a.*
    FROM tbl_log ,
    ( 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(logDate) year , month (logDate) month ,count(*) count FROM tbl_log
    group by year(logDate) ,month (logDate)) y
    on x.year=y.year and x.month=y.month
    Click to expand...

    Query eka nam therune naha.
    Habayi wade nam hari. :yes:

    Thanks machan. :)

    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.*, coalesce( y.count,0) 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
[B]order by x.year, x.month[/B]
     
    S

    sithija.h

    Well-known member
  • Nov 21, 2008
    9,679
    5,904
    113
    Matara
    SELECT COUNT(DITINCT Birthday)
    FROM tableName
    WHERE Birthday>='2017-01-01' AND Birthday<'2017-02-01'

    It's give results of how many birthdays available in January month
     
    Last edited:
    S

    sameemail

    Member
    Mar 20, 2007
    11
    1
    3
    Try This - Works with SQL 2008

    Data Set

    ID StudentName DateOfBirth
    1 saman 2015-01-02
    6 thilina 2015-08-10
    7 lahiru 2015-08-15
    5 nayana 2015-08-25
    4 kamal 2015-09-02
    2 nimal 2016-02-02
    9 kevin 2017-02-20
    8 kapila 2017-05-02
    3 amal 2017-05-02
    10 sirimal 2017-05-22
    ----------------------------------------------
    Query
    --**Need to Enter the year (Any Year)

    DECLARE @year INT
    SET @year = 2015

    SELECT A.MonthName ,
    ISNULL(B.RecCount,0) AS NoOfBirthdays
    FROM ( SELECT number AS MonthNumber ,
    DATENAME(MONTH,
    CAST(@year * 100 + number AS VARCHAR) + '01') AS MonthName
    FROM master.dbo.spt_values
    WHERE ( type = 'P' )
    AND ( number BETWEEN 1 AND 12 )
    ) AS A
    LEFT OUTER JOIN ( SELECT COUNT(ID) AS RecCount ,
    MONTH(DateOfBirth) AS MonthNumber
    FROM tempStudent
    GROUP BY MONTH(DateOfBirth)

    Result

    MonthName NoOfBirthdays
    January 1
    February 2
    March 0
    April 0
    May 3
    June 0
    July 0
    August 3
    September 1
    October 0
    November 0
    December 0
     
    • Like
    Reactions: 0cean
    S

    sithija.h

    Well-known member
  • Nov 21, 2008
    9,679
    5,904
    113
    Matara
    0cean said:
    Query eka nam therune naha.
    Habayi wade nam hari. :yes:

    Thanks machan. :)

    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.*, coalesce( y.count,0) 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
[B]order by x.year, x.month[/B]
    Click to expand...

    ah masa godaka result set ekak ona neh, mata dan therune wade,
    anyway wade therum aragena karapan, hondata code eka kiyawapan, danne nathi keywords google search karapan wade therei
     
    • Like
    Reactions: 0cean
    0cean

    0cean

    Member
    May 25, 2017
    841
    58
    0
    N/A
    cscuser said:
    SELECT DATENAME(month,TA.birthday) + ' ' + CAST(DATEPART(year,TA.birthday) AS VARCHAR(4)),
    COUNT(1) Total
    FROM testt as TA
    GROUP BY DATENAME(month,TA.birthday) + ' ' + CAST(DATEPART(year,TA.birthday) AS VARCHAR(4))
    Click to expand...

    Me widiye result ekak awe bn

    Code: 
    August 2015	 1
February 2016 	 1
January 2015	 1
May 2017	         1
September 2015 1


    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 DATENAME(month,TA.birthday) + ' ' + CAST(DATEPART(year,TA.birthday) AS VARCHAR(4)),
COUNT(1) Total
FROM @mytable as TA
GROUP BY DATENAME(month,TA.birthday) + ' ' + CAST(DATEPART(year,TA.birthday) AS VARCHAR(4))
     
    0cean

    0cean

    Member
    May 25, 2017
    841
    58
    0
    N/A
    NFC said:
    bump
    Click to expand...

    Thanks, machan.

    sithija.h said:
    SELECT COUNT(DITINCT Birthday)
    FROM tableName
    WHERE Birthday>='2017-01-01' AND Birthday<'2017-02-01'

    It's give results of how many birthdays available in January month
    Click to expand...

    Thanks machan. Query eka nam hari.

    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 COUNT(DISTINCT birthday) 
FROM @mytable
WHERE birthday>='2017-01-01' AND birthday<'2017-02-01'

    sithija.h said:
    ah masa godaka result set ekak ona neh, mata dan therune wade,
    anyway wade therum aragena karapan, hondata code eka kiyawapan, danne nathi keywords google search karapan wade therei
    Click to expand...

    Ow macahn.
    Ela aniwa. kohomath program eke mita wada tikak wenas.
    Ekata query eka liyana kota therum ganna wenawa.

    sameemail said:
    Data Set

    ID StudentName DateOfBirth
    1 saman 2015-01-02
    6 thilina 2015-08-10
    7 lahiru 2015-08-15
    5 nayana 2015-08-25
    4 kamal 2015-09-02
    2 nimal 2016-02-02
    9 kevin 2017-02-20
    8 kapila 2017-05-02
    3 amal 2017-05-02
    10 sirimal 2017-05-22
    ----------------------------------------------
    Query
    --**Need to Enter the year (Any Year)

    DECLARE @year INT
    SET @year = 2015

    SELECT A.MonthName ,
    ISNULL(B.RecCount,0) AS NoOfBirthdays
    FROM ( SELECT number AS MonthNumber ,
    DATENAME(MONTH,
    CAST(@year * 100 + number AS VARCHAR) + '01') AS MonthName
    FROM master.dbo.spt_values
    WHERE ( type = 'P' )
    AND ( number BETWEEN 1 AND 12 )
    ) AS A
    LEFT OUTER JOIN ( SELECT COUNT(ID) AS RecCount ,
    MONTH(DateOfBirth) AS MonthNumber
    FROM tempStudent
    GROUP BY MONTH(DateOfBirth)

    Result

    MonthName NoOfBirthdays
    January 1
    February 2
    March 0
    April 0
    May 3
    June 0
    July 0
    August 3
    September 1
    October 0
    November 0
    December 0
    Click to expand...

    Thanks machan. Wade nam hari.

    Mama ube qury eka liyapu widiye mokakhari awulak thiyada...
    Error ekak enawane...

    Code: 
    Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

    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')

DECLARE @year INT
SET @year = 2015

SELECT A.MonthName ,
ISNULL(B.RecCount,0) AS NoOfBirthdays
FROM ( SELECT number AS MonthNumber ,
DATENAME(MONTH,
CAST(@year * 100 + number AS VARCHAR) + '01') AS MonthName
FROM master.dbo.spt_values
WHERE ( type = 'P' )
AND ( number BETWEEN 1 AND 12 )
) AS A
LEFT OUTER JOIN ( SELECT COUNT(id) AS RecCount ,
MONTH(birthday) AS MonthNumber
FROM @mytable
GROUP BY MONTH(birthday)
     
    S

    sameemail

    Member
    Mar 20, 2007
    11
    1
    3
    Try Now

    DECLARE @year INT
    SET @year = 2015

    SELECT A.MonthName ,
    ISNULL(B.RecCount,0) AS NoOfBirthdays
    FROM ( SELECT number AS MonthNumber ,
    DATENAME(MONTH,
    CAST(@year * 100 + number AS VARCHAR) + '01') AS MonthName
    FROM master.dbo.spt_values
    WHERE ( type = 'P' )
    AND ( number BETWEEN 1 AND 12 )
    ) AS A
    LEFT OUTER JOIN ( SELECT COUNT(ID) AS RecCount ,
    MONTH(DateOfBirth) AS MonthNumber
    FROM tempStudent
    GROUP BY MONTH(DateOfBirth)
    ) AS B ON A.MonthNumber = B.MonthNumber
     
    nalinsen

    nalinsen

    Member
    Nov 6, 2016
    716
    90
    0
    ටෙම්පරි ටේබල් එකක් හදලා මාස 12 ඒකට INSERT කරලා Left Outer Join එකක් දාල ගන්න.

    :yes::yes:
     
    0cean

    0cean

    Member
    May 25, 2017
    841
    58
    0
    N/A
    sameemail said:
    DECLARE @year INT
    SET @year = 2015

    SELECT A.MonthName ,
    ISNULL(B.RecCount,0) AS NoOfBirthdays
    FROM ( SELECT number AS MonthNumber ,
    DATENAME(MONTH,
    CAST(@year * 100 + number AS VARCHAR) + '01') AS MonthName
    FROM master.dbo.spt_values
    WHERE ( type = 'P' )
    AND ( number BETWEEN 1 AND 12 )
    ) AS A
    LEFT OUTER JOIN ( SELECT COUNT(ID) AS RecCount ,
    MONTH(DateOfBirth) AS MonthNumber
    FROM tempStudent
    GROUP BY MONTH(DateOfBirth)
    ) AS B ON A.MonthNumber = B.MonthNumber
    Click to expand...


    Hari machan. habayi me query eka use karanna puluwan birthday hamogema birthday eka eka yr ekaka thiyanawanam witharayi neda..

    Query eke apu result eke February walatath value ekak awilla thiyanawa.
    (2015 witharak enawanam feb walata value ekak enna widiyak naha.)
    Eka (2, 'nimal', '2016.02.02') ekenda koheda awilla thiyenne.

    Meke auto ena month name eka deltamoon dipu query ekata add karaganna puluwanda bn

    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')

DECLARE @year INT
SET @year = 2015

SELECT A.MonthName ,
ISNULL(B.RecCount,0) AS NoOfBirthdays
FROM ( SELECT number AS MonthNumber ,
DATENAME(MONTH,
CAST(@year * 100 + number AS VARCHAR) + '01') AS MonthName
FROM master.dbo.spt_values
WHERE ( type = 'P' )
AND ( number BETWEEN 1 AND 12 )
) AS A
LEFT OUTER JOIN ( SELECT COUNT(ID) AS RecCount ,
MONTH(birthday) AS MonthNumber
FROM @mytable
GROUP BY MONTH(birthday)
) AS B ON A.MonthNumber = B.MonthNumber


    Code: 
    January	 1
February	 1
March	 0 
April	         0
May	         1
June	         0
July           0
August       1
September 1 
October	 0
November	 0
December	 0
     
    0cean

    0cean

    Member
    May 25, 2017
    841
    58
    0
    N/A
    nalinsen said:
    ටෙම්පරි ටේබල් එකක් හදලා මාස 12 ඒකට INSERT කරලා Left Outer Join එකක් දාල ගන්න.

    :yes::yes:
    Click to expand...

    Eka harigiye na bn. Ethakota tmp table ekata yrs ehema add karannath wenawa neda..?

    Query eka hari machan.
     
    S

    sameemail

    Member
    Mar 20, 2007
    11
    1
    3
    0cean said:
    Hari machan. habayi me query eka use karanna puluwan birthday hamogema birthday eka eka yr ekaka thiyanawanam witharayi neda..

    Query eke apu result eke February walatath value ekak awilla thiyanawa.
    (2015 witharak enawanam feb walata value ekak enna widiyak naha.)
    Eka (2, 'nimal', '2016.02.02') ekenda koheda awilla thiyenne.

    Meke auto ena month name eka deltamoon dipu query ekata add karaganna puluwanda bn

    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')

DECLARE @year INT
SET @year = 2015

SELECT A.MonthName ,
ISNULL(B.RecCount,0) AS NoOfBirthdays
FROM ( SELECT number AS MonthNumber ,
DATENAME(MONTH,
CAST(@year * 100 + number AS VARCHAR) + '01') AS MonthName
FROM master.dbo.spt_values
WHERE ( type = 'P' )
AND ( number BETWEEN 1 AND 12 )
) AS A
LEFT OUTER JOIN ( SELECT COUNT(ID) AS RecCount ,
MONTH(birthday) AS MonthNumber
FROM @mytable
GROUP BY MONTH(birthday)
) AS B ON A.MonthNumber = B.MonthNumber


    Code: 
    January	 1
February	 1
March	 0 
April	         0
May	         1
June	         0
July           0
August       1
September 1 
October	 0
November	 0
December	 0
    Click to expand...


    No Year is used only to Get the Month Name. You can use any dummy Year (Eg. 2000).
    Yes you can add to deltamoon dipu query but you need to add month number to that query or Cast the Month number From BirthDay field
     

    Similar threads

    Hyaenidae
    Personal Investment Plan with free insurance SCAM
    Replies
    16
    Views
    631
    Omega_Jay
    Omega_Jay
    monson
    CEB curtails solar power intake into national grid; $ 1 B pumped storage project: funding yet to be secured. Expected to be completed by 2031 !
    Replies
    35
    Views
    1K
    monson
    monson
    monson
    කුහක මාක්ස්වාදී ජෙප්පො හූල්ලද්දී Sinopec to maintain a 1-month buffer stock
    Replies
    0
    Views
    336
    monson
    monson
    I
    Sri Lanka’s IMF Saga
    Replies
    5
    Views
    768
    kinkon
    kinkon
    sriamerican
    🤩 Didi Chuxing, a ride-hailing behemoth in China, reveals I.P.O. papers. Could be one of the biggest initial public offerings (IPOs) of the year🤩
    Replies
    1
    Views
    532
    hodakolla
    hodakolla
    Top Bottom