December 9, 2016 at 4:42 am
Hi All
I am new to SqL and would appreciate any help with the following:
I have a written a query where it would return Customer numbers, how much they spent and a given date and which department they bought from.
What I need to do is somehow look at the customer numbers in one day and see if the customer numbers is repeated which tells me one customer bought items from different departments on that day. Now what I need is to see whether one of the departments is Mens and if this is the case then return the loyalty numbers and the departments the shopped in (as long as Mens is one of them) and the day they shopped.
My query below only returns all the duplicate customer numbers but I am only interested in customers who purchased multiple items from different departments but Mens has to be one of the departments they shopped in and then group them by day of shopping customer number and the departments and amount.
Select bk.CustomerNumber, [Sale] = SUM(bk.BasketPriceTOTAL), GSM.Dep_BM
--, CASE When count(bk.customerNumber) > 1 Then 'Duplicate' Else 'Unique' End As Shopped
From db.dbo.SALE_Basket bk
INNER JOIN db.dbo.SALE_Line sl ON sl.Basket_id = bk.Basket_id
INNER JOIN db.dbo.map_Manager GSM ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN db.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Where sl.IsTrade = 1 AND sl.ISVoid = 0 and cal.Day_Uid = 20161203 AND bk.CustomerNumber <> 'Null'
AND bk.IsTrade = 1 AND bk.IsVoid = 0
Group By GSM.AreaName_BM, bk.CustomerNumber
ORDER By bk.CustomerNumber ASC
I hope this is clear as to what I need to achieve and any help i smuch appreciate it
December 9, 2016 at 5:27 am
Hello and welcome to SSC. I think I understand what you're looking for, but we're going to need the DDL for the tables involved. Please see the link in my signature for how to post questions to get the best results.
December 9, 2016 at 5:42 am
Hi Thanks for your reply. I'm not sure how to do this as I am have just started to use SQL. Do I need to create table as I only have read access to where I get the data from, or do I create table within the code . Sorry of this sounds silly
December 9, 2016 at 5:57 am
As Ed said, I think I understand what you're after, but the DDL and sample data is the key here. Well done though on posted your code you have, and in IFCode tags 🙂
Firstly, I have a couple of questions, i've tidied your SQL a little (my perference but makes it easier for me to read) and commented on them.
Select bk.CustomerNumber,
SUM(bk.BasketPriceTOTAL) AS Sale,
GSM.Dep_BM --This isn't being grouped on, does your query actually run?
FROM db.dbo.SALE_Basket bk
INNER JOIN db.dbo.SALE_Line sl ON sl.Basket_id = bk.Basket_id
INNER JOIN db.dbo.map_Manager GSM ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN db.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
WHERE sl.IsTrade = 1
AND sl.ISVoid = 0
AND cal.Day_Uid = 20161203
AND bk.CustomerNumber <> 'Null' --Is this meant to omit where the customer number has a value of "null", or where it is NULL?
AND bk.CustomerNumber IS NOT NULL --If the answer to above is the latter, this is what you want, and you should remove the above line
AND bk.IsTrade = 1
AND bk.IsVoid = 0
GROUP By GSM.AreaName_BM, --This field isn't referenced in your SELECT. Should it be?
bk.CustomerNumber
ORDER By bk.CustomerNumber ASC
My main question at the moment is in regards to your grouping (however, I've queried your 'null' statement as well) as I'm pretty sure your query would fail as it stands. What is the difference here between Dep_BM and AreaName_BM? Again, Sample data will give us that answer 😎
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 6:21 am
v.razaghzadeh (12/9/2016)
Hi AllI am new to SqL and would appreciate any help with the following:
I have a written a query where it would return Customer numbers, how much they spent and a given date and which department they bought from.
What I need to do is somehow look at the customer numbers in one day and see if the customer numbers is repeated which tells me one customer bought items from different departments on that day. Now what I need is to see whether one of the departments is Mens and if this is the case then return the loyalty numbers and the departments the shopped in (as long as Mens is one of them) and the day they shopped.
My query below only returns all the duplicate customer numbers but I am only interested in customers who purchased multiple items from different departments but Mens has to be one of the departments they shopped in and then group them by day of shopping customer number and the departments and amount.
Select bk.CustomerNumber, [Sale] = SUM(bk.BasketPriceTOTAL), GSM.Dep_BM
--, CASE When count(bk.customerNumber) > 1 Then 'Duplicate' Else 'Unique' End As Shopped
From db.dbo.SALE_Basket bk
INNER JOIN db.dbo.SALE_Line sl ON sl.Basket_id = bk.Basket_id
INNER JOIN db.dbo.map_Manager GSM ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN db.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Where sl.IsTrade = 1 AND sl.ISVoid = 0 and cal.Day_Uid = 20161203 AND bk.CustomerNumber <> 'Null'
AND bk.IsTrade = 1 AND bk.IsVoid = 0
Group By GSM.AreaName_BM, bk.CustomerNumber
ORDER By bk.CustomerNumber ASC
I hope this is clear as to what I need to achieve and any help i smuch appreciate it
Good job in posting your query, but there are a few pieces missing, so to speak. You indicate you are new to SQL, so I'm wondering what tool you are using to validate your SQL code? Most folks have access to SSMS (aka SQL Server Management Studio), which is generally installed on either the computer you were provided with, or on the server where SQL Server is installed. If for any reason you do NOT have access to this tool, you're going to have a difficult time. What some of the other posters have asked you for is DDL (aka Data Description Language), which is nothing more than what you can get by using SSMS to connect to the database server, and then navigating to the specific database and then to the relevant tables, and with a right-click on each table name, choosing the Script Table as option, followed by the CREATE To option, and finally, the New Query Editor Window option. This will make SSMS create the necessary SQL that could create the table if that were necessary, so it's not a script you're going to want to execute, but instead, copy and paste into a post here for each of the relevant tables in your query. That way, we can see what fields are available in each table, and together with you describing exactly what a single record in each of these tables represents, contributors here will have enough information to help you in the best way possible.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 9, 2016 at 6:30 am
I'd suggest that you read Thom's comments. However, this is a shot in the dark to show what you might need.
SELECT bk.CustomerNumber,
cal.Day_Uid,
[Sale] = SUM(bk.BasketPriceTOTAL)
From db.dbo.SALE_Basket bk
INNER JOIN db.dbo.SALE_Line sl ON sl.Basket_id = bk.Basket_id
INNER JOIN db.dbo.map_Manager GSM ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN db.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Where sl.IsTrade = 1
AND sl.ISVoid = 0
and cal.Day_Uid = 20161203
AND bk.CustomerNumber <> 'Null'
AND bk.IsTrade = 1
AND bk.IsVoid = 0
Group By cal.Day_Uid, bk.CustomerNumber
HAVING COUNT( CASE WHEN GSM.AreaName_BM = 'Mens' THEN 1 END)
AND (MAX(GSM.AreaName_BM) <> 'Mens'
OR MIN(GSM.AreaName_BM) <> 'Mens')
ORDER By bk.CustomerNumber ASC;
December 9, 2016 at 6:36 am
Luis Cazares (12/9/2016)
I'd suggest that you read Thom's comments. However, this is a shot in the dark to show what you might need.
SELECT bk.CustomerNumber,
cal.Day_Uid,
[Sale] = SUM(bk.BasketPriceTOTAL)
From db.dbo.SALE_Basket bk
INNER JOIN db.dbo.SALE_Line sl ON sl.Basket_id = bk.Basket_id
INNER JOIN db.dbo.map_Manager GSM ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN db.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Where sl.IsTrade = 1
AND sl.ISVoid = 0
and cal.Day_Uid = 20161203
AND bk.CustomerNumber <> 'Null'
AND bk.IsTrade = 1
AND bk.IsVoid = 0
Group By cal.Day_Uid, bk.CustomerNumber
HAVING COUNT( CASE WHEN GSM.AreaName_BM = 'Mens' THEN 1 END)
AND (MAX(GSM.AreaName_BM) <> 'Mens'
OR MIN(GSM.AreaName_BM) <> 'Mens')
ORDER By bk.CustomerNumber ASC;
Luis,
I think you need to change your HAVING clause as follows:
HAVING COUNT(CASE WHEN GSM.AreaName_BM = 'Mens' THEN 1 END) > 0
AND (
MAX(GSM.AreaName_BM) != 'Mens'
OR
MIN(GSM.AreaName_BM) != 'Mens'
)
It was missing the > 0 part.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 9, 2016 at 6:48 am
sgmunson (12/9/2016)
Luis Cazares (12/9/2016)
I'd suggest that you read Thom's comments. However, this is a shot in the dark to show what you might need.
SELECT bk.CustomerNumber,
cal.Day_Uid,
[Sale] = SUM(bk.BasketPriceTOTAL)
From db.dbo.SALE_Basket bk
INNER JOIN db.dbo.SALE_Line sl ON sl.Basket_id = bk.Basket_id
INNER JOIN db.dbo.map_Manager GSM ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN db.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Where sl.IsTrade = 1
AND sl.ISVoid = 0
and cal.Day_Uid = 20161203
AND bk.CustomerNumber <> 'Null'
AND bk.IsTrade = 1
AND bk.IsVoid = 0
Group By cal.Day_Uid, bk.CustomerNumber
HAVING COUNT( CASE WHEN GSM.AreaName_BM = 'Mens' THEN 1 END)
AND (MAX(GSM.AreaName_BM) <> 'Mens'
OR MIN(GSM.AreaName_BM) <> 'Mens')
ORDER By bk.CustomerNumber ASC;
Luis,
I think you need to change your HAVING clause as follows:
HAVING COUNT(CASE WHEN GSM.AreaName_BM = 'Mens' THEN 1 END) > 0
AND (
MAX(GSM.AreaName_BM) != 'Mens'
OR
MIN(GSM.AreaName_BM) != 'Mens'
)
It was missing the > 0 part.
:crazy::blush:
Thank you for noticing and correcting it. I haven't finished my first cup of coffee today and couldn't test the code.
December 9, 2016 at 6:56 am
Dear Steve, Tom
Thanks for the reply , some of the answers are below
I am using SQL Server Management Studio 20912 and only have read access to the tables
Toms questions
I use the null to just give me those who are actually our part of the membership program so they have customer No
I have followed the instruction and below are my tables, hope this is the right way to do it
USE [HUDS]
GO
/****** Object: Table [dbo].[SALE_Basket] Script Date: 09/12/2016 13:50:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SALE_Basket](
[Basket_id] [bigint] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[Day_Uid_TRANSACTION] [int] NULL,
[DateTransaction] [datetime] NULL,
[Site_Code_PHYSICAL] [varchar](20) NULL,
[TillNumber] [varchar](20) NULL,
[TillTransaction] [varchar](20) NULL,
[BasketVAT] [money] NULL,
[BasketPriceExcVAT] [money] NULL,
[BasketPriceIncVAT] [money] NULL,
[BasketPriceDiscount] [money] NULL,
[BasketPriceTOTAL] [money] NULL,
[BasketPriceOVERRIDE] [money] NULL,
[BasketExportDiscount] [money] NULL,
[BasketQty] [int] NULL,
[IsVoid] [bit] NULL,
[IsTrade] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[raw_Site_Code_PHYSICAL] [varchar](20) NULL,
[raw_TradeDate] [datetime] NULL,
[raw_TransactionDate] [datetime] NULL,
[CustomerLoyaltyNumber] [varchar](30) NULL,
[IsSuspend] [bit] NULL,
CONSTRAINT [PK_SALE_Basket] PRIMARY KEY CLUSTERED
(
[Basket_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS_Analytics]
GO
/****** Object: Table [dbo].[map_SiteBusinessManager] Script Date: 09/12/2016 13:52:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[map_SiteBusinessManager](
[Site_Code] [varchar](20) NOT NULL,
[Employee_Code_BM] [varchar](20) NULL,
[AreaName_BM] [varchar](100) NULL,
CONSTRAINT [PK_map_SiteBusinessManager] PRIMARY KEY CLUSTERED
(
[Site_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[SALE_Line] Script Date: 09/12/2016 13:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SALE_Line](
[Line_id] [bigint] NOT NULL,
[Basket_id] [bigint] NOT NULL,
[Day_Uid_TRADE] [int] NULL,
[Day_Uid_TRANSACTION] [int] NULL,
[DateTransaction] [datetime] NULL,
[POSArticleCode] [varchar](20) NULL,
[Article_Code_POSTING] [varchar](20) NULL,
[Site_Code_POSTING] [varchar](20) NULL,
[TransactionType_Code] [varchar](20) NULL,
[ExportRegion_Code] [varchar](20) NULL,
[CouponSku_Code] [varchar](20) NULL,
[UnitQty_OLD] [int] NULL,
[UnitVAT] [money] NULL,
[UnitPriceMAP] [money] NULL,
[UnitPriceExcVAT] [money] NULL,
[UnitPriceIncVAT] [money] NULL,
[LineVAT] [money] NULL,
[LinePriceExcVAT] [money] NULL,
[LinePriceIncVAT] [money] NULL,
[LinePriceDiscount] [money] NULL,
[LineExportDiscount] [money] NULL,
[LinePriceTOTAL] [money] NULL,
[LineOverriddenPrice] [money] NULL,
[Line_Id_ORIGIN] [bigint] NULL,
[IsVoid] [bit] NULL,
[IsTrade] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
[raw_TradeDate] [datetime] NULL,
[raw_TransactionDate] [datetime] NULL,
[raw_Article_Code_POSTING] [varchar](20) NULL,
[raw_Site_Code_POSTING] [varchar](20) NULL,
[raw_TransactionType_Code] [varchar](20) NULL,
[raw_ExportRegion_Code] [varchar](20) NULL,
[raw_CouponSku_Code] [varchar](20) NULL,
[ReceiptLineNo] [int] NULL,
[Barcode] [varchar](20) NULL,
[UnitQty] [decimal](12, 4) NULL,
CONSTRAINT [PK_SALE_Line] PRIMARY KEY CLUSTERED
(
[Basket_id] ASC,
[Line_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data],
CONSTRAINT [UK_SALE_Line] UNIQUE NONCLUSTERED
(
[Line_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
SET ANSI_PADDING OFF
GO
USE [HUDS]
GO
/****** Object: Table [dbo].[dim_Calendar] Script Date: 09/12/2016 13:54:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dim_Calendar](
[Day_Uid] [int] NOT NULL,
[Week_Uid_FISCAL] [int] NULL,
[Period_Uid_FISCAL] [int] NULL,
[Quarter_Uid_FISCAL] [int] NULL,
[Half_Uid_FISCAL] [int] NULL,
[Year_Uid_FISCAL] [int] NULL,
[Month_Uid_MARKET] [int] NULL,
[Quarter_Uid_MARKET] [int] NULL,
[Half_Uid_MARKET] [int] NULL,
[Year_Uid_MARKET] [int] NULL,
[IsCurrent] [bit] NULL,
[Batch_Id_INSERT] [bigint] NULL,
[Batch_Id_UPDATE] [bigint] NULL,
CONSTRAINT [PK_dim_FiscalCalendar] PRIMARY KEY CLUSTERED
(
[Day_Uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data]
) ON [Data]
GO
December 9, 2016 at 7:01 am
Can I also say thank you for everyone who has contributed to this. I am very grateful for all your times and sorry for not following the corrcet procedures, I would learn in time
December 9, 2016 at 7:12 am
OP psoted this in a different topic i was subscribed to, seems to be related to this topic, rather than the one he posted in:
v.razaghzadeh (12/9/2016)
HiThank you so much for your help and your time. Dep_Dm is the same as AreaName_BM I changed the name to Dep_Name but I forgot to change the all the name. Sorry about this, I just wanted to make it simpler for the forum.
My Actual Code is below without any change of names, I managed to edit it a bit to produce a result which I have included at the bottom.
Select * From
(
Select bk.CustomerLoyaltyNumber, [Sale] = SUM(bk.BasketPriceTOTAL), GSM.AreaName_BM, cal.Period_Uid_FISCAL,
CASE When count(bk.customerLoyaltyNumber) > 1 Then 'Duplicate' Else 'Unique' End As Shopped
From HUDS.dbo.SALE_Basket bk
INNER JOIN HUDS.dbo.SALE_Line sl ON sl.Basket_id = bk.Basket_id
INNER JOIN HUDS_Analytics.dbo.map_SiteBusinessManager GSM ON bk.Site_Code_PHYSICAL = GSM.Site_Code
INNER JOIN HUDS.dbo.dim_Calendar cal ON sl.Day_Uid_TRADE = cal.Day_Uid
Where sl.IsTrade = 1 AND sl.ISVoid = 0 and cal.Period_Uid_FISCAL = 201609 AND bk.CustomerLoyaltyNumber <> 'Null'
Group By GSM.AreaName_BM, bk.CustomerLoyaltyNumber, cal.Period_Uid_FISCAL
-- HAVING count(bk.customerLoyaltyNumber) > 1
) As Test
Where Shopped = 'Duplicate'
The code above gives me all the shoppers who have bought more than one item in different departments within specified period. I need to now look into the Customer numbers AND SEE IF THEY HAVE SHOPPED IN MENS AND IF SO WHAT OTHER DEPARTMENTS THEY SHOPPED FROM. iF CUSTOMER HAS SHOPPED IN =VARIOUS DEPARTMENTS BUT NE WAS NOT MENS THEN i DO NOT NEED TO LIST THEM,
Hope this is clear, and sorry about Caps lock.
Below is sample of what I get (hope I used the right IF code)
SaleAreaName_BMLoyalty No
374.76Restaurants00005
157316.65Sports00005
552.41Food 12612
454.8Children12612
5400.9Beauty12745
2143.6Sports12976
560.1Technology13040
87.6Food 13321
649.8Beauty13354
360Accessories13917
19.3Food13917
789.2Beauty13917
33.75Restaurants13917
504.95gifts13917
99.8Women13917
336Accessories14550
716Technology15383
45.8Toys15912
603.9Beauty17322
429Women17322
7.14Food17322
136Food17371
271Beauty17371
159.2Food17496
390.9Restaurants17496
4768.34Beauty17983
4853.28Restaurants18007
5634Beauty18007
11820Sports18007
48Beauty18122
589.5Beauty18288
7852.66Toys18288
2133.64Food18288
1848Restaurants18288
116.31gifts18288
1649.78Children18288
76.41Homewares18361
68Technology18916
2024.68Restaurants19013
35.98Toys19013
178.15Children19013
315.5Beauty19120
568.68Restaurants19120
413.14gifts19120
257.4Children19120
171.44Restaurants19609
54Restaurants20011
1713.75Children20011
96Beauty20136
427.62Restaurants20136
36.96Restaurants20557
790Services20557
171.45Beauty20581
1080gifts20607
1035Mens20607
248.79Food20607
189.68Restaurants20938
1040.1Restaurants21068
860Mens21068
2296Women21720
1493.5Beauty22728
535.8Sports22728
576Restaurants22728
625.5Women22728
1330Mens23726
30.7Food23874
881.9Beauty23874
126.92Food24245
328.5Restaurants24245
71.8gifts24245
53.82gifts24849
420.3Women24849
4264.32Restaurants24849
4518Mens24849
1446.65Beauty24849
56145.7Homewares25192
904.95Beauty25192
78Services25192
1753.65Food25218
62.83gifts25358
119.1Signature25358
0Services25358
37.4Homewares25358
0Restaurants25358
115.83Food25358
278.79Toys25358
30.36Restaurants25697
310.2Signature25986
319.2gifts27016
1230.21Restaurants27016
126Food27016
732Children27032
197.7Food27032
1342.4Beauty27032
780Shoe Heaven27032
450.02Restaurants27446
2149.28Food27768
882.22Beauty28147
1230Mens28147
1803.12Food28147
40.9gifts28147
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 7:16 am
v.razaghzadeh (12/9/2016)
Can I also say thank you for everyone who has contributed to this. I am very grateful for all your times and sorry for not following the corrcet procedures, I would learn in time
Most of us here were in your shoes at some earlier point in time. Now for the perhaps most important part... We need to know what a single record in each of those tables actually represents. That way, we can design any queries we might suggest with at least most of the information in hand that is needed to do so.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 9, 2016 at 7:35 am
Dear Luis, Steve
I tried the code and it work. The only thing is it only returns Mens sales, I need to see where-else they shopped. So if Customer a shopped in Mens and then shopped in Children then I need the customer number and the departments they shopped to be listed. However if Customer A shopped in Childrens and in Sports then I do not need to list them. Hope this is clear.
Can I also ask if I am able to substitute Mens to Mens% in having statement so it gives me all the menswear departments as I have a department Called Mens G and Mens LG?
Thanks in advance
December 9, 2016 at 7:45 am
Dear Steve
In Huds.Sale Basket each record show the amount spent per total transaction for each customer , along with their customer number (if they are a member of the reward program) day of transaction, Basket id (unique field), Department Number
Hud Calendar has days of each month and which period and half they come under so 20161001 will be Period Period 9
Sales_Line has similar information as Sales_Basket but shows details of each basket so there may be three items in each basket (which means the total of that transactions)
Void =0 means that the transaction actually went ahead and Trade =1 means it was a sale
GSM_AreaName shows the department numbers and which divisions they belong to so for example Dept 239 belongs to Menswear G
So I joining the Basket Ids from Sale_Line and Sale Basket to get the transcation details for each customer and the day of transactions along with customer No and how much they spend.
I then joing this to Calendar to get the right days and Periods and I also joiningg the result to AreaName by site codes to be able to identify which departments they belong to whether its Childrenswear or Menswear etc.
Hope this explains the structure a bit clearer
December 9, 2016 at 7:45 am
Post removed on account of what I had to talk about was fixed just a very short time before my post posted.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply