Grouping with condition and like with row over id in the sql

1

I can't get results to group the id together after I put in the case statement
,case when fs.ITEMID like '%ME' then left(fs.[ITEMID],4) -- case 1
ELSE fs.ITEMID
End as 'SKU'

sqlpic
sqlpic1632×379 122 KB

SELECT
ROW_NUMBER() over(order by fs.[ITEMID] asc) as 'Row_ID',
--fs.[ITEMID] as 'SKU',

Convert(varchar(10),CONVERT(date,fs.[STARTDATE],101),101) as 'Date'

		,case when fs.ITEMID like '%ME' then left(fs.[ITEMID],4) -- case 1
		ELSE fs.ITEMID
		End as 'SKU'

		
     ,CASE fs.[FORECASTSALESGROUPIDASL]--case2
        WHEN 'CANA' THEN 'US' ELSE fs.[FORECASTSALESGROUPIDASL]
      END AS 'FSG'


     , fs.[FORECASTCUSTGROUPIDASL] as 'FCG'
     ,SUM(fs.[SALESQTY]) AS 'QTY'

FROM [PCIS_AX2009_BEN].[dbo].[FORECASTSALES] fs
JOIN [PCIS_AX2009_BEN].[dbo].[INVENTTABLE] it
ON fs.[ITEMID] = it.[ITEMID] and fs.[DATAAREAID] = it.[DATAAREAID]
WHERE fs.[DATAAREAID] = 'BCUS'
AND fs.[MODELID] IN ('DH_MANUAL','DH_BASE')
-- AND fs.[STARTDATE] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())- 1,0)
AND fs.[STARTDATE] >= '2017-01-01'
AND fs.[ITEMGROUPID] IN ('100','101','103','202', '203')
AND fs.[FORECASTITEMGROUPIDASL] NOT IN ('CL1T','None','Discon','')
AND it.[PCISCMITEMSTATUSCLASSIFID] NOT IN ('Discontinued','')
AND fs.[FORECASTSALESGROUPIDASL] Not In('7PA','7PC','P_MEXI','P_MIDE','7PT','QVC','7PH','7PK','P_RUS','P_TRI','SDCX','P_URB','URB')

GROUP BY fs.[ITEMID]
,fs.[STARTDATE]
,CASE fs.[FORECASTSALESGROUPIDASL]
WHEN 'CANA' THEN 'US' ELSE fs.[FORECASTSALESGROUPIDASL]
END
,fs.[FORECASTCUSTGROUPIDASL]

2

Welcome!

What is difference between am01 and am01me products?

3

Is this what you are looking for?

   With currentResults
     As (
 Select ForeCastDate = cast(fs.[STARTDATE] As date)
      , SKU = Case When fs.ITEMID Like '%ME' 
                   Then left(fs.[ITEMID], 4) -- case 1
                   Else fs.ITEMID
               End 
      , FSG = Case fs.[FORECASTSALESGROUPIDASL]--case2
                   When 'CANA' Then 'US'
                   Else fs.[FORECASTSALESGROUPIDASL]
               End
      , FCG = fs.[FORECASTCUSTGROUPIDASL]
      , fs.[SALESQTY]
   From [PCIS_AX2009_BEN].[dbo].[FORECASTSALES]         fs
   Join [PCIS_AX2009_BEN].[dbo].[INVENTTABLE]           it On fs.[ITEMID] = it.[ITEMID]
                                                          And fs.[DATAAREAID] = it.[DATAAREAID]
  Where fs.[DATAAREAID] = 'BCUS'
    And fs.[MODELID] In ('DH_MANUAL', 'DH_BASE')
        -- AND fs.[STARTDATE] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())- 1,0)
    And fs.[STARTDATE] >= '2017-01-01'
    And fs.[ITEMGROUPID] In ('100', '101', '103', '202', '203')
    And fs.[FORECASTITEMGROUPIDASL] Not In ('CL1T', 'None', 'Discon', '')
    And it.[PCISCMITEMSTATUSCLASSIFID] Not In ('Discontinued', '')
    And fs.[FORECASTSALESGROUPIDASL] Not In ('7PA', '7PC', 'P_MEXI', 'P_MIDE', '7PT', 'QVC'
                                           , '7PH', '7PK', 'P_RUS', 'P_TRI', 'SDCX', 'P_URB', 'URB')
        )
 Select row_id = row_number() over(Order By @@spid)
      , ForeCastDate
      , SKU
      , FSG
      , FCG
      , QTY = sum(SALESQTY)
   From currentResults
  Group By
        ForeCastDate
      , SKU
      , FSG
      , FCG;
1 Like
4

they basically said they dont' want to see me in the sku and so just have the digits without ME so i cut it off with the left function. however the results still need to be in the data. so if the sku is mn33me, the sku should say mn33 and it will be aggregated with the rest of the mn33 skus.

5

so what is the cut off point where you trim things off on the SKU?
MN333
MN33SE
MN3
MN3SE

What is the rule/pattern?

1 Like
6

the rule is to keep first 4 digits if it ends in ME. I think my like%me formula actually didnt' work either now that I am looking at the data results something didn't sum correctly.

the skus will never be over 6 digits: it can be am22 am22me, am33, am33me, am55di
however they specifically don't want to see skus that end with me.
means am22me should say am22
am33me should say am33
am55di leave it alone and keep it am55di because it doesn't end in me

only anything that ends in me, keep all digits before ME. actually i think that's why it isn't working i noticed now that some are 5 digits before the ME and some are 4.

7

yosiasz- you caught my error. there is no rule for the number of digits in front of ME. basically keep everything before ME.

8

omg i think i got it to work! thank you that was helpful!

1 Like
9

hi

hope this helps :slight_smile:

A different way of doing it ...

Please click arrow to the left for Sample Data

Sample Data

drop table #sampledata
go

create table #sampledata
(
row_id int ,
sku varchar(50),
date1 date,
fsg varchar(10),
FCH varchar(10),
Qty int
)
go

insert into #sampledata select 310,'AM01','2081-09-30','MIDE','DRP_MED',20
insert into #sampledata select 618,'AM01ME','2081-09-30','MIDE','DRP_MED',2
go

select sku ,date1,fsg ,FCH, sum(Qty)
from 
(select left(sku,4) as sku ,date1,fsg ,FCH, Qty from #sampledata 
where sku like '%ME%'
union all 
select sku,date1,fsg, FCH ,Qty from #sampledata 
where sku not like '%ME%'
) a 
group by sku ,date1,fsg ,FCH

image

10

Hi. Thanks for showing me more simple ways as my sql is not very advanced right now. I don't really understand how to query with the query you just posted though.

-Also i have now changed the query to this which works except I don't know how to read the @@pcis or the withcurrent results.

With currentResults
As (
Select Date = cast(fs.[STARTDATE] As date)
, SKU = Case When fs.ITEMID Like '%ME'
Then replace(fs.[ITEMID],'ME','') -- case 1
Else fs.ITEMID
End
, FSG = Case fs.[FORECASTSALESGROUPIDASL]--case2
When 'CANA' Then 'US'
Else fs.[FORECASTSALESGROUPIDASL]
End
, FCG = fs.[FORECASTCUSTGROUPIDASL]
, fs.[SALESQTY]
From [abcd].[dbo].[FORECASTSALES] fs
Join [abcd].[dbo].[INVENTTABLE] it On fs.[ITEMID] = it.[ITEMID]
And fs.[DATAAREAID] = it.[DATAAREAID]
Where fs.[DATAAREAID] = 'BCUS'
And fs.[MODELID] In ('DH_MANUAL', 'DH_BASE')
-- AND fs.[STARTDATE] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())- 1,0)
And fs.[STARTDATE] >= '2017-01-01'
And fs.[ITEMGROUPID] In ('100', '101', '103', '202', '203')
And fs.[FORECASTITEMGROUPIDASL] Not In ('CL1T', 'None', 'Discon', '')
And it.[PCISCMITEMSTATUSCLASSIFID] Not In ('Discontinued', '')
And fs.[FORECASTSALESGROUPIDASL] Not In ('7PA', '7PC', 'P_MEXI', 'P_MIDE', '7PT', 'QVC'
, '7PH', '7PK', 'P_RUS', 'P_TRI', 'SDCX', 'P_URB', 'URB')
)
Select Row_Id = row_number() over(Order By @@spid)

  ,SKU
  ,Date
  , FSG
  , FCG
  , QTY = sum(SALESQTY)

From currentResults
Group By
SKU
, Date
, FSG
, FCG;

11

Hi Jeff,
I was able to change this and it does work. Thank you!
My sql is not that advanced. Are you able to tell me what the withcurrentresults and from current results does? I also googled spid which says @@SPID Returns the session ID of the current user process si I see that it's counting the rows.

With currentResults
As (
Select Date = cast(fs.[STARTDATE] As date)
, SKU = Case When fs.ITEMID Like '%ME'
Then replace(fs.[ITEMID],'ME','') -- case 1
Else fs.ITEMID
End
, FSG = Case fs.[FORECASTSALESGROUPIDASL]--case2
When 'CANA' Then 'US'
Else fs.[FORECASTSALESGROUPIDASL]
End
, FCG = fs.[FORECASTCUSTGROUPIDASL]
, fs.[SALESQTY]
From [PCIS_AX2009_BEN].[dbo].[FORECASTSALES] fs
Join [PCIS_AX2009_BEN].[dbo].[INVENTTABLE] it On fs.[ITEMID] = it.[ITEMID]
And fs.[DATAAREAID] = it.[DATAAREAID]
Where fs.[DATAAREAID] = 'BCUS'
And fs.[MODELID] In ('DH_MANUAL', 'DH_BASE')
-- AND fs.[STARTDATE] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())- 1,0)
And fs.[STARTDATE] >= '2017-01-01'
And fs.[ITEMGROUPID] In ('100', '101', '103', '202', '203')
And fs.[FORECASTITEMGROUPIDASL] Not In ('CL1T', 'None', 'Discon', '')
And it.[PCISCMITEMSTATUSCLASSIFID] Not In ('Discontinued', '')
And fs.[FORECASTSALESGROUPIDASL] Not In ('7PA', '7PC', 'P_MEXI', 'P_MIDE', '7PT', 'QVC'
, '7PH', '7PK', 'P_RUS', 'P_TRI', 'SDCX', 'P_URB', 'URB')
)
Select Row_Id = row_number() over(Order By @@spid)

  ,SKU
  ,Date
  , FSG
  , FCG
  , QTY = sum(SALESQTY)

From currentResults
Group By
SKU
, Date
, FSG
, FCG;

With currentResults
As (
Select Date = cast(fs.[STARTDATE] As date)
, SKU = Case When fs.ITEMID Like '%ME'
Then replace(fs.[ITEMID],'ME','') -- case 1
Else fs.ITEMID
End
, FSG = Case fs.[FORECASTSALESGROUPIDASL]--case2
When 'CANA' Then 'US'
Else fs.[FORECASTSALESGROUPIDASL]
End
, FCG = fs.[FORECASTCUSTGROUPIDASL]
, fs.[SALESQTY]
From [PCIS_AX2009_BEN].[dbo].[FORECASTSALES] fs
Join [PCIS_AX2009_BEN].[dbo].[INVENTTABLE] it On fs.[ITEMID] = it.[ITEMID]
And fs.[DATAAREAID] = it.[DATAAREAID]
Where fs.[DATAAREAID] = 'BCUS'
And fs.[MODELID] In ('DH_MANUAL', 'DH_BASE')
-- AND fs.[STARTDATE] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())- 1,0)
And fs.[STARTDATE] >= '2017-01-01'
And fs.[ITEMGROUPID] In ('100', '101', '103', '202', '203')
And fs.[FORECASTITEMGROUPIDASL] Not In ('CL1T', 'None', 'Discon', '')
And it.[PCISCMITEMSTATUSCLASSIFID] Not In ('Discontinued', '')
And fs.[FORECASTSALESGROUPIDASL] Not In ('7PA', '7PC', 'P_MEXI', 'P_MIDE', '7PT', 'QVC'
, '7PH', '7PK', 'P_RUS', 'P_TRI', 'SDCX', 'P_URB', 'URB')
)
Select Row_Id = row_number() over(Order By @@spid)

  ,SKU
  ,Date
  , FSG
  , FCG
  , QTY = sum(SALESQTY)

From currentResults
Group By
SKU
, Date
, FSG
, FCG;

12

The code builds a common-table expression - and then we can use that expression in the later part of the query.

With currentResults
As (
)

This declares and defines the common-table expression - which you can think of as a dynamic view. We can then use that expression in the later query.

This allows us to define the calculated columns and then use those columns in the GROUP BY statement instead of having to repeat the calculations.

The @@spid is used here only because we must have an order by for the row_number. We don't actually care about that order though since we want a row number over the whole set. By using @@spid which is a fixed value for that process - we satisfy the requirement.

If we want the row number to correspond with increasing SKUs then you could use that column in the row_number function.

13

Hi Jeff,

Do you know how to write this same query without the common table expression and without naming it with sku= etc. For some reason, I am trying to automate this information into anaplan with postman for API and Anaplan doesn't like the common table expressions.

With currentResults
As (
Select Date = cast(fs.[STARTDATE] As date)
, SKU = Case When fs.ITEMID Like '%ME'
Then replace(fs.[ITEMID],'ME','') -- case 1
Else fs.ITEMID
End
, FSG = Case fs.[FORECASTSALESGROUPIDASL]--case2
When 'CANA' Then 'US'
Else fs.[FORECASTSALESGROUPIDASL]
End
, FCG = fs.[FORECASTCUSTGROUPIDASL]
, fs.[SALESQTY]
From [abcd].[dbo].[FORECASTSALES] fs
Join [abcd].[dbo].[INVENTTABLE] it On fs.[ITEMID] = it.[ITEMID]
And fs.[DATAAREAID] = it.[DATAAREAID]
Where fs.[DATAAREAID] = 'BCUS'
And fs.[MODELID] In ('DH_MANUAL', 'DH_BASE')
-- AND fs.[STARTDATE] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())- 1,0)
And fs.[STARTDATE] >= '2017-01-01'
And fs.[ITEMGROUPID] In ('100', '101', '103', '202', '203')
And fs.[FORECASTITEMGROUPIDASL] Not In ('CL1T', 'None', 'Discon', '')
And it.[PCISCMITEMSTATUSCLASSIFID] Not In ('Discontinued', '')
And fs.[FORECASTSALESGROUPIDASL] Not In ('7PA', '7PC', 'P_MEXI', 'P_MIDE', '7PT', 'QVC'
, '7PH', '7PK', 'P_RUS', 'P_TRI', 'SDCX', 'P_URB', 'URB')
)
Select Row_ID = row_number() over(order by SKU)

  ,SKU
  ,Date
  , FSG
  , FCG
  , QTY = sum(SALESQTY)

From currentResults
Group By
SKU
, Date
, FSG
, FCG;

14

I know nothing about Anaplan...sorry. You could move the CTE into a derived table instead but since I don't have any idea what Anaplan is or how it works I have no idea if it will work.

If you are sending the query to SQL Server then it shouldn't be an issue...it sounds like your application isn't actually using SQL Server though...

15

Hi Jeff,
Turns out that the CTE was fine! The developer who codes for it to go through the Postman API just had to add more rows and we had to change the date format and the query you gave me to was good :smiley:.

Thank you for your help!

16

That is good news - glad it worked out for you.