--Create Table

CREATE TABLE WorkOrders
(
WorkOrderID CHAR(5) NOT NULL,
STEP_NBR INTEGER NOT NULL CHECK (step_nbr BETWEEN 0 AND 1000),
STEP_STATUS CHAR(1) NOT NULL CHECK (step_status IN ('C', 'W')), -- complete, waiting
)
GO

--Insert Data
INSERT INTO WorkOrders(WorkOrderID,STEP_NBR,STEP_STATUS) VALUES
('AA100', 0, 'C'),
('AA100', 1, 'W'),
('AA100', 2, 'W'),
('AA200', 0, 'W'),
('AA200', 1, 'W'),
('AA300', 0, 'C'),
('AA300', 1, 'C')
GO


SELECT WorkOrderID,STEP_NBR,STEP_STATUS FROM WorkOrders

--


---------------------------------------
--Sol 1
---------------------------------------

SELECT workorder_id
FROM WorkOrders
GROUP BY workorder_id
HAVING COUNT(*) =    COUNT(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1 ELSE NULL END) 
                   + COUNT(CASE WHEN step_nbr = 0 AND step_status = 'C' THEN 1 ELSE NULL END)


---------------------------------------
--Sol 2
---------------------------------------
				   
SELECT workorder_id
FROM Projects
GROUP BY workorder_id
HAVING COUNT(step_nbr) = SUM
							(
							CASE
							WHEN step_nbr <> 0 AND step_status = 'W' THEN 1
							WHEN step_nbr = 0 AND step_status = 'C' THEN 1
							ELSE 0 END
							)




--

--Create Table
CREATE TABLE [dbo].[EmployeeSalary]
(
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Salary] [float] NULL,
[DOJ] [datetime] NULL
)
GO

--Insert Data
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (1, N'A', 100, CAST(0x0000A3B800000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (2, N'B', 200, CAST(0x0000A18300000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (3, N'C', 300, CAST(0x0000A2A700000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (4, N'D', 400, CAST(0x00009FFA00000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (5, N'E', 500, CAST(0x00009FF100000000 AS DateTime))
GO

--Verify Data
SELECT * FROM [EmployeeSalary]

--


--Solution 1



;WITH CTE AS
(
     SELECT ID , NAME , Salary , DOJ , 1 Value FROM [EmployeeSalary] WHERE DAY(DOJ) > 15
     UNION ALL
     SELECT r.ID , r.NAME , r.Salary , DATEADD(M,1,r.DOJ) DOJ , Value + 1 FROM [EmployeeSalary] r INNER JOIN CTE on r.ID = CTE.ID
     WHERE Value <= 1 AND DAY(r.DOJ) > 15
)
,CTE2 AS 
(
     SELECT  ID , NAME , Salary , MAX(DOJ) DOJ
     FROM CTE 
     GROUP BY ID,Name,Salary
     UNION ALL SELECT * FROM [EmployeeSalary] WHERE DAY(DOJ) < 15
)
,CTE3 AS
(
     SELECT ID , NAME , Salary , DOJ , 1 Value FROM CTE2 a
     UNION ALL
     SELECT b.ID , b.NAME , b.Salary , DATEADD(D,1,b.DOJ) DOJ , Value + 1 FROM CTE3 b INNER JOIN CTE2 a ON a.ID = b.ID
     WHERE DAY(b.DOJ) < DAY(EOMONTH(b.DOJ))     
)
,CTE4 AS
(
     SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE DESC) rnks FROM CTE3 WHERE DATENAME(DW,DOJ) = 'Friday' 
)
SELECT ID , NAME , Salary , DOJ FROM CTE4 WHERE rnks = 1



--

--Create table
CREATE TABLE FruitCount
(
Name VARCHAR(20)
,Fruit VARCHAR(25)
)
GO

--Insert Data
INSERT INTO FruitCount(Name,Fruit) VALUES
('Neeraj'    ,'MANGO'),
('Neeraj'    ,'MANGO'),
('Neeraj'    ,'MANGO'),
('Neeraj'    ,'APPLE'),
('Neeraj'    ,'ORANGE'),
('Neeraj'    ,'LICHI'),
('Neeraj'    ,'LICHI'),
('Neeraj'    ,'LICHI'),
('Isha'     ,'MANGO'),
('Isha'     ,'MANGO'),
('Isha'     ,'APPLE'),
('Isha'     ,'ORANGE'),
('Isha'     ,'LICHI'),
('Gopal' ,'MANGO'),
('Gopal' ,'MANGO'),
('Gopal' ,'APPLE'),
('Gopal' ,'APPLE'),
('Gopal' ,'APPLE'),
('Gopal' ,'ORANGE'),
('Gopal' ,'LICHI'),
('Mayank'  ,'MANGO'),
('Mayank'  ,'MANGO'),
('Mayank'  ,'APPLE'),
('Mayank'  ,'APPLE'),
('Mayank'  ,'ORANGE'),
('Mayank'  ,'LICHI')

--Verify Data
SELECT Name,Fruit FROM FruitCount

--


SELECT 
	 Name
	,SUM(CASE WHEN Fruit='MANGO' THEN 1 ELSE 0 END) MangoCount 
	,SUM(CASE WHEN Fruit='APPLE' THEN 1 ELSE 0 END) APPLECount 
	,SUM(CASE WHEN Fruit='LICHI' THEN 1 ELSE 0 END) LICHICount 
	,SUM(CASE WHEN Fruit='ORANGE' THEN 1 ELSE 0 END) ORANGECount  
FROM FruitCount
GROUP BY Name

`
--

--CREATE TABLE

CREATE TABLE TestTable 
(
  StudentName VARCHAR(100)
, Course VARCHAR(100)
, Instructor VARCHAR(100)
, RoomNo VARCHAR(100)
)
GO

-- Populate table

INSERT INTO TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'
GO

-- Check orginal data

SELECT StudentName, Course, Instructor, RoomNo
FROM TestTable
GO

--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/

SELECT b.StudentName 
			, STUFF 
				((
				SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
				FROM TestTable a
				WHERE ( a.StudentName = b.StudentName )
				FOR XML PATH('')
				) ,1,2,'') 
				AS cusr
FROM TestTable b
GROUP BY b.StudentName

--

--Create Table

CREATE TABLE TestSplitData
(
 Start DATETIME
,EndDt DATETIME
,Amount INT
)
GO

--Insert Data

INSERT INTO TestSplitData(Start,EndDt,Amount)
VALUES
('14-Apr-14','13-May-14',200),
('15-May-14','16-Jun-14',320)

--Verify Data

SELECT Start,EndDt,Amount FROM TestSplitData