Combining Select Queries

[Barry]

Hi

I am intrested in combining the results of the following 2 select queries
into one to display data like this
Count1 Date1 Count2 Date2
10 1/1/2007 6 1/2/2007


SELECT Count(*) as Count1, Str(Month(User.Logged),2) + '-' +
Str(Day(User.Logged),2) + '-' + Str(Year(User.Logged),4) as Date1
FROM User
INNER JOIN Table3 ON User.UserId = Table3.UserID
INNER JOIN Table1 ON Member.Id = Table1.ID
WHERE User.Logged Between '01/01/2007' AND '10/01/2007'
GROUP BY Month(User.Logged), Day(User.Logged), Year(User.Logged)

SELECT Count(*) as Count2, Str(Month(User.Logged),2) + '-' +
Str(Day(User.Logged),2) + '-' + Str(Year(User.Logged),4) as Date2
FROM User
INNER JOIN Table3 ON User.UserId = Table3.UserID
INNER JOIN Table2 ON Member.Id = Table2.ID
WHERE User.Logged Between '01/01/2007' AND '10/01/2007'
GROUP BY Month(User.Logged), Day(User.Logged), Year(User.Logged)


Will someone please Help me.

TIA
Barry

 

[Brian Gideon]

Hi

I am intrested in combining the results of the following 2 select queries
into one to display data like this
Count1 Date1 Count2 Date2
10 1/1/2007 6 1/2/2007

SELECT Count(*) as Count1, Str(Month(User.Logged),2) + '-' +
Str(Day(User.Logged),2) + '-' + Str(Year(User.Logged),4) as Date1
FROM User
INNER JOIN Table3 ON User.UserId = Table3.UserID
INNER JOIN Table1 ON Member.Id = Table1.ID
WHERE User.Logged Between '01/01/2007' AND '10/01/2007'
GROUP BY Month(User.Logged), Day(User.Logged), Year(User.Logged)

SELECT Count(*) as Count2, Str(Month(User.Logged),2) + '-' +
Str(Day(User.Logged),2) + '-' + Str(Year(User.Logged),4) as Date2
FROM User
INNER JOIN Table3 ON User.UserId = Table3.UserID
INNER JOIN Table2 ON Member.Id = Table2.ID
WHERE User.Logged Between '01/01/2007' AND '10/01/2007'
GROUP BY Month(User.Logged), Day(User.Logged), Year(User.Logged)

Will someone please Help me.

TIA
Barry

I'm not sure exactly what you're after. Won't those queries return
the same thing? Are you wanting to crosstab the results in some
manner? How does this related to the .NET Framework?

 

[Barry]

Hi Brian

What is crosstab??

Indeed this question is not directly related to .NET Framework, but
developing website or winforms applicatins using Sql Server queries i
suppose is very much a part of .NET Framework related development, am i
right ???

Barry

 

[Brian Gideon]

Hi Brian

What is crosstab??

Indeed this question is not directly related to .NET Framework, but
developing website or winforms applicatins using Sql Server queries i
suppose is very much a part of .NET Framework related development, am i
right ???

Barry

Cross tabulation is the process pulling rows up into the column list.

Consider the following dataset.

Person Test IQ
------ ---- ---
John 1 105
John 2 106
John 3 104
John 4 109
Jane 1 110
Jane 2 108
Jane 3 115
Jane 4 113

If you crosstab on Person, group by Test, and capture IQ you get the
following result.

Test John Jane
---- ---- ----
1 105 110
2 106 108
3 104 115
4 109 113

Are you wanting to do something similar?

 

[Barry]

Yes something similar, but combining results from 2 select statements

 

[Barry]

What will the crosstab statement be for such a query

 

[Brian Gideon]

Yes something similar, but combining results from 2 select statements

Wait...do you just want to combine the results of 2 queries or do you
want to crosstab the results as well?

If you just want to combine the resultsets of the 2 queries then 2
trivial options come to mind. 1) Use the union SQL keyword to combine
on the server or 2) Execute two commands and combine the results into
one DataTable (or whatever) via code on the client.

 

[Brian Gideon]

What will the crosstab statement be for such a query

To crosstab in a query you'd use a correlated subquery inside the
outer select clause.

However, if there are a variable number of columns you want included
in the resultset then it's probably easier to do the crosstabbing
via .NET code. Crosstabbing logic can be quite tricky depending on
the specific scenario and the performance desired from the algorithm.