| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-06-23 : 16:58:35
|
| I need your help on a simple query. By simple I mean tt would not stire up vb.net vs. C# or dotnet vs. Java Debates. Oh, by the way that thread is so informative, I am going to save it for my weekend reading. The HR users want a report listing the data as how many days in a given week a given employee is late.Here is the table and the sample data. The result is on the end.CREATE TABLE [dbo].[TimeLate] ( [empID] [int] NULL , [startTime] [smalldatetime] NULL , [Notes] [varchar] (255)) ON [PRIMARY] Insert into TimeLate (empID, startTime, Notes)Values ('1','6/15/05','late')Insert into TimeLate (empID, startTime, Notes)Values ('2','6/15/05','')Insert into TimeLate (empID, startTime, Notes)Values ('3','6/15/05','late')Insert into TimeLate (empID, startTime, Notes)Values ('1','6/16/05','')Insert into TimeLate (empID, startTime, Notes)Values ('2','6/16/05','')Insert into TimeLate (empID, startTime, Notes)Values ('3','6/16/05','late')Insert into TimeLate (empID, startTime, Notes)Values ('1','6/22/05','')Insert into TimeLate (empID, startTime, Notes)Values ('2','6/22/05','late')Insert into TimeLate (empID, startTime, Notes)Values ('3','6/22/05','')Insert into TimeLate (empID, startTime, Notes)Values ('1','6/23/05','')Insert into TimeLate (empID, startTime, Notes)Values ('2','6/33/05','')Insert into TimeLate (empID, startTime, Notes)Values ('3','6/33/05','')I could go as far as next select, then I run into syntax error when I tried to further group by empid.select empid, count (notes) NCount from timelate group by notes, empidThanks! |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-06-23 : 17:04:01
|
| oh, here is the ultimate result I am shooting for.[empID][wkEnd6-18][wkEnd6-25][1][1][0][2][0][1][3][2][0] |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-06-23 : 20:22:59
|
| To get the columns that you want, I think you're going to have to do something like this:SELECT empID, SUM(ISNULL(CASE WHEN startTime BETWEEN '06/12/05' AND '06/18/05' THEN CASE WHEN Notes = 'late' THEN 1 ELSE 0 END END, 0)) AS [wkEnd6-18], SUM(ISNULL(CASE WHEN startTime BETWEEN '06/19/05' AND '06/25/05' THEN CASE WHEN Notes = 'late' THEN 1 ELSE 0 END END, 0)) AS [wkEnd6-25] FROM timelate GROUP BY empID ORDER BY empID However, that may not be so desirable for a lengthy range of dates. I think this would be easier to employ:SELECT empID, DATEADD(wk, DATEDIFF(wk, 5, startTime), 5) AS wkEnd, SUM(ISNULL(CASE WHEN Notes = 'late' THEN 1 ELSE 0 END, 0)) FROM timelate GROUP BY empID, DATEADD(wk, DATEDIFF(wk, 5, startTime), 5) ORDER BY empID, DATEADD(wk, DATEDIFF(wk, 5, startTime), 5) |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-07-11 : 15:45:05
|
| I got my help the other day here. The following select solved my problem then. SELECT empID, DATEADD(wk, DATEDIFF(wk, 5, startTime), 5) AS wkEnd, SUM(ISNULL(CASE WHEN Notes = 'late' THEN 1 ELSE 0 END, 0)) FROM timelate GROUP BY empID, DATEADD(wk, DATEDIFF(wk, 5, startTime), 5) ORDER BY empID, DATEADD(wk, DATEDIFF(wk, 5, startTime), 5) Now the users came back with additional requrements. I tried to twist the sql on my own, but got stucked again.How does this line of code do its trick?DATEADD(wk, DATEDIFF(wk, 5, startTime), 5) AS wkEndAccording to BOL, DateDiff needs two dates. How can a startDate be 5?Also, DateAdd needs (datepart, number, date), how can 5 meet its third parameter requrement?I'd appreciate any help! |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-07-11 : 18:20:54
|
| Ok, I figured out the answer. If I gave sql server a number in the place of date parameter, he will think it is the default date plus that number.select dateadd(dd, 0, 9)returns1900-01-10 00:00:00.000select dateadd(dd, 0, 0)returns1900-01-01 00:00:00.000Nothing exciting, it gives you a original starting point to code on. It is a little bit risky though. Just think about the possibility of Microsoft reset the default date to something else in the future release. Then all my code has to be reworked.Also, this reminded me DB2 uses 0001-01-01 as defualt date, and SQL server would not take it as a valid date. My IBM co-workers laughed at Microsoft's short history. |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-11 : 19:25:19
|
| Right. In this case, "5" is just the integer representation of 1/6/1900, which also happens to be a Saturday. I could have chosen any date that fell on a Saturday for the above query, but "5" was easiest to type. Plus, I wanted to choose a date that was pretty much guaranteed to fall before any of your "startTime" dates so that I could cleanly do a DATEDIFF with it.And just to explain further, "DATEADD(wk, DATEDIFF(wk, 5, startTime), 5)" first calculates the number of weeks between 1/6/1900 and the startTime date (using the inner query of "DATEDIFF(wk, 5, startTime)"). Then it adds this number of weeks back to 1/6/1900 to get the Saturday that falls within the same week as the startTime date. Because it's using number of weeks and not days, the result will always fall on a Saturday. Similarly, you can change the starting date to "6" (1/7/1900) to get only Sundays as results. You can also use this technique to find the first or last day of a month given any date. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-07-12 : 10:35:04
|
| Fabulous!It is amazing how much you can do with simple functions like these.I first got the request to have the week ends on Sunday. I accidentally got it right by change 5 to 6, but had no idea why I got it.Now they want a quarterly report. By figured out you code, I got this one to get what they want.DATEADD(qq, DATEDIFF(qq, 89, startTime), 89)I got the magic number 89 from “select dateadd(dd, 0, 89)” by trial and error because I am too lazy to find a calendar of 1900.Thanks again! |
|
|
|
|
|
|
count/recount help