July 29, 2015 at 7:12 pm
My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:
create table #tmp_CusttInfo
(patID varchar(20),
enc_date datetime)
go
insert into #tmp_CustInfo
select '111','2015-01-01 09:25:05.000'
union
select '111','2015-01-01 10:25:05.000'
union
select '111','2015-02-01 09:25:05.000'
union
select '111','2015-03-01 09:25:05.000'
union
select '111','2015-03-01 10:25:05.000'
union
select '111','2015-04-01 09:25:05.000'
union
select '111','2015-05-01 09:25:05.000'
union
select '111','2015-05-01 10:25:05.000'
union
select '111','2015-06-01 09:25:05.000'
union
select '222','2015-01-01 09:25:05.000'
union
select '222', '2015-01-01 10:25:05.000'
union
select '222','2015-02-01 09:25:05.000'
union
select '222','2015-03-01 09:25:05.000'
union
select '222','2015-03-01 10:25:05.000'
union
select '222','2015-04-01 09:25:05.000'
union
select '222','2015-05-01 09:25:05.000'
union
select '222','2015-05-01 10:25:05.000'
union
select '222','2015-06-01 09:25:05.000'
I'd like to get:
CustID OrderDate
111 2015-01-01 09:25:05.000
111 2015-03-01 09:25:05.000
111 2015-05-01 09:25:05.000
222 2015-01-01 09:25:05.000
222 2015-03-01 09:25:05.000
222 2015-05-01 09:25:05.000
Is that possible. Thanks a milliion.
Minh
July 29, 2015 at 8:13 pm
joemai (7/29/2015)
My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:create table #tmp_CusttInfo
(patID varchar(20),
enc_date datetime)
go
insert into #tmp_CustInfo
select '111','2015-01-01 09:25:05.000'
union
select '111','2015-01-01 10:25:05.000'
union
select '111','2015-02-01 09:25:05.000'
union
select '111','2015-03-01 09:25:05.000'
union
select '111','2015-03-01 10:25:05.000'
union
select '111','2015-04-01 09:25:05.000'
union
select '111','2015-05-01 09:25:05.000'
union
select '111','2015-05-01 10:25:05.000'
union
select '111','2015-06-01 09:25:05.000'
union
select '222','2015-01-01 09:25:05.000'
union
select '222', '2015-01-01 10:25:05.000'
union
select '222','2015-02-01 09:25:05.000'
union
select '222','2015-03-01 09:25:05.000'
union
select '222','2015-03-01 10:25:05.000'
union
select '222','2015-04-01 09:25:05.000'
union
select '222','2015-05-01 09:25:05.000'
union
select '222','2015-05-01 10:25:05.000'
union
select '222','2015-06-01 09:25:05.000'
I'd like to get:
CustID OrderDate
111 2015-01-01 09:25:05.000
111 2015-03-01 09:25:05.000
111 2015-05-01 09:25:05.000
222 2015-01-01 09:25:05.000
222 2015-03-01 09:25:05.000
222 2015-05-01 09:25:05.000
Is that possible. Thanks a milliion.
Minh
Based upon your expected results, there has to be more rules than what you've said so far. For instance, let's take a look at just patID = '111':
Why do the results have only the first sale for a date?
The first row has no sales in the prior 2 months. Why is it included in the results?
Same for the second and third rows.
Why do the results only have odd months?
Please outline all of the rules that are necessary to get these results.
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 29, 2015 at 9:58 pm
We use the first sale to pick the first sale of the next 2 month. That's the only rule as they gave me. We know that is the routine.
Thanks,
Minh
July 29, 2015 at 10:59 pm
Quick example of how this can be done, you should be able to work it from there.
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'tempdb..#tmp_CustInfo') IS NOT NULL DROP TABLE #tmp_CustInfo;
CREATE TABLE #tmp_CustInfo
(patID varchar(20),
enc_date datetime)
GO
INSERT INTO #tmp_CustInfo
VALUES
('111','2015-01-01 09:25:05.000'),('111','2015-01-01 10:25:05.000')
,('111','2015-02-01 09:25:05.000'),('111','2015-03-01 09:25:05.000')
,('111','2015-03-01 10:25:05.000'),('111','2015-04-01 09:25:05.000')
,('111','2015-05-01 09:25:05.000'),('111','2015-05-01 10:25:05.000')
,('111','2015-06-01 09:25:05.000'),('222','2015-01-01 09:25:05.000')
,('222','2015-01-01 10:25:05.000'),('222','2015-02-01 09:25:05.000')
,('222','2015-03-01 09:25:05.000'),('222','2015-03-01 10:25:05.000')
,('222','2015-04-01 09:25:05.000'),('222','2015-05-01 09:25:05.000')
,('222','2015-05-01 10:25:05.000'),('222','2015-06-01 09:25:05.000');
/* Nice to have index on the datetime column */
CREATE NONCLUSTERED INDEX NCLIDX_TMP_TCI_DATE ON #tmp_CustInfo(enc_date ASC) INCLUDE(patID);
/* Set this variable to the desired date, in this case it is T - 3 months, the query brings
back anything after this date
*/
DECLARE @NOW DATETIME = CONVERT(DATETIME,GETDATE(),0);
SELECT
TCI.patID
,TCI.enc_date
FROM #tmp_CustInfo TCI
WHERE TCI.enc_date > DATEADD(MONTH,-3,@NOW);
Results
patID enc_date
-------------------- -----------------------
111 2015-05-01 09:25:05.000
222 2015-05-01 09:25:05.000
111 2015-05-01 10:25:05.000
222 2015-05-01 10:25:05.000
111 2015-06-01 09:25:05.000
222 2015-06-01 09:25:05.000
July 30, 2015 at 11:52 am
--Try this
SELECT
x.patID ,
x.Mdt
FROM
( SELECT
patID ,
MIN(tci.enc_date) AS Mdt ,
CONVERT(DATE, tci.enc_date) AS date
FROM
#tmp_CustInfo AS tci
GROUP BY
patID ,
CONVERT(DATE, tci.enc_date)
) x
WHERE
CONVERT(INT, LEFT(REPLACE(x.date, '-', ''), 6)) % 2 = 1
ORDER BY
x.patID;
--Note: you do not have to use convert inside subquery
July 30, 2015 at 3:10 pm
This is really great! Thank you so much for your help Mr. Grasshopper.
It worked exactly the way I wanted.
July 30, 2015 at 4:39 pm
Glad I could be a help!
But I think I am Mr. SolveSQL!
Grasshopper is what SQL central assigned my level to be ??
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply