About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Showing posts with label CTE. Show all posts
Showing posts with label CTE. Show all posts

Sunday, December 26, 2021

Misc SQL: Create monthly Allowance report and Fill Null against those month where emp has not received any allowances

Question: Generate a month wise allowance received report for each employee, keep Null in allowance for those month where employee has not received any allowance.

emp_id emp_name month_number allowance
E001 Aayansh         1         1000
E001 Aayansh         2         3000
E002 Rishika         3         2000
E002 Rishika         5         4000

Output should be :

month_number emp_id emp_name allowance
1 E001 Aayansh 1000
2 E001 Aayansh 3000
3 E001 Aayansh null
4 E001 Aayansh null
5 E001 Aayansh null
6 E001 Aayansh null
7 E001 Aayansh null
8 E001 Aayansh null
9 E001 Aayansh null
10 E001 Aayansh null
11 E001 Aayansh null
12 E001 Aayansh null

Solution: 

Steps:
1) generate a month number derived CTE
2) Cross join MonthCTE with EmpAllowance Table and take the distinct record
3) Left join the CorssJoin result from 2nd step to EmpAllowance table and take allowance from EmpAllowance

Query:

;with MonthCTE AS
(select 1 as month_number 
 union all 
 select month_number + 1 as month_number from MonthCTE 
 where month_number < 12
 ),
ReportTemplateCTE as 
(select DISTINCT m.month_number, e.emp_id, e.emp_name from MonthCTE m cross join EmpAllowance e)
 
select r.*, e.allowance from ReportTemplateCTE r left join EmpAllowance e
on r.month_number = e.month_number and r.emp_id = e.emp_id
order by r.emp_id
 

Post Reference: Vikram Aristocratic Elfin Share

Misc: Generate duplicate rows based on quantity - CTE

Ques: You have below records:

item_name     item_qty
Keyboard          3
Mouse             5

You need to duplicate each records depending upon the value of item_qty values.

Sol:

This can be done using CTE


;With DupCreationCTE(item_name, item_qty, item_counter) as
(
  select item_name, item_qty, 1 as item_counter from DupCreation
  union All
  select item_name, item_qty, item_counter + 1 as item_counter
  from DupCreationCTE where item_counter < item_qty
)
SELECT * from DupCreationCTE order by item_name, item_counter asc


Output:

item_name item_qty item_counter
"Keyboard" 3                 1
"Keyboard" 3                 2
"Keyboard" 3                 3
"Mouse"         5                 1
"Mouse"         5                 2
"Mouse"         5                 3
"Mouse"         5                 4
"Mouse"         5                 5


Post Reference: Vikram Aristocratic Elfin Share

Thursday, June 4, 2015

Generating series, now it’s very simple using recursive CTE


A interesting question asked by one of my Community friend to generate series of 4,8,16…. Without using any kind of looping structure.

This is very simple with the recursive CTE, lets see how we are implementing the solution

; with sam_series (myrow) as (
select 4 as myrow
union all
select myrow+ myrow from sam_series
where myrow<1000
)

select * from sam_series;
 myrow
-----------
4
8
16
32
64
128
256
512
1024

If we dissect the above CTE we can see

The anchor member is:  select 4
Recursive invocation member is: select myrow + myrow which is tied with Union All.
Termination Point is: myRow < 1000.

Conclusion: CTE provides the significant advantage of being able to reference itself, thereby creating a recursive CTE.

Get your cup filled with Tea … Enjoy coding  :)


Post Reference: Vikram Aristocratic Elfin Share

Monday, June 30, 2014

Printing next 5 Sunday, Part-2


Read Prev Post : Printing next 5 Sunday, Part-1

Today I got a mail from my community friend Pathik, saying the alternative way to find next 5 sunday.”
I would like to share the same.

Problem Statement was: “I want a list of next five Sunday, how I can achieve this through T-SQL”

His approach towards solution was through CTE, let see the solution

declare @dt datetime
declare @howMany int = 5

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;
with    cte
          as ( select   0 as number
               union all
               select   number + 1
               from     cte
               where    number < (@howMany - 1)
             )
    select  DATEADD(d, 7 * number, @dt) as [date],
            DATENAME(dw, DATEADD(d, 7 * number, @dt)) as Weekdays
    from    cte
OPTION  ( MAXRECURSION 1000 )

date                    Weekdays
----------------------- ------------------------------
2014-07-06 00:00:00.000 Sunday
2014-07-13 00:00:00.000 Sunday
2014-07-20 00:00:00.000 Sunday
2014-07-27 00:00:00.000 Sunday
2014-08-03 00:00:00.000 Sunday

Lets dissect the code and understand each statement.

I am dividing the compete query into 3 part

Part 1 :
Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

Here if you see, this piece of code assigns date of upcoming Sunday. We are bifurcating the line

select datediff(wk, 0, getdate())
-- this will give the total number of week from 1900-01-01 to todays date.

Then he added those number of week to the first date of datetime that is it add number of week to 1900-01-01.
So now the date is first day of this week.
select dateadd(wk, datediff(wk, 0, getdate()), 0)
-- this will gives the first day of the current week

Then we are adding 6 to the date we got in previous step, for finding the first Sunday.
Select   DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0))
--it gives the upcoming sunday of current week

Part2:
Now I am taking the CTE written to get the series of incremental numbers starting from 0 to number passed as parameter

with    cte
          as ( select   0 as number
               union all
               select   number + 1
               from     cte
               where    number < (@howMany - 1)
             )


It’s a Recursive CTE written, where he is trying to generate the series of 0,1,2,3…..@howMany.

Invocation of Anchor: select   0 as number This statement will execute once only and act as a anchor for 1st iteration of execution.

Recursive innovation of routine:
select   number + 1
               from     cte
               where    number < (@howMany - 1)

And each time it executes the result form a anchor for next iteration.

Termination: The iteration will go till the condition matches, i.e  where    number < (@howMany - 1)
This is the termination condition.

Now if you query this CTE it will produce the result
declare @howMany int = 5;
with    cte
          as ( select   0 as number
               union all
               select   number + 1
               from     cte
               where    number < (@howMany - 1)
             )
select * from cte

number
-----------
0
1
2
3
4

Part 3

select  DATEADD(d, 7 * number, @dt) as [date],
            DATENAME(dw, DATEADD(d, 7 * number, @dt)) as Weekdays
    from    cte     

So it is just just adding 7 days to the first Sunday which we derive in the first step and displaying the name of the day.

Why coders are happy; because they are ignorant creature! J
 

Post Reference: Vikram Aristocratic Elfin Share