need to create an SQL query to create a combinedID

Hi I need to create an SQL query to create a combinedID based on date and postcode. Like below

ID              CombinedID          DateImported             PostCode
1              1                              20/03/2018                      DY6 9HT
2              1                              20/03/2018                      DY6 9HT
3              2                              20/03/2018                      CF1 1AB
4              3                              20/03/2018                      AB1 5QS
5              4                              21/03/2018                      NN1 5PN
6              4                              21/03/2018                  NN1 5PN
7              5                              21/03/2018                      DY6 9HT

It will need to look up the max combinedID first (if null then 1) and only update where combinedID is null
Any records where the post codes are the same and on the same date then they all get the next available number. and so on.
Please help.
LVL 3
taz8020Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vijaya KumarCommented:
clarify expected result in table wise....
0
taz8020Author Commented:
Before
ID            CombinedID               DateImported                 PostCode
1              Null                              20/03/2018                      DY6 9HT
2              Null                              20/03/2018                      DY6 9HT
3              Null                              20/03/2018                      CF1 1AB
4              Null                              20/03/2018                      AB1 5QS
5              Null                              21/03/2018                      NN1 5PN
6              Null                              21/03/2018                      NN1 5PN
7              Null                              21/03/2018                      DY6 9HT

after

ID              CombinedID          DateImported             PostCode
1                1                              20/03/2018                      DY6 9HT
2                1                              20/03/2018                      DY6 9HT
3                2                              20/03/2018                      CF1 1AB
4                3                              20/03/2018                      AB1 5QS
5                4                              21/03/2018                      NN1 5PN
6                4                              21/03/2018                      NN1 5PN
7                5                              21/03/2018                      DY6 9HT
0
SharathData EngineerCommented:
try something like this. My assumption is your CombinedID is all NULL. If not and if this doesn't give your expected result, post some other sample data with desired output.
select ID, dense_rank() over (order by DateImported, c1) CombinedID, DateImported, PostCode
  from (select *,max(id) over (partition by DateImported, PostCode) c1
          from test1) t1 
 order by id
/*
ID	CombinedID	DateImported	PostCode
1	1	20/03/2018	DY6 9HT
2	1	20/03/2018	DY6 9HT
3	2	20/03/2018	CF1 1AB
4	3	20/03/2018	AB1 5QS
5	4	21/03/2018	NN1 5PN
6	4	21/03/2018	NN1 5PN
7	5	21/03/2018	DY6 9HT
*/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
taz8020Author Commented:
Hi thanks this is almost perfect. The first time i run it its spot on, however we will be getting a lot of orders coming through this was so will use this to up date the table and exclude nulls.
The problem is when i re run it, it starts again a 1.

This is what i used:

update dbo.NewOrders
set CombinedID = t3.NewCombinedID
from (
select ID, dense_rank() over (order by CONVERT(date, DateImported), c1) NewCombinedID, DateImported, [Ship To Postcode]
  from (select *,max(ID) over (partition by  CONVERT(date, DateImported), [Ship To Postcode]) c1
          from dbo.NewOrders where not DateImported is null and CombinedID is null) t1 
) as t3
where NewOrders.ID = t3.ID

Open in new window

0
taz8020Author Commented:
Perfect, got a little lost for a sec but took another look and its great thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.