How to Display Data Time Wise - 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 2000 Forums

Transact-SQL (2000)

How to Display Data Time Wise

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-28 : 00:41:13

I want to do something like this

When user provides two dates, FROM_DATE and TO_DATE, if the difference of these two dates is less than or equal to 7 then display the data day wise, this is quite easy, just sort the data date wise and use the DAY function. But in case if the difference is more than 7 and display the data week wise like this

Time_Series Lot_ID Start_Date Finish_Date Wafer_ID
Week1,8th – 15th 1 01-01-2004 11-01-2004 1
Week1,8th – 15th 1 01-01-2004 12-01-2004 2
Week1,8th – 15th 1 01-01-2004 13-01-2004 3
Week2,16th – 22nd 1 01-01-2004 16-01-2004 4
Week2,16th – 22nd 1 01-01-2004 19-01-2004 5

And so on, Similarly Month Wise, Please guide me how to write the query.

I have written a query something like this but it does not work

SET DATEFORMAT MDY
set nocount on
declare @from_date varchar(12), @to_date varchar(12)
select @from_date = '06-15-2004'
select @to_date = '06-23-2004'
SELECT DATEDIFF(d, @from_date, @to_date)
SELECT DATEDIFF(wk, @from_date, @to_date)
SELECT time_series =
(SELECT 'time_series' = CASE
WHEN DATEDIFF(d, @from_date, @to_date) >= 1 AND
DATEDIFF(d, @from_date, @to_date) <= 7 THEN '1st - 7th'
WHEN DATEDIFF(d, @from_date, @to_date) >= 8 AND
DATEDIFF(d, @from_date, @to_date) <= 15 THEN '8th - 15th'
WHEN DATEDIFF(d, @from_date, @to_date) >= 16 AND
DATEDIFF(d, @from_date, @to_date) <= 22 THEN '16th - 22nd'
WHEN DATEDIFF(d, @from_date, @to_date) >= 23 AND
DATEDIFF(d, @from_date, @to_date) <= 31 THEN '23rd - 31st'
END), @to_date Wafer_Finish_Date

gates_micro
Starting Member

29 Posts

Posted - 2004-06-28 : 03:39:06

This shd work.

declare @from_date varchar(12), @to_date varchar(12)
select @from_date = '06-15-2004'
select @to_date = '06/23/2004'
SELECT time_series =
(SELECT 'time_series' = CASE
WHEN DATEDIFF(d, @from_date, @to_date) >= 1 AND
DATEDIFF(d, @from_date, @to_date) <= 7 THEN ltrim(rtrim('WEEK' + cast(DATEDIFF(wk, @from_date, @to_date)as varchar(1))+ ' 1st - 7th'))
WHEN DATEDIFF(d, @from_date, @to_date) >= 8 AND
DATEDIFF(d, @from_date, @to_date) <= 15 THEN ltrim(rtrim('WEEK' + cast(DATEDIFF(wk, @from_date, @to_date)as varchar(1))+ ' 8th - 15th'))
WHEN DATEDIFF(d, @from_date, @to_date) >= 16 AND
DATEDIFF(d, @from_date, @to_date) <= 22 THEN ltrim(rtrim('WEEK' + cast(DATEDIFF(wk, @from_date, @to_date)as varchar(1))+ ' 16th - 22nd'))
WHEN DATEDIFF(d, @from_date, @to_date) >= 23 AND
DATEDIFF(d, @from_date, @to_date) <= 31 THEN ltrim(rtrim('WEEK' + cast(DATEDIFF(wk, @from_date, @to_date)as varchar(1)) + ' 23rd - 31st'))
END), @to_date Wafer_Finish_Date

gates_micro
Starting Member

29 Posts

Posted - 2004-06-28 : 03:49:40

Write varchar(10) instead of varchar(1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources