asked on

Sql Query

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

Guy Hengel [angelIII / a3]

please clarify what you understand as "merged".
it might be a UNION or a JOIN ...

Nico Bontenbal

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.

kaminda

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

pepps11976

ASKER

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

pepps11976

ASKER

Nicobo with your query you posted i get error q1.week is not a recognized datename option

Nico Bontenbal

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.

pepps11976

ASKER

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

ASKER CERTIFIED SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial