May 23, 2013 at 6:50 am
Hi Guys,
I'm designing a report in SSRS,I need to pull that from DB from previous Friday till the end of the current Friday.
this is what I have on WHERE clause
dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())
Please assist
The whole Query:
select distinct Top 6
Count(c.ref_num) as Logged,
p.sym AS [Category]
-- into #TempCat
from call_req c, ca_contact cn, ca_contact cn2, ca_contact cn3, cr_stat st, view_group v, prob_ctg p WITH (NOLOCK), act_log al
where c.type = 'I'
and c.assignee *= cn.contact_uuid
and c.customer = cn2.contact_uuid
and al.analyst = cn3.contact_uuid
and c.status = st.code
and c.group_id *= v.contact_uuid
and c.persid = al.call_req_id
and c.category = p.persid
and dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())
Group by p.sym
order by count(c.ref_num) desc
May 23, 2013 at 7:35 am
GOODS (5/23/2013)
Hi Guys,I'm designing a report in SSRS,I need to pull that from DB from previous Friday till the end of the current Friday.
this is what I have on WHERE clause
dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())
Please assist
The whole Query:
select distinct Top 6
Count(c.ref_num) as Logged,
p.sym AS [Category]
-- into #TempCat
from call_req c, ca_contact cn, ca_contact cn2, ca_contact cn3, cr_stat st, view_group v, prob_ctg p WITH (NOLOCK), act_log al
where c.type = 'I'
and c.assignee *= cn.contact_uuid
and c.customer = cn2.contact_uuid
and al.analyst = cn3.contact_uuid
and c.status = st.code
and c.group_id *= v.contact_uuid
and c.persid = al.call_req_id
and c.category = p.persid
and dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())
Group by p.sym
order by count(c.ref_num) desc
It is pretty tough to help much here because we can't see your table structures. What is the datatype of c.open_date?
Off topic from your question but you are using a very outdated join syntax. You should revise and use the ANSI-92 style joins.
Also, be careful with that NOLOCK hint. It can produce some very strange results.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
Here is my attempt are changing your join syntax to the current version.
SELECT DISTINCT TOP 6 Count(c.ref_num) AS Logged
,p.sym AS [Category]
-- into #TempCat
FROM call_req c
LEFT JOIN ca_contact cn ON c.assignee = cn.contact_uuid
INNER JOIN ca_contact cn2 ON c.customer = cn2.contact_uuid
INNER JOIN act_log al ON c.persid = al.call_req_id
INNER JOIN ca_contact cn3 ON al.analyst = cn3.contact_uuid
INNER JOIN cr_stat st ON c.STATUS = st.code
LEFT JOIN view_group v ON c.group_id = v.contact_uuid
INNER JOIN prob_ctg p WITH (NOLOCK) ON c.category = p.persid
WHERE c.type = 'I'
AND dateadd(ss, c.open_date + 7200, '1970-01-01') > Dateadd(day, - 7, getdate())
GROUP BY p.sym
ORDER BY count(c.ref_num) DESC
To help with your question we need more details as I said earlier. Additionally you might want to take a look at this link for some datetime help.
http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2013 at 7:36 am
GOODS (5/23/2013)
Hi Guys,I'm designing a report in SSRS,I need to pull that from DB from previous Friday till the end of the current Friday.
this is what I have on WHERE clause
dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())
Please assist
The whole Query:
select distinct Top 6
Count(c.ref_num) as Logged,
p.sym AS [Category]
-- into #TempCat
from call_req c, ca_contact cn, ca_contact cn2, ca_contact cn3, cr_stat st, view_group v, prob_ctg p WITH (NOLOCK), act_log al
where c.type = 'I'
and c.assignee *= cn.contact_uuid
and c.customer = cn2.contact_uuid
and al.analyst = cn3.contact_uuid
and c.status = st.code
and c.group_id *= v.contact_uuid
and c.persid = al.call_req_id
and c.category = p.persid
and dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())
Group by p.sym
order by count(c.ref_num) desc
Change this:
and dateadd (ss,c.open_date+7200,'1970-01-01') > Dateadd(day,-7,getdate())
to this:
and c.open_date >= dateadd(week, datediff(week, 0, getdate()),-3)
Also, this may not work if you are not running SQL Server 2008 as assumed by the forum thread you have posted this in. Looking at your query you are using the ANSI-89 style outer joins and that is not supported in SQL Server 2008 (unless, iirc, you are using compatibility mode 80 for SQL Server 2000).
May 23, 2013 at 7:38 am
It is also looking like open_date is stored in seconds since 1/1/1970, is this correct? That would also change my solution.
May 23, 2013 at 7:56 am
Hi SSC-Insane,
What I have here is a DB sitting on SQL server 2000 and I'm currently using SQL server 2012.The design of the DB is so bad..you can work on it.my data type for a.open_date is int data type.One thing I know I need to convert the data type but I'm not sure how do I go about doing that.
May 23, 2013 at 8:19 am
Nope the data type is int???
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply