SQL Puzzle

/*
Update one column in Table1... But that value should not be updated in Table1..But it wil be updated to table2...
Try to create trigger for this process
*/

--Solution:

--As of now This block is empty.. Copy your answer here with your name:

/*Use Instead of Trigger for this Puzzle*/

DROP TABLE TEMP_RECORDS
DROP TABLE TEMP_RECORDS_2
CREATE TABLE TEMP_RECORDS
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

INSERT INTO TEMP_RECORDS
SELECT 'Vicky'


CREATE TABLE TEMP_RECORDS_2
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

INSERT INTO TEMP_RECORDS_2
SELECT 'Vicky'


ALTER TRIGGER INSTEAD_TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
INSTEAD OF UPDATE
AS
BEGIN

DECLARE @ename VARCHAR(100)
SELECT @ename = E_NAME FROM INSERTED /*Magic table used here*/

UPDATE TEMP_RECORDS_2
SET E_NAME = @ename

END

/*Update First Table Record*/
UPDATE TEMP_RECORDS
SET E_NAME = 'Moov'

/*Check Records*/
SELECT * FROM TEMP_RECORDS
SELECT * FROM TEMP_RECORDS_2

*******************************************************
CREATE TABLE Date_Ranges
(
EMP_NO INT,
STARTDATE DATETIME,
ENDDATE DATETIME
)

INSERT INTO Date_Ranges
SELECT 101,'01/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 102,'02/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 103,'01/01/2014','03/31/2014'

INSERT INTO Date_Ranges
SELECT 104,'12/01/2013','01/31/2014'

INSERT INTO Date_Ranges
SELECT 105,'12/01/2013','12/31/2013'

INSERT INTO Date_Ranges
SELECT 106,'06/01/2014','07/31/2014'

INSERT INTO Date_Ranges
SELECT 107,'02/01/2014','03/31/2014'

/*Sample Input*/
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '02/01/2014'
SET @EndDate = '05/01/2014'

/*Answer*/

*********************************************************************
CREATE TABLE Date_Ranges
(
EMP_NO INT,
STARTDATE DATETIME,
ENDDATE DATETIME
)

INSERT INTO Date_Ranges
SELECT 101,'01/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 102,'02/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 103,'01/01/2014','03/31/2014'

INSERT INTO Date_Ranges
SELECT 104,'12/01/2013','01/31/2014'

INSERT INTO Date_Ranges
SELECT 105,'12/01/2013','12/31/2013'

INSERT INTO Date_Ranges
SELECT 106,'06/01/2014','07/31/2014'

INSERT INTO Date_Ranges
SELECT 107,'02/01/2014','03/31/2014'

/*Sample Input*/
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '02/01/2014'
SET @EndDate = '05/01/2014'

/*Answer*/

SELECT * FROM DATE_RANGES

SELECT * FROM DATE_RANGES
WHERE STARTDATE <= @EndDate AND ENDDATE >= @StartDate

SELECT * FROM DATE_RANGES
WHERE NOT (STARTDATE <= @EndDate AND ENDDATE >= @StartDate)

*******************************************************
/*Method 1*/
DROP TABLE #TEMP_CLAIM

CREATE TABLE #TEMP_CLAIM
(
PATID INT,
CLAIMID INT
)

INSERT INTO #TEMP_CLAIM
SELECT 123,1 UNION SELECT 123,2 UNION SELECT 123,3

DROP TABLE #TEMP_LEDGER

CREATE TABLE #TEMP_LEDGER
(
PATIENTID INT,
LEDGERID INT,
AMOUNT MONEY,
PRI_CLAIM_ID INT,
SEC_CLAIM_ID INT,
TER_CLAIM_ID INT
)

SELECT * FROM #TEMP_CLAIM
SELECT * FROM #TEMP_LEDGER

INSERT INTO #TEMP_LEDGER
SELECT 123,100,10,1,2,3 UNION SELECT 123,101,20,0,2,0 UNION SELECT 123,102,30,1,2,0

--Answer :

SELECT PATID,CLAIMID,
SUM( CASE WHEN X.AMOUNT IS NOT NULL THEN X.AMOUNT WHEN Y.AMOUNT IS NOT NULL THEN Y.AMOUNT WHEN Z.AMOUNT IS NOT NULL THEN Z.AMOUNT ELSE 0 END)  AS AMOUNT FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
where PATID is not null
GROUP BY PATID,CLAIMID

--Explanation:
/*Understanding FULL outer Join*/
--Step:1
SELECT * FROM #TEMP_LEDGER where PRI_CLAIM_ID<>0
SELECT * FROM #TEMP_CLAIM where CLAIMID = 1
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
/*Check the NULL values in both part of table*/

--Step:2
SELECT * FROM #TEMP_LEDGER where PRI_CLAIM_ID<>0
SELECT * FROM #TEMP_CLAIM where CLAIMID = 2
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
/*Check the NULL values in both part of table*/

--Step:3
SELECT * FROM #TEMP_LEDGER where PRI_CLAIM_ID<>0
SELECT * FROM #TEMP_CLAIM where CLAIMID = 2
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
/*Check the NULL values in both part of table*/

--Step:4
/*Now Combine All the three Joins*/
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID


/*Now Exclude NULL value in Left table */
SELECT PATID, CLAIMID,X.AMOUNT, Y.AMOUNT, Z.AMOUNT  FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
where PATID is not null

--Final Result!
/*Now  group the values*/
SELECT PATID,CLAIMID,
SUM( CASE WHEN X.AMOUNT IS NOT NULL THEN X.AMOUNT WHEN Y.AMOUNT IS NOT NULL THEN Y.AMOUNT WHEN Z.AMOUNT IS NOT NULL THEN Z.AMOUNT ELSE 0 END)  AS AMOUNT FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
where PATID is not null
GROUP BY PATID,CLAIMID

/*
Note :
Full Outer Join Matches each rows in Left table to Right table and Right Table to Left Table.
If any of the Rows not matched, it reurns Null rows in next table.
So we can, filter the final result for required vaule with NOT NULL condition in where clause!
*/

/*Method 2*/

SELECT CLAIMID,ISNULL(X.AMOUNT,0) FROM #TEMP_CLAIM
LEFT OUTER JOIN (
SELECT PRI_CLAIM_ID,SUM(AMOUNT) AS AMOUNT FROM #TEMP_LEDGER WHERE PRI_CLAIM_ID <> 0
GROUP BY PRI_CLAIM_ID
UNION
SELECT SEC_CLAIM_ID,SUM(AMOUNT) FROM #TEMP_LEDGER WHERE SEC_CLAIM_ID <> 0
GROUP BY SEC_CLAIM_ID
UNION
SELECT TER_CLAIM_ID,SUM(AMOUNT) FROM #TEMP_LEDGER WHERE TER_CLAIM_ID <> 0
GROUP BY TER_CLAIM_ID
) X ON CLAIMID = PRI_CLAIM_ID

/*Please Check; Why Left outer join and ISNULL used here?*/

/*Method 3*/ /*Tricky and Fun one: Amazing use of Group By clause and aggregate functions!*/

/*For Latest claim Only - Giving to understand the use of Group by and aggregate!*/
SELECT PATIENTID,MAX(PRI_CLAIM_ID) AS PRI_CLAIM_ID ,SUM(AMOUNT * CASE WHEN PRI_CLAIM_ID =0 THEN 0 ELSE 1 END) AS PRIMARY_AMOUNT,
MAX(SEC_CLAIM_ID) AS  SEC_CLAIM_ID,SUM(AMOUNT * CASE WHEN SEC_CLAIM_ID =0 THEN 0 ELSE 1 END) AS SEC_AMOUNT,
MAX(TER_CLAIM_ID) AS TER_CLAIM_ID,SUM(AMOUNT * CASE WHEN TER_CLAIM_ID =0 THEN 0 ELSE 1 END) AS TER_AMOUNT
FROM #TEMP_LEDGER
GROUP BY PATIENTID

/*Method 4 - Priyanga and Divya did this! - Really a worth one compared to above all!*/
SELECT CLAIMID, SUM(AMOUNT) FROM #TEMP_CLAIM
INNER JOIN #TEMP_LEDGER ON PATIENTID=PATID
WHERE (CLAIMID=PRI_CLAIM_ID) OR (CLAIMID=SEC_CLAIM_ID) OR (CLAIMID=TER_CLAIM_ID)
GROUP BY CLAIMID

/*You can achieve too many methods to derive required results! But choose which one is unique and different and better performance! :-) */

 /* :-) Have a Nice day!!! */

***************************************************************

What is the differnce between CAST and VARCHAR?

Why these below two queries gives, two different results?

SELECT CAST( GETDATE() AS VARCHAR(112))

SELECT CONVERT(VARCHAR,GETDATE(),112)


One more information is there if u answer it..

Ans:

Cast and Varchar both are same  and converting one datatype to another one!... here in first statement 112 is size... in 2nd one 112 is style.... style always used with datetime only!


One more Info...select convert(varchar,'123456789123456789123456789123456789') what will be the result for this

So be cautious when u convert to varchar,,, default  size is 30.. you ll loss the data!

***********************************************************************

Write a program that prints the numbers from 1 to 100. But for multiples of three print “M-Three” 

instead of the number and for the multiples of five print “M-Five”. For numbers which are multiples 

of both three and five print “M-Three-Five”.

Result Set Example:

1

2

M-Three

4

M-Five 

M-Three 

7

8

M-Three 

M-Five 

11

M-Three 

13

14

M-Three-Five

....

...

..*********************************************************

DECLARE @var BIGINT

DECLARE @FactNo BIGINT

SET @var = 1

SET @FactNo = 12 /*Input*/

SELECT TOP (@FactNo) @var =  ROW_NUMBER() OVER (ORDER BY id) * @var FROM SYSOBJECTS 

SELECT @var

**********************************************************