Does anyone know how to turn this excel function into an sql statement?
The function looks at my address and route. If it is different than the one prior it increments column G by 1 if it is the same it keeps column G as the same number. Ive been trying to piece together for days now but no luck.
IF(D3=D2, IF(C3=C2, G2, G2+1),1)
Yes, with a caveat. Need to know:
1. Is there a unique ascending key to identify each row?
2. If there is, is it numeric?
2a. Is the unique ascending key a composite key (i.e. more than one column)?
3. Is the unique ascending key uniformly populated (i.e. no gaps)?
If the answer to 1 is no, then it can't be done without creating a unique ascending key (i.e. can't be a GUID or some other non-ascending key).
The answers to the others will tell you whether it will be simple or complicated. Bottom line is you have to have some way to join each row with the row prior, so ascending key is crucial. It would have several possible flavors depending on the key structure, but could be structured something like this:
-- Assuming key column k1
select *
from myTable a
left join myTable b
on b.k1 < a.k1
where b.k1 =
(select max(c.k1) from myTable c where c.k1 < a.k1)Obviously, with a composite key it becomes more complicated, but the technique is essentially the same. Once you have the selection working, you can do your calculations and replace your IF statement with a SQL CASE statement.
Some more detail about how your data is structured would be nice so I can code a working example. Otherwise, I hope this is enough to get you going.
I used this on the adjacent column in excel to generate my insert commands. the phone number needed reformatting for SQL, and I needed to test the characters to determine type. After you put this in the top cell of the adjacent column, you can fill down, provided all data matches.
="insert into tblyourtable (dsn, FirstName, LastName,City, Phone1,Extension1, PdNumber, PhoneType1 ) values ('"&A3&"','"&B3&"','"&C3&"','"&D3&"','"&TRIM(LEFT(SUBSTITUTE(E3,"-",""),8))&"','"&IF(LEFT(RIGHT(E3,5))="x",RIGHT(E3,4),"")&"', 'CWP','WORK')"We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.