SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 how to write a query for this
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ramya888
Starting Member

India
11 Posts

Posted - 06/03/2013 :  05:58:06  Show Profile  Reply with Quote
"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

India
52317 Posts

Posted - 06/03/2013 :  06:16:35  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/03/2013 06:17:12
Go to Top of Page

ramya888
Starting Member

India
11 Posts

Posted - 06/03/2013 :  08:35:16  Show Profile  Reply with Quote
hi visakh its showing incorrect syntax apr..

Ramy
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/03/2013 :  08:45:55  Show Profile  Reply with Quote
Have you changed this part ?
month in ([apr], [may], [jun], [jul] ,[aug], [sep], [oct], [nov], [dec], [jan], [feb], [mar] ]))

--
Chandu
Go to Top of Page

ramya888
Starting Member

India
11 Posts

Posted - 06/03/2013 :  12:26:32  Show Profile  Reply with Quote
yes...but still not working

Ramy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  00:00:40  Show Profile  Reply with Quote
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
Go to Top of Page

ramya888
Starting Member

India
11 Posts

Posted - 06/04/2013 :  02:21:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  02:43:34  Show Profile  Reply with Quote
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
Go to Top of Page

ramya888
Starting Member

India
11 Posts

Posted - 06/04/2013 :  03:06:44  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/04/2013 :  03:27:09  Show Profile  Reply with Quote
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

Edited by - bandi on 06/04/2013 03:28:57
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  03:54:50  Show Profile  Reply with Quote
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
Go to Top of Page

ramya888
Starting Member

India
11 Posts

Posted - 06/04/2013 :  05:47:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  06:05:59  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/04/2013 :  06:13:34  Show Profile  Reply with Quote
-- 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

Edited by - bandi on 06/04/2013 06:15:31
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  06:41:31  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/04/2013 06:42:21
Go to Top of Page

ramya888
Starting Member

India
11 Posts

Posted - 06/04/2013 :  07:37:46  Show Profile  Reply with Quote
its shows the following error

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AND'.
Ramy

Edited by - ramya888 on 06/04/2013 07:44:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  07:45:53  Show Profile  Reply with Quote
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
Go to Top of Page

ramya888
Starting Member

India
11 Posts

Posted - 06/04/2013 :  07:58:08  Show Profile  Reply with Quote
i dont no how to create a calendar table with fiscal info added.i am new to sqlserver

Ramy
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/04/2013 :  08:05:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  08:08:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000