Daily Archives: March 23, 2015

T-SQL Query | [ The Patient Puzzle ]

23 Monday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 5 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Patient Puzzle ]  – In this puzzle we have to group data based on Patients admission date and discharge date. If any Patients discharge date + 1 = admission date then we have group both rows into one row and sum costs from both the rows. Please check out the sample input and expected output for details.

Sample Input

PatientID AdmissionDate DischargeDate Cost
1009 27-07-2014 31-07-2014 1050
1009 01-08-2014 23-08-2014 1070
1009 31-08-2014 31-08-2014 1900
1009 01-09-2014 14-09-2014 1260
1009 01-12-2014 31-12-2014 2090
1024 07-06-2014 28-06-2014 1900
1024 29-06-2014 31-07-2014 2900
1024 01-08-2014 02-08-2014 1800

Expected Output

PatientId AdminssionDate DischargeDate Cost
1009 27-07-2014 23-08-2014 2120
1009 31-08-2014 14-09-2014 3160
1009 01-12-2014 31-12-2014 2090
1024 07-06-2014 02-08-2014 6600

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table

CREATE TABLE PatientProblem
(
PatientID INT,
AdmissionDate DATETIME,
DischargeDate DATETIME,
Cost MONEY
)
GO

--Insert Data
INSERT INTO PatientProblem(PatientID,AdmissionDate,DischargeDate
,Cost)
VALUES
(1009,'2014-07-27','2014-07-31',1050.00),
(1009,'2014-08-01','2014-08-23',1070.00),
(1009,'2014-08-31','2014-08-31',1900.00),
(1009,'2014-09-01','2014-09-14',1260.00),
(1009,'2014-12-01','2014-12-31',2090.00),
(1024,'2014-06-07','2014-06-28',1900.00),
(1024,'2014-06-29','2014-07-31',2900.00),
(1024,'2014-08-01','2014-08-02',1800.00)

--Verify Data
SELECT PatientID,AdmissionDate,DischargeDate,Cost 
FROM PatientProblem

Please leave a comment if you need solution to the above puzzle

Keep Learning

T-SQL Query | [ The Case Replace Puzzle ]

23 Monday Mar 2015

Posted by in SQL SERVER, T SQL Puzzles

≈ 3 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Case Replace Puzzle ] – In this puzzle we have to write the query without “Case” statement. You can use IIF and Choose statement or any other more suitable statement to get the output. Please check out the sample input and expected output for details.

Sample Input

Vals
125
350
270
140

Expected Output ( Other than Case statement )

ValCase
100
300
200
100

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table
CREATE TABLE [dbo].[RangeValues]
(
[Vals] [int] NULL
)
GO

--Insert Data
INSERT INTO [RangeValues](Vals)
VALUES
(125),
(350),
(270),
(140)

--Verify Data
SELECT Vals FROM [RangeValues]

--Sample Query with Case Statement

SELECT Vals
,CASE Vals
WHEN 125 then 100
WHEN 350 then 300
WHEN 270 then 200
WHEN 140 then 100
END ValCase
FROM [dbo].[RangeValues]

UPDATE – 24-Apr-2015 – Solution 1 & 2 


--

--SOLUTION 1 | USING IF
SELECT Vals 
,IIF ( Vals = 125 , '100' , IIF (Vals = 350 , '300' , IIF( Vals = 270 , '200', IIF( Vals = 140 , '100', Vals )))) as ValCase
FROM [dbo].[RangeValues]


--SOLUTION 2 | USING CHOOSE
SELECT Vals 
,CHOOSE ( Vals / 100, '100', '200' , '300' , 'N' ) ValCase
FROM [dbo].[RangeValues]

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

T-SQL Query | [ The Running Balance Puzzle ]

23 Monday Mar 2015

Posted by in Integration Services, T SQL Puzzles

≈ 9 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Running Balance Puzzle ] – In this puzzle we have to find the running balance like we have in our bank transaction statements. Please check out the sample input and expected output for details

Sample Input

Dt CID TransactionType Amount
01-01-2015 IshaMattoo CR 18000
02-01-2015 IshaMattoo DB 3000
03-01-2015 IshaMattoo CR 8000
04-01-2015 IshaMattoo DB 9000
04-01-2015 AvtaarKishan CR 1000
05-01-2015 AvtaarKishan CR 6000
05-01-2015 AvtaarKishan DB 5000

Expected Output

Dt CID TransactionType Amount FindRunningBalance
04-01-2015 AvtaarKishan CR 1000 1000
05-01-2015 AvtaarKishan CR 6000 7000
05-01-2015 AvtaarKishan DB 5000 2000
01-01-2015 IshaMattoo CR 18000 18000
02-01-2015 IshaMattoo DB 3000 15000
03-01-2015 IshaMattoo CR 8000 23000
04-01-2015 IshaMattoo DB 9000 14000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solutions in the comments section or send you solutions to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table
CREATE TABLE FindRunningBalance
(
Dt DATETIME,
CID VARCHAR(50),
TransactionType VARCHAR(2),
Amount MONEY
)

--Insert data
INSERT INTO FindRunningBalance(Dt,CID,TransactionType,Amount)
VALUES
( '2015-01-01','IshaMattoo','CR','18,000' ),
( '2015-01-02','IshaMattoo','DB','3,000' ),
( '2015-01-03','IshaMattoo','CR','8,000' ),
( '2015-01-04','IshaMattoo','DB','9,000' ),
( '2015-01-04','AvtaarKishan','CR','1,000' ),
( '2015-01-05','AvtaarKishan','CR','6,000'),
( '2015-01-05','AvtaarKishan','DB','5,000' )

--Verify Data
SELECT Dt,CID,TransactionType,Amount FROM FindRunningBalance

UPDATE – 24-Apr-2015 – Solution 1 


--

;WITH CTE AS 
(
	SELECT Dt , CID , TransactionType 
			, CASE WHEN TransactionType = 'PAY' THEN AMOUNT * -1 ELSE AMOUNT END Amount, Amount Amt
			,ROW_NUMBER() OVER (PARTITION BY CID ORDER BY (SELECT NULL)) rnk
	FROM FindRunningBalance
)
SELECT  Dt , CID , TransactionType , Amt Amount
, (SELECT SUM(Amount) FROM CTE c2 WHERE c2.CID = c1.CID AND c2.rnk <= c1.rnk ) FindRunningBalance
FROM CTE c1

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

T-SQL Query | [ The Candidate Joining Puzzle ]

23 Monday Mar 2015

Posted by in T SQL Puzzles

≈ 10 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Candidate Joining Puzzle ] – In this puzzle we have to find out the valid candidate joining date for each candidate. E.g if you check for CID the joining date is 10-01-2015 and as per the company’s holiday table they have holiday. So in this case we have to prepone the joining by one day. Hence for CJ10101 the valid joining date would be 08-01-2015 as they have holiday on 09-01-2015 also. Please check out the sample input and expected output for details

Sample Input

CandidateJoining
CId CJoiningDate
CJ10101 10-01-2015
CJ10104 10-01-2015
CJ10105 18-02-2015
CJ10121 11-03-2015
CJ10198 11-04-2015
Holidays
ID HolidayDate
101 10-01-2015
102 09-01-2015
103 19-02-2015
104 11-03-2015
105 11-04-2015

Expected Output

CId CJoiningDate ValidJoiningDate
CJ10101 10-01-2015 08-01-2015
CJ10104 10-01-2015 08-01-2015
CJ10105 18-02-2015 18-02-2015
CJ10121 11-03-2015 10-03-2015
CJ10198 11-04-2015 10-04-2015

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table
CREATE TABLE Holidays
(
ID INT
,HolidayDate DATETIME
)
GO

--Insert Data
INSERT INTO Holidays(ID,HolidayDate)
VALUES
(101,'01/10/2015'),
(102,'01/09/2015'),
(103,'02/19/2015'),
(104,'03/11/2015'),
(105,'04/11/2015')

--Verify Data
SELECT ID,HolidayDate FROM Holidays

--Create Table
CREATE TABLE CandidateJoining
(
CId VARCHAR(17)
,CJoiningDate DATETIME
)
GO

--Insert Data
INSERT INTO CandidateJoining(CId,CJoiningDate)
VALUES
('CJ10101','01/10/2015'),
('CJ10104','01/10/2015'),
('CJ10105','02/18/2015'),
'CJ10121','03/11/2015'),
('CJ10198','04/11/2015')

--Verify Data
SELECT CId,CJoiningDate FROM CandidateJoining

UPDATE – 24-Apr-2015 – Solution 1 


--

;WITH CTE AS
(
	SELECT MIN(HolidayDate) MinDate , MAX(HolidayDate) MaxDate FROM 
	(
		SELECT * , DAY(HolidayDate) - ROW_NUMBER() OVER (ORDER BY HolidayDate ASC) rnk FROM Holidays
	) a GROUP BY rnk 
)
SELECT CId , CASE WHEN MinDate IS NULL THEN CJoiningDate ELSE MinDate -1 END CandidateJoining FROM CandidateJoining j
LEFT JOIN CTE c ON j.CJoiningDate BETWEEN c.MinDate AND c.MaxDate
--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

T-SQL Query | The Ticket Approver Puzzle

23 Monday Mar 2015

Posted by in T SQL Puzzles

≈ Leave a comment

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ SQL | The Ticket Approver Puzzle ] – In the puzzle we have ticket details and approver. We have to replace the CreatedBy and ApprovedBy from Name in the approver table. Please check out the sample input and expected output for details.

Sample Input

Table – Approver

ID Name
1001 Ram
1002 Ramesh
1003 Supriya
1004 Sharlee

 

Table – TicketDetails

 

TicketID CreatedBy ApprovedBy
1 1001 1004
2 1001 1004
3 1002 1004
4 1001 1003

Expected Output

TicketID CreatedBy ApprovedBy
1 Ram Sharlee
2 Ram Sharlee
3 Ramesh Sharlee
4 Ram Supriya

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE Approver
(
ID INT
,Name VARCHAR(10)
)
GO


--Insert data
INSERT INTO Approver(ID,Name)
VALUES (1001,'Ram') , (1002,'Ramesh') , (1003,'Supriya') ,(1004,'Sharlee')


--Verify data
SELECT ID,Name FROM Approver


--Create table
CREATE TABLE TicketDetails
(
TicketID INT
,CreatedBy INT
,ApprovedBy INT
)
GO

--Insert data
INSERT INTO TicketDetails(TicketID,CreatedBy,ApprovedBy)
VALUES
(1,1001,1004),
(2,1001,1004),
(3,1002,1004),
(4,1001,1003)

--Verify data
SELECT TicketID,CreatedBy,ApprovedBy FROM TicketDetails

UPDATE – 24-Apr-2015 – Solution 1 


--

SELECT 
	t.TicketID,a.Name CreatedBy , b.Name ApprovedBy 
FROM 
TicketDetails t 
	INNER JOIN Approver a ON a.ID = t.CreatedBy
	INNER JOIN Approver b ON b.ID = t.ApprovedBy
ORDER BY t.TicketID 


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com