Solved

SQL

Posted on 2010-09-04
30
391 Views
Last Modified: 2012-05-10
Hello,
I have a small SQL Script to get some payroll numbers,
I'd like to have the columns to show seperate for certain codes,
Please see attaced sample (Sheet one is the way it is now, Sheet 2 is the way I'd like to have it)

The script is pretty simple,
Select * from Payroll where ...
Any help is appreciated.
Thanks,
sAMPLE.xlsx
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
  • 5
  • +2
30 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33604328
You could try dynamic SQL.  Assuming that Code is an int value...
DECLARE @sql varchar(8000)

SET @sql = 'SELECT Payno, Employee, Code, Amount, Info, Entry, CreatedDATE, ChqDate'

SELECT @sql = @sql + 
    ', CASE WHEN Code = ' + CAST(Code AS varchar(10)) + ' THEN Code ELSE NULL END AS Code' + CAST(Code AS varchar(10)) + ', ' +
    'CASE WHEN Code = ' + CAST(Code AS varchar(10)) + ' THEN Amount ELSE 0 END AS Code' + CAST(Code AS varchar(10)) + 'Amount'
FROM Payroll
GROUP BY Code
ORDER BY Code

SET @sql = @sql + ' FROM Payroll ORDER BY Employee, CreatedDATE'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33604403
Hello,
I get an error message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Sorry , I made a mistake, I checked my script again, it does have a join,

Select Distinct RegData.RegLink,RegData.eeLink,
RegData.ChqType,RegData.RegCode,RegData.RegValue,RegData.RegAltValue,RegData.AutoNumber,
RegData.CreatedDate,RegData.EndDate,RegData.idUser ,
(Select ChqDate from Register where Register.RegLink = RegData.RegLink) AS [ChqDate]
From RegData
Inner Join eeEmployee on RegData.eeLink = eeEmployee.eeLink
where RegData.createddate between '2010-08-01 12:01:19.000' and '2010-08-31 12:01:19.000'
order by RegData.eeLink

Thanks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33604457
Wass_QA,

When I import your sample file into SQL 2005 and run my batch, I get the exact output you said you wanted.

I suggest that you create a view that uses the SQL statement you posted above, and use that view as the table source in my SQL statement.

Patrick
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:W.E.B
ID: 33604514
Hello,
yep, it didn't give me any error,
but nothing shows up?
it just says, Command(s) completed successfully.

Not sure how to view the results.
I use sql 2005

DECLARE @sql varchar(8000)

SET @sql = 'Select Distinct RegData.RegLink,RegData.eeLink,
RegData.ChqType,RegData.RegCode,RegData.RegValue,RegData.RegAltValue,RegData.AutoNumber,
RegData.CreatedDate,RegData.EndDate,RegData.idUser ,
(Select ChqDate from Register where Register.RegLink = RegData.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
', CASE WHEN Code = ' + CAST(RegData.RegCode AS varchar(10)) + ' THEN RegData.RegCode ELSE NULL END AS Code' + CAST(RegData.RegCode AS varchar(10)) + ', ' +
 'CASE WHEN Code = ' + CAST(RegData.RegCode AS varchar(10)) + ' THEN Amount ELSE 0 END AS RegData.RegCode' + CAST(RegData.RegCode AS varchar(10)) + 'Amount'

From RegData
Inner Join eeEmployee on RegData.eeLink = eeEmployee.eeLink
where RegData.createddate between '2010-08-01 12:01:19.000' and '2010-08-31 12:01:19.000'

GROUP BY RegData.RegCode
ORDER BY RegData.RegCode

Thanks,
0
 

Author Comment

by:W.E.B
ID: 33605056
Hello,
Your help is apprciated.
I don't know how to view the results.

Thanks,
0
 

Accepted Solution

by:
chrisghall earned 75 total points
ID: 33606029
Surely this is quite simple to do longhand.....

select Payno, Employee, Code, Amount, Info, Entry, CreatedDATE, ChqDate, case when Code=19 then '19' else NULL end as 'Code19', case when Code=19 then Amount else 0 end as Code19Amount, ....... etc
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33606171
>>Surely this is quite simple to do longhand.....

It should be, but only as long as the codes are known beforehand :)

If the codes are not know beforehand, then dynamic SQL is the only recourse.
0
 

Author Comment

by:W.E.B
ID: 33606298
Hello,

matthewspatrick is correct, the codes are not known,
thre are over few hundred codes, and they differ between each payroll.

Please advice,
Thanks.
0
 

Author Comment

by:W.E.B
ID: 33606643
Hello,

I tried chrisghall way (for my known Codes), it worked,
how can I add the Sum for the Amounts? (Example Below, I need SUM of 'FedTaxAmount')

Select Distinct RegData.RegLink,RegData.eeLink,
RegData.ChqType,RegData.RegCode,RegData.RegValue,RegData.RegAltValue,RegData.AutoNumber,
RegData.CreatedDate,RegData.EndDate,RegData.idUser ,
(Select ChqDate from Register where Register.RegLink = RegData.RegLink) AS [ChqDate],

CASE When RegData.RegCode = '019' THEN 'FedTax'else Null end as 'FedTax',
CASE When RegData.RegCode = '019' then RegData.RegValue else 0 end as 'FedTaxAmount',
From RegData
left Join eeEmployee on RegData.eeLink = eeEmployee.eeLink
where RegData.createddate between '2010-01-01 12:01:19.000' and '2010-01-31 12:01:19.000' and RegData.eelink = 347
order by RegData.eeLink,ChqDate


Help is appreciated,
Thanks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607000
OK, try this...


DECLARE @sql varchar(8000)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue, 
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser, 
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql + 
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join 
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33607061
Hello,

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Amount'.

Thanks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607176
What is the output from this?


DECLARE @sql varchar(8000)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue, 
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser, 
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql + 
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join 
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

SELECT LEN(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33607228
Hello,

No Column Name
8000

Tx
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 total points
ID: 33607272
OK, the query string was getting too long, and was truncated.

Try this:


DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue, 
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser, 
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql + 
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join 
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33607566
Hello,
ok, it says Command(s) completed successfully.,

How do I view it?
Thsnks,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607661
Didn't you get a result set?

Please post the script *exactly* as you ran it.
0
 

Author Comment

by:W.E.B
ID: 33607682
Hello,
I ran it exactly as is.

DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
    ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS Code' + RegCode + ', ' +
    'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount'
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)


Thansk
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33607688
Do you have your options set to output results to the grid, or to a file?
0
 

Author Comment

by:W.E.B
ID: 33607758
Hello,
To Grid.

I just tried it again, same script,
it says
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Amount'.

Thanks,
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33607860
there is no space before 'Amount'. Maybe that is the issue here.
0
 

Author Comment

by:W.E.B
ID: 33607874
Hello,
I Dont see any spaces before 'Amount'

-------- AS Code' + RegCode + 'Amount'
Tx
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33607946
Sorry, my bad, I did not fully understand the purpose of that construct. You want to get a column for each RegCode, which is alphanumeric. So you get e.g.
   CodeAA, CodeAAAmount, CodeBB, CodeBBAmount
aso.

There seems to be an issue with a particular RegCode, which is not valid as column name, maybe containing a space. Then the following should work:

...
SELECT @sql = @sql
+ ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS [Code' + RegCode + '], '
+   'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS [Code' + RegCode + 'Amount]'
...
0
 

Author Comment

by:W.E.B
ID: 33607985
Hello,
I Appreciate your help,

i USED THIS

DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql
+ ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS [Code' + RegCode + '], '
+   'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS [Code' + RegCode + 'Amount]'

FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData rd left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

i get error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'G10'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'G10'.

These are the RegCode ( some are numbers, some start with Letter),

Also,
any way to sum the values of each code?
even if we have to create a new column, or a Sum header per column

thanks,
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33608000
Please print out the @sql var before EXEC, so we can see what is generated.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33608033
Wass_QA,

1) I think you'd better upload some dummy data for your three tables RegData, eeEmployee, and Register.  Make sure you do not post any sensitive/confidential data.  Use "fake" data if necessary

2) As for getting a sum, I think you should stick to your original question, and pursue summing the columns in a new question once this one is resolved

Patrick
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33609009
the error message makes me think that 'G10' is the value of Regcode, right?

DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN RegCode ELSE NULL END AS Code' + RegCode + ' ' +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount '
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)

Open in new window

0
 

Author Comment

by:W.E.B
ID: 33610728
Hello,
Correct, the 'G10'  Is the value of the Regcode

I ran what you've sent,
DECLARE @sql varchar(max)

SET @sql = 'Select Distinct rd.RegLink, rd.eeLink, rd.ChqType, rd.RegCode, rd.RegValue,
    rd.RegAltValue, rd.AutoNumber, rd.CreatedDate, rd.EndDate, rd.idUser,
    (Select r.ChqDate from Register r where r.RegLink = rd.RegLink) AS [ChqDate]'

SELECT @sql = @sql +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN RegCode ELSE NULL END AS Code' + RegCode + ' ' +
    ', CASE WHEN RegCode = ''' + RegCode + ''' THEN rd.RegValue ELSE 0 END AS Code' + RegCode + 'Amount '
FROM RegData
GROUP BY RegCode
ORDER BY RegCode

SET @sql = @sql + ' From RegData r left Join
    eeEmployee e on rd.eeLink = e.eeLink
    where rd.createddate between ''2010-01-01 12:01:19.000'' and ''2010-01-31 12:01:19.000'' and
    rd.eelink = 347
    order by rd.eeLink,ChqDate'

EXEC(@sql)


I get error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Amount'.

I changed to (as per previous)
SELECT @sql = @sql
+ ', CASE WHEN RegCode = ' + RegCode + ' THEN RegCode ELSE NULL END AS [Code' + RegCode + '], '
+   'CASE WHEN RegCode = ' + RegCode + ' THEN rd.RegValue ELSE 0 END AS [Code' + RegCode + 'Amount]'

I get the error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.eeLink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.createddate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.createddate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.eelink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.RegLink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.eeLink" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.ChqType" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.RegCode" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "rd.RegValue" could not be bound.
Msg 4104, Level 16, State 1, Line 1 ---------------

If I change your Join
SET @sql = @sql + ' From RegData r left Join
TO
SET @sql = @sql + ' From RegData rd left Join

I get error
Msg 207, Level 16, State 1, Line 3
Invalid column name 'G10'.
Msg 207, Level 16, State 1, Line 3

Thanks.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33611048
After the last change, please print out the @sql var before EXEC, so we can see what is generated.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33611167
Yes, and please post some dummy data for testing purposes.

In the future, please be sure to give an accurate description of what you are trying to do.  The original SQL I posted did *exactly* what you indicated you wanted in your question; it was something of a shock for you to say that your actual situation was not at all as you described it initially.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33612300
I agree. There is an unexpected expansion of the question in http:#a33606643, instead of concentrating on the issue as-is - no data, or syntax error.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question