[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql Query

Posted on 2011-04-27
8
Medium Priority
?
276 Views
Last Modified: 2012-05-11
Hi all

I have two querys at the moment that are similar

Query 1

SELECT     TOP (100) PERCENT DATENAME(week, createdon) AS Week, projcode, result, COUNT(*) AS Expr2, createdon
FROM         dbo.dmsproj
WHERE     (createdon >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result
ORDER BY createdon

Query 2

SELECT     DATENAME(week, ih_quodate) AS Week, ih_quodate, ih_quotat
FROM         dbo.V_Quotes
WHERE     (ih_quodate >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY ih_quodate, DATENAME(month, ih_quodate) + DATENAME(year, ih_quodate), ih_quotat

Is ther any way that i can have both these querys merged into 1 single query or is this not possible.?

John
0
Comment
Question by:pepps11976
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35473933
please clarify what you understand as "merged".
it might be a UNION or a JOIN ...
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35473977
Not sure I understand what you mean by merge, but is this what you are looking for:
select isnull(q1.Week,q2.Week) as Week, q1.projcode, q1.result, q1.Expr2, q1.createdon, q2.ih_quodate, q2.ih_quotat from
(
SELECT     TOP (100) PERCENT DATENAME(week, createdon) AS Week, projcode, result, COUNT(*) AS Expr2, createdon
FROM         dbo.dmsproj
WHERE     (createdon >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result
ORDER BY createdon
) q1
FULL OUTER JOIN
(
SELECT     DATENAME(week, ih_quodate) AS Week, ih_quodate, ih_quotat
FROM         dbo.V_Quotes
WHERE     (ih_quodate >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY ih_quodate, DATENAME(month, ih_quodate) + DATENAME(year, ih_quodate), ih_quota
) q2 on q1.Week = q2.Week

Open in new window

This query joins the result of the two queries on the Week column.

You could also use
UNION ALL
to merge the results of the two queries below each other, but then the two queries need to have the same number of columns with the same data types.
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35473994
Without knowing the relationship between dmsproj and V_Quotes
it is not possible to answer your question.

But if it is returning same number of columns with same datatypes you can use UNION to merge two resultsets together
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:pepps11976
ID: 35474026
I am using report builder to create a Line Graph, one query shows the total amount Of Opportunities over 52 weeks, and the other query shows the total number of quotes over 52 weeks, the only similarity between the two is the date range. one query will have more results however than the other one.

The reason that i want them to both be included into one query is so i can use the same dataset in report Builder.

Hope this helps
0
 

Author Comment

by:pepps11976
ID: 35474060
Nicobo with your query you posted i get error q1.week is not a recognized datename option
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35474069
Forget my first query. Based on your extra info I know can tell you want to use Union not Join.

Then I don't understand why you have so many columns. When you have
'proj' as Type, DATENAME(week, createdon) AS Week, COUNT(*) as Quant
in the select part of the first query and
'quote' as Type, DATENAME(week, ih_quodate) AS Week, COUNT(*) as Quant
for the second one, you could merge the two results with UNION. Then you can create a graph based on this result the the Type column for you series.
0
 

Author Comment

by:pepps11976
ID: 35474094
Ok i have removed some columns first query now looks like this

SELECT     TOP (100) PERCENT DATENAME(week, createdon) AS Week, projcode, createdon
FROM         dbo.dmsproj
WHERE     (createdon >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result

and secound is this

SELECT     DATENAME(week, ih_quodate) AS Week, ih_quodate, ih_quotat
FROM         dbo.V_Quotes
WHERE     (ih_quodate >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY ih_quodate, DATENAME(month, ih_quodate) + DATENAME(year, ih_quodate), ih_quotat

could you help me with the Union
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35474385
assuming dmsproj.createdon and V_Quotes.ihquodate are both date fields and dmsproj.projcode and V_Quotes.ih_quotat are also of the same data type you can try this query:
SELECT    'dmsproj' as Type,  DATENAME(week, createdon) AS Week, createdon, projcode
FROM         dbo.dmsproj
WHERE     (createdon >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY createdon, DATENAME(month, createdon) + DATENAME(year, createdon), projcode, result

UNION ALL

SELECT   'quote' as Type,  DATENAME(week, ih_quodate) AS Week, ih_quodate, ih_quotat
FROM         dbo.V_Quotes
WHERE     (ih_quodate >= DATEADD(MONTH, - 12, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120)))
GROUP BY ih_quodate, DATENAME(month, ih_quodate) + DATENAME(year, ih_quodate), ih_quotat

Open in new window

It will use the column names of the first query, so you might want to rename those columns to something more generic.
If you need more help it would be nice to have the create table scripts (and possible some fake data) for both tables.

I suppose you want the total number of quotes and opportunities per week in your graph. Then I don't understand what you need the projcode and createdon fields for.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question