Solved

union all between two table

Posted on 2012-12-27

1 solution

303 Views

Last Modified: 2012-12-27

i have the following query

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

Open in new window

DAY      reqport      numberofsms      years      months      days
TH      630590      26        2012        12       27
TH      84448      16        2012        12        27
TH      732222      2        2012        12        27

and the another selection is :

  select reqport
  FROM [radiomix].[dbo].[chat]
  group by reqport

Open in new window

the result
reqport
84448
630590
730165
732265
732222

i want to make a result to get this result :

DAY      reqport      numberofsms      years      months      days
TH      630590      26        2012        12       27
TH      84448      16        2012        12        27
TH      732222      2        2012        12        27
TH 730165 0 2012 12 27
TH 732265 0 2012 12 27

?????????????

Comment

Question by:afifosh

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

10 Comments

LVL 22

Expert Comment

by:mcsween

ID: 387243142012-12-27

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

LVL 1

Author Comment

by:afifosh

ID: 387243222012-12-27

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

LVL 22

Expert Comment

by:mcsween

ID: 387243452012-12-27

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

Open in new window

LVL 60

Expert Comment

by:Kevin Cross

ID: 387243972012-12-27

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.

LVL 1

Author Comment

by:afifosh

ID: 387244082012-12-27

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 )

LVL 60

Accepted Solution

by:Kevin Cross

Kevin Cross earned 500 total points

ID: 387245332012-12-27

Try like this:

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 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
GROUP BY d.DAY
       , r.reqport
       , YEAR(d.datetimein)
       , MONTH(d.datetimein)
       , DAY(d.datetimein)
ORDER BY years DESC, months DESC, days DESC
;

Open in new window

If you require reqport or datetimein values not present in the original table, then you will need to generate the unique lists elsewhere. The assumption with above example is that each of the dates or reqport values have at least one record.

LVL 1

Author Comment

by:afifosh

ID: 387246082012-12-27

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 ?

LVL 60

Expert Comment

by:Kevin Cross

ID: 387247982012-12-27

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
;

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

Open in new window

LVL 1

Author Closing Comment

by:afifosh

ID: 387248112012-12-27

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 :

LVL 1

Author Comment

by:afifosh

ID: 387248382012-12-27

nothing to say really your are an EEEEExxxxxxxxxxxpert :D

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things To Know About Local Load Balancing

Article by: Tony

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.

Detect locking/blocking in SQL and send email alerts

Article by: lcohan

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…