SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case with top
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/08/2013 :  08:47:27  Show Profile  Reply with Quote
Hi,

I'm trying to add another top 6 case statement to calulate the day before stats for a category field. Can this be included in the case statement?

Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat, --actual results
Case When c.OCCURED_DT = DATEADD("d",-1,GETDATE()) --calulate yesterday results
Then count(c.ID)End AS TotalCatYesterday
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by
c.INTI_CATEGORY ,
c.OCCURED_DT
Order By TotalCat Desc

SZ1
Learning and development is the driving force in the universe...!

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/08/2013 :  23:53:36  Show Profile  Reply with Quote
sorry didnt get that. can you illustrate with sample data?
you'll only get 6 rows from above statement so didnt understand need of another top 6 over this!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/09/2013 :  03:09:12  Show Profile  Reply with Quote
Hi

I need to include another top 6 for previous day too if this is possible.

The data I get back is 6 rows by category showing the top highest categories. I also want to be able to see the previous days top 6 categories to be able to compare and see whats gone up and down in the list. So I was thinking if I add to the code below an extra Top 6 case statement for the previous day I could get the results?

can this be achieved in the same query? or do I need 2 seperate datasets...

Maybe something like this:

--todays results
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCatToday,
--yesterdays results
Case When c.OCCURED_DT <= DATEADD("d",-1,GETDATE())
Then count(c.ID)End AS TotalCatYesterday
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by
c.INTI_CATEGORY ,
c.OCCURED_DT
Order By TotalCat Desc

Results: TotalCatYesteday is showing same results as TotalCatToday even though the dateadd is <= previous day?

any ideas?

INTI_CATEGORY TotalCatToday Date TotalCatYesterday
-------------------------------- ------------- ----------------------- -----------------
Central 129 2013-01-08 09:16:11.537 129
Local 93 2013-01-08 09:16:11.537 93
Core Applications 74 2013-01-08 09:16:11.537 74
Printers 54 2013-01-08 09:16:11.537 54
Mail Services 23 2013-01-08 09:16:11.537 23
Thin Client 21 2013-01-08 09:16:11.537 21

(6 row(s) affected)


SZ1
Learning and development is the driving force in the universe...!

Edited by - sz1 on 01/09/2013 03:26:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  03:31:18  Show Profile  Reply with Quote
do you mean getting previous ranks for top 6 items today or do you mean getting top 6 items for today and yesterday side by side?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/09/2013 :  04:04:10  Show Profile  Reply with Quote
Top 6 rank today and yesterday side by side, is this possible in same query?...been trying to just pull the records for yesterday using the occured date field below to exclude calls less than datetime but I'm struggling a bit to get them side by side

Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.OCCURED_DT <= '2013-01-08 23:59:59' /*And c.OCCURED_DT <= '2013-01-08 23:59:59'*/--been trying to just get figure here
--DATEADD(d,-1,GetDate())
Group by c.INTI_CATEGORY
Order By TotalCat Desc

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  04:23:28  Show Profile  Reply with Quote

SELECT Seq,
MAX(CASE WHEN DATEDIFF(dd,0,c.OCCURED_DT)= DATEDIFF(dd,0,GETDATE()) THEN INTI_CATEGORY END) AS TodayCat,
MAX(CASE WHEN DATEDIFF(dd,0,c.OCCURED_DT)= DATEDIFF(dd,1,GETDATE()) THEN INTI_CATEGORY END) AS YestCat
FROM
(
Select c.INTI_CATEGORY, count(c.ID) As TotalCat,
ROW_NUMBER() OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,c.OCCURED_DT),0) ORDER BY count(c.ID) DESC) AS Seq
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.OCCURED_DT >= '2013-01-08' 
And c.OCCURED_DT < '2013-01-10'
Group by c.INTI_CATEGORY,DATEADD(dd,DATEDIFF(dd,0,c.OCCURED_DT),0)
)t
WHERE Seq<=6
GROUP BY Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Edited by - visakh16 on 01/09/2013 04:23:59
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/09/2013 :  04:35:08  Show Profile  Reply with Quote
I'm getting c.OCCURED_DT cant be bound on the first 2 below

MAX(CASE WHEN DATEDIFF(dd,0,c.OCCURED_DT)= DATEDIFF(dd,0,GETDATE()) THEN INTI_CATEGORY END) AS TodayCat,
MAX(CASE WHEN DATEDIFF(dd,0,c.OCCURED_DT)= DATEDIFF(dd,1,GETDATE()) THEN INTI_CATEGORY END) AS YestCat

Also, I see you have entered actual dates, is it possible for the query to recalculate on a daily basis?

Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  04:37:56  Show Profile  Reply with Quote

SELECT Seq,
MAX(CASE WHEN dt = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) THEN INTI_CATEGORY END) AS TodayCat,
MAX(CASE WHEN dt= DATEADD(dd,DATEDIFF(dd,1,GETDATE()),0) THEN INTI_CATEGORY END) AS YestCat
FROM
(
Select c.INTI_CATEGORY, count(c.ID) As TotalCat,DATEADD(dd,DATEDIFF(dd,0,c.OCCURED_DT),0) AS dt,
ROW_NUMBER() OVER (PARTITION BY DATEADD(dd,DATEDIFF(dd,0,c.OCCURED_DT),0) ORDER BY count(c.ID) DESC) AS Seq
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
And c.OCCURED_DT >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) 
And c.OCCURED_DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
Group by c.INTI_CATEGORY,DATEADD(dd,DATEDIFF(dd,0,c.OCCURED_DT),0)
)t
WHERE Seq<=6
GROUP BY Seq


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  04:38:56  Show Profile  Reply with Quote
see this to understand logic used

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/09/2013 :  04:50:06  Show Profile  Reply with Quote
Its not returning what I would expect to see...this the what I have already, what I would like to see is the same for TotalCatYest, so CategoryYest and TotalCatYest in next 2 columns.

Category TotalCatToday
Central 129
Local 93
Core Applications 74
Printers 54
Mail Services 23
Thin Client 21


Your result shows this, also strangely you have one different from my one where Domain Services is Printers.

1 NULL Central
2 NULL Local
3 NULL Core Applications
4 NULL Domain Services
5 NULL Mail Services
6 NULL Thin Client


SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  05:26:57  Show Profile  Reply with Quote
whats the significance of OPEN_FLAG = 1 and c.ETL_CURRENT=1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/09/2013 :  05:37:05  Show Profile  Reply with Quote
OPEN_FLAG = 1? means all calls that are open

and c.ETL_CURRENT=1? means using current up to date dataset information, if not it will return all records and we only want the current ones that are open.

Basically is just means show me everything that is open and valid.


SZ1
Learning and development is the driving force in the universe...!

Edited by - sz1 on 01/09/2013 05:39:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  05:59:25  Show Profile  Reply with Quote
are you sure you'll current day records with these values returned by query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/09/2013 :  06:08:39  Show Profile  Reply with Quote
This returns the top 6 current records, so 2 columns one with the cat name and the other with the cat total. So really just need to include the previous days records side by side or even on its own query for now.

--return top 6 only
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
Order By TotalCat Desc

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/09/2013 :  06:28:56  Show Profile  Reply with Quote
how can you guarantee that same categories will come in top 6 in previous day too?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 01/09/2013 :  06:53:14  Show Profile  Reply with Quote
The top 6 count of ID and grouping of the category returns the top highest records, if all the records that are today are left out by only looking at occured date yesterday and backwards from there I would have thought that would have calculated the top 6 previous records only correctly? leaving out the current days selection. Otherwsie the only other way I can think of is to aggregate all the new current records and subtract these from all other open records from the previous days figure.

The categories may have changed slightly from previous day too, so the may differ slightly if a change has happened.
Does that help?

Also, I can check yesterdays results as I have a record, so can I change this query to just look at yesterday results leaving out todays by using the OCCURRED_DT field, if I have to have 2 queries one for today and one for yesterday I can work with that.

--return top 6 only
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat
From DIM_CALL c
Where c.OCCURED_DT < DateAdd(dd,0,GETDATE())-1
and TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
Order By TotalCat Desc

Also, here I've asked for yesterdays date but the date is not doing anything, I need to bind it to the category count?

Declare @Date1 DateTime
Set @Date1 = '2013-01-08'
--return top 6 only
Select Top 6 c.INTI_CATEGORY, count(c.ID) As TotalCat,@Date1
From DIM_CALL c
--Where c.OCCURED_DT <= '2013-01-08' -- < DateAdd(dd,0,GETDATE())-1
where TYPE = 'Incident'
And c.OPEN_FLAG = 1 and c.ETL_CURRENT =1
Group by c.INTI_CATEGORY
Order By TotalCat Desc

SZ1
Learning and development is the driving force in the universe...!

Edited by - sz1 on 01/09/2013 07:46:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000