December 20, 2011 at 12:32 am
Hi gurus,
I have another query.The output I want is as follows -
Date Count
7/1/2011 2
7/2/2011 1
7/3/2011 2
The table data and scripts are below -
CREATE TABLE XXOH_AMT
(Visit_Date DATETIME,
Patient_Id NVARCHAR(10),
Amount int )
----------------------
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2275', '20')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2275', '30')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2275', '40')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2298', '40')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2298', '10')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/02/2011 12:00:00 AM','2276', '50')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/02/2011 12:00:00 AM','2276', '40')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/03/2011 12:00:00 AM','2279', '20')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/03/2011 12:00:00 AM','2280', '20')
GO
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/03/2011 12:00:00 AM','2280', '20')
GO
Any help is much appreciated.
December 20, 2011 at 12:44 am
somewhat incomplete, just painting the background here...
INSERT INTO XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES
('07/01/2011 12:00:00 AM','2275', '20') ,
('07/01/2011 12:00:00 AM','2275', '30') ,
('07/01/2011 12:00:00 AM','2275', '40') ,
('07/01/2011 12:00:00 AM','2298', '40')
--etc.....
select CAST(VISIT_DATE AS date) AS 'date', count(VISIT_DATE) as count
from XXOH_AMT
GROUP BY Visit_Date
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 20, 2011 at 12:53 am
Henrico the output is not what I want based on your query it gives the output as
Date Count
2011-07-01 00:00:00.0005
2011-07-02 00:00:00.0002
2011-07-03 00:00:00.0003
December 20, 2011 at 1:01 am
are you casting your datetime format to date?
I run 2008 R2 and results:
date count
2011-07-014
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 20, 2011 at 1:04 am
I am on SQL Server 2005 and I get the error below when I use your query.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
December 20, 2011 at 1:09 am
ok replace then:
select (left(convert(varchar, VISIT_DATE, 120),10)) AS 'date', count(VISIT_DATE) as count
from XXOH_AMT
GROUP BY Visit_Date
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 20, 2011 at 1:13 am
Henrico this time there is no error but the result is as I told before and not what I was expecting.
December 20, 2011 at 1:36 am
First, since you are using SQL Server 2005, you really should have posted this question in a SQL Server 2005 forum.
Based on the data provided, try this:
CREATE TABLE dbo.XXOH_AMT
(VISIT_DATE DATETIME,
PATIENT_ID NVARCHAR(10),
Amount int )
----------------------
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2275', '20')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2275', '30')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2275', '40')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2298', '40')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/01/2011 12:00:00 AM','2298', '10')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/02/2011 12:00:00 AM','2276', '50')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/02/2011 12:00:00 AM','2276', '40')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/03/2011 12:00:00 AM','2279', '20')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/03/2011 12:00:00 AM','2280', '20')
GO
INSERT INTO dbo.XXOH_AMT ( VISIT_DATE, PATIENT_ID,Amount ) VALUES (
'07/03/2011 12:00:00 AM','2280', '20')
GO
select VISIT_DATE, count(distinct PATIENT_ID) as count
from XXOH_AMT
GROUP BY VISIT_DATE
GO
drop table dbo.XXOH_AMT;
GO
December 20, 2011 at 1:38 am
Just another aside, when posting code, post it so that it works in a case sensitive environment as well as a case insensitive environment.
My setup at home is case sensitive, so I had to make some adjustments to your code.
December 20, 2011 at 1:45 am
Lynn, he wants the datetime displayed as DATE only...
read my previous posts...which doesnt seem to work on his installation..
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 20, 2011 at 1:45 am
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) a
GROUP BY VISIT_DATE
December 20, 2011 at 1:47 am
Henrico Bekker (12/20/2011)
Lynn, he wants the datetime displayed as DATE only...read my previous posts...which doesnt seem to work on his installation..
I don't think his complaint with your code was to do with the format of the date, rather the issue that you were producing figures higher than he wanted in the "count".
Lynn Pettis (12/20/2011)
select VISIT_DATE, count(distinct PATIENT_ID) as countfrom XXOH_AMT
GROUP BY VISIT_DATE
GO
I always try to avoid distinct, so it didn't even cross my mind. Much more succinct than my version.
December 20, 2011 at 1:49 am
I only inserted a few of his records, not all, so the value of COUNT is irrelevant here.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 20, 2011 at 1:51 am
Henrico Bekker (12/20/2011)
I only inserted a few of his records, not all, so the value of COUNT is irrelevant here.
Yes, you only inserted a few but he inserted all and commented that the result was incorrect.
mathewspsimon (12/20/2011)
Henrico the output is not what I want based on your query it gives the output asDate Count
2011-07-01 00:00:00.0005
2011-07-02 00:00:00.0002
2011-07-03 00:00:00.0003
Run your code against his entire data-set and you'll get the "count" he shows above. He wanted the distinct count, which was : -
VISIT_DATE Count
----------------------- -----------
2011-07-01 00:00:00.000 2
2011-07-02 00:00:00.000 1
2011-07-03 00:00:00.000 2
And is produced by Lynn's and my code.
December 20, 2011 at 2:28 am
thanks it working now.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply