select DAY,reqport,count(message) as numberofsms,Year(datetimein) as years, Month(datetimein) as months, Day(datetimein) as days FROM [radiomix].[dbo].[chat] group by DAY,reqport,Year(datetimein), Month(datetimein), Day(datetimein) order by Year(datetimein) desc, Month(datetimein) desc, Day(datetimein) desc
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.
AFIF JABBADO
ASKER
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
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 daysFROM ( /* Distinct reqport values. */ SELECT reqport FROM [radiomix].[dbo].[chat] GROUP BY reqport) rCROSS 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) dLEFT 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;
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 :
AFIF JABBADO
ASKER
nothing to say really your are an EEEEExxxxxxxxxxxpert :D
http://www.w3schools.com/sql/sql_join_inner.asp