I believe what you're after is this: -

SELECT VISIT_DATE, COUNT(VISIT_DATE) AS [Count]

FROM (SELECT VISIT_DATE

FROM XXOH_AMT

GROUP BY Visit_Date, PATIENT_ID)...

I think you must have gone to school with the DBA...

DECLARE @givenDate AS DATETIME

SET @givenDate = '2011-11-05'

SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@givenDate)+1,0)), --As date

DAY(DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@givenDate)+1,0))) --As days

DECLARE @givenMonth AS INT

SET @givenMonth = '11'

SELECT DAY(DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,(DATEADD(mm, @givenMonth-1, 0)))+1,0)))

Pivot ?

I felt you needed a reply, as you've provided us with DDL and sample data so I've had a go.

There's a possibility I haven't understood your problem correctly, so test...

You're trying to GROUP BY *, which you can't do. You have to explicitly state each column.

Good 😉

Did this script work for you?

SELECT --it.*, cqv.QuestionID, cqv.Question, cqv.Answer

it.NameID, it.IncidentDate, it.ClaCaseID,

MAX(CASE WHEN cqv.QuestionID = 1 THEN cqv.Answer ELSE NULL...

OK, one more question 😀

Is the Question ID always 1 for 'Did did the incident occur at night', always 2 for 'Were there any witnesses' and always 3 for 'Who...

Jeff Moden (12/14/2011)

---

Just to be clear, it's not really a "view". It's actually an Inline Table Value Function which can be made to work as if it were a...

I think you're on the right track. I think I'd have a timezone table and a daylight savings table as lookups, rather than just a timezone table.

You've missed things in your sample data.

The code you use to get the current result is as follows (from your post, but corrected to use the temp tables you provided).

SELECT...

Use RIGHT('0' + yourData, 2)

e.g.

SELECT RIGHT('0' + test,2), test

FROM (SELECT '1' UNION ALL SELECT '2' UNION ALL

SELECT '3' UNION ALL SELECT '4' UNION ALL

...

BEGIN TRAN

--First, let's create some sample data to play with!!

SELECT [Claimant Number], --This comes from your sample data

(ABS(CHECKSUM(NEWID())) % 100) + 1 AS [Claim Number], --This is a pseudo-randomly generated...

mic.con87 (12/14/2011)

---

Thank you very much for your explanations and for providing both solutions. Your query in fact does something that I hadn't initially required but is very useful and I'll...

Jeff Moden (12/13/2011)

---

Okidoki... First, please forgive my OCD nature when it comes to formatting. I just couldn't look at multiple INSERT/VALUES or all lower case object names anymore. These...