September 14, 2012 at 8:01 am
I have a table as:
CREATE TABLE tbItems
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[itemno] [varchar](10) NOT NULL, -- unique index
[Purchasedate] [datetime] NOT NULL,
[Solddate] [datetime] NULL,
[CrDate] [datetime] DEFAULT getdate()
)
Has a lot of rows for last 2 years (from 1/1/2011 till 8/31/2012).
I need to find all items were NOT sold on Nov 2011 (active inventory)
1-All sold items on Nov 2011 means were active
2-All items Purchasedate on Nov 2011 and solddate is null were active
3-All items Purchasedate on Nov 2011 and solddate sold after Nov 2011 were active
I dump all above 3 selects to temp table and finally I select,
I really appreciate any help or suggestion if I’m doing correct, or there is better way to do it.
Thanks
September 14, 2012 at 8:17 am
Difficult to help without test data and a more detailed explanation of what you are trying to achieve but do you mean something like this?
CREATE TABLE #TEMP
(
[ID] [int] NOT NULL,
[itemno] [varchar](10) NOT NULL,
[Purchasedate] [datetime] NOT NULL,
[Solddate] [datetime] NULL,
[CrDate] [datetime]
)
INSERT INTO #TEMP
SELECT *
FROM
tbItems
WHERE
MONTH(SoldDate) <> 11
AND YEAR(SoldDate) <> 2011
UNION
SELECT *
FROM
tbItems
WHERE
MONTH(Purchasedate) = 11
AND YEAR(Purchasedate) = 2011
AND Solddate IS NULL
UNION
SELECT *
FROM
tbItems
WHERE
MONTH(Purchasedate) = 11
AND YEAR(Purchasedate) = 2011
AND MONTH(SoldDate) > 11
AND YEAR(SoldDate) > 2011
SELECT *
FROM #TEMP
DROP TABLE #TEMP
Not the best performance wise but as I was taking a stab in the dark thought I'd post..
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 14, 2012 at 8:21 am
Thank you for your time and reply, I know how to do the script, but my question is: how to get active inventory by given date (month & year) from above table, I need to find out what items NOT sold (active) on that month
September 14, 2012 at 8:24 am
Hi Linda,
There are many ways to acheive the same and mentioned below is the basic query which satisfies your logic..
You can also use other function which optimizes your query output (However to build the same, I need the index details of your table)
SELECT * FROM tbItems
WHERE (MONTH(SOLDDATE) = 11 AND YEAR(SOLDDATE) = 2011)
OR
(MONTH(PURCHASEDATE) = 11 AND YEAR(PURCHASEDATE) = 2011 AND SOLDDATE IS NULL)
OR
(MONTH(PURCHASEDATE) = 11 AND YEAR(PURCHASEDATE) = 2011 AND CONVERT(VARCHAR(6), GETDATE(), 112) > '201111')
Regards,
Bala
September 14, 2012 at 8:30 am
1-All sold items on Nov 2011 means were active
2-All items Purchasedate on Nov 2011 and solddate is null were active
3-All items Purchasedate on Nov 2011 and solddate sold after Nov 2011 were active
So the above is the opposite of what you need?
If you post an explanation of what you need i.e. the criteria and some data I'm sure that you will get a better response..
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 14, 2012 at 8:32 am
SELECT i.*
FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate IS NULL THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate >= CONVERT(DATETIME,'01/12/2011',103) THEN 1
ELSE 0 END
) x
WHERE x.[active inventory] = 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2012 at 8:36 am
Linda_web (9/14/2012)
Thank you for your time and reply, I know how to do the script, but my question is: how to get active inventory by given date (month & year) from above table, I need to find out what items NOT sold (active) on that month
Hard to help when we can't see what you see. Please read the first article I reference below in my signature block. it will walk you through what you should post and how to get teh best possible answers to your questions.
Basically we need the DDL (CREATE TABLE) statement(s) for the table(s) involved, sample data (not real production data) as a series of INSERT INTO statements for the table(s) involved, the expected resutls (best when provided as a table along with INSERT statements to populate it as it provides us something to test against), and the code you have tried so far in an effort to solve your problem.
September 14, 2012 at 8:40 am
ChrisM@Work (9/14/2012)
SELECT i.*FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate IS NULL THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate >= CONVERT(DATETIME,'01/12/2011',103) THEN 1
ELSE 0 END
) x
WHERE x.[active inventory] = 0
I thought it was the month of Nov 2011 not the 1-11-2011?? :ermm:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 14, 2012 at 8:42 am
Andy Hyslop (9/14/2012)
ChrisM@Work (9/14/2012)
SELECT i.*FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate IS NULL THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate >= CONVERT(DATETIME,'01/12/2011',103) THEN 1
ELSE 0 END
) x
WHERE x.[active inventory] = 0
I thought it was the month of Nov 2011 not the 1-11-2011?? :ermm:
I'm fairly sure I'm filtering for the whole month of November 2011, Andy 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2012 at 8:50 am
ChrisM@Work (9/14/2012)
Andy Hyslop (9/14/2012)
ChrisM@Work (9/14/2012)
SELECT i.*FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate IS NULL THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate >= CONVERT(DATETIME,'01/12/2011',103) THEN 1
ELSE 0 END
) x
WHERE x.[active inventory] = 0
I thought it was the month of Nov 2011 not the 1-11-2011?? :ermm:
I'm fairly sure I'm filtering for the whole month of November 2011, Andy 🙂
Oh cr@p of course you are!!! <head hits desk>
Should learn to read one of these days Chris 😉 (or maybe not do 5 things at once!!)
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 14, 2012 at 8:57 am
Andy Hyslop (9/14/2012)
ChrisM@Work (9/14/2012)
Andy Hyslop (9/14/2012)
ChrisM@Work (9/14/2012)
SELECT i.*FROM tbItems i
CROSS APPLY (
SELECT [active inventory] = CASE
WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)
AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate IS NULL THEN 1
WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)
AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)
AND Solddate >= CONVERT(DATETIME,'01/12/2011',103) THEN 1
ELSE 0 END
) x
WHERE x.[active inventory] = 0
I thought it was the month of Nov 2011 not the 1-11-2011?? :ermm:
I'm fairly sure I'm filtering for the whole month of November 2011, Andy 🙂
Oh cr@p of course you are!!! <head hits desk>
Should learn to read one of these days Chris 😉 (or maybe not do 5 things at once!!)
Andy
No worries Andy. Ninety minutes to beer o'clock and focus is slipping 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2012 at 9:04 am
Ninety minutes to beer o'clock and focus is slipping
Judging by my last post I think mine is out of the door and at the pub already!! 😉
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
September 14, 2012 at 9:06 am
Andy Hyslop (9/14/2012)
Ninety minutes to beer o'clock and focus is slipping
Judging by my last post I think mine is out of the door and at the pub already!! 😉
Heh right behind you, geezer 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2012 at 9:55 am
Would the simplest way be correct?
DECLARE @Date date
SET @Date = '20110112' --Any day in November
SET @Date = DATEADD( mm, DATEDIFF( mm, 0, @Date) + 1, 0) -- Get next month
SELECT * FROM tbItems
WHERE (SOLDDATE >= @Date OR SOLDDATE IS NULL)
AND PURCHASEDATE < @Date
This will include any items:
1. purchased before Nov 2011 ended and
2. sold after Nov 2011
So they were active in Nov 2011 but they weren't sold in that month.
This will allow to use indexes as well.
September 14, 2012 at 10:09 am
Thank you all, here the details
CREATE TABLE tbItems
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[itemno] [varchar](10) NOT NULL, -- unique index
[Purchasedate] [datetime] NOT NULL,
[Solddate] [datetime] NULL,
[CrDate] [datetime] DEFAULT getdate()
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Itemno] ON [dbo].[tbItems]([itemno] ASC)
Insert into tbItems (itemno,Purchasedate,solddate )
values
( 1000, dateadd(month,-14, getdate()), Null
),
( 1001, dateadd(month,-13, getdate()), dateadd(month,-13, getdate())
)
,( 1002, dateadd(month,-12, getdate()), dateadd(month,-11, getdate())
)
,( 1003, dateadd(month,-12, getdate()), dateadd(month,-10, getdate())
)
,( 1004, dateadd(month,-10, getdate()), dateadd(month,-10, getdate())
)
,( 1005, dateadd(month,-10, getdate()), dateadd(month,-7, getdate())
)
,( 1006, dateadd(month,-9, getdate()), Null
)
,( 1007, dateadd(month,-8, getdate()), Null
)
,( 1008, dateadd(month,-8, getdate()), Null
)
,( 1009, dateadd(month,-7, getdate()), Null
)
,( 1010, dateadd(month,-5, getdate()), Null
)
select * from tbItems
Given active inventory by month is "Nov 2011"
1000 (Should show active on Jul 2011 till today, no sold date)
1003 (Should show active on Sep 2011, Oct 2011 and Nov 2011 )
1004 (Should show active on Nov 2011 only )
1005 (Should show active on Nov 2011 only, even sold on Feb 2012 )
This table has no active flag by month, and no any other table to know which items were active on any given time
so, I depend on 2 columns (Purchasedate,solddate)
My question is: what is best way to find active inventory for our accounting by given month and year.
they want to know which items were active on Nov 2011 or any month or year (doesn't matter the day or time)
what is the best possible and correct way to do it?
Thank you all for your answers
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply