Help Selecting Records

Question

Post reply

Help Selecting Records

bdgjcd

SSC Rookie

Points: 33
More actions
October 19, 2010 at 3:39 am

#228198

I have a table that contains the following fields: -
Date
SalesPerson
OrderReference
OrderStatus
What I want to do is produce a report grouped by Date and SalesPerson that shows the count of OrderReference - that part I'm ok with.
What I also need to do is add an additioanl column to the report showing the count of OrderStaus but only where OrderStatus is 'Complete'.
Report should look something like: -
Date...................SalesPerson........Orders..........CompletedOrders
01/01/2010...........Fred....................10.................5
01/01/2010...........George.................14.................7
02/01/2010...........Fred.....................9..................4
02/01/2010...........George..................7..................2
Any help greatly appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

prajey SSC Enthusiast Points: 172 More actions · Answer 1

[font="Arial Black"][/font]

if i have understood your question, then here is reply

SELECT SUM(ORDERREFERENCE) ORDERCOUNT , [DATE], SALESPERSON FROM SALES NOLOCK

WHERE ORDERSTATUS = 'COMPLETE'

GROUP BY [DATE],SALESPERSON

Regards,

Prajey

bdgjcd SSC Rookie Points: 33 More actions · Answer 2

Thanks Prajey

I think I see where you are going - I'll try this and see how I get on

nigel. SSChampion Points: 11636 More actions · Answer 3

It would have helped if you had provided more information, such as table definitions some test data etc., please see the link in my sig below.

Anyway what about this?:

DECLARE @t TABLE

(

[OrderDate]DATETIME,

[SalesPerson]VARCHAR(20),

[OrderReference] VARCHAR(20),

[OrderStatus]varCHAR(20)

)

INSERT @t

SELECT '20100101', 'Fred', 'ABC123', ''

UNION ALL

SELECT '20100102', 'Fred', 'ABC124', ''

UNION ALL

SELECT '20100103', 'Fred', 'ABC125', ''

UNION ALL

SELECT '20100103', 'Fred', 'ABC126', ''

UNION ALL

SELECT '20100103', 'Fred', 'ABC127', 'Complete'

UNION ALL

SELECT '20100103', 'George', 'ABC128', 'Complete'

UNION ALL

SELECT '20100103', 'George', 'ABC129', ''

SELECT

OrderDate,

SalesPerson,

Orders = COUNT(*),

Completed = SUM ( CASE WHEN OrderStatus = 'COMPLETE' THEN 1 ELSE 0 END )

FROM

@t

GROUP BY

OrderDate,SalesPerson

Nigel
How to post questions

bdgjcd SSC Rookie Points: 33 More actions · Answer 4

Hi Nigel

Thanks for the feedback - As with being new to SQL it's also new to post on here and learn what 's the best way to post for people to help.

Anyway with your help I have my query working now, not quite the way you suggested (due to me not giving you all the necessary info) but your suggestion enabled me to amend my query to get the data I need

Thanks Again

Brian

nigel. SSChampion Points: 11636 More actions · Answer 5

Thanks for the feedback, glad to be of help.

Nigel
How to post questions