Help with a daily chart in SQL 2000

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

SQL Server Development (2000)

Help with a daily chart in SQL 2000

Author

Topic

FrankRadio
Starting Member

2 Posts

Posted - 2012-08-05 : 22:42:06

HI all!
Is my first post here.

I think that this is a very common issue.
I want to build a report that have the last seven days as column and the clients as rows.

It's a matrix that shows how many registers I have for each user at each day of the last week.

For now I coding this: (It's working but it only show the values as rows)

SELECT TOP 100 PERCENT Client_ID, DATEPART(dd, ActualizadoEn) AS DIA, COUNT(*) AS Actualizaciones
FROM dbo.Historial
WHERE (DATEDIFF(dd, ActualizadoEn, GETDATE()) < 7)
GROUP BY Client_ID, DATEPART(dd, ActualizadoEn)
ORDER BY Client_ID

#01435963 30 77
#01435963 31 135
#01435963 1 60
#01435963 2 115
#01435963 3 121
#01435963 4 130
#01435963 5 106

I want some like this:
CLIENT 30 31 1 2 3 4 5
#01435963 77 135 60 115 121 130 160

Can SQL run this?

Best Regards
Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 00:41:11

use this

http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FrankRadio
Starting Member

2 Posts

Posted - 2012-08-06 : 11:56:57

SELECT TOP 100 PERCENT Client_ID, DATEPART(dd, ActualizadoEn) AS DIA, COUNT(DATEDIFF(dd,ActualizadoEn,GETDATE()-1) AS Day1
COUNT(DATEDIFF(dd,ActualizadoEn,GETDATE()-2) AS Day2
COUNT(DATEDIFF(dd,ActualizadoEn,GETDATE()-3) AS Day3

FROM dbo.Historial
WHERE (DATEDIFF(dd, ActualizadoEn, GETDATE()) < 7)
GROUP BY Client_ID, DATEDIFF(dd,ActualizadoEn,GETDATE()-1),
COUNT(DATEDIFF(dd,ActualizadoEn,GETDATE()-2),
COUNT(DATEDIFF(dd,ActualizadoEn,GETDATE()-3)
ORDER BY Client_ID

I think that my SQL is wrong but I can't do this using a count() function for each day and then order them by the same day?

Other way is using the FILTER from the recorset and loop it filtering and printing each day.

Best Regards
Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 12:23:22

see the article and understand how you can do it usin crosstab method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources