SQL Puzzle | The OrdersAndState puzzle

We need to find out customers who have placed orders in 2017 and for those orders the amount should be equal or greater than $100.
Note that if it has multiple orders all must be at least $100 and the order must be going to IL. Also If there are multiple orders that meet 1 and 2 then all must be going to IL

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

>

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE OrderAndStates
(
	 Customerid INT
	,OrderDate DATETIME 
	,[State] VARCHAR(2)    
	,Orderamount INT
)
GO

INSERT INTO OrderAndStates VALUES
(1,'11/02/2016','IL',100),
(2,'02/05/2017','IL',200),
(3,'06/05/2017','WI',200),
(3,'05/15/2017','WI',200),
(1,'03/02/2017','IL',100)
GO

--

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

Solution – 1

--
SELECT b.*
FROM OrderAndStates b
CROSS APPLY 
( 
	SELECT 1 r FROM OrderAndStates a 
	WHERE a.Customerid = b.Customerid 
	AND (Orderamount) >= 100
	AND State = 'IL' 				
	HAVING MIN(orderdate) >= '20170101'
)r	
--

Output

/*------------------------
SELECT b.*
FROM OrderAndStates b
CROSS APPLY 
( 
	SELECT 1 r FROM OrderAndStates a 
	WHERE a.Customerid = b.Customerid 
	AND (Orderamount) >= 100
	AND State = 'IL' 				
	HAVING MIN(orderdate) >= '20170101'
)r
------------------------*/
Customerid  OrderDate               State Orderamount
----------- ----------------------- ----- -----------
2           2017-02-05 00:00:00.000 IL    200

(1 row(s) affected)


--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | The Merge Puzzle

In this puzzle you have to merge data into FinalReserves table. Data has been frequently coming in Reserves table. We have to read data from Reserves and merge the same into FinalReserves using 3 fields AccountNumber , TransactionDate and SalesRep. It is mandatory to use Merge statement.

Please check out the sample input values and sample expected output below.

Sample Input

Reserves

FinalReserves

Expected Output

FinalReserves

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE dbo.Reserves
(
	 AccountNumber INT ,
	 TransactionDate DATETIME,
	 SalesRep VARCHAR(50),
	 Amount NUMERIC(30,2)
)
GO

INSERT INTO dbo.Reserves VALUES
(1,GETDATE()-4,'Due',100),
(2,GETDATE()-2,'Parsons',100),
(3,GETDATE()-1,'Haverly',1000),
(4,GETDATE()-3,'Jason',100)
GO

CREATE TABLE dbo.FinalReserves
(
	 AccountNumber INT ,
	 TransactionDate DATETIME,
	 SalesRep VARCHAR(50),
	 Amount NUMERIC(30,2)
)
GO

INSERT INTO dbo.FinalReserves VALUES
(2,GETDATE()-2,'Parsons',100),
(3,GETDATE()-1,'Haverly',1000)
GO

SELECT * FROM dbo.FinalReserves
SELECT * FROM dbo.Reserves
--

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

Solution – 1

--


MERGE dbo.FinalReserves AS T
USING dbo.Reserves  AS S
ON (T.AccountNumber = S.AccountNumber AND CAST(T.TransactionDate AS DATE) = CAST(S.TransactionDate AS DATE) and  T.SalesRep = S.SalesRep)
WHEN NOT MATCHED 
    THEN INSERT(AccountNumber, TransactionDate,SalesRep,Amount) 
    VALUES(S.AccountNumber, S.TransactionDate,S.SalesRep,S.Amount)
WHEN MATCHED 
    THEN UPDATE SET T.Amount = S.Amount
;
GO



--

Output

/*------------------------
MERGE dbo.FinalReserves AS T
USING dbo.Reserves  AS S
ON (T.AccountNumber = S.AccountNumber AND CAST(T.TransactionDate AS DATE) = CAST(S.TransactionDate AS DATE) and  T.SalesRep = S.SalesRep)
WHEN NOT MATCHED 
    THEN INSERT(AccountNumber, TransactionDate,SalesRep,Amount) 
    VALUES(S.AccountNumber, S.TransactionDate,S.SalesRep,S.Amount)
WHEN MATCHED 
    THEN UPDATE SET T.Amount = S.Amount
;
GO
------------------------*/

(4 row(s) affected)


/*------------------------
SELECT * FROM dbo.FinalReserves
------------------------*/
AccountNumber TransactionDate         SalesRep                                           Amount
------------- ----------------------- -------------------------------------------------- ---------------------------------------
2             2017-10-05 14:24:20.463 Parsons                                            100.00
3             2017-10-06 14:24:20.463 Haverly                                            1000.00
1             2017-10-03 14:24:20.453 Due                                                100.00
4             2017-10-04 14:24:20.453 Jason                                              100.00

(4 row(s) affected)



--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Get DB Name,LogFile Size, MDB File Size and Owner from System Tables

In this puzzle you have to write a query to fetch database_name, the size of the log file, mdb file and the owner of the database using system tables.

Sample Input

NA…

Expected Output

Script

Use below script to create table and insert sample data into it.

--

NA

--

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

Solution – 1

--


SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))   
    , DB_Size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	, r.[Owner]
FROM sys.master_files m
CROSS APPLY
(
	SELECT 
	suser_sname( owner_sid ) [Owner],
	name DBName 
	FROM sys.databases t
	WHERE t.name = DB_NAME(m.database_id)
)r
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id,r.[Owner]


--

Output

/*------------------------
SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))   
    , DB_Size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	, r.[Owner]
FROM sys.master_files m
CROSS APPLY
(
	SELECT 
	suser_sname( owner_sid ) [Owner],
	name DBName 
	FROM sys.databases t
	WHERE t.name = DB_NAME(m.database_id)
)r
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id,r.[Owner]
------------------------*/
database_name                                                                                                                    log_size_mb                             DB_Size_mb                              Owner
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SAP_INT                                                                                                                          8.00                                    80.00                                   xxx\pawan
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)



--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | The Date Format (yyyy-MM-dd hh:mm:ss) Puzzle

In this puzzle you have to take getdate as input and then remove time from it and then add 23:59:59 into that date and provide the output i format yyyy-MM-dd hh:mm:ss.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

SELECT GETDATE() AS CurrentDate

--

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

Solution – 1

--


SELECT GETDATE() CurrentDate , LEFT(CONVERT(VARCHAR, GETDATE(), 20), 10) + ' 23:59:59' ChangedFormat

--

Output

/*------------------------
SELECT GETDATE() CurrentDate , LEFT(CONVERT(VARCHAR, GETDATE(), 20), 10) + ' 23:59:59' ChangedFormat
------------------------*/
CurrentDate             ChangedFormat
----------------------- -------------------
2017-10-07 13:19:15.800 2017-10-07 23:59:59

(1 row(s) affected)


--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com