Joining data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joining data

Author

Topic

BobbyDigital
Starting Member

5 Posts

Posted - 2014-07-29 : 14:05:41

Hello all, this is my first time posting and wanted to start off by thanking everyone that reads and offers support. I have never had any formal training on SQL and have learned everything I currently know through trial and error and a bit of research.

I work in the retail industry and I am essentially wanting to create a report that compares current inventory to sales data. This will ultimately help us track slow moving or non selling items.

The first table I am dealing with is my inventory table and these are the columns that are pertinent to my report.

Table name - Inv_Qty
Column names - StoreNumber,Qty_On_Hand,PLU_NUM

Basically this is a very large table that holds and inventory record for every UPC we have (PLU_NUM) and stores it in the QTY_ON_HAND field.

The second table I will be using is our sales table.

Table name - Pdetail
Column Names - Stornumber,time_stamp,Detail_for_qty(unit sales),PLU

Basically I would like to display the following: StoreNumber,PLU_NUM,QTY_ON_HAND,Sum(Detail_for_qty) for a given time period. If the sales data does not contain the PLU, i would like for it to still display the qty_on_hand but display the sum(detail_for_qty) as 0.

The problem that I am having is that if the PLU is not in the sales table it will not display that item at all. Below is my query.

select t1.Store,t1.PLU,t1.OnHand,t2.Sales

from

(select Inv_Qty.PLU_NUM as [PLU],Inv_Qty.QTY_ON_HAND as [OnHand],Inv_Qty.StoreNumber as [Store]
from Inv_Qty

where QTY_ON_HAND <> 0)t1,

(select PDetail.StoreNumber as [Store],PDetail.PLU as [PLU],SUM(pdetail.detail_for_qty) as [Sales]

from PDetail

where DETAIL_CLASS = 1 and YEAR(time_stamp) = 2014
and Dept not in (12,14,15,16,17,18,31)
and Dept < 74

group by StoreNumber,PDetail.PLU)t2

where (t1.Store = t2.Store) and (t1.plu = t2.plu) and t2.Sales < 2

order by t1.Store asc

Any help would be appreciated. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-29 : 16:12:18

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

BobbyDigital
Starting Member

5 Posts

Posted - 2014-07-30 : 08:16:52

How nice of you to post a link to a forum that requires registration to even read and apparently is meant to be a lazy way of telling me that I didn't post enough information for you.

Judging by your other recent posts it seems that your only goal in this forum is to post the same best practice link over and over again without really helping anyone.

Since I am a beginner looking for assistance, and I am unfamiliar with recreating my tables and data via scripts and queries, I guess I will take my questions elsewhere. Hopefully I will find a forum or support site that is more friendly and accepting of newbies.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-30 : 09:30:06

This might be close based on what you described, but I did not test it as I do not have the table structures.

SELECT IQ.StoreNumber,IQ.Qty_On_Hand,IQ.PLU_NUM,PD.Sales
FROM Inv_Qty IQ
INNER JOIN
(SELECT Stornumber,PLU_NUM,SUM(Pdetail) Sales
FROM Pdetail
WHERE DETAIL_CLASS = 1 and YEAR(time_stamp) = 2014
and Dept not in (12,14,15,16,17,18,31)
and Dept < 74
GROUP BY Stornumber,PLU_NUM
HAVING SUM(Pdetail) < 2
) PD ON PD.Stornumber = IQ.Stornumber AND PD.PLU_NUM = IQ.PLU_NUM
WHERE IQ.Qty_On_Hand !=0
GROUP BY IQ.StoreNumber,IQ.Qty_On_Hand,IQ.PLU_NUM

If you are using SSMS (sql server management studio) you can right click on an object and select 5thoption down, "SCRIPT AS" and follow the tree to CREATE AS to new window and you can get a copy of your DDLas long as you have view definition.

BobbyDigital
Starting Member

5 Posts

Posted - 2014-07-30 : 11:11:19

Thank you Michael for taking the time to show me the options in SSMS that will help me create the information you may need. Below are the create scripts for the two tables.

I cleaned up your script to match the table names and it didn't error but did not pull all of the data I needed it to

Would there also be a way through SSMS to script out some sample data in a similar fashion?

Basically the biggest problem is that the two tables need linked both on PLU and on Storenumber to match the Quantity on Hand with the sales. Both of these columns will be in the data in the INV_QTY table, but we may not be able to find matching PLU entries in the PDETAIL table if the item was never sold during the given time period.

Pdetail

/****** Object: Table [dbo].[PDetail] Script Date: 07/30/2014 10:29:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PDetail](
[CGS_Acct] [varchar](50) NULL,
[CUR_PRC] [money] NULL,
[FUEL_Z_COUNTER] [bigint] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[Inv_Asset] [varchar](50) NULL,
[Misc_Flags] [bigint] NULL,
[RECEIPT_NUM] [bigint] NULL,
[Sale_Acct] [varchar](50) NULL,
[Serial_Num] [varchar](50) NULL,
[Serial_Type] [bigint] NULL,
[TIME_STAMP] [datetime] NULL,
[SOURCE_CLASS] [bigint] NULL,
[SOURCE_ID] [bigint] NULL,
[DETAIL_CLASS] [bigint] NULL,
[DETAIL_TYPE] [bigint] NULL,
[EMP_ID] [int] NULL,
[MODE_FLAGS] [bigint] NULL,
[REG_NUM] [int] NULL,
[REG_Z_COUNTER] [bigint] NULL,
[CSHR_NUM] [int] NULL,
[CSHR_Z_COUNTER] [bigint] NULL,
[PLU] [varchar](50) NULL,
[DEPT] [int] NULL,
[DETAIL_AT_QTY] [float] NULL,
[DETAIL_FOR_QTY] [float] NULL,
[DETAIL_ITEM_PRC] [money] NULL,
[DETAIL_LINE_AMT] [money] NULL,
[ITEM_COST] [money] NULL,
[AVG_COST] [money] NULL,
[BUYDOWN_AMT] [money] NULL,
[BUYDOWN_ID] [bigint] NULL,
[COSTPLUSDISC_AMT] [money] NULL,
[CUR_ONHAND_QTY] [money] NULL,
[CUSTDEPTDISC_AMT] [money] NULL,
[EXCISE_TAX] [money] NULL,
[KIT_QTY] [money] NULL,
[KITUPC] [varchar](50) NULL,
[MIXMATCH_AMT] [money] NULL,
[PERCENT_DISCT] [money] NULL,
[PROMO_AMT] [money] NULL,
[PROMO_ID] [bigint] NULL,
[SUBTOTAL_DISCT] [money] NULL,
[SYSDISCOUNT_AMT] [money] NULL,
[SYSDISCOUNT_ID] [bigint] NULL,
[TRANSFERRED] [bit] NULL,
[VERSION_FLAG] [int] NULL,
[RT_RECEIPT_NUM] [bigint] NULL,
[RT_REG_NUM] [int] NULL,
[RT_CSHR_NUM] [int] NULL,
[CUST_ID] [varchar](50) NULL,
[SubAcctID] [bigint] NULL,
[PUMP_NUM] [varchar](50) NULL,
[ezExported] [bit] NULL,
[CorpExported] [bit] NULL,
[StoreNumber] [int] NULL,
[StoreRowID] [int] NULL,
CONSTRAINT [PK_PDetail] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PDetail] ADD CONSTRAINT [DF__PDetail__TRANSFE__308E3499] DEFAULT ((0)) FOR [TRANSFERRED]
GO

ALTER TABLE [dbo].[PDetail] ADD CONSTRAINT [DF__PDetail__ezExpor__318258D2] DEFAULT ((0)) FOR [ezExported]
GO

ALTER TABLE [dbo].[PDetail] ADD CONSTRAINT [DF_PDetail_CorpExported] DEFAULT ((0)) FOR [CorpExported]
GO

ALTER TABLE [dbo].[PDetail] ADD CONSTRAINT [DF_PDetail_StoreNumber] DEFAULT ([dbo].[funcGetStoreNum]()) FOR [StoreNumber]
GO

ALTER TABLE [dbo].[PDetail] ADD DEFAULT ((0)) FOR [StoreRowID]
GO

INV_QTY

/****** Object: Table [dbo].[Inv_Qty] Script Date: 07/30/2014 10:28:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Inv_Qty](
[ROW_ID] [int] IDENTITY(1,1) NOT NULL,
[PLU_NUM] [varchar](50) NOT NULL,
[QTY_ON_HAND] [float] NULL,
[QTY_RECEIVED] [float] NULL,
[QTY_SOLD] [float] NULL,
[QTY_ADJUSTED] [float] NULL,
[QTY_TRANSFERRED] [float] NULL,
[LAST_PHY_QTY] [float] NULL,
[LAST_PHY_TIME] [datetime] NULL,
[SOLD_SINCE_CLEAR] [float] NULL,
[ADJUSTS_SINCE_CLEAR] [float] NULL,
[TIME_CLEARED] [datetime] NULL,
[INV_IN_PROGRESS] [int] NULL,
[QTY_ON_ORDER] [float] NULL,
[ezExported] [bit] NULL,
[StoreNumber] [int] NULL,
[CorpExported] [bit] NULL,
CONSTRAINT [Inv_Qty$PrimaryKey] PRIMARY KEY CLUSTERED
(
[ROW_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__QTY_ON___45BE5BA9] DEFAULT ((0)) FOR [QTY_ON_HAND]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__QTY_REC__46B27FE2] DEFAULT ((0)) FOR [QTY_RECEIVED]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__QTY_SOL__47A6A41B] DEFAULT ((0)) FOR [QTY_SOLD]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__QTY_ADJ__489AC854] DEFAULT ((0)) FOR [QTY_ADJUSTED]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__QTY_TRA__498EEC8D] DEFAULT ((0)) FOR [QTY_TRANSFERRED]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__LAST_PH__4A8310C6] DEFAULT ((0)) FOR [LAST_PHY_QTY]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__SOLD_SI__4B7734FF] DEFAULT ((0)) FOR [SOLD_SINCE_CLEAR]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__ADJUSTS__4C6B5938] DEFAULT ((0)) FOR [ADJUSTS_SINCE_CLEAR]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__INV_IN___4D5F7D71] DEFAULT ((0)) FOR [INV_IN_PROGRESS]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__QTY_ON___4E53A1AA] DEFAULT ((0)) FOR [QTY_ON_ORDER]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF__Inv_Qty__ezExpor__4F47C5E3] DEFAULT ((0)) FOR [ezExported]
GO

ALTER TABLE [dbo].[Inv_Qty] ADD CONSTRAINT [DF_InvQty_StoreNumDef] DEFAULT ([dbo].[funcGetStoreNum]()) FOR [StoreNumber]

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-30 : 12:00:19

Would there also be a way through SSMS to script out some sample data in a similar fashion?
You probably don't want to script out you data unless you obfuscate it in some way. in SSMS there is not a way to script out data as far as I know, but in visual studio there is. I usually write a script

example:
SELECT 'INSERT INTO TABLE X VALUES(''' + ClaimNumber + ''',''' + CAST(ClaimDate as Varchar(20)) + ''')'
FROM Core.ClaimTable

I will create your tables and put in some test data and try this out, but what you are describing lens itself toward a Left join.

SELECT IQ.StoreNumber,IQ.Qty_On_Hand,IQ.PLU_NUM,PD.Sales
FROM Inv_Qty IQ
LEFT JOIN
(SELECT Stornumber,PLU_NUM,SUM(Pdetail) Sales
FROM Pdetail
WHERE DETAIL_CLASS = 1 and YEAR(time_stamp) = 2014
and Dept not in (12,14,15,16,17,18,31)
and Dept < 74
GROUP BY Stornumber,PLU_NUM
HAVING SUM(Pdetail) < 2
) PD ON PD.Stornumber = IQ.Stornumber AND PD.PLU_NUM = IQ.PLU_NUM
WHERE IQ.Qty_On_Hand !=0
GROUP BY IQ.StoreNumber,IQ.Qty_On_Hand,IQ.PLU_NUM

-- this will bring back all the rows in Inv_Qty, but only those matching rows in the subquery. I will lokk at this later

BobbyDigital
Starting Member

5 Posts

Posted - 2014-07-30 : 14:36:06

Michael, thanks so much for being patient with me and helping me out. I was able to clean up a few things and add a case statement to remove the Null entries with 0s. Here is the final script that I ended up with and everything seems to be working like I wanted it to.

Is there a kudos button or anything on this forum that will give you any preference points? You have been a big help and I appreciate it.

SELECT IQ.StoreNumber,IQ.Qty_On_Hand,IQ.PLU_NUM,

Case when PD.Sales is null then '0'
ELSE PD.Sales
End
FROM Inv_Qty IQ
left JOIN
(SELECT pdetail.StoreNumber,PDetail.PLU,SUM(DETAIL_FOR_QTY) as [Sales]
FROM Pdetail
WHERE DETAIL_CLASS = 1 and YEAR(time_stamp) = 2014
and Dept not in (12,14,15,16,17,18,31)
and Dept < 74
GROUP BY StoreNumber,PLU
HAVING SUM(DETAIL_FOR_QTY) < 2
) PD ON PD.StoreNumber = IQ.Storenumber AND PD.PLU = IQ.PLU_NUM
WHERE IQ.Qty_On_Hand !=0
GROUP BY IQ.StoreNumber,IQ.Qty_On_Hand,IQ.PLU_NUM,pd.sales
order by IQ.StoreNumber,IQ.PLU_NUM asc

BobbyDigital
Starting Member

5 Posts

Posted - 2014-07-30 : 16:21:08

The data appeared to be better at first because more items were being returned from the query, but after further investigation it doesn't appear that the # of sales is being reported properly.

here is a snippet of the data

StoreNumber Qty_On_Hand PLU_NUM sales
1 21 008660007322 0

This PLU actually has 3 sales within the past week.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-30 : 18:21:52

Happy to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources