[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

add grand total to query FAO riteshshah

Posted on 2009-05-12
11
Medium Priority
?
386 Views
Last Modified: 2012-05-06
I wish to add the grand total of t0.linetotal to this query also?  I did try using hte same method as you but found a few errors unfortunately.
SELECT T1.[SlpName], T2.CardName
, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date
, sum(T0.[Quantity]) Total_fixes
, sum(T0.LineTotal) Invoicing_total
, sum(T3.[U_IIS_TIME]) TaskTime_Total
, T0.[u_iis_INS2], T0.[u_IIS_INS3]
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]

Open in new window

0
Comment
Question by:KeirMcCann
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24362862
try using this.



SELECT T1.[SlpName]
, T2.CardName
, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date
, sum(T0.[Quantity]) Total_fixes
, sum(T0.LineTotal) Invoicing_total
, sum(T3.[U_IIS_TIME]) TaskTime_Total
, T0.[u_iis_INS2], T0.[u_IIS_INS3]
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
 
union
 
SELECT 'Z Grand Total'
, ''
, NULL
, null
, sum(T0.LineTotal) Invoicing_total
, NULL
, NULL
, NULL
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
 
 
group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]

Open in new window

0
 

Author Comment

by:KeirMcCann
ID: 24362906
OSLP.slpname is invalid in the select list because it is not contained in either the aggregate function or group by clause

Thanks

Keir
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24362982
what about this?



SELECT T1.[SlpName]
, T2.CardName
, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date
, sum(T0.[Quantity]) Total_fixes
, sum(T0.LineTotal) Invoicing_total
, sum(T3.[U_IIS_TIME]) TaskTime_Total
, T0.[u_iis_INS2], 
T0.[u_IIS_INS3]
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]
 
union
 
SELECT 'Z Grand Total'
, ''
, NULL
, null
, sum(T0.LineTotal) Invoicing_total
, NULL
, NULL
, NULL
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:KeirMcCann
ID: 24362990
works a treat again!!!
Many thanks!
0
 

Author Comment

by:KeirMcCann
ID: 24362993
will this go straight into the email code again?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24363028
YES, IT WILL, if you face any problem, let me know.
0
 

Author Comment

by:KeirMcCann
ID: 24363058
doesnt appear to work in sql agent with the email command unfortunately  :(
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24363061
can you show me your code here?
0
 

Author Comment

by:KeirMcCann
ID: 24363069
EXEC msdb.dbo.sp_send_dbmail
@recipients='keir@lala.com',
@body='labour report',
@subject ='Labour Costs',
@profile_name = 'Keir',
@query ='set nocount on; SELECT T1.[SlpName]
, T2.CardName
, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date
, sum(T0.[Quantity]) Total_fixes
, sum(T0.LineTotal) Invoicing_total
, sum(T3.[U_IIS_TIME]) TaskTime_Total
, T0.[u_iis_INS2],
T0.[u_IIS_INS3]
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]
 
union
 
SELECT 'Z Grand Total'
, ''
, NULL
, null
, sum(T0.LineTotal) Invoicing_total
, NULL
, NULL
, NULL
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)); set nocount off;',
@execute_query_database = 'advanced',
@attach_query_result_as_file = 0,
@query_result_header = 0,
@query_result_width = 1000,
@exclude_query_output = 0,
@query_result_separator = ';'
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24363096
look at this.
EXEC msdb.dbo.sp_send_dbmail
@recipients='keir@lala.com',
@body='labour report',
@subject ='Labour Costs',
@profile_name = 'Keir',
@query ='set nocount on; SELECT T1.[SlpName]
, T2.CardName
, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date
, sum(T0.[Quantity]) Total_fixes
, sum(T0.LineTotal) Invoicing_total
, sum(T3.[U_IIS_TIME]) TaskTime_Total
, T0.[u_iis_INS2], 
T0.[u_IIS_INS3]
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = ''6''
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]
 
union
 
SELECT ''Z Grand Total''
, ''
, NULL
, null
, sum(T0.LineTotal) Invoicing_total
, NULL
, NULL
, NULL
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = ''6''
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)); set nocount off;',
@execute_query_database = 'advanced',
@attach_query_result_as_file = 0,
@query_result_header = 0,
@query_result_width = 1000,
@exclude_query_output = 0,
@query_result_separator = ';'

Open in new window

0
 

Author Comment

by:KeirMcCann
ID: 24363107
thanks again!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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