Home ›
ASP.NET Forums » Advanced ASP.NET » SQL Server Reporting Services » need help with select statement with group by clause in my report

need help with select statement with group by clause in my report

Last post 05-09-2008 2:52 PM by klakieros. 4 replies.

Sort Posts:

  • need help with select statement with group by clause in my report

    05-06-2008, 5:30 PM
    • Loading...
    • klakieros
    • Joined on 04-19-2008, 7:00 PM
    • Posts 14

    Hi All,

    I have a following query:

    SELECT Description, SUM(Tcash) as Total_Cash

    FROM DEV_LabourCost

    WHERE LabStart between ? and ?

    GROUP BY Description

    Description is the name of worker, Tcash is money earned in single transaction by the worker (it may be quite a lot during one day) and LabStart is a date/timestamp of each transaction

    I have passed the LabStart parameters to two datetimepickers and got sample report:

    Daily Pay Slip Sample Company xxxx
    Name Agnieszka Kotonska Minimum Wage Make Up 2.178
    Cash Earned 32.538    
    Hours Worked 5.5    

    That 32.538 is actually the SUM(Tcash) for that day which is what i wanted. Now, there are 'xxxx' in the top right corner of the report. I would like to have the date/timestamp there so whoever will get the payslip will know what date this refers to. I figured out that I need to add the LabStart field in SELECT part in my query, and in the report fill the textbox with expression pointing the LabStart field in the dataset. So I have changed my query to: 

    SELECT     Description, LabStart, SUM(Tcash) as Total_Cash
    FROM DEV_LabourCost
    WHERE   LabStart between ? and ?
    GROUP BY Description

     It didn't accepted the query because there was no LabStart in the GROUP BY clause... So I have added this and here what i got on my report:

    Daily Pay Slip Sample Company
    Sunday, August 05, 2007
    Name Agnieszka Kotonska Minimum Wage Make Up 0.480
    Cash Earned 6.000    
    Hours Worked 1.0    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 0.000    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up 0.200
    Cash Earned 14.000    
    Hours Worked 2.5    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 0.000    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up 1.240
    Cash Earned 4.000    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 0.000    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 2.600    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 0.200    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 0.000    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up 2.440
    Cash Earned 5.200    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 0.338    
    Hours Worked 0.5    
    Name Agnieszka Kotonska Minimum Wage Make Up ##########
    Cash Earned 0.200    
    Hours Worked 0.5    
     

     

    		    

    So I got the date in the corner but the whole payslip has been broke down by each timestamp which is not acceptable. There must be some way...

    I am absolute beginner so any advise will be much appreciated....

    Thanks in advance.

    Paul

  • Re: need help with select statement with group by clause in my report

    05-06-2008, 5:55 PM
    • Loading...
    • johram
    • Joined on 06-13-2006, 10:36 AM
    • Sweden
    • Posts 1,936
    • Moderator

    I assume you want to truncate the time part of the datetime LabStart and get only the date part?

    You can do this by a combination of CAST and CONVERT: 

    SELECT CAST(CONVERT(VARCHAR, GetDate(), 110) AS DATETIME)

    And if we apply this to your query, it should be:

    SELECT Description, CAST(CONVERT(VARCHAR, LabStart, 110) AS DATETIME), SUM(Tcash) as Total_Cash
    FROM DEV_LabourCost
    WHERE LabStart between ? and ?
    GROUP BY Description, CAST(CONVERT(VARCHAR, LabStart, 110) AS DATETIME)
    You could also put this "time truncation" in a function of its own to make the code more readable.
    If this post was useful to you, please mark it as answer. Thank you!

  • Re: need help with select statement with group by clause in my report

    05-07-2008, 8:15 AM
    Answer
    • Loading...
    • klakieros
    • Joined on 04-19-2008, 7:00 PM
    • Posts 14

    Thanks johram.

    I didn't really needed that date converted from date/time but it definately will be useful when I will need that.

    I had to retrieve the LabStart value from my ms access db. To do that I had to add this to 'SELECT' part in my query. Having done that, I also had to add this to 'GROUP BY' section which caused unwanted grouping by LabStart in my report. Got help from my friend and he suggested to do:

    SELECT     Description,  min(LabStart) as firstdate, SUM(Tcash) as Total_Cash
    FROM DEV_LabourCost
    WHERE   LabStart between ? and ?
    GROUP BY Description

    Will try this tonight.

     

    Thanks again 

  • Re: need help with select statement with group by clause in my report

    05-08-2008, 3:52 PM
    Answer
    • Loading...
    • johram
    • Joined on 06-13-2006, 10:36 AM
    • Sweden
    • Posts 1,936
    • Moderator

    Klakieros, did you ever get this thing working?

    If this post was useful to you, please mark it as answer. Thank you!

  • Re: need help with select statement with group by clause in my report

    05-09-2008, 2:52 PM
    • Loading...
    • klakieros
    • Joined on 04-19-2008, 7:00 PM
    • Posts 14

    Yes, the above solution works very well!

    Thanks
Page 1 of 1 (5 items)
RSS Available

Advertise on ASP.NET

About this Site