query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query question

Author

Topic

aikman
Starting Member

22 Posts

Posted - 2008-08-12 : 12:42:13

hi,

I would like to do a select query that show X bookings last week in the last year of my database. I have come so far that i get the last week of the database. but i need last week in december. Can some one help me to modify query?

Declare @Date Datetime
Select @Date=MAX(DateAdd(d,DateDiff(d,0,Time),0))
from dbo.booking
Select Convert(VarChar(10),Time,120), Count(x)
from dbo.booking
Where [Time]>=DateAdd(Week,-1,@Date)
Group By Convert(VarChar(10),Time,120)

Thanks
Håkan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 12:50:30

[code]Declare @Date Datetime
Select @Date=DATEADD(d,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

Select Convert(VarChar(10),Time,120), Count(x)
from dbo.booking
Where [Time]>=DATEADD(d,-7,@Date)
Group By Convert(VarChar(10),Time,120)[/code]

aikman
Starting Member

22 Posts

Posted - 2008-08-12 : 13:16:53

this is an old database, so cant use getdate. is 2005 and 2006 to april. need t get 2005-12-26 to 2005-12-31

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 13:28:07

quote:
Originally posted by aikman

this is an old database, so cant use getdate. is 2005 and 2006 to april. need t get 2005-12-26 to 2005-12-31

then replace it by your date value


SET DATEFORMAT dmy
Declare @Date Datetime
Select @Date=31/12/2005'
--DATEADD(d,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

Select Convert(VarChar(10),Time,120), Count(x)
from dbo.booking
Where [Time]>=DATEADD(d,-6,@Date)
Group By Convert(VarChar(10),Time,120)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 13:47:39

Try using CURRENT_TIMESTAMP instead if GETDATE()

E 12°55'05.25"
N 56°04'39.16"

aikman
Starting Member

22 Posts

Posted - 2008-08-12 : 14:01:17

i still get all dates in results:
2006.03.27
2005.12.30
2006.03.07
2006.04.24
2006.04.14
2006.04.04
2006.04.08
2006.03.11
2006.04.18
2006.03.19
2006.04.28
2006.03.21
2006.03.15
2005.12.28
2006.03.01
2006.03.13
2006.04.30
2006.03.29
2006.03.09
2006.03.25
2006.03.31
2006.03.05
2006.04.22
2006.03.03
2006.04.10
2006.04.26
2006.04.02
2006.04.12
2006.04.06
2006.04.16
2006.04.20
2006.03.17
2005.12.26
2006.03.23

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 14:08:46

How are the date values stored in database?
Are they VARCHAR or DATETIME?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 14:09:24

Is Time a datetime field?

aikman
Starting Member

22 Posts

Posted - 2008-08-12 : 14:11:10

it´s datetime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 14:11:51

Strange, because . (dot) is not the normal date separator in Sweden.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 14:16:35

Run this and post back the result

USE Booking
GO

SELECT	DATA_TYPE
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	COLUMN_NAME = 'Time'
	AND TABLE_NAME = 'Booking'

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 14:21:49

Are you using MS SQL Server? Having seen such a dateformat before.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 14:24:24

[code]DECLARE @Year SMALLINT,
@dtFrom DATETIME,
@dtTo DATETIME,
@dtLast DATETIME

SET @Year = 2005

SELECT @dtLast = DATEADD(YEAR, @Year - 1899, '18991231'),
@dtFrom = DATEADD(DAY, DATEDIFF(DAY, '19000101', @dtLast) / 7 * 7, '19000101'),
@dtTo = CASE
WHEN DATEDIFF(DAY, @dtFrom, @dtLast) < 8 THEN DATEADD(DAY, 1, @dtLast)
ELSE DATEADD(DAY, 7, @dtFrom)
END

SELECT *
FROM dbo.Booking
WHERE [Time] >= @dtFrom
AND [Time] < @dtTo[/code]

E 12°55'05.25"
N 56°04'39.16"

aikman
Starting Member

22 Posts

Posted - 2008-08-12 : 14:31:11

result is datetime, yes it´s win sql 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 14:34:35

quote:
Originally posted by aikman

result is datetime, yes it´s win sql 2005

then post query you used.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 14:36:47

quote:
Originally posted by Peso

Try using CURRENT_TIMESTAMP instead if GETDATE()

E 12°55'05.25"
N 56°04'39.16"

Peso,
Is there much difference in performance between CURRENT_TIMESTAMP & GETDATE()?

aikman
Starting Member

22 Posts

Posted - 2008-08-12 : 14:37:58

i use the one peso write above,

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Time'
AND TABLE_NAME = 'Booking'

and get the results datetime

aikman
Starting Member

22 Posts

Posted - 2008-08-12 : 14:44:21

this is working but i get some info i don´t need and it dont calculate how many bookid/day:
bookid product salesperson customer time
170 9 22 320 2005-12-26 00:00:00.000
226 7 18 324 2005-12-26 00:00:00.000
239 1 20 784 2005-12-26 00:00:00.000
514 1 2 52 2005-12-26 00:00:00.000

need it to look something like this:

date bookings
2005-12-26 or 2005.12.26 (doesn´t matter) 23
2005-12-27 35
2005-12-28 95

quote:

Originally posted by Peso

DECLARE	@Year SMALLINT,
	@dtFrom DATETIME,
	@dtTo DATETIME,
	@dtLast DATETIME

SET	@Year = 2005

SELECT	@dtLast = DATEADD(YEAR, @Year - 1899, '18991231'),
	@dtFrom = DATEADD(DAY, DATEDIFF(DAY, '19000101', @dtLast) / 7 * 7, '19000101'),
	@dtTo =	CASE
			WHEN DATEDIFF(DAY, @dtFrom, @dtLast) < 8 THEN DATEADD(DAY, 1, @dtLast)
			ELSE DATEADD(DAY, 7, @dtFrom)
		END

SELECT	*
FROM	dbo.Booking
WHERE	[Time] >= @dtFrom
	AND [Time] < @dtTo

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 15:55:26

[code]SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', Time), '19000101') AS Time,
COUNT(*)
FROM dbo.Booking
WHERE [Time] >= @dtFrom
AND [Time] < @dtTo
GROUP BY DATEADD(DAY, DATEDIFF(DAY, '19000101', Time), '19000101')[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources