February 13, 2002 at 10:06 am
Newbie needs to create a date table with all the dates between start date and end date.
Mark D. Simpson
Edited by - mdsnorge on 02/13/2002 11:01:58 AM
Edited by - mdsnorge on 02/13/2002 12:22:49 PM
Mark D. Simpson
February 13, 2002 at 10:47 am
Hey Mark,
Before we tackle how, could you expound on why? Just curious!
Andy
February 13, 2002 at 10:58 am
Easiest way I have seen is a cursor. But pls answer Andy's question. We may have a better solution.
Steve Jones
February 13, 2002 at 12:24 pm
quote:
Newbie needs to create a date table with all the dates between start date and end date.I am producing productivity stats for about 100 practitioners. I have all of their procedural and qty. info. along with the date that the procedures were completed. I must separate each practitioner by ID, and Total Revenue on each date between a chosen start and end date.
Mark D. Simpson
Edited by - mdsnorge on 02/13/2002 11:01:58 AM
Edited by - mdsnorge on 02/13/2002 12:22:49 PM
Mark D. Simpson
Mark D. Simpson
February 13, 2002 at 12:30 pm
Not sure I understand, but I think a GROUP by will do this. Can you post a sample of 3 or 4 practitioners with a few dates for each?
Steve Jones
February 13, 2002 at 1:08 pm
The following are Column Headers:
PRACTID, RVU, DISCIPLINE, BILLDATE, NUMBPERFORMED, PATIENTTYPE
The following is data for four practitioners:
PT0001, 3, ORT, 12/31/2001, 1, I
OT0002, 1, BRN, 01/03/2002, 2, O
SP0003, 4, OTO, 02/01/2002, 1, I
PT0001, 2, ORT, 01/25/2002, 2, I
Hope this helps.
Mark D. Simpson
Mark D. Simpson
February 13, 2002 at 1:19 pm
So are you looking for, say between 12/31/01 and 1/15/02, the following data:
PT001 3
OT002 1
What I am asking is what results go with this source data.
Steve Jones
February 13, 2002 at 1:38 pm
First I would isolate all the information for PT0001, then I would multiply all the RVU's by the Number Performed for that Practitioner. After that has been accomplished, I would report for each billing date the sum of RVU generated.
The Outcome would look like this:
12/23/2002 32
12/24/2002 27
12/25/2002 0
12/26/2002 0
12/27/2002 29
etc...adnauseam
Does this make sense?
As you can see, this could even become more complicated. I am sure at some point Admn. will want this information broken out by Discipline and PatientType as well.
Mark D. Simpson
Edited by - mdsnorge on 02/13/2002 1:43:51 PM
Edited by - mdsnorge on 02/13/2002 1:51:30 PM
Mark D. Simpson
February 14, 2002 at 11:07 am
The zeros would be a problem unless you store a 0 value for each date. I see why you want a table of all days.
For the basic data, couldn't you do a
select practid
, RVU
, sum( numberperformed)
, date
group by practid, rvu, date
To add in the zeros, you'd have to do some join with a table or use a the client to fill in the days.
Steve Jones
February 16, 2002 at 8:44 am
I am still looking for some advice about how to create the date table.
Mark D. Simpson
Mark D. Simpson
February 16, 2002 at 9:49 am
will this solution work??
SELECT DATEADD(dd,Numbers.Num,'2001-01-01')
FROM
(
SELECT Tens.Num + Hundreds.Num As Num
FROM
(
SELECT 0 AS Num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) As Tens
CROSS JOIN
(
SELECT 0 AS Num UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90
) As Hundreds
) As Numbers
WHERE (DATEADD(dd,Numbers.Num,'2001-01-01') BETWEEN '2001-01-01' AND '2001-01-31')
ORDER BY DATEADD(dd,Numbers.Num,'2001-01-01')
you can increase the period by adding another more numbers (i.e) thousands etc
i saw this solution some time ago and have been using it to create look up date or number lists , without having to create a table!!!
Hope this helps
February 16, 2002 at 8:46 pm
Short and simple:
DECLARE @date AS DATETIME
DECLARE @int AS INT
SET @date = '1/2/2002' /* Change to the day you wish to start at. */
SET @int = 0
WHILE @int < 20 /* Change 20 to your value to number of days to insert + 1 for instance with this example to insert thru 1/10/2002 change value to 9 due to the fact that once @int = 9 it will stop executing the items in the middle. */
BEGIN
INSERT INTO tblX (datefld) VALUES (DATEADD(d,@int,@date))
SET @int = @int + 1
END
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply