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

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

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

SQL12 Generate duplicate records as per the count of sequence

Want to generate dupkciate ID in sequence, 

For 1 , generate 1 record

For 2 , generate 2 records

For 3 , generate 3 records

For 4 , generate 4 records

and so on

these records will be generated all in sequence.

Output Needed

id
1
2
2
3
3
3
4
4
4
4

--Oracle

WITH SQL12
AS (
	SELECT LEVEL col
	FROM dual connect BY LEVEL < 5
	)
SELECT a.*
FROM SQL12 a
JOIN SQL12 b ON b.col <= a.col;

--SQL Server
WITH SQL12 (N)
AS (
	SELECT 1
	
	UNION ALL
	
	SELECT N + 1
	FROM SQL12
	WHERE N < 4
	)
SELECT a.*
FROM SQL12 a
JOIN SQL12 b ON b.N <= a.N
ORDER BY 1;

Create Single Line Border Table HTML/CSS

I used table tag in html to create a table, but by default this tab create table with double line border and extended to the full length of Page.

But my requirement to use single line table. You can use below properties for TABLE tag to create single line border table.

<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse;" width="200px">

Sample table created (click here)

SQL11 Generate Duplicate Data to Some specific count

We have given data in table, along with the count of quantity with respect to each order and item, we need to generate number of rows equal to the count of QTY given in table.

Assume, for first row 5 count is given, as a output we need to generate 1 row 5 time as duplicate row.

Data in Table 

orderid	item	qty
o1	A1	5
o2	A2	1
o3	A3	3

Output Needed

orderid	item	qty
o1	A1	1
o1	A1	2
o1	A1	3
o1	A1	4
o1	A1	5
o2	A2	1
o3	A3	1
o3	A3	2
o3	A3	3

SELECT *
INTO SQL11
FROM (
	SELECT 'o1' OrderID
		,'A1' Item
		,5 QTY
	
	UNION ALL
	
	SELECT 'o2'
		,'A2'
		,1
	
	UNION ALL
	
	SELECT 'o3'
		,'A3'
		,3
	) t1

Solution 1

WITH t
AS (
	SELECT orderid
		,item
		,qty
	FROM SQL11
	
	UNION ALL
	
	SELECT orderid
		,item
		,qty - 1
	FROM t
	WHERE qty > 1
	)
SELECT *
FROM t
ORDER BY 1
	,3

note* - abvove query can also be possible to execute in Oracle, before execute in oracle, change the with clause "with t (orderid, item, qty) as ("

Oracle Specific Query

SELECT OrderID
	,Item
	,1
FROM (
	SELECT DISTINCT OrderID
		,Item
		,LEVEL
	FROM SQL11 EO connect BY LEVEL <= (
			SELECT QTY
			FROM SQL11 EI
			WHERE EO.OrderID = EI.OrderID
			)
	)
ORDER BY 1
	,2
	,3;