Click here to Skip to main content
15,123,367 members
Please Sign up or sign in to vote.
0.00/5 (No votes)

I am trying to select a record which has all the distinct values. My question as follows:

1. Firstly I want to display the list of dates scheduled in particular month(s).
2. Then in that particular month i want to display down the events held in the particular date.
3. Now I want to display all the details based on both the data in a single query with the "above two condition counts"

Note: There should be only distinct values and not any repeating row/column values another irrespective of EventDates

The Expected Output is :
eventid	#ofparticipants	   EventName	          EventDate
101	   15		             Event1               [31/10/2011]
115	   11		             Event2               [02/11/2011]
114	   11		             Event3               [03/11/2011]

--This is my first query:

select EventDate,count(distinct EventCode) as [TotalEvents]
from Tt_Events a join Mt_EventsSchedule b on a.EventId = b.EventId
where EventDate != ''
group by EventDate
order by EventDate

--This is my second query:

select distinct b.EventCode as Paper,count(a.EventAppId) as EvntId ,b.EventName
from Tt_StudentAppliForm_Details a join EventsSchedule b on a.EventId = b.EventId
join Tt_StudentAppliForm_Basic c on a.EventAppId= c.EventAppId

group by EventCode ,EventName
order by EvntId desc

Now I want to merge the two queries and get the above output.

Please Help...!!!!

Thank You
Updated 4-Nov-11 1:13am
Amir Mahfoozi 4-Nov-11 5:55am
Please provide sample raw data for this output :)
Amir Mahfoozi 4-Nov-11 14:16pm
Please provide table structures and raw data :)

Here are some Hints around some concepts which will help you to write your queries by your own.

MONTH (Transact-SQL)


UNION (Transact-SQL)
You don't supply any table data so it isn't clear where the output came from But... I give it a try.

You mention you want distinct records so first about that. It is important to know that distinct is actually a simplified "group by". With this in mind you can easily group the records in the way you like.

SELECT eventid, ofparticipants, EventName, EventDate 
   FROM Events
   WHERE EventDate IN (date1, date2, ...)
   GROUP BY eventid, EventDate;

Keep in mind that by using eventid first in the group by, you only will get one record with that eventid. But if eventid is the primary index of this table you can leave it out because it will always be a distinct value.

Hopefully this will get you going and otherwise you might add some extra info (like some table records and the output you expect)

Good luck!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900