Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
Solved
union all between two table
Posted on 2012-12-27
i have the following query
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 :
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
?????????????
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
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
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
?????????????
[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
-
5
3
2
10 Comments
Active 3 days ago
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
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
but the problem in some day,month,year i don';t have records of some reqport
Active 3 days ago
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
Active today
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 )
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 )
Active today
Try like this:
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.
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
;
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.
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 ?
please when i check your query i got some reqport with some day not included and with some month not included ?
Active today
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.
If that does not fix the issue, try generating the dates this way.
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
;
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)
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 :
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 :
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Course of the Month6 days, 23 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.