Link to home
Start Free TrialLog in
Avatar of gracie1972
gracie1972Flag for United States of America

asked on

Convert formula to max at a specific date by month

In my database I have a [Sell out week ending date] and [week].  Both dates are Saturday dates.  I need to modify my query to only pull the most recent date.

For example, my data has dates:

Date                                                    Inventory ON Hand
2017-01-07 00:00:00.000                 101
2017-01-14 00:00:00.000                  99
2017-01-21 00:00:00.000                  60
2017-01-28 00:00:00.000                  200
2017-02-04 00:00:00.000                  125

I am building a table that will pull in the max date by month for the [Sell out week ending date] and the total inventory on hand at that time.

If I were to run this next Monday I would only see:
2017-01-28 00:00:00.000      200
2017-02-04 00:00:00.000      125

I also want to convert to the date show:
Date            Qty
01-2017      200
02-2017      125

Nothing I am doing is working, please help.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Gracie,
Just created full and tested solution for you.

--

CREATE TABLE MaxMonth
(
	 [Date] DATETIME                                                   
	,[Inventory ON Hand] INT
)
GO

INSERT INTO MaxMonth VALUES
('2017-01-07 00:00:00.000',                 101  ),
('2017-01-14 00:00:00.000',                  99  ),
('2017-01-21 00:00:00.000',                  60  ),
('2017-01-28 00:00:00.000',                  200 ),
('2017-02-04 00:00:00.000',                  125 )
GO

SELECT [Inventory ON Hand],MONTH([Date]) [Date] FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([DATE]) ORDER BY [Date] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1


SELECT [Inventory ON Hand],([Date]) FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([DATE]) ORDER BY [Date] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1

--

Open in new window


OUTPUT - 1

/*------------------------

SELECT [Inventory ON Hand],MONTH([Date]) [Date] FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([DATE]) ORDER BY [Date] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1
------------------------*/
Inventory ON Hand Date
----------------- -----------
200               1
125               2

(2 row(s) affected)

Open in new window


OUTPUT - 2

/*------------------------
SELECT [Inventory ON Hand],([Date]) FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([DATE]) ORDER BY [Date] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1
------------------------*/
Inventory ON Hand Date
----------------- -----------------------
200               2017-01-28 00:00:00.000
125               2017-02-04 00:00:00.000

(2 row(s) affected)

Open in new window


Hope it helps!
Avatar of gracie1972

ASKER

These are not necessarily max values, there are about 282,000 values in the data. I was the max date, total amount by that data so I can build current point in time totals at the end of each month by customer, does this make sense?
Hi,
can you please explain with an example?
Here is an example output from my data:
Sell Out Week Ending	        ARTICLE	Color	Customer_Name	     Sell_Out_Week_Ending_Onhand	 
2017-01-21 00:00:00.000	01511	Black 	X	                            18172	 
2017-01-28 00:00:00.000	01511	Black 	X	                            18246	 
2017-01-14 00:00:00.000	01511	Black 	X	                             18335	 
2017-02-04 00:00:00.000	01511	Black 	X	                             18346	 

Open in new window


I only want to see:
Sell Out Week Ending              ARTICLE       Color      Customer_Name           Sell_Out_Week_Ending_Onhand       
2017-01-28 00:00:00.000      01123       Black       ABC                                   18246       
2017-02-04 00:00:00.000      01123       Black       ABC                                   18346

(Converted to mm-yyyy if possible)
Sell Out Week Ending              ARTICLE       Color      Customer_Name           Sell_Out_Week_Ending_Onhand       
01-2017                                      01123       Black       ABC                                   18246       
02-2017                                      01123       Black       ABC                                   18346


Does this help?
Hi,
Please try this -

CREATE TABLE MaxMonth
(
	 [Sell Out Week Ending] DATETIME                                                   
	,[Inventory ON Hand] INT
	,ARTICLE VARCHAR(10)      
	,color VARCHAR(10)
	,Customer_Name VARCHAR(1000)
	,Sell_Out_Week_Ending_Onhand BIGINT
)
GO

INSERT INTO MaxMonth VALUES
('2017-01-07 00:00:00.000',                 101   ,'01123', 'Black',       'ABC',                                   18246 ),
('2017-01-14 00:00:00.000',                  99   ,'01123','Black',       'ABC',                                   18246 ),
('2017-01-21 00:00:00.000',                  60   ,'01123','Black',       'ABC',                                   18246 ),
('2017-01-28 00:00:00.000',                  200  ,'01123','Black',       'ABC',                                   18246 ),
('2017-02-04 00:00:00.000',                  125  ,'01123','Black',       'ABC',                                   18246 )
GO

SELECT CONCAT(RIGHT(CONCAT('0',MONTH([Sell Out Week Ending])),2),'-',YEAR([Sell Out Week Ending])) [Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1

Open in new window


Current data

/*------------------------
select * from MaxMonth
------------------------*/
Sell Out Week Ending    Inventory ON Hand ARTICLE    color      Customer_Name                                                                                                                                                                                                                                                    Sell_Out_Week_Ending_Onhand
----------------------- ----------------- ---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
2017-01-07 00:00:00.000 101               01123      Black      ABC                                                                                                                                                                                                                                                              18246
2017-01-14 00:00:00.000 99                01123      Black      ABC                                                                                                                                                                                                                                                              18246
2017-01-21 00:00:00.000 60                01123      Black      ABC                                                                                                                                                                                                                                                              18246
2017-01-28 00:00:00.000 200               01123      Black      ABC                                                                                                                                                                                                                                                              18246
2017-02-04 00:00:00.000 125               01123      Black      ABC                                                                                                                                                                                                                                                              18246

(5 row(s) affected)

Open in new window


Output

/*------------------------

SELECT CONCAT(RIGHT(CONCAT('0',MONTH([Sell Out Week Ending])),2),'-',YEAR([Sell Out Week Ending])) [Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1

------------------------*/
Sell Out Week Ending Inventory ON Hand ARTICLE    color      Customer_Name                                                                                                                                                                                                                                                    Sell_Out_Week_Ending_Onhand
-------------------- ----------------- ---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
01-2017              200               01123      Black      ABC                                                                                                                                                                                                                                                              18246
02-2017              125               01123      Black      ABC                                                                                                                                                                                                                                                              18246

(2 row(s) affected)

Open in new window


Hope it helps!
I get this in SQL 2014

Msg 195, Level 15, State 10, Line 1
'CONCAT' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'k'.
Do I have to turn anything on for the CONCAT to work?
Hi,
It should work if you are using SQL 2012+, no problem , please use below -

SELECT 
	RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
	+ '-'
	+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) 
	[Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1

Open in new window


Output

/*------------------------
SELECT 
	RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
	+ '-'
	+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) 
	[Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1
------------------------*/
Sell Out Week Ending Inventory ON Hand ARTICLE    color      Customer_Name                                                                                                                                                                                                                                                    Sell_Out_Week_Ending_Onhand
-------------------- ----------------- ---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
01-2017              200               01123      Black      ABC                                                                                                                                                                                                                                                              18246
02-2017              125               01123      Black      ABC                                                                                                                                                                                                                                                              18246

(2 row(s) affected)

Open in new window


Hope it helps!
I do not have create permissions in SQL, how can I get around that?
You just have to run the below query , no creation required.

Replace yourtablename with my tablename and columns from yourtable.

SELECT 
	RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
	+ '-'
	+ CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) 
	[Sell Out Week Ending]
,[Inventory ON Hand] ,ARTICLE ,color , Customer_Name,Sell_Out_Week_Ending_Onhand FROM 
(
	SELECT *,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
	FROM MaxMonth
)k WHERE rnk = 1

Open in new window

Okay, the month and year worked but nothing else.  

Here is my query as a start over from scratch.

--------------------------------------------------------------------------------------------------------------
SELECT
      RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
      + '-'
      + CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
      ,MAX([WEEK])
      ,ARTICLE
      ,[SAH_Customer_Name]
      ,Sell_Out_Week_Ending_Onhand

      FROM [dbo].[VW_Tableau_Sell_Out]

WHERE
[Sell Out Week Ending] > '12/31/2016'
AND [SAH_Customer_Name] = 'ABC'  >>>FOR TESTING ONLY TO GET A SMALL POPULATION
AND [ARTICLE] = '0151123' >>>FOR TESTING ONLY TO GET A SMALL POPULATION

GROUP BY
[Sell Out Week Ending]
,[ARTICLE]
,[NRF_Color]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onhand]
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RESULT:
Sell Out Week Ending      (No column name)      ARTICLE               SAH_Customer_Name      Sell_Out_Week_Ending_Onhand
01-2017      2017-01-21 00:00:00.000                        0151123         ABC                                      18,172
01-2017      2017-01-28 00:00:00.000                        0151123         ABC                                      18,246
01-2017      2017-01-14 00:00:00.000                        0151123         ABC                                      18,335
01-2017      2017-01-07 00:00:00.000                        0151123         ABC                                      18,346

OPTIMAL RESULT:
Sell Out Week Ending           [WEEK]                                        ARTICLE         SAH_Customer_Name      Sell_Out_Week_Ending_Onhand
01-2017                                   2017-01-28 00:00:00.000        015110                  ABC                                      18,246
Please try this-

SELECT 
 [Sell Out Week Ending]
,[WEEK]
,[ARTICLE]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onhand]
FROM 
(
	SELECT 
		  RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
		  + '-'
		  + CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
		  ,([Sell Out Week Ending]) [WEEK]
		  ,ARTICLE 		  
		  ,[SAH_Customer_Name]
		  ,Sell_Out_Week_Ending_Onhand 
		  ,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
		  FROM [dbo].[VW_Tableau_Sell_Out] 
	WHERE 
	[Sell Out Week Ending] > '12/31/2016'
	AND [SAH_Customer_Name] = 'ABC' 
	AND [ARTICLE] = '0151123' 
)m WHERE rnk = 1

 

Open in new window

That did not work.
If I use the WHERE rnk = 1, then I only get 1 result.  
There are over 476K records so far.

I only need to return the records for the MAX ([Sell Out Week Ending]) by Month.
Every Month will have a max data returned.  Nothing is working.....
I have also used Rnk = 1 in all my samples and we are getting multiple rows. Each row per month.  How you are saying you will get only 1 row.?
Please execute and the query and provide me the results?
I got it - Your where clause is the problem, you are taking data > 12/31/2016 so effectively you will have only 1 month that is Jan 2017.

[Sell Out Week Ending] > '12/31/2016'
AND [SAH_Customer_Name] = 'ABC'
AND [ARTICLE] = '0151123'


Removed above and please try again.

SELECT 
 [Sell Out Week Ending]
,[WEEK]
,[ARTICLE]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onhand]
FROM 
(
	SELECT 
		  RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
		  + '-'
		  + CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
		  ,([Sell Out Week Ending]) [WEEK]
		  ,ARTICLE 		  
		  ,[SAH_Customer_Name]
		  ,Sell_Out_Week_Ending_Onhand 
		  ,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
		  FROM [dbo].[VW_Tableau_Sell_Out] 	
)m WHERE rnk = 1

Open in new window

Hi,
Just created full proof of concept for you- My Solution is working perfectly fine.

--

CREATE TABLE [dbo].[VW_Tableau_Sell_Out] 
(
	 [Sell Out Week Ending] DATETIME  
	,ARTICLE VARCHAR(10)      
	,[SAH_Customer_Name] VARCHAR(100)		
	,Sell_Out_Week_Ending_Onhand BIGINT
)
GO

INSERT INTO [dbo].[VW_Tableau_Sell_Out]  VALUES
('2017-01-07 00:00:00.000',   '01123', 'ABC',    18246 ),
('2017-01-14 00:00:00.000',   '01123', 'ABC',    18246 ),
('2017-01-21 00:00:00.000',   '01123', 'ABC',    18246 ),
('2017-01-28 00:00:00.000',   '01123', 'ABC',    18246 ),
('2017-02-04 00:00:00.000',   '01123', 'ABC',    18246 ),
('2017-03-03 00:00:00.000',   '0151123', 'ABC',  18246 ),
('2017-03-03 00:00:00.000',   '0151123', 'ABC',  18246 ),
('2017-03-03 00:00:00.000',   '0151123', 'ABC',  18246 ),
('2017-03-03 00:00:00.000',   '0151123', 'ABC',  18246 ),
('2017-03-03 00:00:00.000',   '0151123', 'ABC',  18246 ),
('2017-04-04 00:00:00.000',   '01511',   'ABC',  18246 ),
('2017-05-04 00:00:00.000',   '01511',   'ABC',  18246 )
GO
--

Open in new window


My Solution

--

SELECT 
 [Sell Out Week Ending]
,[WEEK]
,[ARTICLE]
,[SAH_Customer_Name]
,[Sell_Out_Week_Ending_Onhand]
FROM 
(
	SELECT 
		  RIGHT('0'+ CAST(MONTH([Sell Out Week Ending]) AS VARCHAR(2)),2)
		  + '-'
		  + CAST(YEAR([Sell Out Week Ending]) AS VARCHAR(4)) [Sell Out Week Ending]
		  ,([Sell Out Week Ending]) [WEEK]
		  ,ARTICLE 		  
		  ,[SAH_Customer_Name]
		  ,Sell_Out_Week_Ending_Onhand 
		  ,ROW_NUMBER() OVER(PARTITION BY MONTH([Sell Out Week Ending]) ORDER BY [Sell Out Week Ending] DESC) rnk
		  FROM [dbo].[VW_Tableau_Sell_Out] 	 
)m WHERE rnk = 1

--

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
Sell Out Week Ending WEEK                    ARTICLE    SAH_Customer_Name                                                                                    Sell_Out_Week_Ending_Onhand
-------------------- ----------------------- ---------- ---------------------------------------------------------------------------------------------------- ---------------------------
01-2017              2017-01-28 00:00:00.000 01123      ABC                                                                                                  18246
02-2017              2017-02-04 00:00:00.000 01123      ABC                                                                                                  18246
03-2017              2017-03-03 00:00:00.000 0151123    ABC                                                                                                  18246
04-2017              2017-04-04 00:00:00.000 01511      ABC                                                                                                  18246
05-2017              2017-05-04 00:00:00.000 01511      ABC                                                                                                  18246

(5 row(s) affected)

Open in new window


NOTE - I am getting data all 5 months i have in my table

01-2017
02-2017
03-2017
04-2017
05-2017


Hope it helps!
The concept seems to work but see below, however, I am excluding hundreds of thousands of data for each customer.
Sell Out Week Ending	WEEK	ARTICLE	SAH_Customer_Name	Sell_Out_Week_Ending_Onhand
Nov-16	11/26/2016	B12345	ABC	0
Oct-16	10/29/2016	B12346	CDF	92
Jul-16	7/30/2016	B12347	EFG	2
Feb-16	2/27/2016	B12348	HIJ	1
Jun-16	6/25/2016	B12349	LOO	5
Mar-16	3/26/2016	B12350	MKJ	1
Apr-16	4/30/2016	B12351	AMA 	1
Dec-16	12/31/2016	B12352	BST 	-3
May-16	5/28/2016	B12353	FOO	1
Sep-16	9/24/2016	B12354	AMK	12
Jan-17	1/28/2017	B12355	TAF	1
Aug-16	8/27/2016	B12356	AMP	8

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial