Need some help - 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

General SQL Server Forums

Database Design and Application Architecture

Need some help

Author

Topic

LEOx037
Starting Member

12 Posts

Posted - 2010-06-22 : 20:58:15

Forgive me if I have posted this in the wrong place but I need some help. Here's my problem.

I need to generate a report to display sales data from our stores across the nation. At this time we want the report to show the most active 15 minutes for the time the store front is open. Seems simple but this is what I have to work with:

1. I get all the data from two SQL server and I store the fields I need into one master table.
2. I then filter the data (via views) to get me summary data. For example:

PCID StandID Trans_Date mTimeStart CountOfTransID SumOfSumOfItemQty SumOfSumOfItemUnitPrice
610639300 101 6/8/2010 1640 1 8 12.25
610639300 101 6/8/2010 1646 1 1 4
610639300 101 6/8/2010 1647 2 8 31
610639300 101 6/8/2010 1653 1 1 2
610639300 101 6/8/2010 1654 1 5 12.25
610639300 101 6/8/2010 1658 1 5 12.25
610639300 101 6/8/2010 1700 1 1 4
610639300 101 6/8/2010 1704 2 6 14
610639300 101 6/8/2010 1705 1 3 8.5
...

PCID = store Id
StandID = stands in the store (most cases more than one)
mTimeStart = hour and minutes
CountOfTransID = Transaction count
SumOfSumOfItemQty = sum of items sold per total transaction per min
SumOfSumOfItemUnitPrice = sum of sales per total transaction per min

Most of the store fronts are open for 3-4 hours. And here's the trick, I have to loop through all the data and find the most active 15 for the 3-4 hour opening for each store.

I would be glad to give more information. I need to find a way to loop through the data and calculate the most active 15 minutes. I am not looking to report data for each 15 minutes.

I hope someone can help? Thank you.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-23 : 02:49:33

To have any success with this you'll need to convert the Trans_Date and mTimeStart columns to one datetime column. If you do that I *think* this is what you need (it seemed to work with my sample data):



DECLARE @dummydate datetime = '2010-06-23 00:00:00'

;WITH cte AS (
	SELECT 
		[Mod] = DATEDIFF(MILLISECOND, @dummydate, transtime) % (15 * 60 * 1000), --> 15 mins to milliseconds
		*
	FROM mastertable
	)
SELECT 
	PCID,
	Period = DATEADD(MILLISECOND, -[Mod], transtime), 
	CountOfTransID = MAX(SUM(CountOfTransID))
FROM cte
GROUP BY PCID, DATEADD(MILLISECOND, -[Mod], transtime)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

LEOx037
Starting Member

12 Posts

Posted - 2010-06-23 : 13:11:12

Hello Lumbago,

Thanks for the response.

I would like to clarify why I have the Trans_Date and mTimeStart fields formatted as they are. I needed to sum the transactions into minutes so I converted the original date/time into two fields (Date and military time). I then sum the data transactions of all sales within the same minutes.

For example,

06/08/2010 1647 <---- 2010-06-08 16:47:04.000
06/08/2010 1647 <---- 2010-06-08 16:47:57.000

So in this case I would need to drop the seconds and milliseconds. How could I go about do that so that I would get the following:

2010-06-08 16:47:00.000
2010-06-08 16:47:00.000

Also, the script you posted, is that SQL or SP?

Regards

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-23 : 13:56:05

quote:
Originally posted by LEOx037

Also, the script you posted, is that SQL or SP?

Regards

It is T-SQL 2005 +

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-24 : 02:20:28

Hi Leo,

the script I posted is pure TSQL and I actually created a blog post about your problem because it's not something you come by every day. You can see it here where I also generate a sample table with some sample data: http://thefirstsql.com/2010/06/23/finding-the-most-active-15-minute-time-period/. If you run the example in the blog post you'll see that it's doing what you need it to do, just with another data set. The concept is however the same.

About the grouping by minute: you can still do this with a datetime column. I'm sure there is a better way than this but for starters you could do SELECT CAST(CONVERT(VARCHAR(16), GETDATE(), 120) AS DATETIME). I'll see if I can find another way.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-24 : 04:51:42

I think this one is better performing than the last one since it doesn't involve any data type conversion: SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()), 0)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

LEOx037
Starting Member

12 Posts

Posted - 2010-06-24 : 09:35:35

Hi Lumbago,

Thanks you very much for you help. I'm going to review your solution and hopefully all will go well. I will keep you informed.

Again, thank you very much.
Leo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources