Solved

SQL requests grouped by dates

Posted on 2015-02-24
31
60 Views
Last Modified: 2015-03-03
Hello experts,

I have the following sql table with the following information:

Id      Request_date      Requester      Status      Contributor      Open date      Effective resolution
4355      4/2/2014 9:17      Tom      Pending      ID1      5/2/2014 9:10      6/4/2014 9:17
6128      4/3/2014 7:17      William      Solved      ID2      4/3/2014 7:19      6/4/2014 9:17
6148      4/4/2014 9:17      Thomas      Untreated      ID3      4/4/2014 9:17      6/4/2014 9:17
      5/4/2014 9:17      Thomas      In progress      ID1      6/4/2014 9:17      4/3/2014 7:19


I would like to set up the following count queries:

1-Global query grouped by Status and by month

Treated Pending Solved Untreated
01/2015
02/2015
03/2015
04/2015

2-Global query grouped by Status and by week

Treated Pending Solved In Progress
W1
W2
W3
W4

3-Global query grouped by Status and by day

Treated Pending Solved Untreated In progress
Yesterday
Today


4-Query grouped by Contributor and filterd by effective resolution by date

Tom, William, Thomas
24/02/2015
23/02/2015
22/02/2015


Thank you in advance for your help!
0
Comment
Question by:LD16
  • 15
  • 13
  • 2
  • +1
31 Comments
 
LVL 13

Expert Comment

by:Mark Bullock
ID: 40629956
This SQL Fiddle example I wrote should get you started.
http://sqlfiddle.com/#!6/4a64a/5

Here's the idea. You can work out the formatting.
select LEFT(CAST(dt as varchar(20)), 3) + '-' + CAST(YEAR(dt) as CHAR(4))
, SUM(CASE WHEN status='Pending' THEN 1 ELSE 0 END) as Pending
, SUM(CASE WHEN status='Solved' THEN 1 ELSE 0 END) as Solved
, SUM(CASE WHEN status='Untreated' THEN 1 ELSE 0 END) as Untreated
, SUM(CASE WHEN status='In Progress' THEN 1 ELSE 0 END) as In_Progress
from mytable
group by LEFT(CAST(dt as varchar(20)), 3) + '-' + CAST(YEAR(dt) as CHAR(4));

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40630381
Please explain what is to be counted?

e.g.
if raised in 2014/04 but resolved in 2014/07 what row is it on?

how is each status counted if there is no date specific to that status? (or is there a date for each status?)
Q2 how do you define "week"? (starts on Monday? & week number one is first Monday of year?)

Q4 is complex. How many columns do you expect?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40631391
Hi,

select dateadd( day, datediff( day, 0, somedatetimemaybenow ), 0 )

will return the start of today. Works well for year/quarter/month/day/hour. Week is more problematic.

HTH
  David
0
 

Author Comment

by:LD16
ID: 40631599
Hello experts,

Here are some clarifications:

-Example of the table:

INT_Id      INT_Query_Date      INT_Contributor      INT_Status      INT_Effective_resolution
4355      4/2/2014 9:17      ID263025      Solved      4/11/2014 9:59
6128      12/4/2013 17:41      ID73753      Solved      3/5/2014 12:04
6148      12/5/2013 16:13      ID291989      Solved      7/24/2014 15:03
6331      2/24/2014 11:50      ID80568      Pending      
6350      3/11/2014 15:49      ID249401      Solved      3/11/2014 15:59
6360      2/14/2014 11:58      ID73753      In Progress      
6416      3/12/2014 15:43      ID263025      Solved      4/11/2014 11:56
6494      2/6/2014 14:36      ID249401      Solved      2/6/2014 14:36
6496      1/1/2014 11:38      ID73753      Solved      3/7/2014 9:28
6501      1/1/2014 11:45      ID263025      Pending      
6580      1/15/2014 11:44      ID80568      Solved      2/3/2014 23:06
6601      1/16/2014 15:01      ID263025      In Progress      
6636      1/17/2014 13:49      ID73753      Solved      2/14/2014 11:29
6652      2/11/2014 13:44      ID263025      Untreated

-All the query that I want are related to select count INT_Id

1)Count of Int_Id grouped by status and by day
In Progress      Pending      Solved      Untreated      Grand Total
12/4/2013                  1            1
12/5/2013                  1            1
1/1/2014                  1            1
1/1/2014             1                  1
Grand Total      

2)Count of Int_Id grouped by status and week from Monday to Sunday
In Progress      Pending      Solved      Untreated      Grand Total
W1            1            1
W2                  1            1
W3                  1            1
W4            1                  1
Grand Total

3)Same process by month

4) Count of Int_Id grouped by Contributor reference date: today

In Progress      Pending      Solved      Untreated      Grand Total
ID249401                  2            2
ID263025      1      1      2      1      5
ID291989                  1            1
ID73753      1            3            4
ID80568            1      1            2
Grand Total      2      2      9      1      14

Let me know if you need more clarifications.
0
 
LVL 13

Expert Comment

by:Mark Bullock
ID: 40631620
To get the grand total you can do a UNION with the same query, leaving off the GROUP BY clause.
0
 

Author Comment

by:LD16
ID: 40631655
I am not very familiar with SQL, could you please provide the full query?

Thank you again for your help.
0
 

Author Comment

by:LD16
ID: 40631857
@Mark I tried your request but the data that I have is not consistent.
Capture.GIF
Int_DebutTraitement is the Int_Querydate, so this take into account the sum of In_Id related to queries opened on Feb.
The thing is for the Pending and In progress ticket is a uptodate count because you can have ticket which still pending in Feb however the Int_QueryDate is equal to jan_2015 or probably 2014.

For the solved ticket I can Appy the effective resolution which it means that the ticket was close at one specific date and I don't take into account when the query was done.

How can I combine properly all this stuff in such a way that for pending and In progress I don't take into account the Int_Querydate but the status of the pending ticket from the first day of the month till the last day of the month.
Then at the same time for Solved ticket I apply the Int_effectiveresolution and Opened ticket I appy IntQueryDate.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40631892
there are options for grand total, such as group by with rollup
and/or use of CTEs to help performance
and lastly, to use UNION ALL
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40631907
@LD16

Thank you for the more accurate set of records from the table.

However your example results should be accurate based on the sample provided.

I think you are trying to explain that the results are cumulative in some way, but words are so easily misunderstood. Things become much clearer to all involved if the "expected result" is fully provided.

(and yes, this may be difficult, but if its difficult for you who understands the requirements imagine what it's like for someone who does not understand the requirement yet)
by the way, why not also tell us the real table name (that way we don't use table1 or mytable etc.)

{+edit} oh! I see it in the image T_Intervention_INT
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40632043
this result:
|   YR | MN | IN_PROGRESS | PENDING | SOLVED | UNTREATED | TOTAL |
|------|----|-------------|---------|--------|-----------|-------|
| 2013 | 12 |           0 |       0 |      2 |         0 |     2 |
| 2014 |  1 |           1 |       1 |      5 |         0 |     7 |
| 2014 |  2 |           2 |       2 |      3 |         1 |     8 |
| 2014 |  3 |           2 |       2 |      2 |         1 |     7 |
| 2014 |  4 |           2 |       2 |      1 |         1 |     6 |
| 2014 |  5 |           2 |       2 |      1 |         1 |     6 |
| 2014 |  6 |           2 |       2 |      1 |         1 |     6 |
| 2014 |  7 |           2 |       2 |      0 |         1 |     5 |
| 2014 |  8 |           2 |       2 |      0 |         1 |     5 |
| 2014 |  9 |           2 |       2 |      0 |         1 |     5 |
| 2014 | 10 |           2 |       2 |      0 |         1 |     5 |
| 2014 | 11 |           2 |       2 |      0 |         1 |     5 |
| 2014 | 12 |           2 |       2 |      0 |         1 |     5 |
| 2015 |  1 |           2 |       2 |      0 |         1 |     5 |
| 2015 |  2 |           2 |       2 |      0 |         1 |     5 |

Open in new window


Produced by this query:
declare @start_at as datetime = '20131201'

;with r (start_dt, end_dt) as (
  
  select @start_at as start_dt, dateadd(month,1,@start_at) as end_dt
  union all
  select dateadd(month,1,start_dt), dateadd(month,1,end_dt)
  from r
  where dateadd(month,1,start_dt) < getdate()
  
  )
select
      YEAR(r.start_dt) Yr
    , MONTH(r.start_dt) Mn
    , COUNT( CASE WHEN INT_Status = 'In Progress' THEN T.INT_ID END ) AS In_Progress
    , COUNT( CASE WHEN INT_Status = 'Pending'     THEN T.INT_ID END ) AS Pending
    , COUNT( CASE WHEN INT_Status = 'Solved'      THEN T.INT_ID END ) AS Solved
    , COUNT( CASE WHEN INT_Status = 'Untreated'   THEN T.INT_ID END ) AS Untreated
    , COUNT( INT_ID )                                                 AS Total
from r
left join T_Intervention_INT as T 
on r.end_dt <= ISNULL(T.INT_Effective_resolution,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) 
and r.start_dt >= DATEADD(MONTH, DATEDIFF(MONTH, 0,T.INT_Query_Date), 0)
group by
      YEAR(r.start_dt), MONTH(r.start_dt)
order by
      YEAR(r.start_dt), MONTH(r.start_dt)
;

Open in new window


from this data:
CREATE TABLE T_Intervention_INT
	([INT_Id] int, [INT_Query_Date] datetime, [INT_Contributor] varchar(8)
     , [INT_Status] varchar(11), [INT_Effective_resolution] DATETIME)
;
	
INSERT INTO T_Intervention_INT
	([INT_Id], [INT_Query_Date], [INT_Contributor], [INT_Status], [INT_Effective_resolution])
VALUES
	(4355, '2014-04-02 09:17:00', 'ID263025', 'Solved', '2014-04-11 09:59:00'),
	(6128, '2013-12-04 17:41:00', 'ID73753', 'Solved', '2014-03-05 12:04:00'),
	(6148, '2013-12-05 16:13:00', 'ID291989', 'Solved', '2014-07-24 15:03:00'),
	(6331, '2014-02-24 11:50:00', 'ID80568', 'Pending', NULL),
	(6350, '2014-03-11 15:49:00', 'ID249401', 'Solved', '2014-03-11 15:59:00'),
	(6360, '2014-02-14 11:58:00', 'ID73753', 'In Progress', NULL),
	(6416, '2014-03-12 15:43:00', 'ID263025', 'Solved', '2014-04-11 11:56:00'),
	(6494, '2014-02-06 14:36:00', 'ID249401', 'Solved', '2014-02-06 14:36:00'),
	(6496, '2014-01-01 11:38:00', 'ID73753', 'Solved', '2014-03-07 9:28:00'),
	(6501, '2014-01-01 11:45:00', 'ID263025', 'Pending', NULL),
	(6580, '2014-01-15 11:44:00', 'ID80568', 'Solved', '2014-02-03 23:06:00'),
	(6601, '2014-01-16 15:01:00', 'ID263025', 'In Progress', NULL),
	(6636, '2014-01-17 13:49:00', 'ID73753', 'Solved', '2014-02-14 11:29:00'),
	(6652, '2014-02-11 13:44:00', 'ID263025', 'Untreated', NULL)
;

Open in new window


inspect this working query at: http://sqlfiddle.com/#!3/a1f43/1

NOTE PLEASE.
If you are seeking cumulative results such as you see above, then I don't understand why you want a "grand total" row.
0
 

Author Comment

by:LD16
ID: 40633143
Hello PortelPaul,

Your request works perfectly:

Capture.GIF
Sorry, my requirements were not clear at all. And even though you were able to understand its.

Some extra comments:

1-The total column is related to the tickets received, in that case we cannot differentiate by t status but by the Int_Query_Date.
Is there a way to add the Tota_Opened_Tickets based on the Int_QueryDate?

2-Is there a way to have the same query including the Tota_Opened_Tickets day by day of the current month?


3-Is there a way to split by Contributor in order to see the IN_PROGRESS  PENDING SOLVED  TOTAL_OPENED_TICKETS of today?

Thank again for your help!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40634240
I'm simply going to repeat part of an earlier comment

@LD16

Thank you for the more accurate set of records from the table.

However your example results should be accurate based on the sample provided.

I think you are trying to explain that the results are cumulative in some way, but words are so easily misunderstood. Things become much clearer to all involved if the "expected result" is fully provided.

The skill of defining a specification is TO BE ACCURATE AND COMPLETE

take a small set of sample data
manually, convert that data into the "expected result"
not just a layout
the data shown in the "expected result" should be accurate and relate back to the sample data

The reason I am re-stating this is because I don't really understand you additional requests. Please illustrate what you are asking for with data and results.
0
 

Author Comment

by:LD16
ID: 40634301
Hello PortletPaul,

I am going to take your recent db as an example and output all the results that I want however it is not available anymore. Could you please re-make available your db which was at available at the following link:

http://sqlfiddle.com/#!3/a1f43/1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40634332
sqlfiddle isn't my service, I simply use it

while it's a terrific free service it isn't always up and running well

by the way, Excel is often very useful for the type of results I'm suggesting are needed
0
 

Author Comment

by:LD16
ID: 40634368
Ok, In order to simply my requests I will do it one by post.

1) Include a   Total_Opened_Tickets column to your last query (related to the comment id ID: 40632043) as it is explain above


Here is the reference data:
INT_Id      INT_Query_Date      INT_Contributor      INT_Status      INT_Effective_resolution
4355       2014-04-02 09:17:00      ID263025      Solved      2014-04-11 09:59:00
6128       2013-12-04 17:41:00       ID73753       Solved       2014-03-05 12:04:00
6148       2013-12-05 16:13:00       ID291989       Solved       2014-07-24 15:03:00
6331       2014-02-24 11:50:00       ID80568       Pending       NULL
6350       2014-03-11 15:49:00       ID249401       Solved       2014-03-11 15:59:00
6360       2014-02-14 11:58:00       ID73753       In Progress       NULL
6416       2014-03-12 15:43:00       ID263025       Solved       2014-04-11 11:56:00
6494       2014-02-06 14:36:00       ID249401       Solved       2014-02-06 14:36:00
6496       2014-01-01 11:38:00       ID73753       Solved       2014-03-07 9:28:00
6501       2014-01-01 11:45:00       ID263025       Pending       NULL
6580       2014-01-15 11:44:00       ID80568       Solved       2014-02-03 23:06:00
6601       2014-01-16 15:01:00       ID263025       In Progress       NULL
6636       2014-01-17 13:49:00       ID73753       Solved       2014-02-14 11:29:00
6652       2014-02-11 13:44:00       ID263025       Untreated       NULL

Here is the final result of the Total that I want to include in your last query

                     Total_Opened_Tickets
12 2013                 2
01 2014                 5
02 2014                 4
03 2014                 5

The count is based on the INT_Query_Date placed in column B of the reference data

It is clearer or you need more information for this requirement?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:LD16
ID: 40634404
2) Make a count of INT_Id breakdown by Status and on a daily basis of the current month

The reference data is the same of the one of comment above:

The output should be
                               IN PROGRESS  PENDING SOLVED UNTREATED   TOTAL_OPEN_TICKETS
2015 02 27                  2                      2             0                   1                           0
2015 02 26                 2                      2             0                   1                           0
.....
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40634415
1)
ok, that is exactly the type of result wanted - thank you

 in the query I provided in comment ID: 40632043 lines 12-19 are:
select
      YEAR(r.start_dt) Yr
    , MONTH(r.start_dt) Mn
    , COUNT( CASE WHEN INT_Status = 'In Progress' THEN T.INT_ID END ) AS In_Progress
    , COUNT( CASE WHEN INT_Status = 'Pending'     THEN T.INT_ID END ) AS Pending
    , COUNT( CASE WHEN INT_Status = 'Solved'      THEN T.INT_ID END ) AS Solved
    , COUNT( CASE WHEN INT_Status = 'Untreated'   THEN T.INT_ID END ) AS Untreated
    , COUNT( INT_ID )                                                 AS RunTotal

for a test, add another row to that

    , COUNT( DISTINCT T.INT_ID )                           AS TotalReceivedInMonth -- related to the tickets received
0
 

Author Comment

by:LD16
ID: 40634426
3) Make a count of INT_Id breakdown by contributor, all data displayed is related to cur date (in that case for 28/02/2015)

The reference data is the same

The output should be

                           IN PROGRESS  PENDING SOLVED UNTREATED   TOTAL_OPEN_TICKETS
 ID263025                1                        1            
 ID73753                 1
 ID291989
 ID80568                                             1
 ID249401
0
 

Author Comment

by:LD16
ID: 40634428
I will test all tomorrow as for me is 0:42AM.

Thank you again for your help!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40634431
2)
this cannot be produced at the same time as the other data.  
it could be "appended" to the former results by using a  UNION ALL, but this requires slight alteration to the former query as well

It  appears this data is cumulative like the monthly figures? please confirm.
0
 

Author Comment

by:LD16
ID: 40634434
Yes it is.
0
 

Author Comment

by:LD16
ID: 40634451
Just to clarify each of the three requirement is related to différents queries.
0
 

Author Comment

by:LD16
ID: 40634456
Requirement 2 and 3 as requirement 1 was to include a line to the previous query and it is almost done I just need to test it.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 40634627
2) needs to works at a daily level. (the query I have supplied already works at a monthly level.)

So it is a matter of moving from moths as units to working with days as units; HOWEVER

because the calculations are cumulative, the next question to ask is, where does the daily calculation start? Form the beginning of this month? beginning of last month? last quarter? this fiscal year?

each different starting point could produce a different result.

3) counting by contributor

declare @start_at as datetime = '20131201'

;with r (start_dt, end_dt) as (
  
  select @start_at as start_dt, dateadd(month,1,@start_at) as end_dt
  union all
  select dateadd(month,1,start_dt), dateadd(month,1,end_dt)
  from r
  where dateadd(month,1,start_dt) < getdate()
  
  )
select
      INT_Contributor      
    , COUNT( CASE WHEN INT_Status = 'In Progress' THEN T.INT_ID END ) AS In_Progress
    , COUNT( CASE WHEN INT_Status = 'Pending'     THEN T.INT_ID END ) AS Pending
    , COUNT( CASE WHEN INT_Status = 'Solved'      THEN T.INT_ID END ) AS Solved
    , COUNT( CASE WHEN INT_Status = 'Untreated'   THEN T.INT_ID END ) AS Untreated
    , COUNT( DISTINCT T.INT_ID )                                       AS Total
from r
left join T_Intervention_INT as T 
on r.end_dt <= ISNULL(T.INT_Effective_resolution,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) 
and r.start_dt >= DATEADD(MONTH, DATEDIFF(MONTH, 0,T.INT_Query_Date), 0)
group by
     INT_Contributor      
order by
     INT_Contributor      
;

Open in new window

0
 

Author Comment

by:LD16
ID: 40634682
3) I forgot this information beginning of this month.
4) though the query calculation is cumulative I was wondering if start date variable can be set up dynamically instead of static?

Ex: today (27/02/2015) if I launch the query start date will be 01/02/2015. As of Sunday (01/03/2015) if I launch the query stray date will be 01/03/2015.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40634704
yes that can be done

this gives the 1st day of the current month:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE() ) , 0)
0
 

Author Comment

by:LD16
ID: 40634917
Hello Peter,

I tested your last query and it is not displaying what I am want. I have the sum of In Progress Pending, Solved and Untreated tickets
And me I would like to have just the received tickets of the month I imagine that  we need to proceed  with something like this

 CASE WHEN INT_QueryDate is between the first day the month and the last day of the month.

Thank you for your date.

Regards,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40634939
You need to take care with this query due to the need for cumulative calculations. There are way more data rows in the joined resultset than in the source table, so if counting the result could easily be too large.

That is why I introduced DISTINCT in the count for that added column.

for example, using COUNT( DISTINCT INT_Id  )

6128       2013-12-04 17:41:00       ID73753       Solved       2014-03-05 12:04:00
6148       2013-12-05 16:13:00       ID291989       Solved       2014-07-24 15:03:00

The above two rows will produce as result of 2 for 2013 02
and it would remain a result of 2 even if those rows are expanded by the cumulative self join

A simple case expression by itself will not resolve this.

I am unable to test anything currently, and over the next few days may have no availability for this voluntary effort.
0
 

Author Comment

by:LD16
ID: 40634961
Ok, don't worry thank you anyway for your help!
Just one last thing is there a way to have different query with just the opened ticket month by month?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40635165
>>"is there a way to have different query"
yes

>>"with just the opened ticket month by month?"
yes (I think)

but I see no reason why the approach I have given doesn't work anyway, so perhaps I still don't understand.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40643523
Thank you. I trust you got all the queries working as you wanted then to.

Cheers, Paul
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now