Solved

6 month query and count

Posted on 2006-11-02
17
538 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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