|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 3rd, 2005, 01:35 PM
|
|||
|
|||
SQL Loops
Is it possible to loop through a range of dates? I am tring to INSERT records into a table by SELECTing records from another within a certain date range, except I dont want to have to run the stored procedure over and over again to get it.
I am trying something like this in MS SQL INSERT INTO tblB Select SUM(column1) WHERE date BETWEEN @date1 AND @date2 I need to repeat this in 15 min intervals So I get the following Sum for first 15 min of daterange Sum for second 15 min daterange Sum for third 15 min daterange .... From @startdate to @enddate (range is one day or one week) I am new to SQL, any help is appreciated. I am also calling this from VB.NET so if the two can be combined.
|
|
#4
May 3rd, 2005, 09:56 PM
|
|||
|
|||
This code avoids loops and produces sales sums in 15 minute intervals:
Code:
DECLARE @StartDT DATETIME, @EndDT DATETIME SET @StartDT = '5/3/05 0:00' SET @EndDT = '5/3/05 23:59:59' SELECT datepart(dy,SaleDateTime) AS DateBlock, (datepart(hh,SaleDateTime)*60+datepart(n,SaleDateT ime))/15 AS QuarterHourBlock, sum(SaleAmount) FROM tblTimeSales WHERE (SaleDateTime BETWEEN @StartDT AND @EndDT) GROUP BY datepart(dy,SaleDateTime), (datepart(hh,SaleDateTime)*60+datepart(n,SaleDateT ime))/15 ORDER BY datepart(dy,SaleDateTime), (datepart(hh,SaleDateTime)*60+datepart(n,SaleDateT ime))/15 The integer division produces the desired result. In your stored procedure, use a date or date range to specify the time period for data summation. You'll need some additional code to handle the date validation and nulling if needed. Hope this helps.
|
|
#6
May 4th, 2005, 11:24 AM
|
|||
|
|||
|
Code:
DECLARE @StartDT DATETIME,
@EndDT DATETIME,
@LocationID uniqueidentifier
SET @StartDT = '4/25/05 11:00:00'
SET @EndDT = '4/26/05 03:00:00'
SET @LocationID = '{EF637600-C8EB-4883-BF82-9E9FA7C001C1}'
SELECT datepart(dy,PullTime) AS DateBlock, (datepart(hh,PullTime)*60+datepart(n,PullTime))/15 AS QuarterHourBlock, SUM(DollarValueSold)
FROM tbl_MenuItemDailySales
WHERE (PullTime BETWEEN @StartDT AND @EndDT) AND (LocationID = CONVERT(uniqueidentifier, @LocationID)) AND (Category = 1) AND (Complete = 1)
GROUP BY datepart(dy,PullTime), (datepart(hh,PullTime)*60+datepart(n,PullTime))/15
ORDER BY datepart(dy,PullTime), (datepart(hh,PullTime)*60+datepart(n,PullTime))/15
Produces DateBlock QuarterHourBlock ----------- ---------------- ---------- 116 3 2115.72 No more results. (1 row(s) returned) @RETURN_VALUE = 0 Is there a way so that it will split the groups up into 15 min records?
|
|
#7
May 4th, 2005, 11:58 AM
|
|||
|
|||
|
The code looks right but according to the output, all sales occurred on day 116 in the fourth quarter hour (blocks are zero based). That is, all sales occurred between 0:45 AM and 0:59:59 AM. Is that what the source data says? Also, you may want to try datepart(mi,PullTime) in place of datepart(n,PullTime) though they should both produce the minutes.
I have tried this against sample data and it did work.
|
|
#8
May 4th, 2005, 12:19 PM
|
|||
|
|||
|
Could you send me a copy or sample of your data you are using? It may help me identify the problem. Also the sales are spread out during the day. Also if I change the date range to be the same day 4/25/2005 11:00 to 4/25/2005 23:00 I get no rows returned.
|
|
#9
May 4th, 2005, 01:06 PM
|
|||
|
|||
|
This is the table script:
Code:
/****** Object: Table [dbo].[tblTimeSales] Script Date: 5/4/2005 11:57:51 AM ******/ CREATE TABLE [dbo].[tblTimeSales] ( [SDTID] [int] IDENTITY (1, 1) NOT NULL , [SaleDateTime] [datetime] NOT NULL , [SaleAmount] [money] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[tblTimeSales] WITH NOCHECK ADD CONSTRAINT [PK_tblTimeSales] PRIMARY KEY CLUSTERED ( [SDTID] ) ON [PRIMARY] GO This is the raw data: SDTID SaleDateTime SaleAmount 1 2005-05-05 00:01:00.000 21.3700 2 2005-05-05 00:07:00.000 14.5300 3 2005-05-05 00:08:00.000 5.1200 4 2005-05-05 02:34:00.000 61.3000 5 2005-05-05 02:42:00.000 30.0000 6 2005-05-05 04:17:00.000 4.7600 7 2005-05-05 04:19:00.000 14.0100 8 2005-05-05 05:20:00.000 12.1200 9 2005-05-05 05:25:00.000 12.1000 10 2005-05-05 05:27:00.000 45.9700 11 2005-05-05 21:10:00.000 29.5000 12 2005-05-06 08:05:00.000 15.0500 13 2005-05-06 08:10:00.000 34.0500 14 2005-05-06 12:51:00.000 23.8500 15 2005-05-06 12:55:00.000 8.2500 16 2005-05-06 01:04:00.000 120.9900 (16 row(s) affected) This is the output for 5/5/05 0:00 to 5/5/05 23:59:59 DateBlock QuarterHourBlock QuarterHourSum NumOfSales 125 0 41.0200 3 125 10 91.3000 2 125 17 18.7700 2 125 21 70.1900 3 125 84 29.5000 1 (5 row(s) affected) I have added a count to simply verification. This was done using SQL Server 2000 though I did the original at home in SQL Server 7.0. Just a thought; locale setting?
|
|
#11
May 4th, 2005, 02:29 PM
|
|||
|
|||
|
I put seconds into the data and got the same result. It shouldn't make any difference since seconds are not part of the calculation. You may want to look at the raw output without the group by and sum functions to see if the blocks are being properly calculated. You may also want to recreate my table and test that data. If it doesn't work, I'm at a loss (at least temporarily) to explain why.
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > SQL Loops |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
