February 12, 2014 at 4:12 am
I have a data output with many rows. In order to group things with flags, I do this in excel using 2 formulas which *** a flag of 0 or 1 in 2 new columns.
This takes a long long time as I have hundreds of thousands of rows and wondered of I could do it in sql?
Its transact SQL and the formulas I use in excel are:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2>=C2-1/24)*($C$2:$C2< C2+1/24))> 1,0,1)
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Any idea how I can do this in sql??
The columns above do not relate to the actual columns I use, just an example.
February 12, 2014 at 5:28 am
...
The columns above do not relate to the actual columns I use, just an example.
just as an example...
you can do in T-SQL:
SELECT ...
,CASE WHEN SUM(ColumnA + ColumnB) > 1 THEN 1 ELSE 0 END
FROM ....
GROUP BY ...
You really need to check the link at the bottom of my signature. Providing a bit more details in your questions (as per article behind that link) will help you to get prompt and relevant answer...
February 12, 2014 at 9:57 am
Both the customer ID and Order ID fields are a mix of nummers and letters.
If I do:
SELECT ...
,CASE WHEN SUM(Customer.CustomerID + Order.OrderID) > 1 THEN 1 ELSE 0 END
FROM etc
GROUP BY ...
I am getting many rows for each customer which isnt right. I want a flat on each order to say that thats a unique customer. and thats a unique customer order etc.
So
SELECT Customer.CistomerId,
Order.OrderID,
Order.StartDate,
Order.Type
FROM
Order, Customer
Where
Order.CustomerId *= Order.OrderID
AND Order.StartDate > '02/11/2014 ;
Thats a basic query, how would I get the flags I describe above?
CASE WHEN SUM(Customer.CustomerID + Order.OrderID) > 1 THEN 1 ELSE 0 END??
February 12, 2014 at 10:22 am
Sorry Carol, but summing CustomerId with OrderId looks to me as summing Prices with House Numbers.
So, I have three possible answers:
1. http://www.sqlservercentral.com/articles/Best+Practices/61537/
2. 42
3. wait for someone who will understand your question better from what you provided.
I really suggest to go for option #1, it will allow to get things sorted much quicker and nicer...
February 12, 2014 at 10:28 am
Actually, I guess I might know what you are trying to do:
Do you want to list all customers and flag if there were any orders since some day?
If so, then this will do:
SELECT c.CistomerId
, CASE WHEN COUNT(o.OrderID) > 0 THEN 1 ELSE 0 END AS OrderSinceRequriedDateExists
, COUNT(o.OrderID) AS NumberOfOrdersSinceRequriedDate
FROM Customer c
LEFT JOIN [Order] o ON o.CustomerId = c.CustomerId AND o.StartDate > '02 Nov 2014'
GROUP BY c.CistomerId
February 14, 2014 at 5:38 am
What I have is data like:
CustId OrderIdCountUnique
John Smith11
John Smith10
Ann James11
Laura Simpson21
Laura Simpson31
Laura Simpson11
James Wright11
James Wright10
Scott Campbell11
The third column is the one I want to calsulate in SQL, and in excel I use:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
This gives me the unique flags in the third column.
Is there a way to do that in SQL?
I have various columns I want to do it on that are a mox of numbers, dates and IDs/
In addition, those that have a date I would like to show whether its unique in the month for example:
CustId OrderIdOrder Date CountUnique
John Smith101/01/20141
John Smith120/01/20140
John Smith101/05/20141
Ann James105/06/20141
Laura Simpson201/01/20141
Laura Simpson202/05/20141
Laura Simpson105/05/20140
February 14, 2014 at 7:17 am
;WITH cte (CustId,OrderId,OrderDate,RowNumber) AS (
SELECT CustId,OrderId,OrderDate
,ROW_NUMBER() OVER (PARTITION BY CustId,YEAR(OrderDate),MONTH(OrderDate) ORDER BY YEAR(OrderDate),MONTH(OrderDate))
FROM
)
SELECT CustId,OrderId,OrderDate,SIGN(RowNumber)-SIGN(RowNumber-1) AS [CountUnique]
FROM cte
Far away is close at hand in the images of elsewhere.
Anon.
February 17, 2014 at 3:04 am
Hi, thanks for the reply. Is there a bit missing at the start?
I get an error when starting with
; WITH ...
?
Thanks
February 17, 2014 at 3:22 am
What error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2014 at 4:50 am
Incorrect syntax near keyword 'with'
February 17, 2014 at 4:51 am
Incorrect syntax near keyword 'with'
February 17, 2014 at 8:02 am
Syntax looks right.
Could you please post exact query you are trying to execute.
February 17, 2014 at 8:09 am
; WITH cte (Patient.PatientId,Course.CourseId,Course.StartDateTime,RowNumber) AS (
SELECT Patient.PatientId,Course.CourseId,Course.StartDateTime
,ROW_NUMBER() OVER (PARTITION BY Patient.PatientId,YEAR(Course.StartDateTime),MONTH(Course.StartDateTime ORDER BY YEAR(Course.StartDateTime),MONTH(Course.StartDateTime
))
FROM
Patient,
Course
WHERE
Patient.PatientSer = Course.PatientSer
)
SELECT Patient.PatientId,Course.CourseId,Course.StartDateTime,SIGN(RowNumber)-SIGN(RowNumber-1) AS [CountUnique]
FROM cte ;
I get the error I posted above.
February 17, 2014 at 8:19 am
What version of SQL Server are you using?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2014 at 8:25 am
Transact SQL? Im using a package that comes with our system but can also use SQL advantage of that makes any difference.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply