Need help

Transact-SQL
1

Hello,
I have a table – tableA
Fields:
Services Date
Patient id
Diagnosis_1
Diagnosis_2
Diagnosis_3
Diagnosis_4
Diagnosis_desc_1
Diagnosis_desc_2
Diagnosis_desc_3
Diagnosis_desc_4
Example
Tran_id Billing_id Service_Date Diagnosis_1 Diagnosi_Desc_1 Diagnosis2 Diagnosi_Desc_2 Diagnosis3 Diagnosi_Desc_3 Diagnosis4 Diagnosi_Desc_4
1 99213 20150922 461.9 Sinusitis, acute 786.2 cough
2 99213 20150203 V70.01 Well exam 401.1 Hypertension 461.9 Sinusitis 564.00 constipation
3 99211 20150305 461.9 Sinusitis V70.01 Well exam 786.2 cough
4 99213 20150203 401.1 hypetension V70.01
5 99213 20150402 486 Pneumonia 786.2 Cough 401.1 hypertension

How can I write a query to get Most Used Diagnosis?
Or how can I get count of Diagnosis?
If I get query like diagnosis and there count
Diagnosis1 Count Diagnosis2 count Diagnosis3 count Diagnosis4 count
461.9 2 786.2 2 461.9 1 564.00 1
V70.01 1 V70.01 2 786.2 1
486 1 401.1 1 401.1 1
401.1 1
OR
Can I have result like this as well (sort by diagnosis)
Diagnosis count
786.2 3
V70.01 3
461.9 2
401.1 2
486 1
564.00 1
please help me to get above result

2

something like:

with cte as
( 
    select d.diag
    from tablea
    cross apply
    (
        values (Diagnosis_1), (Diagnosis_2), (Diagnosis_3)
   ) d(diag)

select diag as diagnosis, count(diag) as 'count'
from cte
group by count(diag)
order by count(diag) desc
3

thank you for taking time to reply my query
I am new to SQL
table name is billing_detail
when i run query it is asking me to enter value

Thanks again
hdr

4

sorry wrong info for service_date
service_date is on different table --- billing_header
and would like to filter by service date

Thanks
hiral

5

Is the service date a range or a single value? Is it being passed in or built within the query? (Like last day of last month, etc.)