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
 General SQL Server Forums
 New to SQL Server Programming
 RESOLVED: Help with a Pivot table on overlappi...

Author  Topic 

SergioM
Posting Yak Master

170 Posts
Posted - 2014-09-11 : 12:22:14
I am trying to figure out the latest stock count of the last 7 days & compare it to the latest stock count of the 7 days before that. I've built out this pretty long query and it does get me the SKUs that I need along with the IDs. My problem is that they appear on different rows. I think I need the pivot option, but I keep getting errors when I try to pivot. This is what I have so far - working & before pivoting. Can someone help me get through the last mile?

SELECT i.[ID]
      ,i.[ProductID]
      ,i.[Actual_Quantity]
      ,iThisWeek.thisWeekID
      ,iLastWeek.lastWeekID
FROM [SC_SSIS].[dbo].[tmpInv] i
FULL JOIN (
	SELECT
		MAX([ID]) AS thisWeekID
		,[ProductID]
	FROM [SC_SSIS].[dbo].[tmpInv]
	WHERE DateChanged BETWEEN GETDATE()-7 AND GETDATE()
	GROUP BY [ProductID]
	) iThisWeek
	ON i.ID = iThisWeek.thisWeekID
FULL JOIN (
	SELECT
		MAX([ID]) AS lastWeekID
		,[ProductID]
	FROM [SC_SSIS].[dbo].[tmpInv]
	WHERE DateChanged BETWEEN GETDATE()-14 AND GETDATE()-7
	GROUP BY [ProductID]
	) iLastWeek
	ON i.ID = iLastWeek.lastWeekID
WHERE (
	iLastWeek.ProductID IS NOT NULL 
	or iThisWeek.ProductID IS NOT NULL
	)


The resulting dataset for that query is:
ID	ProductID	Actual_Quantity	thisWeekID	lastWeekID
609363	DH-JBQSA109	12	NULL	609363
609364	DH-JBQSA110	12	NULL	609364
609378	DH-JJE01211S1	60	NULL	609378
609383	DH-JJE40011S3	84	NULL	609383
609389	DH-JJP01311S1	27	NULL	609389
1455533	DH-JBQSA109	12	1455533	NULL
1455534	DH-JBQSA110	12	1455534	NULL
1455548	DH-JJE01211S1	62	1455548	NULL
1455554	DH-JJE40011S3	76	1455554	NULL
1455560	DH-JJP01311S1	30	1455560	NULL



The raw data is:
USE [SC_SSIS]
GO
/****** Object:  Table [dbo].[tmpInv]    Script Date: 09/11/2014 12:15:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tmpInv](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProductID] [nvarchar](500) NULL,
	[Actual_Quantity] [int] NULL,
	[Cost] [decimal](18, 2) NULL,
	[Posted_Quantity] [int] NULL,
	[DateChanged] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tmpInv] ON
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (35672, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39500C8AED2 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (35673, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39500C8AED2 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (35701, N'DH-JJE01211S1', 71, CAST(22.91 AS Decimal(18, 2)), 64, CAST(0x0000A39500C8AED2 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (35707, N'DH-JJE40011S3', 103, CAST(84.22 AS Decimal(18, 2)), 93, CAST(0x0000A39500C8AED2 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (35713, N'DH-JJP01311S1', 32, CAST(17.34 AS Decimal(18, 2)), 27, CAST(0x0000A39500C8AED2 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (127158, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39600EE4102 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (127159, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39600EE4102 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (127173, N'DH-JJE01211S1', 73, CAST(22.91 AS Decimal(18, 2)), 66, CAST(0x0000A39600EE4102 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (127178, N'DH-JJE40011S3', 99, CAST(84.22 AS Decimal(18, 2)), 89, CAST(0x0000A39600EE4102 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (127184, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39600EE4102 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (409709, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39B012CAD75 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (409710, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39B012CAD75 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (409724, N'DH-JJE01211S1', 60, CAST(22.91 AS Decimal(18, 2)), 54, CAST(0x0000A39B012CAD76 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (409729, N'DH-JJE40011S3', 86, CAST(84.22 AS Decimal(18, 2)), 77, CAST(0x0000A39B012CAD76 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (409735, N'DH-JJP01311S1', 27, CAST(17.34 AS Decimal(18, 2)), 22, CAST(0x0000A39B012CAD76 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (940396, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39E01301A34 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (940397, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39E01301A34 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (940411, N'DH-JJE01211S1', 59, CAST(22.91 AS Decimal(18, 2)), 53, CAST(0x0000A39E01301A35 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (940416, N'DH-JJE40011S3', 80, CAST(84.22 AS Decimal(18, 2)), 72, CAST(0x0000A39E01301A35 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (940422, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39E01301A35 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1239669, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A3A100D52E0F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1239670, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A3A100D52E0F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1239684, N'DH-JJE01211S1', 66, CAST(22.91 AS Decimal(18, 2)), 59, CAST(0x0000A3A100D52E0F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1239689, N'DH-JJE40011S3', 77, CAST(84.22 AS Decimal(18, 2)), 69, CAST(0x0000A3A100D52E0F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1239695, N'DH-JJP01311S1', 30, CAST(17.34 AS Decimal(18, 2)), 25, CAST(0x0000A3A100D52E0F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1296441, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A3A20010FC5E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1296442, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A3A20010FC5E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1296456, N'DH-JJE01211S1', 66, CAST(22.91 AS Decimal(18, 2)), 59, CAST(0x0000A3A20010FC5F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1296462, N'DH-JJE40011S3', 77, CAST(84.22 AS Decimal(18, 2)), 69, CAST(0x0000A3A20010FC5F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1296468, N'DH-JJP01311S1', 30, CAST(17.34 AS Decimal(18, 2)), 25, CAST(0x0000A3A20010FC5F AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (609363, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39C00AAB6E6 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (609364, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39C00AAB6E6 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (609378, N'DH-JJE01211S1', 60, CAST(22.91 AS Decimal(18, 2)), 54, CAST(0x0000A39C00AAB6E6 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (609383, N'DH-JJE40011S3', 84, CAST(84.22 AS Decimal(18, 2)), 76, CAST(0x0000A39C00AAB6E6 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (609389, N'DH-JJP01311S1', 27, CAST(17.34 AS Decimal(18, 2)), 22, CAST(0x0000A39C00AAB6E6 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1176493, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A3A000B722F5 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1176494, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A3A000B722F5 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1176508, N'DH-JJE01211S1', 59, CAST(22.91 AS Decimal(18, 2)), 53, CAST(0x0000A3A000B722F5 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1176513, N'DH-JJE40011S3', 80, CAST(84.22 AS Decimal(18, 2)), 72, CAST(0x0000A3A000B722F5 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1176519, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A3A000B722F5 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (272493, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A3980099DBBA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (272494, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A3980099DBBA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (272508, N'DH-JJE01211S1', 63, CAST(22.91 AS Decimal(18, 2)), 57, CAST(0x0000A3980099DBBA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (272513, N'DH-JJE40011S3', 98, CAST(84.22 AS Decimal(18, 2)), 88, CAST(0x0000A3980099DBBA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (272519, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A3980099DBBA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (206745, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A3970160B364 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (206746, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A3970160B364 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (206760, N'DH-JJE01211S1', 63, CAST(22.91 AS Decimal(18, 2)), 57, CAST(0x0000A3970160B365 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (206765, N'DH-JJE40011S3', 98, CAST(84.22 AS Decimal(18, 2)), 88, CAST(0x0000A3970160B365 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (206771, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A3970160B365 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (888254, N'DH-JJE01211S1', 59, CAST(22.91 AS Decimal(18, 2)), 53, CAST(0x0000A39E0110219A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (888259, N'DH-JJE40011S3', 80, CAST(84.22 AS Decimal(18, 2)), 72, CAST(0x0000A39E0110219A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (888265, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39E0110219A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (538586, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39C006CF75E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (538587, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39C006CF75E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (538601, N'DH-JJE01211S1', 60, CAST(22.91 AS Decimal(18, 2)), 54, CAST(0x0000A39C006CF75E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (538606, N'DH-JJE40011S3', 85, CAST(84.22 AS Decimal(18, 2)), 76, CAST(0x0000A39C006CF75E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (538612, N'DH-JJP01311S1', 27, CAST(17.34 AS Decimal(18, 2)), 22, CAST(0x0000A39C006CF75E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (632270, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39C00FF4C0A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (632271, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39C00FF4C0A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (632285, N'DH-JJE01211S1', 60, CAST(22.91 AS Decimal(18, 2)), 54, CAST(0x0000A39C00FF4C0A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (632290, N'DH-JJE40011S3', 84, CAST(84.22 AS Decimal(18, 2)), 76, CAST(0x0000A39C00FF4C0A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (632296, N'DH-JJP01311S1', 27, CAST(17.34 AS Decimal(18, 2)), 22, CAST(0x0000A39C00FF4C0A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (888239, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39E0110219A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (888240, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39E0110219A AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1116890, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A3A000B5634B AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1116891, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A3A000B5634B AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1116905, N'DH-JJE01211S1', 59, CAST(22.91 AS Decimal(18, 2)), 53, CAST(0x0000A3A000B5634C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1116910, N'DH-JJE40011S3', 80, CAST(84.22 AS Decimal(18, 2)), 72, CAST(0x0000A3A000B5634C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1116916, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A3A000B5634C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (837678, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39E00BC82AA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (837679, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39E00BC82AA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (837693, N'DH-JJE01211S1', 59, CAST(22.91 AS Decimal(18, 2)), 53, CAST(0x0000A39E00BC82AA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (837698, N'DH-JJE40011S3', 80, CAST(84.22 AS Decimal(18, 2)), 72, CAST(0x0000A39E00BC82AA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (837704, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39E00BC82AA AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1041101, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39F00D7566E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1041102, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39F00D7566E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1041116, N'DH-JJE01211S1', 59, CAST(22.91 AS Decimal(18, 2)), 53, CAST(0x0000A39F00D7566E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1041121, N'DH-JJE40011S3', 80, CAST(84.22 AS Decimal(18, 2)), 72, CAST(0x0000A39F00D7566E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1041127, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39F00D7566E AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (332151, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39900A2B88C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (332152, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39900A2B88C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (332166, N'DH-JJE01211S1', 63, CAST(22.91 AS Decimal(18, 2)), 57, CAST(0x0000A39900A2B88C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (332171, N'DH-JJE40011S3', 98, CAST(84.22 AS Decimal(18, 2)), 88, CAST(0x0000A39900A2B88C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (332177, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39900A2B88C AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1455533, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A3A300A98F64 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1455534, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A3A300A98F64 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1455548, N'DH-JJE01211S1', 62, CAST(22.91 AS Decimal(18, 2)), 56, CAST(0x0000A3A300A98F64 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1455554, N'DH-JJE40011S3', 76, CAST(84.22 AS Decimal(18, 2)), 68, CAST(0x0000A3A300A98F64 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (481363, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39B013F9BA0 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (481364, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39B013F9BA0 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (481378, N'DH-JJE01211S1', 60, CAST(22.91 AS Decimal(18, 2)), 54, CAST(0x0000A39B013F9BA1 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (481383, N'DH-JJE40011S3', 86, CAST(84.22 AS Decimal(18, 2)), 77, CAST(0x0000A39B013F9BA1 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (481389, N'DH-JJP01311S1', 27, CAST(17.34 AS Decimal(18, 2)), 22, CAST(0x0000A39B013F9BA1 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (736538, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39D011241A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (736539, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39D011241A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (736553, N'DH-JJE01211S1', 59, CAST(22.91 AS Decimal(18, 2)), 53, CAST(0x0000A39D011241A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (736558, N'DH-JJE40011S3', 80, CAST(84.22 AS Decimal(18, 2)), 72, CAST(0x0000A39D011241A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (736564, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39D011241A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (68917, N'DH-JBQSA109', 12, CAST(146.84 AS Decimal(18, 2)), 7, CAST(0x0000A39600B4A1A9 AS DateTime))
GO
print 'Processed 100 total records'
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (68918, N'DH-JBQSA110', 12, CAST(164.24 AS Decimal(18, 2)), 7, CAST(0x0000A39600B4A1A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (68932, N'DH-JJE01211S1', 73, CAST(22.91 AS Decimal(18, 2)), 66, CAST(0x0000A39600B4A1A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (68937, N'DH-JJE40011S3', 99, CAST(84.22 AS Decimal(18, 2)), 89, CAST(0x0000A39600B4A1A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (68943, N'DH-JJP01311S1', 31, CAST(17.34 AS Decimal(18, 2)), 26, CAST(0x0000A39600B4A1A9 AS DateTime))
INSERT [dbo].[tmpInv] ([ID], [ProductID], [Actual_Quantity], [Cost], [Posted_Quantity], [DateChanged]) VALUES (1455560, N'DH-JJP01311S1', 30, CAST(17.34 AS Decimal(18, 2)), 25, CAST(0x0000A3A300A990D6 AS DateTime))
SET IDENTITY_INSERT [dbo].[tmpInv] OFF


-Sergio
I use Microsoft SQL 2008

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-09-11 : 12:28:47
Can you please post your pivoted query that give you errors and the error messages as well?
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts
Posted - 2014-09-11 : 13:26:30
Try this:
select a.ProductID
      ,b.Actual_Quantity as thisWeekActualQuantity
      ,c.Actual_Quantity as lastWeekActualQuantity
      ,b.ID as thisWeekID
      ,c.ID as lastWeekID
  from (select ProductID
              ,max(case when DateChanged>=dateadd(dd,-6,getdate() then ID else null end) as thisWeekID
              ,max(case when DateChanged<dateadd(dd,-6,getdate() then ID else null end) as lastWeekID
          from SC_SSIS.dbo.tmpInv
         where DateChanged>=dateadd(dd,-13,getdate())
           and DateChanged<=getdate()
         group by ProductID
       ) as a
       left outer join SC_SSIS.dbo.tmpInv as b
                    on b.ID=a.thisWeekID
       left outer join SC_SSIS.dbo.tmpInv as c
                    on c.ID=a.lastWeekID
Go to Top of Page

SergioM
Posting Yak Master

170 Posts
Posted - 2014-09-12 : 09:18:37
quote:
Originally posted by bitsmed

Try this:
select a.ProductID
      ,b.Actual_Quantity as thisWeekActualQuantity
      ,c.Actual_Quantity as lastWeekActualQuantity
      ,b.ID as thisWeekID
      ,c.ID as lastWeekID
  from (select ProductID
              ,max(case when DateChanged>=dateadd(dd,-6,getdate() then ID else null end) as thisWeekID
              ,max(case when DateChanged<dateadd(dd,-6,getdate() then ID else null end) as lastWeekID
          from SC_SSIS.dbo.tmpInv
         where DateChanged>=dateadd(dd,-13,getdate())
           and DateChanged<=getdate()
         group by ProductID
       ) as a
       left outer join SC_SSIS.dbo.tmpInv as b
                    on b.ID=a.thisWeekID
       left outer join SC_SSIS.dbo.tmpInv as c
                    on c.ID=a.lastWeekID


Bingo! Thanks! Usually when I get to about 3 subqueries, I realize that I'm making a huge mistake. JOIN statements make a lot more sense & are much easier to read & manipulate. Thanks!

quote:
Originally posted by gbritton

Can you please post your pivoted query that give you errors and the error messages as well?

I finally got the pivot table to work & then I realized it did something *useful* which I didn't need right now... I was guessing at what had to happen next.

-Sergio
I use Microsoft SQL 2008
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site