Trying to group my query results by date range bu

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

Trying to group my query results by date range bu

Author

Topic

bentham7246
Starting Member

6 Posts

Posted - 2014-11-07 : 06:00:08

Hi all

I'm running an SQL query as below:

SELECT
pstn_cyc_id as Date1,
etl_err_id as Error Code,
count(err_notes_tx) as Records

FROM t_alm_etl_obj_fld_err_log

group by etl_err_id, pstn_cyc_id
order by pstn_cyc_id, etl_err_id

This gives me an output like this:

Date1---------Error Code--------Records
20140930----------A-----------------7
20130705----------B-----------------1
20141004----------C-----------------13
20131222----------D-----------------2
20141105----------A-----------------2
20140901----------B-----------------24
20130916----------C-----------------6
20140914----------D-----------------7
20140129----------Z-----------------9
20140608----------A-----------------44
20140211----------E-----------------1
20144923----------K-----------------8
and so on

There are many records across many dates, this is only a very small sample. Essentially this is what I would like my output to look like:

DateRange-----------------------------Error Code--------Records
20140901 to 20140907---------------------A-----------------7
20140901 to 20140907---------------------B-----------------7
20140901 to 20140907---------------------C-----------------7
20140908 to 20140914---------------------A-----------------7
20140908 to 20140914---------------------D-----------------7
20140915 to 20140921---------------------A-----------------7
20140922 to 20140928---------------------A-----------------7

So i would have date groupings in the first column (by week) and the grouping of error codes in the middle column and finally the no of records in the last.

I'm really stuck with the coding of the first column

Any ideas?

Thanks

Ryan

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-11-10 : 14:18:13

I'd suggest creating and populating a look-up table that defined the date range to group by. Join that table to your t_alm_etl_obj_fld_err_log table and group by the date range. Perhaps something like:

create table DateRanges (
   StartDate date not null,
   EndDate   date not null
   )

insert into DateRanges....

select
   dr.StartDate + ' to ' + dr.EndDate as DateRange,  -- Use Convert to get the date format you want!
   a.etl_err_id as Error Code,
   count(*) as Records
from
   t_alm_etl_obj_fld_err_log a
inner join
   DateRange dr
      on a.pstn_cyc_id between dr.StartDate and dr.EndDate
group by
   dr.StartDate,
   dr.EndDate,
   a.etl_err_id

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources