?
Solved

6 month query and count

Posted on 2006-11-02
17
Medium Priority
?
562 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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