| 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.
|
|
