How to minimize repetitive coding? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to minimize repetitive coding?

Author

Topic

yeung_lcd
Starting Member

5 Posts

Posted - 2011-06-28 : 22:41:21

User request to generate a report from back-end with specified different time ranges and count by the time ranges.

The time ranges are “odd” so the resulted query is long and messy.

This kind of requests is common but the time range and required columns are different in each request. How can I make the code tidy for better checking and management?

Thank you.
yeung

/**** count columnA by requested Datetime ****/
--08:15 to 08:35
--09:00 to 10:35
--11:00 to 12:00
--13:00 to 14:15

select
Time_cat = case

when (datepart(hour,Requested_Datetime)=8 and
datepart(minute,Requested_Datetime)>=15 and
datepart(minute,Requested_Datetime)<35)
then '08:15 to 08:35'

when (datepart(hour,Requested_Datetime)=9
or
(datepart(hour,Requested_Datetime)=10 and
datepart(minute,Requested_Datetime)<35))
then '09:00 to 10:35'

when (datepart(hour,Requested_Datetime)=11)
then '11:00 to 12:00'

when (datepart(hour,Requested_Datetime)=13 or
(datepart(hour,Requested_Datetime)=14 and
datepart(minute,Requested_Datetime)<15))
then '13:00 to 14:15'
End,

count(distinct columnA) Cnt

from Table

where
Requested_Datetime>='01May2009'and
Requested_Datetime<'01May2010'and

(
--8:15 to 8:35
(datepart(hour,Requested_Datetime)=8 and datepart(minute,Requested_Datetime)>=15 and datepart(minute,Requested_Datetime)<35)
or
--9:00 to 10:35
( datepart(hour,Requested_Datetime)=9
or
(datepart(hour,Requested_Datetime)=10 and datepart(minute,Requested_Datetime)<35)
)
or
--11:00 to 12:00
(datepart(hour,Requested_Datetime)=11)
or
--13:00 to 14:15
( datepart(hour,Requested_Datetime)=13
or
(datepart(hour,Requested_Datetime)=14 and datepart(minute,Requested_Datetime)<15)
)
group by
case when (datepart(hour,Requested_Datetime)=8 and
datepart(minute,Requested_Datetime)>=15 and
datepart(minute,Requested_Datetime)<35)
then '08:15 to 08:35'

when (datepart(hour,Requested_Datetime)=9
or
(datepart(hour,Requested_Datetime)=10 and
datepart(minute,Requested_Datetime)<35))
then '09:00 to 10:35'

when (datepart(hour,Requested_Datetime)=11)
then '11:00 to 12:00'

when (datepart(hour,Requested_Datetime)=13 or
(datepart(hour,Requested_Datetime)=14 and
datepart(minute,Requested_Datetime)<15))
then '13:00 to 14:15'
End
order by time_Cat

compute sum(count(distinct columnA))

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-28 : 23:35:06

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

Do not use COMPUTE; it is proprietary and deprecated.

WITH ReportPeriod (start_time, end_time(
AS
(VALUES (CAST('08:15:00' AS TIME), CAST ('08:35:00' AS TIME))
('09:00:00', '10:35:00'),
('11:00:00', '12:00:00'),
('13:00:00', '14:15:00'))

SELECT ..
FROM Foobar
WHERE CAST (requested_datetime AS DATE) BETWEEN '2009-05-01' AND '2010-05-02'
AND CAST (requested_datetime AS TIME) BETWEEN start_time AND end_time;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources