DATES - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DATES

Author

Topic

sqlguy82
Starting Member

14 Posts

Posted - 2007-08-30 : 14:07:46

Here is what I'm working with ...

INSERT INTO PayRoll (EmployeeID, ForMonthOf, Pay)

SELECT EmployeeID, MonthOf = (SELECT DateName(month, DateAdd(month , -1, GETDATE()))),
CASE PayCode
WHEN 'H' THEN PayRate * (SELECT SUM(Hours) FROM HourTracking WHERE EmployeeID = em.EmployeeID)
WHEN 'S' THEN PayRate / 12
WHEN 'C' THEN PayRate * (SELECT SUM(SalePrice) FROM Sales WHERE EmployeeID = em.EmployeeID)
END PayCheckTotal

FROM EmployeeMaster em

* Very Simple ... However, WHEN 'H' Selects ALL hours from the HourTracking Table where the PayCode is H , WHEN 'C' Selects ALL sales from the Sales Table, and I only want it to SUM the hours and sales for each month. I have tried a stored procedure, but I do not want any user input. I simply want to execute the statement and have the paychecktotal for the month in my payroll table.
The first Select Statement enters the name of the month into the tale. I.E. - if today were Sept 1 and I ran this, it would enter August into the table. The issue is obtaining the sums for the month based on the Date column, as opposed to the sums of the entire table.

Any suggestions ? Appreciate it ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 14:31:21

You must have a datetime in both the hourtracking and sales for this.
Otherwise you are only going to get accumulated values.

INSERT		PayRoll
		(
			EmployeeID,
			ForMonthOf,
			Pay
		)
SELECT		em.EmployeeID,
		MonthOf = DATENAME(MONTH, DATEADD(MONTH, -1, CURRENT_TIMESTAMP)),
		CASE PayCode 
			WHEN 'H' THEN em.PayRate * ht.Hours * 1.0
			WHEN 'S' THEN em.PayRate / 12.0
			WHEN 'C' THEN em.PayRate * s.SalePrice * 1.0
		END AS PayCheckTotal
FROM		EmployeeMaster AS em
LEFT JOIN	(
			SELECT		EmployeeID,
					SUM(Hours) AS Hours
			FROM		HourTracking
			GROUP BY	EmployeeID
		) AS ht ON ht.EmployeeID = em.EmployeeID
LEFT JOIN	(
			SELECT		EmployeeID,
					SUM(SalePrice) AS SalePrice
			FROM		Sales
			GROUP BY	EmployeeID
		) AS s ON s.EmployeeID = em.EmployeeID

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 14:38:56

This is what you want

INSERT		PayRoll
		(
			EmployeeID,
			ForMonthOf,
			Pay
		)
SELECT		em.EmployeeID,
		SUBSTRING(CONVERT(VARCHAR, DATEADD(MONTH, -1, CURRENT_TIMESTAMP), 106), 4, 8),
		CASE PayCode
			WHEN 'H' THEN em.PayRate * ht.Hours * 1.0
			WHEN 'S' THEN em.PayRate / 12.0
			WHEN 'C' THEN em.PayRate * s.SalePrice * 1.0
		END AS PayCheckTotal
FROM		EmployeeMaster AS em
LEFT JOIN	(
			SELECT		EmployeeID,
					SUM(Hours) AS Hours
			FROM		HourTracking
			WHERE		<SomeDateTimeCol> >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
					AND <SomeDateTimeCol> < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
			GROUP BY	EmployeeID
		) AS ht ON ht.EmployeeID = em.EmployeeID
LEFT JOIN	(
			SELECT		EmployeeID,
					SUM(SalePrice) AS SalePrice
			FROM		Sales
			WHERE		<SomeDateTimeCol> >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
					AND <SomeDateTimeCol> < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
			GROUP BY	EmployeeID
		) AS s ON s.EmployeeID = em.EmployeeID

E 12°55'05.25"
N 56°04'39.16"

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-04 : 09:25:00

Thank You. It worked perfectly. NOW, if I may just ask a few questions regarding the syntax.

1. What is the SUBSTRING(CONVERT(VARCHAR( ... purpose and why is it necessary ?

2. What is the significance on the LEFT JOIN , as opposed to using an INNER or RIGHT JOIN ?

--- Any references or documentation you can point me towards regarding a better understanding of SQL would be great.

Thanks Again !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 09:28:13

Check out Books Online, the SQL Server help file and your next best friend!

E 12°55'05.25"
N 56°04'39.16"

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-10 : 10:28:41

INSERT PayRoll
(EmployeeID, ForMonthOf, Pay)
SELECT em.EmployeeID,
DATENAME(month, dateadd(MONTH,-1,GETDATE())),
CASE PayCode
WHEN 'H' THEN em.PayRate * (SELECT SUM(Hours)
FROM HourTracking WHERE EmployeeID = em.EmployeeID)
WHEN 'S' THEN em.PayRate / 12.0
WHEN 'C' THEN em.PayRate * s.SalePrice * 1.0
END AS PayCheckTotal
FROM EmployeeMaster AS em

This worked also ... except I did not return a Month/Year format for the date column.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 10:56:00

If you need to group by Month, I would either:

group by Year(YourDate), Month(YourDate)

or "round" each date to the first day of each month:

group by dateadd(month, datediff(month, 0, getdate()),0)

You should rarely, if ever, group on just a month number or a month name, since if your data spans multiple years you will have more than one year's worth of data in each month potentially.

- Jeff
http://weblogs.sqlteam.com/JeffS

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-10 : 11:03:52

Well the month gets put into a column in another table. I need the year to go with it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 11:43:26

quote:
Originally posted by sqlguy82

Well the month gets put into a column in another table. I need the year to go with it.

I don't understand what you are saying. I just showed you two ways to get "the year to go with" the month, that was my entire point.

- Jeff
http://weblogs.sqlteam.com/JeffS

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-10 : 11:53:11

I'm grouping by employee ID, which relates to a certain payrate. The payrate is determined by the data within the range of the date function. What I need in my SELECT statement - " SELECT em.EmployeeID,
DATENAME(month, dateadd(MONTH,-1,GETDATE())), " is a year added to it. So one of us is REALLY confused.
And if it's me, please explain.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 12:28:24

sqlguy -- The datename() function does not return a year, it just returns a month's name, right? So, if the data spans more than 1 year, the "January" group will contain more than one year of data for january.

Therefore, if you want to also GROUP BY a year to avoid this, do exactly as I showed you -- group by Year() as well, or keep it all as a datetime data type simply rounded to the first day of the month.

Maybe read this, see if it helps at all:

http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 12:37:52

The suggestion posted 08/30/2007 : 14:38:56 works fine too.
OP just decided to dismiss it.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 12:43:05

I still don't quite understand what he is looking for -- maybe some sample data would help? It doesn't make much sense. Also, the PayRoll table has a bad design if "MonthOf" is a varchar -- it should be either a DateTime data type, or separate integer Year and Month columns.

It seems like inserting redundant data into an additional table is not necessary at all, it can be just a View, but, again, it is hard to know for sure.

- Jeff
http://weblogs.sqlteam.com/JeffS

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-10 : 12:51:32

The table has 4 columns - Checknumber (auto generated/primary key) , EmployeeID , MonthOf , and Pay.

I can see how the MonthOf would be redundant -- However, for common queries, this column proves simple and useful.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 12:55:54

quote:

The table has 4 columns - Checknumber (auto generated/primary key) , EmployeeID , MonthOf , and Pay.

We can see that. And kind of data does MonthOf contain? What data type is it? You told us everything we already know, and nothing we didn't!

If you want this table to have any meaning, as I already mentioned, your Month column should be a DateTime data type -- dates rounded to the first day of each month, as I showed -- or you should have a separate Month and Year columns in that table.

- Jeff
http://weblogs.sqlteam.com/JeffS

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-10 : 13:10:12

IF ANYONE CAN EXPLAIN THE SUBSTRING WRITTEN BY PESO , there wouldn't be further discussion of the table. THE DATA type 106 ?
I am unfamiliar with this seeing as how I am a beginner.

SUBSTRING(CONVERT(VARCHAR, DATEADD(MONTH, -1, CURRENT_TIMESTAMP), 106), 4, 8),

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 13:21:16

The option setting 106 formats the datetime value to "10 SEP 2007" with CONVERT.
I then extract the month and year part with SUBSTRING starting at position 4 and 8 characters in length.

You don't have to shout. Both CONVERT and SUBSTRING functions are described in detail in Books Online, the SQL Server help file.
If you haven't made yourself familiar with it yet, now is the time to do so.

E 12°55'05.25"
N 56°04'39.16"

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-10 : 13:27:31

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-26 : 14:02:34

WHERE <SomeDateTimeCol> >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)
AND <SomeDateTimeCol> < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

If I'm looking for a month, doesn't the 31 mean 31 months later? I've unsuccessfully tried DAYS. I'm lost.

I did however get a daily rate for my salary employees in case they get fired halfway through the month. As long as they have an entry in a column from the table, they get paid for it.

LEFT JOIN (SELECT EmployeeID, Count(Attended) AS TotalDays
FROM Attendance
WHERE Attended = 'Y'
GROUP BY EmployeeID) AS a ON a.EmployeeID = em.EmployeeID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:13:02

31 means "January 31, 1900". From this date you calculate the difference in months.
Read BOOKS ONLINE as you suggested long ago.
This is THE best way to learn how functions available in SQL Server works.

E 12°55'05.25"
N 56°04'39.16"

sqlguy82
Starting Member

14 Posts

Posted - 2007-09-26 : 14:20:09

I'm not looking for a difference though ... I want data between a range of dates ... I might have just answered my own query. I'm going to try a between statement

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources