SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help with a daily chart in SQL 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

FrankRadio
Starting Member

2 Posts

Posted - 08/05/2012 :  22:42:06  Show Profile  Reply with Quote
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
52325 Posts

Posted - 08/06/2012 :  00:41:11  Show Profile  Reply with Quote
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/

Go to Top of Page

FrankRadio
Starting Member

2 Posts

Posted - 08/06/2012 :  11:56:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/06/2012 :  12:23:22  Show Profile  Reply with Quote
see the article and understand how you can do it usin crosstab method

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000