October 3, 2013 at 12:34 am
I have this formula I use within Excel, I'm moving most of the work into SQL & can't think how I can replicate this formula...
IF(AND(A9=A8,L8="MoreThan1"),"MoreThan1",IF(A9=A8,IF(E9=E8,"OneStop","MoreThan1"),"OneStop"))
Any ideas? o_O
October 3, 2013 at 12:45 am
SELECT
CASE WHEN (A9 = A8) AND (L8 = 'MoreThan1')
THEN 'MoreThan1'
ELSE CASE WHEN (A9 = A8)
THEN CASE WHEN (E9 = E8)
THEN 'OneStop'
ELSE 'MoreThan1'
END
ELSE 'OneStop'
END
END
FROM myTable
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 3, 2013 at 1:14 am
Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within Excel
Column A = PersonID
Column E = DateOfAppt
Column L = is the column that I will use to return the answer within the formula.
October 3, 2013 at 1:16 am
J39L4753 (10/3/2013)
Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within ExcelColumn A = PersonID
Column E = DateOfAppt
Column L = is the column that I will use to return the answer within the formula.
I realized that, but I was not going to guess column names.
I'm confident you can put the column names yourself in the query.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 3, 2013 at 1:29 am
Koen Verbeeck (10/3/2013)
J39L4753 (10/3/2013)
Sorry forgot to add that A8, A9 E8, E9, L8 are cell rows within ExcelColumn A = PersonID
Column E = DateOfAppt
Column L = is the column that I will use to return the answer within the formula.
I realized that, but I was not going to guess column names.
I'm confident you can put the column names yourself in the query.
But the bit I'm struggling with is that like with A9=A8, this is comparing PersonID with the row above etc
October 3, 2013 at 1:34 am
To get some decent answers, it might help if you post table DDLs, sample data and desired output.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 3, 2013 at 2:25 am
Koen Verbeeck (10/3/2013)
To get some decent answers, it might help if you post table DDLs, sample data and desired output.
Attached is a sample data set from excel, that I'm creating in SQL
October 3, 2013 at 2:31 am
Which version of SQL Server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 3, 2013 at 2:50 am
Koen Verbeeck (10/3/2013)
Which version of SQL Server?
2008R2
October 3, 2013 at 3:20 am
You'll probably need a cursor to update your values, because to calculate the OneStopOrNot column for one row, you need the calculated result of OneStopOrNot column of the previous row.
With a set-based solution all the rows would be updated at once, which means you cannot use the result of the previous row.
To get you started on cursors:
SQL Server Cursor Example[/url]
Just to be perfectly clear: cursors are bad and slow for performance. However, in your specific business case you might need them. (unless someone else proves me wrong of course and uses set-based TSQL to solve this issue)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 3, 2013 at 3:29 am
Koen Verbeeck (10/3/2013)
You'll probably need a cursor to update your values, because to calculate the OneStopOrNot column for one row, you need the calculated result of OneStopOrNot column of the previous row.With a set-based solution all the rows would be updated at once, which means you cannot use the result of the previous row.
To get you started on cursors:
SQL Server Cursor Example[/url]
Just to be perfectly clear: cursors are bad and slow for performance. However, in your specific business case you might need them. (unless someone else proves me wrong of course and uses set-based TSQL to solve this issue)
Cool, thanks for this, I'll have a look and give it a go
October 3, 2013 at 5:38 am
Hi,
This can be achieved using CTE
declare @person table (PersonID int, ApptDate datetime, ApptStatus varchar(10), NoOfAppts int, OneStopOrNot VARCHAR(10))
insert into @person (PersonID, ApptDate, ApptStatus, NoOfAppts) values
(49820,'07-09-2013','Seen',1),
(49827,'12-sep-2013','Seen',1),
(49831,'07-sep-2013','Seen',1),
(49834,'07-sep-2013','Seen',1),
(50084,'07-sep-2013','Seen',1),
(50097,'05-sep-2013','Seen',1),
(50172,'05-sep-2013','Seen',1),
(50172,'27-sep-2013','Seen',2),
(50175,'05-sep-2013','Seen',1)
;WITH persons AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY PersonID, ApptStatus),
*
FROM @person
)
UPDATE cur SET OneStopOrNot =
( case WHEN cur.PersonID != prev.PersonID THEN 'OneStop'
WHEN prev.PersonID IS NULL THEN 'OneStop'
WHEN cur.PersonID = prev.PersonID AND prev.OneStopOrNot = 'MoreThan1' THEN 'MoreThan1'
WHEN cur.PersonID = prev.PersonID AND cur.ApptDate = prev.ApptDate THEN 'OneStop'
ELSE 'MoreThan1' END)
FROM persons cur
LEFT JOIN persons prev on prev.rownum = cur.rownum - 1
select * from @person
October 3, 2013 at 5:41 am
This can be achieved using CTE
declare @person table (PersonID int, ApptDate datetime, ApptStatus varchar(10), NoOfAppts int, OneStopOrNot VARCHAR(10))
insert into @person (PersonID, ApptDate, ApptStatus, NoOfAppts) values
(49820,'07-09-2013','Seen',1),
(49827,'12-sep-2013','Seen',1),
(49831,'07-sep-2013','Seen',1),
(49834,'07-sep-2013','Seen',1),
(50084,'07-sep-2013','Seen',1),
(50097,'05-sep-2013','Seen',1),
(50172,'05-sep-2013','Seen',1),
(50172,'27-sep-2013','Seen',2),
(50175,'05-sep-2013','Seen',1)
;WITH persons AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY PersonID, ApptStatus),
*
FROM @person
)
UPDATE cur SET OneStopOrNot =
( case WHEN cur.PersonID != prev.PersonID THEN 'OneStop'
WHEN prev.PersonID IS NULL THEN 'OneStop'
WHEN cur.PersonID = prev.PersonID AND prev.OneStopOrNot = 'MoreThan1' THEN 'MoreThan1'
WHEN cur.PersonID = prev.PersonID AND cur.ApptDate = prev.ApptDate THEN 'OneStop'
ELSE 'MoreThan1' END)
FROM persons cur
LEFT JOIN persons prev on prev.rownum = cur.rownum - 1
select * from @person
October 3, 2013 at 6:34 am
By mistaken two times posted the same.
October 3, 2013 at 9:20 am
parulprabu (10/3/2013)
By mistaken two times posted the same.
Thanks for the CTE, I'll give that a go 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply