| Author |
 Topic  |
|
|
FrankRadio
Starting Member
2 Posts |
 Posted - 08/05/2012 : 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
India
48119 Posts |
|
|
FrankRadio
Starting Member
2 Posts |
Posted - 08/06/2012 : 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
India
48119 Posts |
Posted - 08/06/2012 : 12:23:22
|
see the article and understand how you can do it usin crosstab method
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Help with a daily chart in SQL 2000
