Solved

# ms sql + top 1 for each customer

Posted on 2016-10-17
Medium Priority
123 Views
i have a table with these columns

Customer ID
ScheduleDate
Activity
Scheduled

I need a query that pulls the first row for each customerID that has 2 columns, the one column would be the first entry for the customer with the criteria of scheduled = 1 and then the next column would be the first entry in the list where scheduled = 0

the data is a list with multiple entries for each customer on any given day, can be as many as 10 entries per customer, and each row can have an entry of scheduled =1 or 0

Thanks
0
Question by:dkilby

LVL 56

Expert Comment

ID: 41847613
do you have sample data with expected output?
0

LVL 25

Accepted Solution

chaau earned 2000 total points
ID: 41847649
There are number of ways you can achieve it. With correlated queries:
``````SELECT a.CustomerID,
(SELECT MIN(ScheduleDate) FROM tbl WHERE CustomerID = a.CustomerID and scheduled = 1) as sch_1,
(SELECT MIN(ScheduleDate) FROM tbl WHERE CustomerID = a.CustomerID and scheduled = 0) as sch_0
FROM tbl a
GROUP BY a.CustomerID
``````
More cool way with the ROW_NUMBER() window function:
``````with e as (
SELECT CustomerID,
scheduled,
ScheduleDate,
ROW_NUMBER() OVER(PARTITION BY CustomerID, scheduled ORDER BY ScheduleDate) rn
FROM tbl)
SELECT e1.CustomerID, e1.ScheduleDate as sch_1, e2.ScheduleDate as sch_0
FROM e e1 LEFT JOIN e e2 ON e1.CustomerID=e2.CustomerID AND e1.scheduled=1 and e2.scheduled=0 and e1.rn=1 and e2.rn=1
``````
With just a plain GROUP BY:
``````SELECT CustomerID,
MIN(CASE WHEN scheduled = 1 THEN ScheduleDate END) as sch_1,
MIN(CASE WHEN scheduled = 0 THEN ScheduleDate END) as sch_0
FROM tbl
GROUP BY CustomerID
``````

All should produce the same result
0

LVL 54

Expert Comment

ID: 41847820
I understood your question a little different from Chaau so if I'm right then this should work for you:
``````SELECT [Customer ID], scheduled, MIN(ScheduleDate) MinScheduleDate
FROM tblName
GROUP BY [Customer ID], scheduled
``````
0

## Featured Post

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.