Multiple column values ​​in the where clause

Question

I need to display a single-user schedule for each week, for example, a schedule,

Scenario: The faculty is assigned several batches in one week (for example: BBA, Maths and Forenoon for 1 and 2 hours) and (MBA, Maths, Forenoon for Hour 3 and 4) they say on the same day (30-06-2015). A row of grids will be stored as 1 and 2 rows as stores as 2 and so on .........

My table definition:

CREATE TABLE [dbo].[test] ( [datedif] NVARCHAR (50) NOT NULL, [hour] INT NULL, [subject] NVARCHAR (MAX) NULL, [faculty] NVARCHAR (MAX) NULL, [attendence] BIT NULL, [dayweek] NVARCHAR (50) NULL, [weekmonth] NVARCHAR (MAX) NULL, [batch] NVARCHAR (MAX) NULL, [section] NVARCHAR (MAX) NULL, [session] NVARCHAR (MAX) NULL ); 

The table is as follows:

 Datefdiff | hour | subject | faulty| batch ----------+-------+----------+---------+-------+----------+--------+-------+----------+---------+------- 30-06-2015| 1| Maths | Kevin | BBA 30-06-2015| 2| Science | Amal | MBA 30-06-2015|3 | chemistry|Jaya |BBA 30-06-2015|4 | chemistry|Jaya |BBA 30-06-2015|5 | chemistry|Jaya |BBA 31-06-2015 |1| science | Amal |BBA 31-06-2015 |2| Maths | kevin |BBA 31-06-2015 |3| Science | Amal |BBA 31-06-2015 |4 | chemistry|Jaya |BBA 31-06-2015 |5| science | Amal |BBA 

The expected result will be provided only for teachers: Amal

 Datefdiff |hour|subject| batch |hour|subject | batch |faculty|hour | subject | batch | hour | subject | batch| hour | subject | batch | ----------+-------+----------+-------+-------+----------+-------+-------+----------+ 30-06-2015| 1 | Maths| BBA| 2| Science | MBA | 3| Science | BBA| 4| chemistry| BBA | 5 |Physics |MBA 31-06-2015| 1 | Maths| BBA| 2| Science | MBA | 3| Science | BBA| 4| chemistry| BBA | 5 |Physics |MBA 

Answer 1

This could be the starting point for your table design:

 declare @tbFaculty table ( FacultyID int --identity(1,1) primary key , Name varchar(50) ) insert into @tbFaculty ( FacultyID, Name ) values ( 1, 'Kevin' ) , ( 2, 'Amal' ) declare @tbBatch table( BatchID int --identity(1,1) primary key , Name char(3) ) insert into @tbBatch ( BatchID, Name ) values ( 1, 'BBA' ) , ( 2, 'MBA' ) declare @tbClass table ( [Hour] tinyint , [Subject] nvarchar (128) , [FacultyID] int , [Attendence] bit , [BatchID] char(3) , [ClassDate] date ) insert into @tbClass ( [Hour], [Subject], FacultyID, Attendence, BatchID, ClassDate ) values ( 1, 'Maths', 1, 1, 1, '2015-06-30' ) , ( 2, 'Maths', 1, 1, 1, '2015-06-30' ) , ( 3, 'Science', 2, 1, 1, '2015-06-30' ) , ( 1, 'Science', 2, 1, 2, '2015-06-30' ) , ( 2, 'Science', 2, 1, 2, '2015-06-30' ) , ( 3, 'Maths', 1, 1, 2, '2015-06-30' ) select cl.ClassDate , cl.[Hour] , cl.[Subject] , ba.Name as BatchName , fa.Name as FacultyName from @tbClass cl inner join @tbBatch ba on ba.BatchID = cl.BatchID inner join @tbFaculty fa on fa.FacultyID = cl.FacultyID where fa.Name = 'Amal' 

You can also normalize the topic.

Answer 2

I will just add a separate answer. Try it:

 --INSERT INTO dbo.test(datedif,[hour],[subject],faculty,batch) --VALUES --('30-06-2015',1,'Maths','Kevin','BBA'), --('30-06-2015',2,'Science','Amal','MBA'), --('30-06-2015',3,'chemistry','Jaya','BBA'), --('30-06-2015',4,'chemistry','Jaya','BBA'), --('30-06-2015',5,'chemistry','Jaya','BBA'), --('31-06-2015',1,'science','Amal','BBA'), --('31-06-2015',2,'Maths','kevin','BBA'), --('31-06-2015',3,'Science','Amal','BBA'), --('31-06-2015',4,'chemistry','Jaya','BBA'), --('31-06-2015',5,'science','Amal','BBA'); WITH CTE_Hours AS ( SELECT 1 AS hour1, MAX(CASE WHEN [Hour] = 1 THEN [subject] END) AS subject1, MAX(CASE WHEN [Hour] = 1 THEN [batch] END) AS batch1 , 2 AS hour2, MAX(CASE WHEN [Hour] = 2 THEN [subject] END) AS subject2, MAX(CASE WHEN [Hour] = 2 THEN [batch] END) AS batch2 --etc... FROM dbo.test WHERE faculty = 'Amal' ) SELECT * FROM ( SELECT DISTINCT datedif FROM dbo.test ) A CROSS JOIN CTE_Hours 

Answer 3

Here's how to normalize your data, which will make querying it a lot easier.

Using a table definition, I inserted your data

 INSERT INTO test(Datedif,hour1,subject1,faculty1,hour2,subject2,faculty2,hour3,subject3,faculty3,batch) VALUES ('30-06-2015',1,'Maths','Kevin',1,'Maths','Kevin',1,'Science','Amal','BBA'), ('30-06-2015',1,'Science','Amal',1,'Science','Amal',1,'Maths','Kevin','MBA'); 

Then I created a new normalized structure for your data with narrower data types. This helps keep your data clean and also does not waste storage space, which means that requests will be faster because they don’t need to process so much data.

 CREATE TABLE new_test ( dt DATE NULL, hr TINYINT NULL, --holds values between 0 to 255 subj VARCHAR(100) NULL, --plenty big enough. No need for NVARCHAR unless you are using Unicode characters faculty VARCHAR(100) NULL, attendance BIT NULL, dayweek TINYINT NULL, weekmonth TINYINT NULL, section VARCHAR(100) NULL, --not sure what this is sess VARCHAR(100) NULL, --not sure what this is batch CHAR(3) NULL --looks like there are three character codes ); 

Here I am normalizing the data. I am not sure if you have attendance of 2,3,4 etc. If you do in your actual table, then you should fix my code.

Note: I calculated the daily week and week from the date. I did my best if they are incorrect, then feel free to correct them.

 WITH norm_data AS ( SELECT datedif,hour1,subject1,faculty1,attendence1,dayweek,weekmonth,section,[session],batch FROM test UNION ALL SELECT datedif,hour2,subject2,faculty2,attendence1,dayweek,weekmonth,section,[session],batch FROM test UNION ALL SELECT datedif,hour3,subject3,faculty3,attendence1,dayweek,weekmonth,section,[session],batch FROM test UNION ALL SELECT datedif,hour4,subject4,faculty4,attendence1,dayweek,weekmonth,section,[session],batch FROM test UNION ALL SELECT datedif,hour5,subject5,faculty5,attendence1,dayweek,weekmonth,section,[session],batch FROM test ) INSERT INTO new_test SELECT PARSE(datedif AS DATE USING 'de-DE') AS [datedif], hour1, subject1, faculty1, attendence1, DATEPART(WEEKDAY,PARSE(datedif AS DATE USING 'de-DE')) AS dayweek, datepart(day, datediff(day, 0, PARSE(datedif AS DATE USING 'de-DE'))/7 * 7)/7 + 1 AS weekmonth, section, [session], batch FROM norm_data 

Now look at your new table

 SELECT * FROM new_test 

If this is correct, then here's how to rename tables

 EXEC SP_rename @objname = 'test', --if you don't want to drop the old table @newname = 'test_old' EXEC SP_rename @objname = 'new_test', --now give the new table the actual name @newname = 'test' SELECT * FROM test