Query for frequencies - 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)

Query for frequencies

Author

Topic

guiz
Starting Member

7 Posts

Posted - 2011-10-05 : 06:23:36

Hi friends, let's say I have a table and data as follows:

datesold | prdtype

01/15/2010 | a
01/22/2010 | b
01/29/2010 | a
02/05/2010 | a
02/12/2010 | b
02/26/2010 | a
03/19/2010 | a
03/26/2010 | a
04/02/2010 | a
04/09/2010 | b

what t-sql query command could be used in order to produce a result like the following:

prdtype | sold in no. of weeks | frequencies
a | 1 | 3
a | 2 | 1
a | 3 | 2
b | 3 | 1
b | 8 | 1

Please, help. Thank you.
Cheers,
Guiz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 06:31:18

can you tell how you calculate value for sold in no. of weeks ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shatrughna
Starting Member

1 Post

Posted - 2011-10-05 : 07:00:14

Hi,
It is totally depend upon on your calculation logic of week.

DECLARE @Sold TABLE
(datesold DATETIME,prdtype CHAR(1))

INSERT INTO @Sold
SELECT '01/15/2010','a' UNION ALL
SELECT '01/16/2010','a' UNION ALL
SELECT '01/22/2010','b' UNION ALL
SELECT '01/21/2010','b' UNION ALL
SELECT '01/29/2010','a' UNION ALL
SELECT '02/05/2010','a' UNION ALL
SELECT '02/12/2010','b' UNION ALL
SELECT '02/26/2010','a' UNION ALL
SELECT '03/19/2010','a' UNION ALL
SELECT '03/26/2010','a' UNION ALL
SELECT '04/02/2010','a' UNION ALL
SELECT '04/09/2010','b'

SELECT prdtype,DATEPART(ww, datesold) AS 'sold in no. of weeks',
COUNT(*) AS 'Frequencies' FROM @Sold
GROUP BY prdtype,DATEPART(ww, datesold)

guiz
Starting Member

7 Posts

Posted - 2011-10-05 : 07:17:28

@visakh, 'sold in no of weeks' is calculated by counting tallies for week1, week2, week3 and so on for a prdtype and keeping the last date to compare with the next date. For example, prdtype a was sold on 01/15/2010 and sold again on 01/29/2010, thus week2 tallies is incremented. Then, prdtype a was sold again on 02/05/2010, so week1 tallies is incremented. But I don't know how to do it in t-sql. Thank you for your kind attention.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 07:37:17

sorry your explanation doesnt make much sense.
how 3rd selling again adds to week1 tallies itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

guiz
Starting Member

7 Posts

Posted - 2011-10-05 : 08:12:02

@visakh16, I produce tallies like...

prdA | prdB
1 week lll | 0
2 week l | 0
3 week ll | l
8 week 0 | l

So, there are 3 prd type A sold in a skip of one week, 1 prd type A sold in a skip of two weeks, and so on. I hope it is clearer. If not, please, let me know, I'll try to come up with a better explanation. Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 08:21:07

[code]
select t.prdtype ,
(DATEDIFF(dd,t1.datesold ,t.datesold )/7) as [sold in no. of weeks],
COUNT(*) AS [Frequencies]
from @Sold t
cross apply (select top 1 datesold
from #temp
where prdtype =t.prdtype
and datesold < t.datesold
order by datesold desc) t1
GROUP BY t.prdtype ,(DATEDIFF(dd,t1.datesold ,t.datesold )/7)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

guiz
Starting Member

7 Posts

Posted - 2011-10-05 : 08:32:47

@visakh16, supposedly there is a lottery 6/49 winning number, i.e. no 18 came out on 01/01/2000, 01/08/2000, 01/22/2000, 01/29/2000, 02/12/2000. So I could say that with no 18, there are 2 occurences in one week which are (01/01/2000 to 01/08/2000), and (01/22/2000 to 01/29/2000), similarly, there are 2 occurences of two skipped weeks. My intention is to find seasonality characteristics in those prdtype, but i have too many prdtypes to deal with. I hope it's clear how I calculate 'sold in no of weeks' Thank's for kind attention.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 08:37:06

sorry i dont know what you're talking about. I dont see any lottery fields here. As for me I can see only datesold and prdtype which is what you posted. So if you can explain relative to that it would be great. Alternatively post your original query with sample data as in last post and I will try to understand your problem.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

guiz
Starting Member

7 Posts

Posted - 2011-10-05 : 08:51:49

Thank you for your help. Really appreciate it. It's exactly what I want.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 11:08:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources