one more view quest - 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)

one more view quest

Author

Topic

aikman
Starting Member

22 Posts

Posted - 2008-07-07 : 11:12:33

Table:

book_id product_id sale_id cust_id time
1 8 13 895 2005-10-09 00:00:00.000
2 2 5 40 2005-11-12 00:00:00.000
3 5 22 364 2005-10-31 00:00:00.000
4 2 2 5 2006-04-22 00:00:00.000
5 10 15 429 2006-03-23 00:00:00.000
6 6 18 209 2005-10-05 00:00:00.000
7 6 15 476 2006-04-10 00:00:00.000
8 7 7 233 2005-12-18 00:00:00.000
9 10 38 79 2006-03-05 00:00:00.000
10 7 10 982 2006-03-07 00:00:00.000
11 10 22 500 2005-11-26 00:00:00.000
12 4 24 712 2005-11-14 00:00:00.000
13 6 38 96 2006-03-07 00:00:00.000
14 8 14 890 2006-04-02 00:00:00.000
15 6 2 914 2005-12-02 00:00:00.000
16 5 22 122 2005-12-12 00:00:00.000

I want a view that display X booking last week (yyyy:mm:dd). Every row is one booking. And I have to display it like this example:

SELECT * from view

Date bookings

2005-12-26 3
2005-12-27 2
2005-12-28 2
2005-12-29 3
2005-12-30 5
2005-12-31 2

thanks for all help

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-07 : 11:21:28

SELECT CONVERT(varchar(10),time,120) ,count(*)
FROM yourTable
group by CONVERT(varchar(10),time,120)

Jim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 11:22:52

" last week "
Your current date is 2006 Jan ?


2005-12-26 3
2005-12-27 2

How do you get the 3 bookings for 2005-12-26 ? I don't see such record in your sample data

KH
[spoiler]Time is always against us[/spoiler]

aikman
Starting Member

22 Posts

Posted - 2008-07-07 : 11:42:55

the output is just an example on how its should look like, i need from the last week from table

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-07 : 11:56:20

SELECT CONVERT(varchar(10),time,120) ,count(*)
FROM yourTable
WHERE [TIME] >= DATEADD(week,-1,GETDATE())
GROUP BY CONVERT(varchar(10),time,120)

will give you the last 7 days

Jim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 12:31:45

You don't have to convert the time to string at all

select [time], cnt = count(*)
from   table1
where  [time] >= dateadd(week, -1, getdate())
group by [time]

KH
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-07 : 12:41:48

But that will display the hh:mm:ss part of the field, which the op doesn't want.

Jim

aikman
Starting Member

22 Posts

Posted - 2008-07-07 : 13:41:59

on jimf's i get 2 colum with name no colum name and no data.

and with khtan i get 2 colum named

time and cnt but no data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 13:49:22

then i think you probably need this:-

DECLARE @MaxDate datetime

SELECT @MaxDate=MAX(DATEADD(d,DATEDIFF(d,0,time),0))
FROM Table


SELECT CONVERT(varchar(10),time,120) ,count(*)
FROM yourTable
WHERE [TIME] >= DATEADD(week,-1,@MaxDate)
GROUP BY CONVERT(varchar(10),time,120)

aikman
Starting Member

22 Posts

Posted - 2008-07-07 : 14:46:12

that works great thanks, but i need two more things, the colums are not named. should be named date and booking. and my output are not ordered by date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 19:41:54

[code]
declare @max_time datetime

select @max_time = max([time])
from table1

select [time] as date, count(*) as booking
from table1
where [time] >= dateadd(week, -1, @max_time)
group by [time]
order by [time]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources