Code for 'week to date' DATETIME() mine is displaying previous weeks

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 17, 2017 at 3:14 pm

  #402604

  (Invoice_Exceptions.DateTime >= DATEADD(dd, DATEDIFF(dd,'17530101', GETDATE()) / 7 * 7 - 7, '17530101')) AND (Invoice_Exceptions.DateTime <= DATEADD(dd, DATEDIFF(dd,'17530101', GETDATE()) / 7 * 7, '17530101'))

  This code is displaying the last weeks from Monday (the 9th) to this Sunday (the 15th)

  I have tried all variations but I need it to display two different ways:
  previous 7 days (from yesterday)
  and the month to date (from 1st to previous day)

  Thanks for any nudge.

  I would assume dd has something to do with it as every variation of the 7 * 7 - 7 code doesnt do much...

  Best,
  Chris

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 17, 2017 at 4:13 pm

  #1964109

  chef423 - Tuesday, October 17, 2017 3:14 PM

  (Invoice_Exceptions.DateTime >= DATEADD(dd, DATEDIFF(dd,'17530101', GETDATE()) / 7 * 7 - 7, '17530101')) AND (Invoice_Exceptions.DateTime <= DATEADD(dd, DATEDIFF(dd,'17530101', GETDATE()) / 7 * 7, '17530101'))

  This code is displaying the last weeks from Monday (the 9th) to this Sunday (the 15th)

  I have tried all variations but I need it to display two different ways:
  previous 7 days (from yesterday)
  and the month to date (from 1st to previous day)

  Thanks for any nudge.

  I would assume dd has something to do with it as every variation of the 7 * 7 - 7 code doesnt do much...

  Best,
  Chris

  Does this help?

  
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);

  SELECT @Yesterday, @PeriodStart, @PeriodEnd, @MonthStart;

  -- for previous 7 days:
  WHERE Invoice_Exceptions.DateTime >= @PeriodStart AND Invoice_Exceptions.DateTime < @PeriodEnd

  -- for month to date:
  WHERE Invoice_Exceptions.DateTime >= @MonthStart AND Invoice_Exceptions.DateTime < @PeriodEnd

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 17, 2017 at 4:15 pm

  #1964111

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 17, 2017 at 4:31 pm

  #1964115

  Lynn Pettis - Tuesday, October 17, 2017 4:15 PM

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

  How to inject that into here.....Ill play around. Thanks Lynn....I'm learning with your assistance 🙂

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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]

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 17, 2017 at 4:33 pm

  #1964116

  Lynn Pettis - Tuesday, October 17, 2017 4:15 PM

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >=

  So far, this works...not sure about the SELECT part tho...

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 17, 2017 at 4:36 pm

  #1964118

  chef423 - Tuesday, October 17, 2017 4:33 PM

  Lynn Pettis - Tuesday, October 17, 2017 4:15 PM

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >=

  So far, this works...not sure about the SELECT part tho...

  I don't see any WHERE clause.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 17, 2017 at 4:45 pm

  #1964119

  Lynn Pettis - Tuesday, October 17, 2017 4:36 PM

  chef423 - Tuesday, October 17, 2017 4:33 PM

  Lynn Pettis - Tuesday, October 17, 2017 4:15 PM

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >=

  So far, this works...not sure about the SELECT part tho...

  I don't see any WHERE clause.

  I didn't know you cud use a WHERE clause on a two table JOIN ?

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  October 17, 2017 at 5:06 pm

  #1964123

  chef423 - Tuesday, October 17, 2017 4:45 PM

  Lynn Pettis - Tuesday, October 17, 2017 4:36 PM

  chef423 - Tuesday, October 17, 2017 4:33 PM

  Lynn Pettis - Tuesday, October 17, 2017 4:15 PM

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >=

  So far, this works...not sure about the SELECT part tho...

  I don't see any WHERE clause.

  I didn't know you cud use a WHERE clause on a two table JOIN ?

  Have a look at the following for the UPDATE statement.
  https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 17, 2017 at 10:07 pm

  #1964141

  Lynn Pettis - Tuesday, October 17, 2017 4:36 PM

  chef423 - Tuesday, October 17, 2017 4:33 PM

  Lynn Pettis - Tuesday, October 17, 2017 4:15 PM

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >=

  So far, this works...not sure about the SELECT part tho...

  I don't see any WHERE clause.

  I put this together, works like a charm. Thank you, again.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >= @MonthStart AND [ie].[DateTime] < @PeriodEnd
     
ORDER BY
[ie].[DateTime] ASC

  Now I need to figure out how to add a Row, at the bottom that gives me the SUM('$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY))) 
  I have seen the WITH ROLLUP, but I dont know how to Display the word TOTAL at the bottom just before the Amount column.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  October 17, 2017 at 10:14 pm

  #1964143

  SELECT '$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY)),
   Name = CASE WHEN GROUPING([ie].[Amount]) = 0 THEN [ie].[Amount] ELSE 'Total' END,
   Value = SUM([ie].[Amount])
 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] >= @MonthStart AND [ie].[DateTime] < @PeriodEnd
GROUP BY [ie].[Amount] WITH ROLLUP
HAVING GROUPING([ie].[Amount]) = 1
;

  This almost works....The data displays for a second then then i get the "Msg 8114, Level 16, State 5, Line 31
  Error converting data type varchar to numeric." (line 31 in bold)

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  October 18, 2017 at 5:55 am

  #1964190

  chef423 - Tuesday, October 17, 2017 10:14 PM

  SELECT '$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY)),
   Name = CASE WHEN GROUPING([ie].[Amount]) = 0 THEN [ie].[Amount] ELSE 'Total' END,
   Value = SUM([ie].[Amount])
 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] >= @MonthStart AND [ie].[DateTime] < @PeriodEnd
GROUP BY [ie].[Amount] WITH ROLLUP
HAVING GROUPING([ie].[Amount]) = 1
;

  This almost works....The data displays for a second then then i get the "Msg 8114, Level 16, State 5, Line 31
  Error converting data type varchar to numeric." (line 31 in bold)

  That appears to be an error in the data itself.  You need to find the data that cannot be converted.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• DesNorton

  SSC-Insane

  Points: 24382

  More actions

  October 18, 2017 at 7:48 am

  #1964208

  I believe that the issue is in this line.  Your case statement contains both numeric and text outputs.
  Name = CASE WHEN GROUPING([ie].[Amount]) = 0 THEN [ie].[Amount] ELSE 'Total' END,

  You can try
  Name = CASE WHEN GROUPING([ie].[Amount]) = 0 THEN CAST([ie].[Amount] AS VARCHAR(15)) ELSE 'Total' END,

  ---

  How to post data/code on a forum to get the best help.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 18, 2017 at 9:24 am

  #1964238

  chef423 - Tuesday, October 17, 2017 10:07 PM

  Lynn Pettis - Tuesday, October 17, 2017 4:36 PM

  chef423 - Tuesday, October 17, 2017 4:33 PM

  Lynn Pettis - Tuesday, October 17, 2017 4:15 PM

  Note, the above will return nothing for month to date on the first.  If you need that there is an adjustment to make to the code.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >=

  So far, this works...not sure about the SELECT part tho...

  I don't see any WHERE clause.

  I put this together, works like a charm. Thank you, again.

  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]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
--, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
--, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY))) AS [Total$_Voided]
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] >= @MonthStart AND [ie].[DateTime] < @PeriodEnd
     
ORDER BY
[ie].[DateTime] ASC

  Now I need to figure out how to add a Row, at the bottom that gives me the SUM('$' + CONVERT(VARCHAR(10), CAST([ie].[Amount] AS MONEY))) 
  I have seen the WITH ROLLUP, but I dont know how to Display the word TOTAL at the bottom just before the Amount column.

  You really need to learn how to write T-SQL code.  A WHERE clause is where you filter data.  Yes, with the inner joins your code seems to work, but once you start writing more complex code using OUTER JOINs you may start finding issues.

  This is what I was looking for you to write:
  
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]
  --, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[LineNum],100),4))  AS [Line_Item]
  --, LTRIM(RIGHT(CONVERT(varchar(20), [ie].[EmpName],100),7))  AS [Employee]
  --, FORMAT([ie].[DateTime], 'hh:mm tt')        AS [Void_Time]
  --, '$' + CONVERT(VARCHAR(10), SUM(CAST([ie].[Amount] AS MONEY)))  AS [Total$_Voided]
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]
WHERE
   [ie].[DateTime] >= @MonthStart
   AND [ie].[DateTime] < @PeriodEnd
ORDER BY
  [ie].[DateTime] ASC;


  Now, can you explain what is happening in the DECLAREs you simply copied from my earlier post?  If you can't, don't use it.  You will have to support this code if it suddenly doesn't work.  Plus, what I posted was meant to SHOW you date manipulation so you could figure out what you needed to do to make your code work.

  Also, when run on the first day of the month this code will return NO DATA.  Is this what you want?  If not, how would you modify the code to return the previous month if that is what is actually needed.