Date formatted as "mm/yyyy" and still maintain date properties 1

[njvsummit]

I have a table that contains a couple of million records. The records are individual services provided by a couple hundred service providers. Most of what I need to review (the table is updated on a weekly basis) revolves around monthly service totals for each of the providers. An example of some typical rows would be:

PROVIDER DATE SERVICE UNITS BILLED
SUPPORT 7/21/2003 COUNSELING 1 $80.00
SUPPORT 7/28/2003 COUNSELING 1 $80.00
SUPPORT 9/21/2003 COUNSELING 1 $80.00
JAIL 7/21/2003 ASSESSMT 1 $80.00
JAIL 7/23/2003 ASSESSMT 1 $80.00
JAIL 8/21/2003 COUNSELING 1 $80.00


Since creating the totals on the fly in views or within a reporting package takes forever, I’m trying to create a summary table that would look something like this:

PROVIDER DATE SERVICE UNITS BILLED
SUPPORT 7/2003 COUNSELING 2 $160.00
SUPPORT 9/2003 COUNSELING 1 $80.00
JAIL 7/2003 ASSESSMT 2 $160.00
JAIL 8/2003 COUNSELING 1 $80.00

The problem that I am having is with the date. Can I have a date formatted as “7/2003” and still have it maintain its date properties for selection and sorting purposes ( I’d like to be able to create a where clause that will select, for example, all the entries between 1/2003 and 12/2003)?

Thanks for the help,

Nick

 

[john76]

My first comment would be, have you checked the styles available for the CONVERT function? Perhaps you could use that in your select query to format the datetime value to the format you would like to see.

SELECT  PROVIDER,
        CONVERT(varchar, DATE, [COLOR=red]101[/color]) DATE,
        SERVICE,
        UNITS,
        BILLED
  FROM    TABLE

Alternately, you could format the date like this:

SELECT  PROVIDER,
        [COLOR=red]CONVERT(varchar, MONTH(DATE))+'/'+CONVERT(varchar, YEAR(DATE))[/color] DATE,
        SERVICE,
        UNITS,
        BILLED
  FROM    TABLE

Hope this helps,
John

 

[njvsummit]

Thanks,

The styles do not cover it, but your second option worked. I did have to switch it to yyyy/mm to maintain the "sortability", but it does the trick.

 

[njvsummit]

Actually, I spoke to soon. Still having issues with sorting...

 

[john76]

Well, you don't have to order by the newly generated date value, try this:

SELECT  PROVIDER,
        CONVERT(varchar, MONTH([COLOR=red]YourDateField[/color]))+'/'+CONVERT(varchar, YEAR([COLOR=red]YourDateField[/color])) DATE,
        SERVICE,
        UNITS,
        BILLED
  FROM    TABLE
  [COLOR=red]ORDER BY YourDateField [DESC][/color]

If this didn't get it, please explain a little more concisely what problem you are having.

John

 

[TJRTech]

Maybe I am missing something, but it looks like your summary table is a summary of billed totals, by provider, service by month. And, I assume that your DATE column is a DATETIME type in both tables...if NOT I recommend you change it to one if you can.

So, when you do a query from your detailed records to fill your summary table you should do something like the following:

INSERT INTO MonthlySummary
  (PROVIDER, DATE, SERVICE, UNITS, BILLED)
SELECT PROVIDER, 
       convert(datetime, 
               convert(varchar,datepart(mo, DATE))+
               '/01/'+
               convert(varchar,datepart(yy, DATE))), 
       SERVICE, 
       SUM(UNITS), 
       SUM(BILLED)
FROM DetailsTable
GROUP BY PROVIDER,
         convert(datetime, 
                 convert(varchar,datepart(mo, DATE))+
                 '/01/'+
                 convert(varchar,datepart(yy, DATE)))

Then, you can simply do an order by on the DATE column when selecting from the summary table and all will work just fine.

I recommend keeping the DATE column as a DATETIME column, not varchar or else, as I see SO many people ask SO many questions on this site about parsing and sorting of string columns that hold DATE values. The BEST way to get around it is to ALWAYS use datetime, then to format via SQL Views or UI functions when you want to DISPLAY a different format.

TR

 

[TJRTech]

Just realized I forgot the "SERVICE" column in the GROUP by for the insert/select.

TR

 

[TJRTech]

And, another thing...take a close look at the SELECT portion of the query.

I suspect if you could put an index on the PROVIDER and SERVICE columns, and a clustered index on DATE (all in your detailed records table), that even for a couple of million records you will find that you CAN aggregate the monthly statistics in just a few seconds (10, 20 maybe at most on a higher end server).

TR

 

[njvsummit]

TJRTech,

Thank you. This is exactly what I was looking for. I don't mean to suggest more work, but it seems like a perfect "FAQ" topic.

Thanks again,

Nick

 

[TJRTech]

What, in particular was of most help?

a. The insert/select syntax?

b. The idea of indexes and forgoing the summary table

c. The advise to avoid using varchar columns to hold dates

d. All of the above


Regards,
TR

 

[TJRTech]

Oh, and another thing, I was showing some pretty fancy date calcs to get first day of the month. You can do it more easily with:

select convert(datetime, convert(varchar(11), dateadd(dd, -datepart(dd, GetDate()-1), GetDate())))

Dunno if that is more easy or not.

Also, instead of grouping by the converted datetime column, you could group by: datepart(yy, DATE), datepart(mo, DATE)
...in other words by the year, then the month.

Anyone, good luck.

TR

 

[njvsummit]

TR,

C was most helpful. Adding the '01' and keeping the resultant field a date escaped me as I tried manipulating the varchar result in such a way that I could sort it as if it were a date. The index suggestion is also good, but in this case my lower end server still chugs away for a couple of minutes (although fewer minutes) with the appropriate indices.

Thanks,

Nick