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

x
?
Solved

Need help for SQL query

Posted on 2014-08-18
2
Medium Priority
?
171 Views
Last Modified: 2014-08-20
I have the following table

create table student (Name varchar(50), Attdate datetime )


insert into student values ('aaa','2014-08-01 08:43:12.037');
insert into student  values ('bbb','2014-08-01 08:43:12.037');
insert into student  values ('bbb','2014-08-02 07:43:12.037');
insert into student  values ('aaa','2014-08-02 08:43:12.037');
insert into student  values ('ccc','2014-08-03 11:43:12.037');
insert into student  values ('ddd','2014-08-04 07:43:12.037');
insert into student  values ('aaa','2014-08-04 07:43:12.037');
insert into student values  ('eee','2014-08-04 08:43:12.037');
insert into student  values ('bbb','2014-08-04 07:43:12.037');
insert into student  values ('ccc','2014-08-05 07:43:12.037');
insert into student  values ('qqq','2014-08-05 07:43:12.037');
insert into student  values ('fff','2014-08-05 08:43:12.037');
insert into student  values ('ccc','2014-08-05 16:43:12.037');
insert into student  values ('aaa','2014-08-06 08:43:12.037');
insert into student  values ('zzz','2014-08-06 08:43:12.037');

I need to find who are the odd students and who attended only on Monday.

Result :
Name
ddd
qqq
eee
0
Comment
Question by:Varshini S
2 Comments
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 1000 total points
ID: 40268600
This might help:

SELECT Name 
	FROM student
	WHERE  DATENAME(DW, Attdate) = 'Monday'
	GROUP BY Name
	HAVING COUNT(*) % 2 = 1

EXCEPT

SELECT Name 
	FROM student
	WHERE  DATENAME(DW, Attdate) <> 'Monday' 

Open in new window

0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 1000 total points
ID: 40269444
Hi,

Try this

SELECT Name 
FROM student
WHERE  DATENAME(DW, Attdate) = 'Monday'
AND NAME NOT IN (SELECT NAME FROM student WHERE DATENAME(DW, Attdate) <> 'Monday')

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

872 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