Help with this SQL Statement - 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

General SQL Server Forums

New to SQL Server Programming

Help with this SQL Statement

Author

Topic

Rickkap
Starting Member

5 Posts

Posted - 2014-08-15 : 17:32:41

Hello all

I really need some help with the following.

SELECT DATEADD(month, DATEDIFF(month, '20010101', DATEADD(day, - 10, date)), '20010111') AS PeriodStart, SUM(pay) AS total FROM payroll
WHERE (date BETWEEN DATEADD(month, DATEDIFF(month, '20010101', DATEADD(day, - 10, CURRENT_TIMESTAMP)), '20001211') AND DATEADD(month, DATEDIFF(month,'20010101', DATEADD(day, - 10, CURRENT_TIMESTAMP)), '20010210')) AND (email = xx@.xx.com')
GROUP BY DATEADD(month, DATEDIFF(month, '20010101', DATEADD(day, - 10, date)), '20010111')ORDER BY PeriodStart

Basically what I have is 2 tables (payroll and history). In the payroll table I have the following items, date pay username timeon timeoff. and in the history table the columns are the months of the year.

When a user enters his timeon and timeoff details he also enters a value into the pay column(if there is one, 0 if none) along with the date. SO a typical row of the table will be:

Date           username    timeon timeoff  pay
14/08/2014     xxxxxxx     08:00   09:00   80

etc...

When the user has submitted the details the script above reads the total pay and updates the history table with the total figure for the answer. (this is done in a separate script) the information from this script is collected by an array. One of the reason why I need help

Also our payroll runs from the 11th of the month to the 10th of the next month and that's what the above SQL code does. It looks for all pay items from the 11th to the 10th and sums as total. It also gives me the values for the previous month. The output view looks something like this:

PeriodStart    total
2014-07-11     180       (Previous month)
2014-08-11     45        (This month)

Previous month being 11/07/2014 - 10/08/2014
This month being 11/08/2014 - 10/09/2014

What I need is for the above code to give me 4 separate variables ie. previous_month, ptotal, this_month, ttotal. The reason for this is a need to get away from collecting the information be an array.

Hope someone can help me.

If you need anymore info please let me know.

Thanks in advance.

Rickkap

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-15 : 19:05:48

Maybe:

select username
      ,sum(case when [date]<dateadd(month,datediff(month,'19010101',dateadd(day,-10,getdate())),'19010111') then pay else 0) as prev_pay
      ,sum(case when [date]<dateadd(month,datediff(month,'19010101',dateadd(day,-10,getdate())),'19010111') then 0 else pay) as curr_pay
  from payroll
 where [date]>=dateadd(month,datediff(month,'19010101',dateadd(day,-10,getdate())),'19001211')
   and [date]<dateadd(month,datediff(month,'19010101',dateadd(day,-10,getdate())),'19010211')
 group by username

Rickkap
Starting Member

5 Posts

Posted - 2014-08-16 : 03:34:59

I forgot to mention that the 4 separate variables will be used in my classic ASP coding.

I've also had a thought overnight, if the above coding was spilt into 2 sql statements that would be fine.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-16 : 10:30:49

[code]DECLARE @Sample TABLE
(
UserName VARCHAR(10) NOT NULL,
WorkTime DATETIME NOT NULL,
Pay DECIMAL(10, 2) NOT NULL
);

INSERT @Sample
(
UserName,
WorkTime,
Pay
)
VALUES ('Peso', '20140708', 132),
('Peso', '20140709', 345),
('Peso', '20140710', 152),
('Peso', '20140711', 623),
('Peso', '20140712', 523),
('Peso', '20140713', 452),
('Peso', '20140714', 724),
('Peso', '20140715', 734),
('Peso', '20140806', 236),
('Peso', '20140807', 634),
('Peso', '20140808', 266),
('Peso', '20140809', 363),
('Peso', '20140810', 574),
('Peso', '20140811', 744),
('Peso', '20140812', 644);

-- SwePeso
DECLARE @PreviousStart DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), DATEADD(DAY, -10, '19000111')),
@CurrentStart DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), DATEADD(DAY, -10, '19000111')),
@FollowingStart DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), DATEADD(DAY, -10, '19000211'));

SELECT SUM(CASE WHEN WorkTime >= @PreviousStart AND WorkTime < @CurrentStart THEN Pay ELSE 0 END) AS PreviousMonth,
SUM(CASE WHEN WorkTime >= @CurrentStart AND WorkTime < @FollowingStart THEN Pay ELSE 0 END) AS ThisMonth
FROM @Sample
WHERE WorkTime >= @PreviousStart
AND WorkTime < @FollowingStart;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Rickkap
Starting Member

5 Posts

Posted - 2014-08-16 : 15:04:55

@Bitsmed I tried your code but I got an error
@SwePeso Sorry I am very new to SQL and I'm not sure how I can use this code in my Classic ASP code.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-16 : 16:58:36

quote:
Originally posted by Rickkap

@Bitsmed I tried your code but I got an error

And the error is?

Rickkap
Starting Member

5 Posts

Posted - 2014-08-16 : 17:07:01

Error in list of function arguments: ')' not recognized.
Unable to parse query text.

Executed SQL statement: select username
,sum(case when [date]<dateadd(month,datediff(month,'19010101',dateadd(day,-10,getdate())),'19010111') then pay else 0) as prev_pay
,sum(case when [date]<dateadd(month,datediff(month,'19010101',dateadd(day,-10,getdate())),'19010111'...
Error Source:.Net SQLClientData Provider
Error Message: Incorrect syntax near ')'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-16 : 17:20:16

Looks like you retyped this instead of copying and pasting. You have extra parentheses. Count them and you'll see where they are unbalanced.

Rickkap
Starting Member

5 Posts

Posted - 2014-08-16 : 17:27:43

Yes I know it's unbalanced but if you look at Bitsmed original post they are balanced. The message you can see is what SQL Management Studio gives me when I execute his query...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-17 : 08:11:25

Well, you have to balance the parentheses.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources