Advertisements

SQL Puzzle | The BCP OUT with Field Headers Puzzle

Tags

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


SQL Puzzle | The BCP-OUT with Field Headers Puzzle

In this puzzle you have to read data from a table and use Bcp Out command using xp_cmdshell command. The text file should have column names as well as data from the table. Please check out the sample input values and sample expected output below.

Sample Input

1 Pawan
2 Sharlee
3 Jason
1 Pawan
2 Sharlee
3 Jason

Expected Output

Field1 Field2
1 Pawan
2 Sharlee
3 Jason
1 Pawan
2 Sharlee
3 Jason

Script

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

--

CREATE TABLE test_SQL
(
     Id INT
     ,Val VARCHAR(100)
)
GO

INSERT INTO test_SQL VALUES ( 1,'Pawan' ) , ( 2 , 'Sharlee' ) , (3,'Jason' )
GO

INSERT INTO test_SQL VALUES ( 1,'Pawan' ) , ( 2 , 'Sharlee' ) , (3,'Jason' )
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


--

SET NOCOUNT ON;
       
DECLARE 
     @filename varchar(150),
     @query varchar(5000),
     @bcp varchar(5000),
     @year char(4) = '2015',
     @month varchar(7) = 'March',
     @debug bit = 0
     
SET @filename = '"C:\Pawan\tblName_' + @month + '_' + @year + '.txt" -c -T'

/* BELOW CODE should be on 1 LINE */

SET @query = '" SELECT ''''field1'''',''''field2'''' UNION ALL SELECT CAST(Id AS VARCHAR(15)),CAST(Val AS VARCHAR(100)) FROM HMIS_Mayank.dbo.test_SQL "'

SET @bcp = ' EXEC master..xp_cmdshell '' bcp ' + @query + ' queryout ' + @filename + ''''

EXEC ( @bcp )

SET NOCOUNT OFF;

--

Output

--

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
Starting copy...
NULL
7 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (7000.00 rows per sec.)
NULL


--

OUTPUT -Image text file

Text file showing data with Field Headers

Text file showing data with Field Headers

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

Advertisements

SQL Puzzle | The Additional Quantity Puzzle

Tags

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


SQL Puzzle | The Additional Quantity Puzzle

In this puzzle you have to generate a new column called AdditionQty. When the value of a orderid is 0 then the first row of that advice should have the value of Qty other wise just show NULL. Please check out the sample input values and sample expected output below.

Sample Input

AdviceId DetailId OrderId ItemId Qty
19140 76841 73036 6194 400
19140 76842 73037 437 500
19140 76843 73038 6194 550
19140 76844 0 6194 100
19141 76845 10001 6194 100
19141 76846 10002 6100 50
19141 76847 10004 6194 50
19142 76848 10006 6100 50
19142 76849 10006 6100 50
19142 76850 0 437 10
19143 76851 10101 250 100
19143 76851 10102 251 20
19143 76853 10103 252 10
19143 76853 0 253 5

Expected Output

AdviceId DetailId OrderId ItemId Qty AdditionQty
19140 76841 73036 6194 400 100
19140 76842 73037 437 500 NULL
19140 76843 73038 6194 550 NULL
19140 76844 0 6194 100 NULL
19141 76845 10001 6194 100 NULL
19141 76846 10002 6100 50 NULL
19141 76847 10004 6194 50 NULL
19142 76848 10006 6100 50 10
19142 76849 10006 6100 50 NULL
19142 76850 0 437 10 NULL
19143 76851 10101 250 100 5
19143 76851 10102 251 20 NULL
19143 76853 10103 252 10 NULL
19143 76853 0 253 5 NULL

Script

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

--

CREATE TABLE Advices
(
	 AdviceId INT
	,DetailId INT 
	,OrderId INT 
	,ItemId INT 
	,Qty INT
)

INSERT INTO Advices VALUES
(19140,76841,73036,6194,400),
(19140,76842,73037,437,500),
(19140,76843,73038,6194,550),
(19140,76844,0,6194,100),
(19141,76845,10001,6194,100),
(19141,76846,10002,6100,50),
(19141,76847,10004,6194,50),
(19142,76848,10006,6100,50),
(19142,76849,10006,6100,50),
(19142,76850,0,437,10),
(19143,76851,10101,250,100),
(19143,76851,10102,251,20),
(19143,76853,10103,252,10),
(19143,76853,0,253,5)
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 AdviceId  ,  DetailId  ,  OrderId ,    ItemId    ,  r.Qty
	, CASE WHEN rnk = 1 THEN rq.Qty ELSE NULL END AdditionQty 
	FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY AdviceId ORDER BY DetailId) rnk
	FROM Advices
)r
OUTER APPLY 
(
	SELECT TOP 1 QTY FROM Advices a
	WHERE a.AdviceId = r.AdviceId 
	AND a.OrderId = 0
)rq


--

Output

--

/*------------------------
OUTPUT 
------------------------*/
AdviceId    DetailId    OrderId     ItemId      Qty         AdditionQty
----------- ----------- ----------- ----------- ----------- -----------
19140       76841       73036       6194        400         100
19140       76842       73037       437         500         NULL
19140       76843       73038       6194        550         NULL
19140       76844       0           6194        100         NULL
19141       76845       10001       6194        100         NULL
19141       76846       10002       6100        50          NULL
19141       76847       10004       6194        50          NULL
19142       76848       10006       6100        50          10
19142       76849       10006       6100        50          NULL
19142       76850       0           437         10          NULL
19143       76851       10101       250         100         5
19143       76851       10102       251         20          NULL
19143       76853       10103       252         10          NULL
19143       76853       0           253         5           NULL

(14 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 | Average Time Puzzle

Tags

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


SQL Puzzle | Average Time Puzzle

In this puzzle you have to find the average a column, this column contains time data. Please check out the sample input values and sample expected output below.

Sample Input

a
03:59:00.0000000
05:13:00.0000000

Expected Output

AvgTime
04:36:00.0000000

Script

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

--

SELECT a  FROM
(
	SELECT CAST('03:59:00.0000' AS TIME) a UNION ALL
	SELECT CAST('05:13:00.0000' AS TIME) a
)i

--

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 cast(cast(avg(cast(CAST(a as datetime) as float)) as datetime) as time) AvgTime FROM
(
	SELECT CAST('03:59:00.0000' AS TIME) a UNION ALL
	SELECT CAST('05:13:00.0000' AS TIME) a
)i

--

Output

--

/*------------------------
SELECT cast(cast(avg(cast(CAST(a as datetime) as float)) as datetime) as time) AvgTime FROM
(
	SELECT CAST('03:59:00.0000' AS TIME) a UNION ALL
	SELECT CAST('05:13:00.0000' AS TIME) a
)i
------------------------*/
AvgTime
----------------
04:36:00.0000000

(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 3 Integers with 2 Dots Puzzle

Tags

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


SQL Puzzle | The 3 Integers 2 Dots Puzzle

In this puzzle you have to read a column called ipadr and find out data which is in the format of Interger.Integer.Integer. Note that the decimal points are mandatory in this case. Please check out the sample input values and sample expected output below.

Sample Input

ID ipadr rating
1 10.11.44 OK
2 1.1 not enough points
3 2.3.1 OK
4 a.b.c letters instead of numbers
5 12..78 wrong – two points
8 $.1.1 Ok
9 1aaa.2bbb.3ccc bad, letters
10 1.2.3.4 bad, too many periods

Expected Output

ID ipadr rating
1 10.11.44 OK
3 2.3.1 OK

Script

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

--

Declare @test As Table(ID int, ipadr Varchar(100), rating Varchar(50));

INSERT INTO @test VALUES (1,'10.11.44','OK')
   ,                     (2,'1.1','not enough points')
   ,                     (3,'2.3.1','OK')
   ,                     (4,'a.b.c','letters instead of numbers')
   ,                     (5,'12..78','wrong - two points')
   ,			 (8,'$.1.1','Ok')
   ,                     (9,'1aaa.2bbb.3ccc','bad, letters')
   ,                     (10,'1.2.3.4','bad, too many periods')

--

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 * from @test WHERE ipadr LIKE '[0-9]%.[0-9]%.[0-9]%'
AND TRY_CAST( REPLACE(ipadr,'.','') AS BIGINT ) IS NOT NULL AND DATALENGTH(ipadr)  - DATALENGTH(REPLACE(ipadr,'.','')) = 2

--

Solution – 1


--

SELECT Id,ipadr,rating 
FROM 
(
    SELECT * , 
     SUBSTRING( ipadr, 0 , CHARINDEX ( '.' , ipadr , 0 ) ) a
    ,SUBSTRING(ipadr, CHARINDEX ( '.' , ipadr , 0 )+1 , CHARINDEX ( '.', ipadr ,CHARINDEX ( '.' , ipadr , 0 )  )-1  ) b
    ,SUBSTRING(ipadr, CHARINDEX ( '.', ipadr ,CHARINDEX ( '.' , ipadr , 0 ))+ LEN(SUBSTRING(ipadr, CHARINDEX ( '.' , ipadr , 0 )+1 
	    , CHARINDEX ( '.', ipadr ,CHARINDEX ( '.' , ipadr , 0 )  )-1  ))+2, LEN(ipadr) ) c
    FROM @test
)t 
WHERE TRY_CAST(a AS BIGINT) IS NOT NULL AND TRY_CAST(b AS BIGINT) IS NOT NULL AND TRY_CAST(c AS BIGINT) IS NOT NULL
AND DATALENGTH(ipadr)  - DATALENGTH(REPLACE(ipadr,'.','')) = 2

--

Output

--

/*------------------------
OUTPUT
------------------------*/

(8 row(s) affected)
ID          ipadr        rating
----------- ------------ --------------------------------------------------
1           10.11.44     OK
3           2.3.1        OK

(2 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 Previous and Next Value Puzzle

Tags

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


SQL Puzzle | Get Previous and Next Value Puzzle

In this puzzle you have to Get the previous and next value of the Value column. Please check out the sample input values and sample expected output below.

Sample Input

Source ObjectID Key Value AuditAction AuditDate
Compounds 1 CompoundName Comp A I 2017-10-24 16:50:50.100
Compounds 1 Dosage 125.50 I 2017-10-24 16:50:50.100
Compounds 1 Route IT I 2017-10-24 16:50:50.100
Compounds 1 CompoundName Comp A U 2017-10-24 17:50:50.100
Compounds 1 Dosage 160.50 U 2017-10-24 17:50:50.100
Compounds 1 Route IT U 2017-10-24 17:50:50.100
Compounds 1 CompoundName Comp A U 2017-10-24 18:50:50.100
Compounds 1 Dosage 130.50 U 2017-10-24 18:50:50.100
Compounds 1 Route IV U 2017-10-24 18:50:50.100

Expected Output

Source ObjectID Key From To AuditAction AuditDate
Compounds 1 CompoundName NULL Comp A I 2017-10-24 16:50:50.100
Compounds 1 Dosage NULL 125.50 I 2017-10-24 16:50:50.100
Compounds 1 Route NULL IT I 2017-10-24 16:50:50.100
Compounds 1 Dosage 125.50 160.50 U 2017-10-24 17:50:50.100
Compounds 1 Dosage 160.50 130.50 U 2017-10-24 18:50:50.100
Compounds 1 Route IT IV U 2017-10-24 18:50:50.100

Script

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

--

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VW_AuditHistory](
	[Source] [varchar](9) NOT NULL,
	[ObjectID] [int] NOT NULL,
	[Key] [varchar](12) NOT NULL,
	[Value] [varchar](6) NOT NULL,
	[AuditAction] [varchar](1) NOT NULL,
	[AuditDate] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'CompoundName', N'Comp A', N'I', CAST(0x0000A8160115A256 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Dosage', N'125.50', N'I', CAST(0x0000A8160115A256 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Route', N'IT', N'I', CAST(0x0000A8160115A256 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'CompoundName', N'Comp A', N'U', CAST(0x0000A81601261D16 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Dosage', N'160.50', N'U', CAST(0x0000A81601261D16 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Route', N'IT', N'U', CAST(0x0000A81601261D16 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'CompoundName', N'Comp A', N'U', CAST(0x0000A816013697D6 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Dosage', N'130.50', N'U', CAST(0x0000A816013697D6 AS DateTime))
GO
INSERT [dbo].[VW_AuditHistory] ([Source], [ObjectID], [Key], [Value], [AuditAction], [AuditDate]) VALUES (N'Compounds', 1, N'Route', N'IV', N'U', CAST(0x0000A816013697D6 AS DateTime))
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 * FROM 
(
	select v.Source, v.ObjectID, v.[Key], lag(Value) over(partition by source,[key] order by AuditDate) [From],
	v.Value [To], v.AuditAction, v.AuditDate
	from VW_AuditHistory v
)u WHERE ([From] <> [To] or [From] IS NULL)
order by AuditDate, [Key]
--

Output

--

/*------------------------
SELECT * FROM 
(
	select v.Source, v.ObjectID, v.[Key], lag(Value) over(partition by source,[key] order by AuditDate) [From],
	v.Value [To], v.AuditAction, v.AuditDate
	from VW_AuditHistory v
)u WHERE ([From] <> [To] or [From] IS NULL)
order by AuditDate, [Key]
------------------------*/
Source    ObjectID    Key          From   To     AuditAction AuditDate
--------- ----------- ------------ ------ ------ ----------- -----------------------
Compounds 1           CompoundName NULL   Comp A I           2017-10-24 16:50:50.100
Compounds 1           Dosage       NULL   125.50 I           2017-10-24 16:50:50.100
Compounds 1           Route        NULL   IT     I           2017-10-24 16:50:50.100
Compounds 1           Dosage       125.50 160.50 U           2017-10-24 17:50:50.100
Compounds 1           Dosage       160.50 130.50 U           2017-10-24 18:50:50.100
Compounds 1           Route        IT     IV     U           2017-10-24 18:50:50.100

(6 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