Solved

6 month query and count

Posted on 2006-11-02
17
548 Views
Last Modified: 2008-01-09
I have a query that counts the number of recorders and it is supposed to be able to select records for 6 month from the current date but it has problem not sure why HELP PLEASE... Here is the query...

SELECT     COUNT(SRDATETIMESTAMP) AS Total, DATEDIFF(month, - 6 > getdate(), getdate()) AS no_of_days
FROM         dbo.caseData
0
Comment
Question by:lhaluska
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +3
17 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 17863170
Hi,

Try

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   caseData.TransDate > DATEDIFF(month, - 6,  getdate())

I'm guesing was the date field in your table is though ...

Regards
  David
0
 
LVL 1

Author Comment

by:lhaluska
ID: 17863250
I am sorry my date field is :  SRDATETIMESTAMP

here is the new query

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   caseData.TransDate > DATEDIFF(month, - 6,  getdate())

I am getting a nvarchar to int error here is what the SRDATETIMESTAMP looks like..
October 06, 2005 09:10
0
 
LVL 35

Expert Comment

by:David Todd
ID: 17863272
Hi

Try
SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   caseData.SRDATETIMESTAMP > DATEDIFF(month, - 6,  getdate())

Can you post the table schema? In Query Analyzer, right click on the table in the object browser window, and select script to clipboard ...

Regards
  David
0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
LVL 1

Author Comment

by:lhaluska
ID: 17863343
here is what the error looks like

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'April 03, 2006 06:00' to a column of data type int.

Here is the output for the script...

SELECT [caseID], [customerPhone], [customerEmail], [CaseState], [dateNeeded], [SysName], [SysLoc], [SysType], [OwnDept], [SRDATETIMESTAMP], [problemDescription], [chowned], [routedTo], [Owner], [ReceiveMail], [totalOnHOldTIme], [submitTime], [SubmitAdjustments], [SRQuarter], [Region], [custMang], [subType] FROM [NSKInfo].[dbo].[caseData]


Thank you very much so far...:)
0
 
LVL 35

Expert Comment

by:David Todd
ID: 17863458
Hi,

Can you pick up the script table as Create rather than a select.

What I'm after are the column types, as it appears that the datetime is not a datetime but characters. But I don't know which way round the problem is with out the full create sciprt.

For instance, picking a table at random from our server
USE [databasename]
GO
/****** Object:  Table [dbo].[audit]    Script Date: 11/03/2006 14:17:30 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[audit](
      [audit_id] [int] IDENTITY(1,1) NOT NULL,
      [process_date] [datetime] NOT NULL,
      [start_datetime] [datetime] NOT NULL,
      [end_datetime] [datetime] NULL,
      [type] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [runid] [int] NULL,
      [command] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [params] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [message] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [status_code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_AUDIT] PRIMARY KEY CLUSTERED
(
      [audit_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Regards
  David
0
 
LVL 1

Author Comment

by:lhaluska
ID: 17863470
CREATE TABLE [caseData] (
      [caseID] [int] IDENTITY (1, 1) NOT NULL ,
      [customerPhone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [customerEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CaseState] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [dateNeeded] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SysName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SysLoc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SysType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [OwnDept] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SRDATETIMESTAMP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [problemDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [chowned] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [routedTo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Owner] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ReceiveMail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [totalOnHOldTIme] [int] NULL ,
      [submitTime] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SubmitAdjustments] [int] NULL ,
      [SRQuarter] [datetime] NULL ,
      [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [custMang] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [subType] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      CONSTRAINT [PK_caseData] PRIMARY KEY  CLUSTERED
      (
            [caseID]
      )  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17863541
Try this

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   CAST(caseData.SRDATETIMESTAMP as datetime) > DATEDIFF(month, - 6,  getdate())
0
 
LVL 1

Author Comment

by:lhaluska
ID: 17863578
I tried the last query and I am getting stuff that is for the 6 months but for last year too.
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17863586
Youn can add more codition

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   CAST(caseData.SRDATETIMESTAMP as datetime) > DATEDIFF(month, - 6,  getdate()) AND
YEAR(CAST(caseData.SRDATETIMESTAMP as datetime)) = YEAR(getdate())
0
 
LVL 35

Expert Comment

by:David Todd
ID: 17863808
Hi,

datediff should in fact be dateadd.

The cast should be using a convert and the style parameter for the date.

Try
SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   convert( datetime, caseData.SRDATETIMESTAMP, 107 ) > dateadd(month, - 6,  getdate())

if your six months includes last year, but you don't want last year (for example in may, six months includes the previous december)

then add
and  convert( datetime, caseData.SRDATETIMESTAMP, 107 )  > dateadd( year, datediff( year, 0 , getdate() ) - 1, 0 )

Regards
  David
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 17863815
Sorry, I have missing

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   CAST(caseData.SRDATETIMESTAMP as datetime) > DATEADD(month, - 6,  getdate()) AND
YEAR(CAST(caseData.SRDATETIMESTAMP as datetime)) = YEAR(getdate())
0
 
LVL 35

Expert Comment

by:David Todd
ID: 17863818
Hi,

Try

SELECT     COUNT( caseData.SRDATETIMESTAMP )  AS Total
FROM         dbo.caseData
where   convert( datetime, caseData.SRDATETIMESTAMP, 107 ) > dateadd(month, - 6,  getdate())
and  convert( datetime, caseData.SRDATETIMESTAMP, 107 )  > dateadd( year, datediff( year, 0 , getdate() ) - 1, 0 )


Sorry. Should have picked up the datediff thing before.

Regards
  David
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17863914
for a quick and dirty approach, try this...

SELECT     COUNT(SRDATETIMESTAMP) AS Total
FROM         dbo.caseData
where SRDATETIMESTAMP >getdate()-180

for SRDATETIMESTAMP's that are greater than the last 6 months (assuming none can be later than today)

or for a bounded range...

SELECT     COUNT(SRDATETIMESTAMP) AS Total
FROM         dbo.caseData
where SRDATETIMESTAMP between getdate()-180 and getdate()


your question did not make clear whether you wanted the past 6 months or the next 6 months... for the future 6 month period...


SELECT     COUNT(SRDATETIMESTAMP) AS Total
FROM         dbo.caseData
where SRDATETIMESTAMP between getdate() and getdate()+180

6 months is a vague concept... I am using 180 days as a surrogate (you could use 182.5 to make it a "half year" - also an inexact value because of leapyears)


0
 
LVL 35

Expert Comment

by:David Todd
ID: 17864002
Hi,

Based on Aaron's work, think about this idea - not often do you want the split months. You'll want the last six complete months.

select count( caseData.srdatetimestamp ) as total
from dbo.caseData
where datediff( month, 0, caseData.srdatetimestamp )
    between datediff( month, 0 , getdate() ) - 6 and datediff( month, 0, getdate() )

Regards
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 17864094
Hi,

Okay, try again.

select count( caseData.srdatetimestamp ) as total
from dbo.caseData
where
      datediff( month, 0, convert( datetime, caseData.SRDATETIMESTAMP, 107 ) ) >= datediff( month, 0 , getdate() ) - 6
    and datediff( month, 0, convert( datetime, caseData.SRDATETIMESTAMP, 107 ) ) < datediff( month, 0, getdate() )

The between above will add the current month data in here.

To illustrate the datetime manipulation, try this code
select getdate()

select datediff( month, 0, getdate() )

select datediff( month, 0 , getdate() ) - 6

select dateadd( month, datediff( month, 0, getdate() ), 0 )

select dateadd( month, datediff( month, 0 , getdate() ) - 6, 0 )

Regards
  David
0
 
LVL 4

Accepted Solution

by:
satish_nagdev earned 500 total points
ID: 17865404
hi,
could you try this please
--i guess you've some primary key field
declare @dt varchar
select getdate()
set @dt = cast(year(dateadd(mm,-6,getdate())) as varchar) +'-' + cast(datepart(mm,dateadd(mm,-6,getdate())) as varchar)+'-'+'01-' //get first date of six months back, you could take exact date using datepart six months back like 3rd march in today's case
--select @dt

SELECT     COUNT( PID )  AS Total, *
FROM         dbo.caseData
where   convert(varchar,caseData.TransDate,103) between @dt and convert(varchar, DATEDIFF(month, - 6,  getdate())

hope it helps,

regards,
satish.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17865797
I am sorry I jumping although this post is already crowded, I am though surprised that datediff was continiously used in wrong way in nearly all examples and I am sure you had your DateDiff and DateAdd confused

here is what BOL says about datediff

DATEDIFF (T-SQL)
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF(datepart, startdate, enddate)

and about dateadd
DATEADD (T-SQL)
Returns a new datetime value based on adding an interval to the specified date.
Syntax
DATEADD(datepart, number, date)


With DateDiff all you need was in your where clause
Where DateDiff(dd,SRDATETIMESTAMP,Getdate())<=180 --- if you want to go back 180 days or
Where DateDiff(mm,SRDATETIMESTAMP,Getdate())<=6--- if you want to go back 6 months

With DateAdd all you need is following in your where clause
Where SRDATETIMESTAMP>=DateAdd(dd,-180,Getdate())-- if you want to go back 180 days or
Where SRDATETIMESTAMP>=DateAdd(mm,-6,Getdate())-- if you want to go back 6 months

you can also try any of Aaron's solution, I dont think there is any thing dirty about that as Aaron pointed :)

0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

717 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