add grand total to query FAO riteshshah

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

KeirMcCannAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
RiteshShahConnect With a Mentor Commented:
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
 
RiteshShahCommented:
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
 
KeirMcCannAuthor Commented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
KeirMcCannAuthor Commented:
works a treat again!!!
Many thanks!
0
 
KeirMcCannAuthor Commented:
will this go straight into the email code again?
0
 
RiteshShahCommented:
YES, IT WILL, if you face any problem, let me know.
0
 
KeirMcCannAuthor Commented:
doesnt appear to work in sql agent with the email command unfortunately  :(
0
 
RiteshShahCommented:
can you show me your code here?
0
 
KeirMcCannAuthor Commented:
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
 
RiteshShahCommented:
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
 
KeirMcCannAuthor Commented:
thanks again!
0
All Courses

From novice to tech pro — start learning today.