October 23, 2017 at 3:33 pm
DECLARE @Yesterday DATETIME = DATEADD(DAY,-1,CAST(GETDATE() AS DATE));
DECLARE @PeriodStart DATETIME = DATEADD(DAY,-6,@Yesterday),
@PeriodEnd DATETIME = CAST(GETDATE() AS DATE),
@MonthStart DATETIME =DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0);
SET NOCOUNT ON;
SELECT
LTRIM(RIGHT(CONVERT(varchar(6), [ie].[Invoice_Number],100),7)) AS [Invoice#]
--, FORMAT([ie].[DateTime], 'MM-dd-yyyy') AS [Void_Date]
, CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-')) AS [Void_Date]
, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[DateTime],100),7)) AS [Void_Time]
, [ie].ItemNum AS [Item_#_Removed]
, [inv].[ItemName]
, '$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY)) AS [Amount]
, CONVERT(VARCHAR(10), CAST([ie].[Quantity] AS INT)) AS [Quantity]
, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[Reason_Code],100),14)) AS [Reason_Code]
FROM
Invoice_Exceptions AS [ie]
JOIN inventory AS [inv]
ON [ie].[ItemNum] = [inv].[ItemNum]
JOIN Invoice_Totals AS [it]
ON [ie].[Invoice_Number] = [it].[Invoice_Number]
AND [ie].[DateTime] >= @PeriodStart AND [ie].[DateTime] < @PeriodEnd
ORDER BY
[ie].[DateTime] ASC
So at the end of the 16th (as this code is for the last 7 days) I will have a line: TOTAL $56.77 (ie].[Amount])
then begin the 17th and so forth
I understand this is better achieved at the Presentation level, but due to my project I dont have the luxury.
GROUP BY (Amount)
WITH ROLLUP ?
October 23, 2017 at 4:23 pm
This works, but its at the end and thats not as sexy as I would like it to be. BUT the output to my csv file is formatted perfectly. Its just all at the bottom...Plus I want to have the TOTAL_DELETION format with a $ sign in front of the total and only .00 at the end.
DECLARE @Yesterday DATETIME = DATEADD(DAY,-1,CAST(GETDATE() AS DATE));
DECLARE @PeriodStart DATETIME = DATEADD(DAY,-6,@Yesterday),
@PeriodEnd DATETIME = CAST(GETDATE() AS DATE),
@MonthStart DATETIME =DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0);
SET NOCOUNT ON;
SELECT
LTRIM(RIGHT(CONVERT(varchar(6), [ie].[Invoice_Number],100),7)) AS [Invoice#]
--, FORMAT([ie].[DateTime], 'MM-dd-yyyy') AS [Void_Date]
, CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-')) AS [Void_Date]
, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[DateTime],100),7)) AS [Void_Time]
, [ie].ItemNum AS [Item_#_Removed]
, [inv].[ItemName]
, '$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY)) AS [Amount]
, CONVERT(VARCHAR(10), CAST([ie].[Quantity] AS INT)) AS [Quantity]
, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[Reason_Code],100),14)) AS [Reason_Code]
FROM
Invoice_Exceptions AS [ie]
JOIN inventory AS [inv]
ON [ie].[ItemNum] = [inv].[ItemNum]
JOIN Invoice_Totals AS [it]
ON [ie].[Invoice_Number] = [it].[Invoice_Number]
AND [ie].[DateTime] >= @PeriodStart AND [ie].[DateTime] < @PeriodEnd
ORDER BY
[ie].[DateTime] ASC
SELECT
SUM(CAST([ie].[Amount] AS money)) AS TOTAL_DELETION
, CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-')) AS [Void_Date]
FROM
Invoice_Exceptions AS [ie]
JOIN inventory AS [inv]
ON [ie].[ItemNum] = [inv].[ItemNum]
JOIN Invoice_Totals AS [it]
ON [ie].[Invoice_Number] = [it].[Invoice_Number]
AND [ie].[DateTime] >= @PeriodStart AND [ie].[DateTime] < @PeriodEnd
GROUP BY CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), [ie].[DateTime],101),'/','-'))
October 23, 2017 at 5:40 pm
You should know better than to post a question without DDL and DLM. 🙂
Without that, I'd suggest using CTEs to get your initial dataset, then resolve your results and daily aggregates in 2 latter CTEs (with some kind of Row Numbering) and then, finally, select the whole lot using a UNION in your final SELECT statement (outside the CTEs).
But yes, you're right, this is better done at your presentation layer (for example grouping in SSRS). As should your formatting (in relation to showing your figure as a currency not a decimal).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2017 at 11:54 am
Thom A - Monday, October 23, 2017 5:40 PMYou should know better than to post a question without DDL and DLM. 🙂Without that, I'd suggest using CTEs to get your initial dataset, then resolve your results and daily aggregates in 2 latter CTEs (with some kind of Row Numbering) and then, finally, select the whole lot using a UNION in your final SELECT statement (outside the CTEs).
But yes, you're right, this is better done at your presentation layer (for example grouping in SSRS). As should your formatting (in relation to showing your figure as a currency not a decimal).
Its not possible at the Presentation Layer. As this is a file that is generated by a Batch file from SQL, output to a csv file and then auto emailed to clients so they can track how many VOIDS their cashiers are doing daily.
It is probably possible, but I am not that smart to work that out...so I need to do as much in SQL as I can...
October 24, 2017 at 12:00 pm
Ok, ill be better with my posts from here on out. Just takes a long time to add some small data for a little fix, IMO. Maybe I am not doing it right....
Can you help with that link that takes the data and formats it?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply