Solved

SQL

Posted on 2010-09-04
30
340 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:Wass_QA
  • 13
  • 10
  • 5
  • +2
30 Comments
 
LVL 92

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:Wass_QA
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 92

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
 

Author Comment

by:Wass_QA
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:Wass_QA
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 92

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:Wass_QA
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:Wass_QA
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 92

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:Wass_QA
ID: 33607061
Hello,

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

Thanks,
0
 
LVL 92

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:Wass_QA
ID: 33607228
Hello,

No Column Name
8000

Tx
0
 
LVL 92

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:Wass_QA
ID: 33607566
Hello,
ok, it says Command(s) completed successfully.,

How do I view it?
Thsnks,
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 92

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:Wass_QA
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 92

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:Wass_QA
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 68

Expert Comment

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

Author Comment

by:Wass_QA
ID: 33607874
Hello,
I Dont see any spaces before 'Amount'

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

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:Wass_QA
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 68

Expert Comment

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

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 142

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:Wass_QA
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 68

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 92

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 68

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now