SQL Server Performance

Selecting values from table based on the date

Discussion in 'SQL Server 2008 General Developer Questions' started by Rajasri, Sep 5, 2011.

  1. Rajasri New Member

    Hi All,

    My table contains UserName, Date, Count.

    UserName Date Count

    Test 01-08-2011 1
    Test 01-08-2011 2
    Test 02-08-2011 1
    Test 03-08-2011 5

    I need to select the values based on the date with group by UserName and date.
    Eg:

    UserName 1 2 3 .....31

    Test 3 1 5 ......

    Here 1,2,3 .... indicates date.
    2 1 1 ...... indicates count

    Can anyone tell me the query for this.

    Thanks in advance.
    Rajasri.
  2. preethi Member

    hi Rajasri,
    Welcome to forums.
    On the Date column do you have dates of only one month? or it spans across multiple months/years?
  3. preethi Member

    I added some code; but it may change based on your answer:
    Code:
    -- Create Table
    create table Mytable (
    UserName varchar(20),
    MyDate date,
    MyCount int
    )
    
    GO
    
    -- Insert test data
    declare @Seed varchar(100) ='this is a test example it contains some data I need to select the values based on the date with group by UserName and date'
    insert into Mytable values(SUBSTRING(@seed, CONVERT(int, RAND()*10000)%50 +1, CONVERT(int, RAND()*10000)%20 +1), DATEADD(day, CONVERT(int, RAND()*300), '01/01/2011'), CONVERT(int, RAND()*300)%10+1)
    GO 10000
    
    --Declare input values
    declare @name varchar(20), @DateList varchar(20)
    set @name = ' contains '
    Set @DateList = '1,3,4,5,6,7'
    
    -- Convert the comma separated value to table variable
    declare @RetTable table (DayNumber int)
        DECLARE @FirstPos int, @EndPos int, @Len int
    
        SELECT @Len = Len(@DateList), @FirstPos = 0
    
        WHILE @FirstPos < @Len
        BEGIN
            SELECT @EndPos = charindex(',', @DateList, @FirstPos + 1)
            IF @EndPos = 0 SELECT @EndPos = @Len + 1
            INSERT INTO @RetTable (DayNumber) VALUES (convert(int, LTrim(SUBSTRING(@DateList, @FirstPos + 1, @EndPos - @FirstPos - 1))))
            SELECT @FirstPos = @EndPos
        END
    -- Query the data
    select @name, r.DayNumber, SUM(a.MyCount)
    FROM Mytable a inner join @RetTable r
    ON DAY(a.MyDate) = r.DayNumber
    AND a.UserName =@name
    GROUP BY r.DayNumber
    

Share This Page