Solved

6 month query and count

Posted on 2006-11-02
17
528 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:lhaluska
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I tried the last query and I am getting stuff that is for the 6 months but for last year too.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 17

Expert Comment

by:HuyBD
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now