Solved

SQL

Posted on 2010-09-04
30
364 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
  • 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: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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

How do I view it?
Thsnks,
0
 
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: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 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: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 69

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 69

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 69

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: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 69

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 69

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 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