Find the availability on the user
-
Hi there,
this is the sample table
create table #sample
(
Productid varchar(10),
purchasedate datetime,
customerid varchar(100)
)
this table used capture the purchase details of customers.
1. need to create report for purchasedate 2013-07 to 2013-12
2. I need to find number of customers who purchased in 2013-07 and contiguously purchased up to 2013-12
this is required for every time slab..
how many purchased 2013-07 to 2013-08
how many purchased 2013-07 to 2013-09
how many purchased 2013-07 to 2013-10
how many purchased 2013-07 to 2013-11
how many purchased 2013-07 to 2013-12
Thanks
-
Sounds like homework to me. What have you tried?
John
-
John Mitchell-245523 (1/17/2014)
Sounds like homework to me. What have you tried?John
I did this in a very complex way..
steps :
1. created columns for every month
2. update corresponding month column if customer found on that month
following(attachment) is what I exactly want..
-
You still haven't shown us what you've tried. But from your required results, it looks as if you need a PIVOT query. If you search for "pivots and crosstabs" you'll find lots of suggestions on how to do it.
John
-
John Mitchell-245523 (1/17/2014)
You still haven't shown us what you've tried. But from your required results, it looks as if you need a PIVOT query. If you search for "pivots and crosstabs" you'll find lots of suggestions on how to do it.John
create table #DataTable
(
Productid varchar(10),
purchasedate datetime,
customerid varchar(100)
)
select * into #JulyCustomers from #DataTable where convert(varchar(6),purchasedate ,112) = 201307
alter table #JulyCustomers add aug2013 tinyint,sep2013 tinyint,oct2013 tinyint,nov2013 tinyint,dec2013 tinyint
update #JulyCustomers set aug2013 = 1
from #JulyCustomers jc
join (select * from #DataTable where convert(varchar(6),purchasedate ,112) = 201308) dt
on jc.customerid = dt.customerid
and jc.Productid = dt.Productid
update #JulyCustomers set sep2013 = 1
from #JulyCustomers jc
join (select * from #DataTable where convert(varchar(6),purchasedate ,112) = 201309) dt
on jc.customerid = dt.customerid
and jc.Productid = dt.Productid
where aug2013= 1
vise versa for remaining months
I need to optimize this ...
-
Sounds like a "gaps and islands" question. You're looking for the maximum size of an island.
-
pietlinden (1/18/2014)
Sounds like a "gaps and islands" question. You're looking for the maximum size of an island.Exactly! but I cant find any efficient way... help me out...
-
I would probably look for an article by Itzik Ben-Gan on it... being that he's a lot smarter than I am.
-
http://www.manning.com/nielsen/SampleChapter5.pdf
From SQL Server Deep Dives. I think I need to buy this book... <g>
There is also this article...
http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions
using LAG/LEAD. definitely worth a read!
-
pietlinden (1/20/2014)
http://www.manning.com/nielsen/SampleChapter5.pdfFrom SQL Server Deep Dives. I think I need to buy this book... <g>
There is also this article...
http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions
using LAG/LEAD. definitely worth a read!
LAG/LEAD, while interesting, are certainly not the fastest way to find gaps.
The Performance of the T-SQL Window Functions [/url]
One of the test harnesses in that article contains several ways to calculate gaps that are faster.
Assuming of course that this is what you need.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
So you've posted DDL which is a good start:
vignesh.ms (1/17/2014)
this is the sample tablecreate table #sample(
Productid varchar(10),
purchasedate datetime,
customerid varchar(100)
);
And expected output in the pretty table you posted. Now how about some consumable sample data to populate the #sample table with?
I'm thinking this is not a gaps problem at all. Rather it is a problem that can be solved with a window frame.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply