union all between two table

This query needs an inner join not a union; do you have a common column between the tables?

http://www.w3schools.com/sql/sql_join_inner.asp

excuse me  the two result from the same table [radiomix].[dbo].[chat]

but the problem in some day,month,year i don';t have records of some  reqport

Sorry, I didn't notice it was the same table.  Are you looking to remove the rows from the query result that have no value for reqport?

WHERE reqport is not NULL

Select allOpen in new window

It looks like you need to generate a list of unique reqport, a list of all year-month-day combinations you want to report on, and then cross join the two. The result can then be used as the left-most table in a LEFT OUTER JOIN to your table of data. This will yield zeros for the missing data.

yes  mwvisa1 this is what i want :S but how ??
i have some reqport with null value i want to add this missing reqport and 0 value for numberofsms and ad each years,month,years

hope you can help me )

your code is powerful thank you a lot  mwvisa1 :)
please when i check your query i got some reqport with some day not included and with some month not included ?

That would mean that the day or month is not in the data at all; therefore, you will have to build up the dates via a dates (or numbers) table or through a common table expression. Plus I just realized that you probably have timestamps in the original data why you are using GROUP BY DAY(datetimein). Therefore, you probably just need to truncate the time portion.

SELECT d.DAY
     , r.reqport
     , COUNT(c.message) AS numberofsms
     , YEAR(d.datetimein) AS years
     , MONTH(d.datetimein) AS months
     , DAY(d.datetimein) AS days
FROM ( /* Distinct reqport values. */
    SELECT reqport
    FROM [radiomix].[dbo].[chat]
    GROUP BY reqport
) r
CROSS JOIN ( /* Distinct dates. */
    SELECT DATEADD(DD, DATEDIFF(DD, 0, datetimein), 0) AS datetimein, DAY
    FROM [radiomix].[dbo].[chat]
    /* Filter to desired date range. */
    WHERE datetimein >= '2012'
    AND datetimein < '2013'
    GROUP BY datetimein, DAY
) d
LEFT JOIN [radiomix].[dbo].[chat] c
  ON c.reqport = r.reqport 
 AND c.datetimein >= d.datetimein 
 AND c.datetimein < DATEADD(DD, 1, d.datetimein)
GROUP BY d.DAY
       , r.reqport
       , YEAR(d.datetimein)
       , MONTH(d.datetimein)
       , DAY(d.datetimein)
ORDER BY years DESC, months DESC, days DESC
;

Select allOpen in new window

If that does not fix the issue, try generating the dates this way.

CROSS JOIN ( /* Distinct dates. */
    SELECT DATEADD(DD, [number], '2012')
         , UPPER(LEFT(DATENAME(DW, DATEADD(DD, [number], '2012')),2))
    FROM master..spt_values
    WHERE [type] = 'P'
    AND DATEADD(DD, [number], '2012') < '2013'
) d(datetimein, DAY)

Select allOpen in new window

powerfull please  mwvisa1 i will add now new question for month and day ..
because after i run your script i get some field  like this

DAY      reqport      numberofsms      years      months      days
SU      84448      3      2011      1      9
SU      732265      0      2011      1      9
SU      730165      0      2011      1      9
SU      732222      0      2011      1      9
SU      630590      0      2011      1      9
SA      730165      0      2011      1      8
SA      732265      0      2011      1      8
SA      84448      4      2011      1      8
SA      630590      0      2011      1      8
SA      732222      0      2011      1      8

year 2011 and month 1 start with day 8 and not from 1 to 30 or 1 it's depending from
so i should add the all reqport with 0 number of sms and year 2011 and month 1 ..

and same i have like year 2011 or 2011 without a month for example 2 so ..

what should be the modification :

nothing to say really your are an EEEEExxxxxxxxxxxpert :D