another date , grouping issue - 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)

another date , grouping issue

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-17 : 11:26:41

hi guys,
I have been trying to group my records into periods based on the date.
but have hit another problem.
I need to group my records, firstly between dates, 20 - 19,
then display the text current for the recent group, current -1 for last period, current -2 for the period before that, and so on.

To work out my periods I am currently doing :
Case
When Datepart(dd, convert(char(10),p.date,23)) > 19 and Datepart(mm, convert(char(10),p.date,23)) < 12 then
CAST(datepart(yy, convert(char(10),p.date,23)) AS varchar) + CAST(datepart(mm,dateadd(mm, 1,convert(char(10),p.date,23))) AS varchar)
When Datepart(dd, convert(char(10),p.date,23)) > 19 and Datepart(mm, convert(char(10),p.date,23)) = 12 then
CAST(datepart(yy,dateadd(yy, 1,convert(char(10),p.date,23))) as varchar) + CAST(datepart(mm,dateadd(mm, 1,convert(char(10),p.date,23))) AS varchar)
Else
CAST(datepart(yy, convert(char(10),p.date,23)) AS varchar) + CAST(datepart(mm, convert(char(10),p.date,23)) AS varchar)
End

can anyone asisst me?
thank you.
jamie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 11:40:27

get the results grouped by Period onto a temp table along with Period column (calculated as per above)

write a CTE as follows
;
With Date_CTE (RowNo,{other fields})as
(
SELECT ROW_NUMBER() OVER (ORDER BY Period DESC) AS Row_No,
...(other fields)
FROM temptable
)

SELECT Period +
(CASE WHEN (Row_No -1) >0
THEN '-' + (Row_No -1)
ELSE ''
END) AS 'Text',
.........other fields
FROM Date_CTE

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-17 : 11:46:24

theres no way of doing it using datediff ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-18 : 11:57:59

hi, rather than working this out I have created a table with periods, start and end date in.
my question is how can I link this table to show which period a record belongs too..?
I have tried this but I do not get correct result s:
SELECT count(id) AS Total, period
FROM users
INNER JOIN periods p on users.logindate >=p.startdate
AND users.logindate <=p.enddate
WHERE users.logindate >= @Date1 AND users.logindate <= @Date2
group by period

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources