LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Scripts, Code Samples and SSMS Custom Reports » SQL Server 2005/2008 » Line Chart

Line Chart

Options

Previous Topic · Next Topic

apileggi@yahoo.com

Posted: Thursday, May 13, 2010 9:40:40 PM

Rank: Newbie

Joined: 10/28/2009
Posts: 5
Points: 15
Where do you live?: Ohio

I am trying to create a line chart for one year of data. I would like to sum "in punches" for the (range of dates) one year and plot total in punches by each minute as category = minutes 00-59.

Here's my query, but I need to sum the total in punches by each minute.

SELECT COUNT(*), INPUNCHDTM
FROM VP_PUNCHORIGIN
WHERE (TO_CHAR(INPUNCHDTM, 'MI') BETWEEN '00' AND '59') AND (INPUNCHDTM BETWEEN
TO_DATE('2009/1/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') AND TO_DATE('2009/12/31:11:59:59PM', 'yyyy/mm/dd:hh:mi:ssam'))
GROUP BY INPUNCHDTM
ORDER BY INPUNCHDTM

I have course 160 and 162 but, not sure how to display 00-59 minutes for category and sum the "in punches" from 1-10000 for line chart. I don't believe the query above is correct.

Thanks

Smile

Scott Whigham

Posted: Friday, May 14, 2010 5:14:24 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Hmmm - I'm a bit confused about what the result set should look like. I think I understand 90% of the question but if you could post what the columns/rows look like, it would be easier.

Clarification question: what if there are no "in punches" for a given minute - do you still want to show that minute but with a "0" count?

apileggi@yahoo.com

Posted: Saturday, May 15, 2010 9:21:41 AM

Rank: Newbie

Joined: 10/28/2009
Posts: 5
Points: 15
Where do you live?: Ohio

Hi Scott,

The columns would be 2, MINUTES and QTY. I would think that there will be a punch for each minute over a years period, but yes it would "0" if no punch happened for that minute.
MINUTE QTY
0 2043
1 1548
2 1475
3 1388
4 1318
5 1274
6 1234
7 1168
8 1106
9 1109
10 1020
...

Thanks,
Armand

Scott Whigham

Posted: Saturday, May 15, 2010 9:33:41 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

If you want "0" to show up for each minute then you will need to use a numbers table/function - you need to JOIN to a set of data that has an unbroken set of numbers 0-59.

If you have course 150, I talk it about it here: http://www.learnitfirst.com/Course/150/Video/1238/Create-and-Use-Table-to-Find-Gaps-in-Another-Table.aspx

Basically the driving table - the "LEFT" table - will be the numbers table and you will want the join condition to be on the minute number

SELECT *
FROM dbo.NumbersTable nt
LEFT JOIN VP_PUNCHORIGIN p
ON nt.Number = DATEPART(minute, INPUNCHDTM)
WHERE nt.Number BETWEEN 0 AND 59

That's not the entire query of course but that covers the basics of using a numbers table. Just create a numbers table with the numbers 0-59 in it and use the above snippet as a starting point.

SELECT nt.Number, COUNT(*)
FROM dbo.NumbersTable nt
LEFT JOIN VP_PUNCHORIGIN p
ON nt.Number = DATEPART(minute, INPUNCHDTM)
WHERE nt.Number BETWEEN 0 AND 59
AND (INPUNCHDTM BETWEEN
TO_DATE('2009/1/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') AND TO_DATE('2009/12/31:11:59:59PM', 'yyyy/mm/dd:hh:mi:ssam'))
GROUP BY DATEPART(minute, INPUNCHDTM)
ORDER BY nt.Number

apileggi@yahoo.com

Posted: Sunday, May 16, 2010 10:46:48 AM

Rank: Newbie

Joined: 10/28/2009
Posts: 5
Points: 15
Where do you live?: Ohio

Scott,

I do have course 150 and will and go back and view that section.

Thank you

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.