how to write a query for this - 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

SQL Server 2012 Forums

SQL Server Administration (2012)

how to write a query for this

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources