homepage Welcome to WebmasterWorld Guest from 184.72.72.182
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Ugly but effective . I guess there is a better way
caspita




msg:4407937
 10:05 pm on Jan 17, 2012 (gmt 0)

I got a requirement to display a grid based on counts by different days.. let say you want to know how many coffes people got from the coffe-maker at your co :-).. you install a movement sensor and implant a chip to each worker then read the sensor when they pour the coffe :-p and store the timestamp (to simplify I'm using only date in my example) in DB.. then you have a couple to tables:

EmpTbl (ID, Name)
CoffeTbl (EmpTbl.ID, Date)

I'm required to show a grid with EmpTbl.Name and let's say 5 columns for the week with counters for each Empl/Date. My Ugly but working query looks like this:

T-SQL


DECLARE @Today DATE = CAST(GETDATE() AS DATE);

SELECT
EmpTbl.Name,
(SELECT Count(*) FROM CoffeTbl WHERE ID = EmpTbl.ID AND Date = DATEADD(Day, -4, @Today)) AS [4_ago],
(SELECT Count(*) FROM CoffeTbl WHERE ID = EmpTbl.ID AND Date = DATEADD(Day, -3, @Today)) AS [3_ago],
(SELECT Count(*) FROM CoffeTbl WHERE ID = EmpTbl.ID AND Date = DATEADD(Day, -2, @Today)) AS [2_ago],
(SELECT Count(*) FROM CoffeTbl WHERE ID = EmpTbl.ID AND Date = DATEADD(Day, -1, @Today)) AS [1_ago],
(SELECT Count(*) FROM CoffeTbl WHERE ID = EmpTbl.ID AND Date = @Today) AS [Today]
FROM
EmpTbl


As I said, it is ugly but does the work, however, I don't like ugly code and beside I'd like to have it returning a variable number of columns so a start/end date or #days back can be given as a parameter so no code changes are required to expand/reduce the view.

I hope someone can help and thaks a lot in advance.

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved