Click here to Skip to main content
14,427,368 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a table with data ,In weeks columns Missing some sequence number(week).
In that place i want to add missing number.

for example:

Columns :  week  year platformname operatingsystem
            1    2018        sql         w7
            2    2018        sql         w7 
            5    2018        sql         w7
            6    2018        sql         w7


in this data missing number 3,4 .how to add this data in table.

Please help me out ASAP.

Thanks in advance.

What I have tried:

i tried sequence number but its comming only 3 (1st number only coming)
Posted
Updated 10-Jan-20 5:21am
v2
Comments
OriginalGriff 10-Jan-20 4:23am
   
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
So show us the input data you have and the output you want, show us the code that generates the output above, and explain what is wrong with it.

And don't tell us you need it ASAP: everyone wants a quick answer and you are not our boss. So acting like one does not help you get a faster answer, it just makes you look arrogant ...
Use the "Improve question" widget to edit your question and provide better information.
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
Comments
Maciej Los 10-Jan-20 12:27pm
   
5ed!
0x01AA 10-Jan-20 12:35pm
   
Thank you Maciej. To be honest, at least one of the links I noted in the past from one of your answers ;)
Maciej Los 10-Jan-20 13:45pm
   
;)
Rate this:
Please Sign up or sign in to vote.

Solution 2

The principle is that you need to select a contiguous list of numbers and then match them to the data you have in your table.

Here is your data
declare @demo table ([week] int, [year] int, platformname nvarchar(30), operatingsystem nvarchar(30))
insert into @demo ([week], [year], platformname, operatingsystem) values
(1, 2018, 'sql', 'w7'),(2, 2018, 'sql', 'w7'), 
(5, 2018, 'sql', 'w7'),(6, 2018, 'sql', 'w7')
As you say there is a gap where 3 and 4 should be.

Here is a neat and efficient way of generating all the numbers you want
select * from 
(select top (@top) row_number() over (order by  a.object_id) AS seqNum 
from sys.columns a) as nums
That query uses the system table sys.columns just because I know there is going to be some data in there, probably quite a lot depending on my database schema. In my case there are 1167 columns in my database, but if that isn't enough you can use a cross join to the same table to get even more numbers - 1361889 in my case i.e. 1167 x 1167

So I didn't end up with 1361883 empty rows in my results I've limited the number of numbers I want by using top (@top) where
declare @top int = (select max([week]) from @demo)
I could just as easily have hard-coded this to 10 for example.

Combine the two queries with a LEFT OUTER join using the numbers table as the left table and your data table as the right table i.e.
select nums.seqNum, [year], platformname, operatingsystem
from 
  (select top (@top) row_number() over (order by  a.object_id) AS seqNum 
  from sys.columns a cross join sys.columns b)  as nums
left outer join @demo  on seqNum = [week]
which gives the results
seqNum	year	platformname	operatingsystem
1	2018	sql		w7
2	2018	sql		w7
3	NULL	NULL		NULL
4	NULL	NULL		NULL
5	2018	sql		w7
6	2018	sql		w7
You'll have to decide what to do with the NULL values
   
Comments
0x01AA 10-Jan-20 11:04am
   
Wow, great compact and easy explanation. +5
CHill60 10-Jan-20 11:13am
   
Thank you! I'm quite embarrassed about the cross join - I had a similar query that worked for ages until the number of rows required exceeded the number of columns in my schema. It was around about then that I started insisting on a numbers table in our database :-)
Maciej Los 10-Jan-20 12:27pm
   
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100