Hello sql expert,
I am trying to count ... and here are the query that I am working on:
Materials Unit# Status StatusDate CompleteddDate
Drywall H225 Return 01/17/2015 01/17/2015
Drywall H225 Repair 01/19/2015 01/22/2015
Drywall H225 Bought 02/02/2015 02/02/2015
Ladders H339 Return 01/05/2015 01/05/2015
Ladders H339 Bought 01/10/2015 01/10/2015
Drywall H225 Return 02/05/2015 02/05/2015
Ladders H339 Return 02/11/2015 02/11/2015
Ladders H339 Repair 02/20/2015 02/25/2015
Ceiling H405 Repair 05/02/2015 06/05/2015
So, what I want to do is to have a new field that count how many status on each material. For example: I want to be able to display how many status being Return, Repair and Bought on each Materials (like Drywall, Ladders, Ceiling) but also still showing the detail of the StatusDate and CompletedDate. If I remove the date fields, I can count on each status on each Materials but keeping the date as the detail, I need help! Anyone? So it would be like this:
Materials Unit# Status Count StatusDate CompleteddDate
Drywall H225 Return 2 01/17/2015 01/17/2015
Drywall H225 Repair 1 01/19/2015 01/22/2015
Drywall H225 Bought 1 02/02/2015 02/02/2015
Drywall H225 Return 2 02/05/2015 02/05/2015
Ladders H339 Return 2 01/05/2015 01/05/2015
Ladders H339 Bought 1 01/10/2015 01/10/2015
Ladders H339 Return 2 02/11/2015 02/11/2015
Ladders H339 Repair 1 02/20/2015 02/25/2015
Ceiling H405 Repair 1 05/02/2015 06/05/2015
Here is my query:
Select Materials, Unit#, Status, StatusDate, CompletedDate
From MaterialDB
Where StatusDate between '01/01/2015' to '09/30/2015
Thank you all