total in query - 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

General SQL Server Forums

New to SQL Server Programming

total in query

Author

Topic

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-29 : 09:43:40

guyz i wanna add total in my sql query i want to show in cross tab

DECLARE @query1 NVARCHAR(max)
SET @query1 = 'select case when( (select COUNT(id) from ##cmd_result where ##cmd_result.temp=FD.Work_Date ) > 0 )then CONVERT(VARCHAR(20), FD.Work_Date, 103)+ ''-'' + ''PH'' else CONVERT(VARCHAR(20), FD.Work_Date, 103) end as Total_Dates from form_details FD
WHERE FD.Work_Date BETWEEN '''+CONVERT(nvarchar(30), @start_Date , 110) +''' and '''+CONVERT(varchar(30),@end_Date, 110) +'''
GROUP BY FD.Work_Date
ORDER BY FD.Work_Date'

this is for getting work_date

i tried using

DECLARE @GrandTotalCol NVARCHAR (MAX)
SELECT TOP 5 COALESCE (@GrandTotalCol + 'ISNULL _
([' + CAST (Work_Date AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(Work_Date AS VARCHAR)+ '],0) + ')
FROM dbo.Form_Details ORDER BY Work_Date
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
print (@GrandTotalCol)

but am getting
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +

PLEASE GUIDE ME FOR ANY OTHER METHODS..ASAP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 14:16:42

can you post some sample data from Form_Details table and then specify how you want output to be?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-29 : 14:58:41

ISNULL([2012-01-01],0) + ISNULL _
([2012-01-02],0)
ISNULL(SUM([2012-01-01]),0),ISNULL(SUM([2012-01-02]),0)
SELECT Name ,BlockType, Block_Name ,[2012-01-01],[2012-01-02], ISNULL([2012-01-01],0) + ISNULL _
([2012-01-02],0) AS [Grand Total] INTO #temp_MatchesTotal
from
(
select EM.Employee_Name as Name ,
BN.Block_Name as Block_Name,
FD.Work_Date AS Date_Issued ,

CASE BN.BlockType WHEN 1 THEN 'Domestic' ELSE 'OverSeas' END AS BlockType,
sum(FD.Work_Hours) AS HOURS

from Employee_Master EM , Block_Master BN , Form_Details FD where Em.Employee_Master_Id = FD.Employee_Id
and BN.Block_Master_Id = FD.Block_Id
and FD.Work_Date BETWEEN '01-01-2012' and '01-02-2012'
AND EM.Employee_Master_Id = 1

GROUP BY BlockType,work_date ,Block_Name , EM .Employee_Name ,BlockType

) p PIVOT (MAX(HOURS) FOR Date_Issued IN ( [2012-01-01],[2012-01-02] ) ) AS pvt
order BY blocktype
SELECT * FROM #temp_MatchesTotal UNION ALL

SELECT Grand Total','',ISNULL(SUM([2012-01-01]),0),ISNULL(SUM([2012-01-02]),0), _
ISNULL (SUM([Grand Total]),0) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2012-01-02'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'p'.
Msg 105, Level 15, State 1, Line 23
Unclosed quotation mark after the character string ',',ISNULL(SUM([2012-01-01]),0),ISNULL(SUM([2012-01-02]),0), _
ISNULL (SUM([Grand Total]),0) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 15:33:18

hmm...I asked for sample data and output. No use posting whole query.

see guidelines to post question here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-29 : 15:56:40

quote:
Originally posted by visakh16

hmm...I asked for sample data and output. No use posting whole query.

see guidelines to post question here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sry
form_Dt_id frmi empid depid work_Date blck_id hrs
2 1 1 1 2012-01-01 4 3.00

i want output like

Name BlockName 2012-01-01 2012-01-02 total

Kshitiz IT 3 0 3

total 3 0 3

hope ur getting this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 16:49:57

no help seeing this as posted data is not complete
your output shows name which is not even in posted data.
please dont keep us guessing. Please understand that we cant access your system and also dont know what your rules are
so post proper sample data and explain your output if you REALLY want somebody to help you on this!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources