[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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

0
KeirMcCann
Asked:
KeirMcCann
  • 6
  • 5
1 Solution
 
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
 
RiteshShahCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now