Query to display the fields

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello,

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

Posted 3-Nov-11 23:34pm

priya naidu1

Updated 4-Nov-11 0:13am

v3

Add a Solution

Comments

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 :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RaisKazi · Answer 1 · 2011-11-03T23:45:00

Solution 1

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

MONTH (Transact-SQL)

SQL SELECT DISTINCT Statement

UNION (Transact-SQL)

Posted 3-Nov-11 23:45pm

RaisKazi

E.F. Nijboer · Answer 2 · 2011-11-03T23:55:00

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.

SQL

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!