DateTime Conversion & Running Count 1

[LLowrance]

Let me first apologize for what is probably a very easy solution, but I'm new to SQL and our IT dept is swamped, therefore I left to try to do this on my own. Here goes:

I'm trying to create a view that I can then use to build a Crystal Report. I have a dept of nurses that approve medical procedures called decisions. I'm trying to count the number of decisions a nurse does per day. The problem is I'm trying to convert a date time field to just a date field as I do not need the time portion. What happens is the time is 00:00:00.000 but it still is grouping by time. A nurse should have one entry appear per date. Here is a copy of my select statement:

SELECT decision_by, (convert(datetime,convert(varchar(12),final_decision_date))) AS Final_Decision_date, (count(treatment_decision_key)) as CompletedReviews
FROM tbl_treatment_decision
WHERE year(final_decision_date)=2004
GROUP BY decision_by, final_decision_date
ORDER BY decision_by ASC, final_decision_date ASC
GO

I also need to create a field that counts the number of days worked in a month. (Running total that resets at the beginning of the next month)

Here is a sample of what my result set is:

decision_by final_decision_date completedreviews
aharris 2004-09-27 00:00:00.000 1
aharris 2004-09-27 00:00:00.000 1
aharris 2004-09-27 00:00:00.000 1
aharris 2004-09-27 00:00:00.000 1

This should read:
aharris 2004-09-27 4

I haven't made it to the running total yet (Daysworked in the month) I was trying to get the datetime conversion working first.

Thanks for any help you can give.

Lowell

 

[mrdenny]

I see your problem. First we need to tell it what format we want the date displayed in. That will remove the time from the view. Then we need to enclude the convert statement in the group by and order by clauses. Otherwise it will display the time in the new format, but will group it in the old format. Changes are red.

SELECT decision_by, convert(varchar(1[COLOR=red]0[/color]),final_decision_date[COLOR=red], 101[/color]) AS Final_Decision_date, count(treatment_decision_key) as CompletedReviews
 FROM tbl_treatment_decision
 WHERE year(final_decision_date)=2004
 GROUP BY decision_by, [COLOR=red]convert(varchar(10),final_decision_date, 101)[/color]
 ORDER BY decision_by ASC,  [COLOR=red]convert(varchar(10),final_decision_date, 101)[/color]ASC
GO

There is also no need to reconvert to datetime. Displaying in varchar will work just fine.

Denny

--Anything is possible. All it takes is a little research. (Me)