Knowledge is Here...

Thursday, February 4, 2021

SQL 16 Populate total number of employee working in Department

Here we have Simple basic table structure same as of Emp dept tables available in Oracle .

Here we need to Find the total number of employees working in Department. I am adding various alternate ways of doing this task.

Table Employee Data Structure

Id	Name	DeptId
1	A	10
2	B	10
3	C	10
4	D	10
5	E	20
6	F	20
7	G	20
8	H	30
9	I	30

Table Department Data Structure

DeptId	Name
10	Sales
20	Dev
30	Tester

Expected Output

Id	Name	DeptId	Count
1	A	10	4
2	B	10	4
3	C	10	4
4	D	10	4
5	E	20	3
6	F	20	3
7	G	20	3
8	H	30	2
9	I	30	2

Create table Query

SELECT *
INTO SQL16
FROM (
	SELECT 1 id
		, 'A' name
		, 10 deptid
	
	UNION ALL
	
	SELECT 2
		, 'B'
		, 10
	
	UNION ALL
	
	SELECT 3
		, 'C'
		, 10
	
	UNION ALL
	
	SELECT 4
		, 'D'
		, 10
	
	UNION ALL
	
	SELECT 5
		, 'E'
		, 20
	
	UNION ALL
	
	SELECT 6
		, 'F'
		, 20
	
	UNION ALL
	
	SELECT 7
		, 'G'
		, 20
	
	UNION ALL
	
	SELECT 8
		, 'H'
		, 30
	
	UNION ALL
	
	SELECT 9
		, 'I'
		, 30
	) t

Alternate Solution 1

SELECT *
INTO SQL16_1
FROM (
	SELECT 10 id
		, 'Sale' Dname
	
	UNION ALL
	
	SELECT 20
		, 'Dev'
	
	UNION ALL
	
	SELECT 30
		, 'QA'
	) t1

Alternate Solution 2
SELECT ID
	, NAME
	, DEPTID
	, COUNT(*) OVER (
		PARTITION BY DEPTID ORDER BY DEPTID
		) AS CUMMULATIVE_SUM
FROM SQL16;

Alternate Solution 3
SELECT ID
	, NAME
	, DEPTID
	, (
		SELECT SUM(1) CUMMULATIVE_SUM
		FROM SQL16 CI
		WHERE CI.DEPTID = CO.DEPTID
		GROUP BY DEPTID
		) AS CUMMULATIVE_SUM
FROM SQL16 CO;

Alternate Solution 4
SELECT CO.ID
	, CO.NAME
	, CO.DEPTID
	, (
		CASE 
			WHEN CI.DEPTID = CO.DEPTID
				THEN SUM(1) OVER (
						PARTITION BY CO.DEPTID ORDER BY CO.DEPTID
						)
			END
		) AS CUMMULATIVE_SUM
FROM SQL16 CO
JOIN SQL16 CI ON CO.ID = CI.ID;

Alternate Solution 5
SELECT *
FROM SQL16 p14
LEFT JOIN (
	SELECT count(*) cnt
		, deptid
	FROM SQL16
	GROUP BY deptid
	) t1 ON t1.deptid = p14.deptid

SQL15 Commulative SUM of Sales

Data is given table, we have date along with the sales. We need to calculate commulative sum for all the sales

below are the various ways to perform this task

Data in Table

Year	Sales
2019-09-01	10
2019-10-01	60
2019-11-01	20
2019-12-01	10
2020-01-01	30
2020-02-01	20
2020-03-01	80
2021-01-01	70
2021-02-01	30
2021-03-01	10

Expected Result

year	sales	Sum_sales
2019-09-01	10	10
2019-10-01	60	70
2019-11-01	20	90
2019-12-01	10	100
2020-01-01	30	130
2020-02-01	20	150
2020-03-01	80	230
2021-01-01	70	300
2021-02-01	30	330
2021-03-01	10	340

SELECT *
INTO SQL15
FROM (
	SELECT '2019-09-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2019-10-01' AS MONTH
		, 60 AS SALES
	
	UNION ALL
	
	SELECT '2019-11-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2019-12-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2020-01-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2020-02-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2020-03-01' AS MONTH
		, 80 AS SALES
	
	UNION ALL
	
	SELECT '2021-01-01' AS MONTH
		, 70 AS SALES
	
	UNION ALL
	
	SELECT '2021-02-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2021-03-01' AS MONTH
		, 10 AS SALES
	) t

SELECT t1.month
	, year(t1.month)
	, t1.sales
	, sum(t2.sales)
FROM sql15 t1
JOIN sql15 t2 ON (t1.month) >= (t2.month)
GROUP BY t1.month
	, t1.sales
	, year(t1.month)
ORDER BY 1
	, 2

Releated post - SQL 14 (Click Here)

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

Friday, January 29, 2021

SQL14 Commulative SUM of Sales Year wise

Data is given table, we have date along with the sales. We need to calculate commulative sum for every year given in table.

below are the various ways to perform this task

Data in Table

Year	Sales
2019-09-01	10
2019-10-01	60
2019-11-01	20
2019-12-01	10
2020-01-01	30
2020-02-01	20
2020-03-01	80
2021-01-01	70
2021-02-01	30
2021-03-01	10

Expected Result

year	sales	Sum
2019-09-01	10	10
2019-10-01	60	70
2019-11-01	20	90
2019-12-01	10	100
2020-01-01	30	30
2020-02-01	20	50
2020-03-01	80	130
2021-01-01	70	70
2021-02-01	30	100
2021-03-01	10	110

Table Insert Query

SELECT *
INTO SQL14
FROM (
	SELECT '2019-09-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2019-10-01' AS MONTH
		, 60 AS SALES
	
	UNION ALL
	
	SELECT '2019-11-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2019-12-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2020-01-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2020-02-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2020-03-01' AS MONTH
		, 80 AS SALES
	
	UNION ALL
	
	SELECT '2021-01-01' AS MONTH
		, 70 AS SALES
	
	UNION ALL
	
	SELECT '2021-02-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2021-03-01' AS MONTH
		, 10 AS SALES
	) t

--oracle
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND EXTRACT(YEAR FROM TO_DATE(O.MONTH, 'YYYY-MM-DD')) = EXTRACT(YEAR FROM TO_DATE(I.MONTH, 'YYYY-MM-DD'))
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 1
SELECT year(month)
	, sum(sales) OVER (
		PARTITION BY year(month) ORDER BY month
		)
	, *
FROM sql14

--SQLServer Solution 2
SELECT MONTH
	, SALES
	, (
		SELECT SUM(SALES)
		FROM SQL14 I
		WHERE I.MONTH <= O.MONTH
			AND YEAR(O.MONTH) = YEAR(I.MONTH)
		) AS YEARTODATE_SALE
FROM SQL14 O;

--SQLServer Solution 3
WITH cte
AS (
	SELECT *
		, year(month) y
	FROM sql14
	)
SELECT DISTINCT *
FROM SQL14 c
CROSS APPLY (
	SELECT DISTINCT sum(sales) AS total
	FROM SQL14
	WHERE (c.month) >= (month)
		AND year(c.month) = year(month)
		--group by year(month)
	) t

--SQLServer Solution 4
SELECT t1.month
	, year(t1.month)
	, t1.sales
	, sum(t2.sales)
FROM sql14 t1
JOIN sql14 t2 ON (t1.month) >= (t2.month)
	AND year(t1.month) = year(t2.month)
GROUP BY t1.month
	, t1.sales
	, year(t1.month)
ORDER BY 1
	, 2

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

Monday, January 11, 2021

Install SSMA/No SQL Server instance was found on this machine

Error : No SQL Server instance was found on this machine. Please try after installing an SQL Server instance.

Scenario : This error faced while installing SSMA on machine.

Correct Step to Install ( Below step are for oracle SSMA):

Download installer from link (click here) "https://www.microsoft.com/en-us/download/details.aspx?id=54258"

Download File - SSMAforOracle_x.xx.x.msi

Download File - SSMAforOracleExtensionPack_x.xx.x.msi

STEP 1

Install SSMAforOracle_x.xx.x.msi on your computer/machine (after double click)
Accept Agreements
Click on "Typical" type installation
Click on "install" button

STEP 2

Double click on SSMAforOracleExtensionPack_x.xx.x.msi
Accept Agreements
Click on "Typical" type installation
Click On "Install" Button
Installation will finish, allow for any permission if it asked
Popup will visible on screen after few seconds, you can see 2 option on screen

Local instance (chose this option if SQL Server Engine is installed on local computer)
Remote instance ( Chose this option if SQL Server Engine is installed on remote machine, cloud, network computer)

Add connection details (Hostname, port, username, password)
Click check box "Trust server Certificate", if "Remote instance" option is selected
on next Screen default option is selected " Install Untilities Database .....", Click on next
Let the installtion complete, after that you will be ready to use

Why error was occured : User was selecting option "Local Instance" while installation, however SQL Server engine was installed on remote machine

Thursday, January 7, 2021

SQL13 How to Fill/populate data until next value

Scenerio is Data is populate in table but in between some null occurance are available. Need to populate the in null starting from copy from 1 populate data and copy until next occurance of data and so on.

This can be done by various ways, few of the best way, solution writing in this post.

Data in Table

id	COL1	COL2
1	A	B
2
3
4
5
6	A	C
7
8
9
10	A	D
11
12
13
14
15
16
17

Expected Result

id	COL1	COL2
1	A	B
2		B
3		B
4		B
5		B
6	A	C
7		C
8		C
9		C
10	A	D
11		D
12		D
13		D
14		D
15		D
16		D
17		D

SELECT *
INTO SQL13
FROM (
	SELECT 1 ID, 'A' COL1, 'B' COL2
	
	UNION ALL
	
	SELECT 2, NULL, NULL
	
	UNION ALL
	
	SELECT 3, NULL, NULL
	
	UNION ALL
	
	SELECT 4, NULL, NULL
	
	UNION ALL
	
	SELECT 5, NULL, NULL
	
	UNION ALL
	
	SELECT 6 ID, 'A' COL1, 'C' COL2
	
	UNION ALL
	
	SELECT 7, NULL, NULL
	
	UNION ALL
	
	SELECT 8, NULL, NULL
	
	UNION ALL
	
	SELECT 9, NULL, NULL
	
	UNION ALL
	
	SELECT 10 ID, 'A' COL1, 'D' COL2
	
	UNION ALL
	
	SELECT 11, NULL, NULL
	
	UNION ALL
	
	SELECT 12, NULL, NULL
	
	UNION ALL
	
	SELECT 13, NULL, NULL
	
	UNION ALL
	
	SELECT 14, NULL, NULL
	
	UNION ALL
	
	SELECT 15, NULL, NULL
	
	UNION ALL
	
	SELECT 16, NULL, NULL
	
	UNION ALL
	
	SELECT 17, NULL, NULL
	) test

--Solution 1
SELECT *
	, CASE 
		WHEN COL2 IS NULL
			THEN (
				SELECT TOP 1 COL2
				FROM SQL13
				WHERE ID < T.ID
					AND COL2 IS NOT NULL
				ORDER BY ID DESC
		    	)
		ELSE COL2
		END AS COL3
FROM SQL13 T
--Solution 2
WITH CTE AS (
	SELECT ID
		, COL2
		, COALESCE(LEAD(ID) OVER (
				ORDER BY ID
				), (
				SELECT COUNT(*) + 1
				FROM SQL13
				)) NXT_SEQ
	FROM SQL13
	WHERE COL1 IS NOT NULL
	)

SELECT EP.ID
	, EP.COL1
	, EP.COL2
	--,EP.COL3
	, (
		SELECT CTE.COL2
		FROM CTE
		WHERE EP.ID >= CTE.ID
			AND EP.ID < CTE.NXT_SEQ
		) POPULATED
FROM SQL13 EP;

--Solution 3
SELECT c1.id AS id
	, coalesce(c1.col2, t.col2)
FROM SQL13 c1
OUTER APPLY (
	SELECT TOP 1 c2.col2
	FROM SQL13 c2
	WHERE c1.col2 IS NULL
		AND c2.col2 IS NOT NULL
		AND c2.id < c1.id
	ORDER BY c2.id DESC
	) t

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice