Query to show result in the charts.

Question

Hi,

I have to implement charts in my application. Suppose i have a table structure

 DECLARE @SONGS TABLE
 (
    [ID] INT IDENTITY,
    [SONGNAME] VARCHAR(20),
    [CREATEDDATE] DATETIME
 )


 INSERT INTO @SONGS
 SELECT 'SONG1','20091102' UNION ALL
 SELECT 'SONG2','20091103' UNION ALL
 SELECT 'SONG3','20091107' UNION ALL
 SELECT 'SONG4','20091107' UNION ALL
 SELECT 'SONG5','20091107' UNION ALL
 SELECT 'SONG6','20091109'

Now user will pass start date and end date from outside as parameters like below

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME 

SET @STARTDATE='20091101'
SET @ENDDATE='20091111'

Now user has further one more option(SAY @OPTION VARCHAR(20) ) whether he wants the results with dates split into individual dates between the start date and end date, second option he can choose to have the results with dates into the months between the start date and end date, similarly for year.

--OUTPUT I NEED IS when @OPTION IS DATE

DATE        [SONGCOUNT]
------------------------------------------
20091101       0
20091102       1
20091103       1
20091104       0
20091105       0
20091106       0
20091107       3
20091108       0
20091109       1
20091110       0
20091111       0

Similarly i want the results with dates splitted according the option(day,week,month,year) having count next to it. My goal is to display date on xaxis and count on y axis, can you suggest me a way to implement the same.

Answer 1

DECLARE @dimDate TABLE (
 myDate datetime
,dt int
,yr int
,ym int
)

DECLARE  @dte datetime

SET @dte = @STARTDATE
WHILE @dte <= @ENDDATE
    BEGIN
       INSERT INTO @dimDate (myDate, dt, yr, ym) 
         VALUES(
             @dte
            ,datepart(yy,@dte)*10000+ datepart(mm,@dte)*100 + datepart(dd,@dte)
            ,datepart(yy,@dte)
            ,datepart(yy,@dte)*100+ datepart(mm,@dte)
            )
     SET @dte = dateadd(dd,1,@dte)
    END

.

DECLARE @option varchar(2)
SET @option ='dt'

.

-- per day
IF @option ='dt'
BEGIN
SELECT d.dt, COUNT(s.ID) AS "song_count"
  FROM @dimDate AS d 
    LEFT JOIN @SONGS AS s ON d.myDate = s.CREATEDDATE
  GROUP BY d.dt
END

.

-- per year
IF @option ='yr'
BEGIN
SELECT d.yr, COUNT(s.ID) AS "song_count"
  FROM @dimDate AS d 
    LEFT JOIN @SONGS AS s ON d.myDate = s.CREATEDDATE
  GROUP BY d.yr
END

.

-- per year-month
IF @option ='ym'
BEGIN
SELECT d.ym, COUNT(s.ID) AS "song_count"
  FROM @dimDate AS d 
    LEFT JOIN @SONGS AS s ON d.myDate = s.CREATEDDATE
  GROUP BY d.ym
END

Answer 2

For making the results in x & y axis, use PIVOT(SQL server 2005+).

This kind of queries are called CROSS TAB QUERIES

For your reference SQL Server PIVOT examples