| Author |
Topic |
|
ramya888
Starting Member
11 Posts |
 Posted - 2013-06-03 : 05:58:06
|
"select empname,empcode,month,year,ptax from employeepayslip where month='" + element + "'and empname='" + drp_empwise.Text + "' and year='" + c1 + "' group by empname,empcode"
its shows the output like
empname empcode month year ptax
Raghu CDO1 May 2013 200
Raghu CD01 June 2013 200
like the ways it display upto april 2013 to march 2014,if there is no records nothing is displayed,its all working fine.but my problem is i want to display the particular employee in the crystal report like the below requirements
empname empcode apr may jun jul aug sep oct nov dec jan feb mar totamt
i dont no how can i display like this?please help me
Ramy |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-03 : 06:16:35
|
you need to do pivoting.
something like
"select * , [apr] + [may] + [jun] + [jul] + [aug] + [sep] + [oct] + [nov] + [dec] + [jan] + [feb] + [mar] as totamt
from(empname,empcode,month,year,ptax
from employeepayslip
where month='" + element + "'and empname='" + drp_empwise.Text + "' and year='" + c1 + "'
group by empname,empcode)t
pivot(sum(ptax) for month in ([apr], [may], [jun], [jul] ,[aug], [sep], [oct], [nov], [dec], [jan], [feb], [mar] ]))p"
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
 |
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-03 : 08:35:16
|
hi visakh its showing incorrect syntax apr..
Ramy |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-03 : 08:45:55
|
Have you changed this part ?
month in ([apr], [may], [jun], [jul] ,[aug], [sep], [oct], [nov], [dec], [jan], [feb], [mar] ]))
--
Chandu |
|
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-03 : 12:26:32
|
yes...but still not working
Ramy |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-04 : 00:00:40
|
quote:
Originally posted by ramya888
yes...but still not working
Ramy
do a print of string and pot the result here
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-04 : 02:21:24
|
previous output
empname empid month year ptax
raghu 123 april 2013 345
raghu 123 may 2013 345
raghu 123 februa 2013 345
raghu 123 march 2013 345
expected output
empname empid april may february march totptax
raghu 123 345 345 345 345 1380
Ramy |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-04 : 02:43:34
|
quote:
Originally posted by ramya888
previous output
empname empid month year ptax
raghu 123 april 2013 345
raghu 123 may 2013 345
raghu 123 februa 2013 345
raghu 123 march 2013 345
expected output
empname empid april may february march totptax
raghu 123 345 345 345 345 1380
Ramy
Sorry if i wasnt clear
I was asking to print the sql string created by my suggestion and post it here to see where you're getting the error
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-04 : 03:06:44
|
this is your query u suggest me.
select * , [apr] + [may] + [jun] + [jul] + [aug] + [sep] + [oct] + [nov] + [dec] + [jan] + [feb] + [mar] as totamt
from(empname,empcode,month,year,ptax
from edepotpayslip
where month='" + element + "'and empname='Ganesh Kunder' and year=2013
group by empname,empcode)t
pivot(sum(ptax) for month in ([apr], [may], [jun], [jul] ,[aug], [sep], [oct], [nov], [dec], [jan], [feb], [mar]]))p
this is the error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 'mar]))p
'.
Ramy |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-04 : 03:27:09
|
quote:
Originally posted by ramya888
this is your query u suggest me.
select * , [apr] + [may] + [jun] + [jul] + [aug] + [sep] + [oct] + [nov] + [dec] + [jan] + [feb] + [mar] as totamt
from(empname,empcode,month,year,ptax
from edepotpayslip
where month='" + element + "'and empname='Ganesh Kunder' and year=2013
group by empname,empcode)t
pivot(sum(ptax) for month in ([apr], [may], [jun], [jul] ,[aug], [sep], [oct], [nov], [dec], [jan], [feb], [mar]]))p
strike off that extra square bracket
this is the error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 'mar]))p
'.
Ramy
make sure to put exact month values(as per your table data) in the IN clause
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-04 : 03:54:50
|
one more thing is if your fiscal year starts from Apr of one calendar year and runs through to mar of next year you might want to build a calendar table with fiscal year defined with month nos. otherwise your result will have only data based on year groupings ie it will show jan to dec 2012, jan 2013 to dec 2013 etc as rows rather than from Apr 2012 to Mar 2013 which is what you may require in same row.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-04 : 05:47:16
|
i am using to display the april 2013 to march 2014 like this
select empname,empid,ptax,month,year from payslip where month in('april','may','june','july','august','september','october','november','december) from payslip where year=2013 or month in (''january','february','march') from payslip where year=2014
its show proper result like this
empname empid ptax month year
aaa 123 345 april 2013
'
aaa 123 345 march 2014
is there any possibilities to display like the below..using the above query
empname empid apr may june july august september october november december
aaa 123 345 0 0 0 0 0 0 0 345
Ramy |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-04 : 06:05:59
|
quote:
Originally posted by ramya888
i am using to display the april 2013 to march 2014 like this
select empname,empid,ptax,month,year from payslip where month in('april','may','june','july','august','september','october','november','december) from payslip where year=2013 or month in (''january','february','march') from payslip where year=2014
its show proper result like this
empname empid ptax month year
aaa 123 345 april 2013
'
aaa 123 345 march 2014
is there any possibilities to display like the below..using the above query
empname empid apr may june july august september october november december
aaa 123 345 0 0 0 0 0 0 0 345
Ramy
Hmm... I doubt that
as that query is not even syntactically correct
Anyways, this is exactly what i explained in my last post
for getting it in way you want, you need a calendar table with fiscal year,month etc info
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-04 : 06:13:34
|
-- Run this script. Let me know the result
SELECT empname, empcode, [april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]
from(SELECT empname,empcode,month,year,ptax
FROM edepotpayslip
WHERE ( month IN ([april], [may], [june], [july] ,[august], [september], [october], [november], [december]) and year=2013 )
OR (MONTH IN [january], [february], [march]) AND YEAR = 2014)
)
AND empname='Ganesh Kunder'
)t
PIVOT(SUM(ptax) for month in ([april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]))p
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-04 : 06:41:31
|
quote:
Originally posted by bandi
-- Run this script. Let me know the result
SELECT empname, empcode, [april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]
from(SELECT empname,empcode,month,year,ptax
FROM edepotpayslip
WHERE ( month IN ([april], [may], [june], [july] ,[august], [september], [october], [november], [december]) and year=2013 )
OR (MONTH IN [january], [february], [march]) AND YEAR = 2014)
)
AND empname='Ganesh Kunder'
)t
PIVOT(SUM(ptax) for month in ([april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]))p
--
Chandu
Apart from the obvious syntax issue with set of values passed for IN
this solution has a limitation that for data spanning multiple fiscal years this wont work as intended. You might have to write separate selects in those cases for handle each of those fiscal years.
It would be much easier and flexible if this can be handled by maintaining a calendar table with fiscal year, month numbers etc maintained against actual calendar year,month etc which is what we usually do.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-04 : 07:37:46
|
its shows the following error
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AND'.
Ramy |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-04 : 07:45:53
|
quote:
Originally posted by ramya888
hi visakh..its shows the following error
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'january'
Ramy
It will which is what i told in my last post
you best bet is to create a calendar table with fiscal info added.
Anyways Bandis suggestion corrected is this
SELECT empname, empcode, [april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]
from(SELECT empname,empcode,month,year,ptax
FROM edepotpayslip
WHERE month IN ('april', 'may', 'june', 'july' ,'august', 'september', 'october', 'november', 'december') and year=2013 )
OR (MONTH IN 'january', 'february', 'march') AND YEAR = 2014)
)
AND empname='Ganesh Kunder'
)t
PIVOT(SUM(ptax) for month in ([april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]))p
Again to restate
above suggestion will work only when a single fiscal year is included and will give different result when data includes more than one fiscal
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-04 : 07:58:08
|
i dont no how to create a calendar table with fiscal info added.i am new to sqlserver
Ramy |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-04 : 08:05:08
|
fixed another typo
quote:
Originally posted by visakh16
quote:
Originally posted by ramya888
hi visakh..its shows the following error
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'january'
Ramy
It will which is what i told in my last post
you best bet is to create a calendar table with fiscal info added.
Anyways Bandis suggestion corrected is this
SELECT empname, empcode, [april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]
from(SELECT empname,empcode,month,year,ptax
FROM edepotpayslip
WHERE month IN ('april', 'may', 'june', 'july' ,'august', 'september', 'october', 'november', 'december') and year=2013 )
OR (MONTH IN ('january', 'february', 'march') AND YEAR = 2014)
)
AND empname='Ganesh Kunder'
)t
PIVOT(SUM(ptax) for month in ([april], [may], [june], [july] ,[august], [september], [october], [november], [december], [january], [february], [march]))p
Again to restate
above suggestion will work only when a single fiscal year is included and will give different result when data includes more than one fiscal
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-04 : 08:08:49
|
ok here you go.
SELECT *,
YEAR(DATEADD(mm,-3,date)) AS FiscalYear,
CASE WHEN MONTH(date) - 3 > 0 THEN MONTH(date) - 3 ELSE 12 + (MONTH(date) - 3) END AS FiscalMonth
FROM dbo.CalendarTable('20050101','20151231',0,0)f
for illustration i've included only dates from 1 jan 2005 to 31st dec 2015. you can extend it to any inetrval you want and also insert this to permanent table using SELECT ...INTO syntax
see the definition of calendar date function here
http://visakhm.blogspot.in/2010/02/generating-calendar-table.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
|
|
how to write a query for this