CODEWITHSUNDEEP
sqlsql-serversql-server-2008
4est
4est

Reputation: 3168

How to add dummy month (SQL query)

I have these columns in table1:

enter image description here

Now, I need to add dummy month. How to quickly write SQL query, which divide Q1 (as one row) by 3 months?

Result should be like this:

enter image description here

Is this possible?

EDIT 1:

Here it is the SQL statement which produces the first table

select year, time, q, th, l1, l2, par, par2, sum(value)
from table1
group by year, time, q, th, l1, l2, par, par2

Upvotes: 1

Views: 708

Answers (4)

sqlandmore.com
sqlandmore.com

Reputation: 163

This should work:

declare @t table (id int identity(1,1),q char(2))
insert into @t 
select 'q1'
union all select 'q1'
union all select 'q2'
union all select 'q2'
union all select 'q2'
union all select 'q1'
union all select 'q4'
union all select 'q3'
union all select 'q3'
union all select 'q4'
union all select 'q4'
union all select 'q3'

--select * from @t
select mn =  ROW_NUMBER() OVER(ORDER BY q) ,q
from @t

Upvotes: 0

Shukri Gashi
Shukri Gashi

Reputation: 535

This is not exactly what you are asking, but you can have an idea how it can be adopted in your case. I used table variable you have to substitute it with your table name. I did some inserts just for testing purpose:

declare @table table (Q varchar(10),amount decimal(18,2))
insert into @table values ('Q1',80000),('Q2',500000),('Q3',457000),('Q4',75000)
declare @table2 table (Q varchar(10),muaji int)
insert into @table2 values ('Q1',1),('Q1',2),('Q1',3),('Q2',4),('Q2',5),('Q2',6),('Q3',7),('Q3',8),('Q3',9),('Q4',10),('Q4',11),('Q4',12)
select t1.Q,t2.muaji,t1.amount/3 from @table t1 inner join @table2 t2 on t1.Q=t2.Q

Upvotes: 0

Kaf
Kaf

Reputation: 33839

Try this approach using a CROSS JOIN and ROW_NUMBER().

CROSS JOIN with a dynamic table (table2) which has 3 records will output 3 rows for each quarter in the main table and then use ROW_NUMBER() function to get the month number for each year order by quarter.

Following is an example.

--Add some sample data to represent your table1
DECLARE @table1 TABLE ([Year] int, Q varchar(10))

INSERT INTO @table1 ([Year], Q)
VALUES (2016, 'Q1'),
       (2016, 'Q2'),
       (2016, 'Q3'),
       (2016, 'Q4')

--Query
SELECT [year], Q,  
       ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY qtr) [Month]
FROM @Table1 t1
        CROSS JOIN (SELECT qtr FROM (VALUES(1),(2),(3)) Table2 (qtr)) t2

Upvotes: 3

Ross Presser
Ross Presser

Reputation: 6259

Use convert to turn Q1,Q2,Q3,Q4 into a starting month number; then use union to replicate the row three times (dividing the sum by three).

select year, time, q, 
    month = convert(int, right(q,1))*3 - 2,
    th, l1, l2, par, par2, sum(value)/3 
from table1 group by year, time, q, th, l1, l2, par, par2
union all
select year, time, q, 
    month = convert(int, right(q,1))*3 - 1,
    th, l1, l2, par, par2, sum(value)/3 
from table1 group by year, time, q, th, l1, l2, par, par2
union all
select year, time, q, 
    month = convert(int, right(q,1))*3,
    th, l1, l2, par, par2, sum(value)/3 
from table1 group by year, time, q, th, l1, l2, par, par2

Upvotes: 1

Related Questions