December 9, 2008 at 1:04 pm
I have an existing stored procedure to do counts on multiple tables based on a static date range input by the User as a simple startdate and enddate. I need to further break thiis down by frequency. For example startdate = 11/1/2008 and enddate = 12/1/2008 and frequency = 7 days. So I would need a count for 11/1-11/8, AND 11/9-11/16, AND 11/17-11/24 AND 11/25-12/1. And if the next date frequency goes to a date PAST the End date then set the end of the range to the global end date. The current temp table takes the count from several different select statements, CONT1 is the example here, but there are about 10 individual selects for counts that currently use the simple date range. But I now need to break the date range down by frequency and send the resulting data to Crystal.
CREATE PROCEDURE [dbo].[sp_Trend]
@StartDate varchar(20),
@Enddate varchar(20),
@User numeric
DECLARE @FROM AS DATETIME
DECLARE @TO AS DATETIME
DECLARE @createdby AS NUMERIC
DECLARE @UMVISIT AS INT
CREATE TABLE #TEMPTREND(CREATEDBY NUMERIC,COUNT1INT, COUNT2 INT,COUNT3 INT, COUNT4 INT)
--COUNT1
SELECT @count1=COUNT(*)
FROM PROP_MEETING_GEN PMG
left outer JOIN PROP_X_MEET_PERSN PXMP ON
PXMP.MEETING = PMG.REFERENCE
left outer JOIN ENTITY_TABLE ET ON ET.ENTITY_ID=PMG.REFERENCE
left outer join LK_ENTITY_ROLE LER ON
LER.ENTITY_ID=PXMP.PERSON
WHERE PMG.MEET_STAT=8259042 AND LER.ROLE_ID=111 AND ET.CREATED_BY=@CREATEDBY AND PMG.STARTDATE BETWEEN @FROM2 AND @TO2
INSERT INTO #TEMPTREND VALUES(@CREATEDBY,@COUNT1,@COUNT2,@COUNT3,@COUNT4)
If anyone has any ideas let me know
Thank You
December 9, 2008 at 2:17 pm
You can do this using a Tally table.
See the following article for an explanation of what a Tally Table is:
http://www.sqlservercentral.com/articles/TSQL/62867/
---[Edit]---
Here is an example:
[font="Courier New"]DECLARE
@StartDate VARCHAR(20),
@Enddate VARCHAR(20),
@User numeric,
@Freq VARCHAR(20)
SET @Freq = 7
SET @StartDate = '1/1/2008'
SET @EndDate = '3/1/2008'
SELECT IC.N,
IC.StartDate,
IC.EndDate,
( SELECT COUNT(*) FROM SomeTable
WHERE open_date BETWEEN IC.StartDate AND IC.EndDate) SomeCount
FROM ( SELECT N,
DATEADD(d, (@Freq * (N-1)),@StartDate) StartDate,
CASE WHEN
DATEADD(d,(@Freq * N),@StartDate) > @EndDate THEN @EndDate
ELSE DATEADD(d,(@Freq * N),@StartDate)
END EndDate
FROM dbo.Tally T
WHERE T.N <= DATEDIFF(d,@StartDate,@EndDate) / @Freq + 1 -- Restrict Tally Rows
) IC
ORDER BY N[/font]
December 9, 2008 at 2:50 pm
December 10, 2008 at 10:25 am
Thanks for the help!
The select works to get me the counts. And the idea of the Tally table does make the selects more simplistic. I just have to figure out how to make each date "range" a declared variable or column to add it to my TEMP table that already exists for each of the database pulls for data counts.
i.e.
Create one table with the date ranges (in your example the IC table), using the 'global' start and end date parameters
Pull from that table for each count I do based on a separate select for data, i.e where date is between startdate and enddate ranges in the temp table
Dump the date ranges into another table with THE FOLLOWING
STARTDATE1, ENDDATE1, COUNT1, COUNT2, COUNT3
So I have to have a declared variable for the interim start and end date based on the ranges to dump it all into the temp table with the date ranges listed and the resulting counts in the remaining columns for the selects that do the counting
ALTER PROCEDURE [dbo].[sp_STDate]
@StartDate varchar(20),
@Enddate varchar(20),
@User numeric,
@Freq integer
AS
DECLARE @createdby AS NUMERIC
DECLARE @COUNT1 AS INT
DECLARE @STARTDATE1 AS VARCHAR(20)
DECLARE @ENDDATE1 AS VARCHAR(20)
CREATE TABLE #TEMPTREND(STARTDATE1 VARCHAR(20), ENDDATE1 VARCHAR(20), CREATEDBY NUMERIC,COUNT1 INT)
DECLARE CUR_TREND CURSOR FOR
SELECT
DISTINCT CONSULTANT
FROM
PROP_YOH_GEN PYG
LEFT OUTER JOIN PROP_OWN_CONS POC ON POC.REFERENCE = PYG.REFERENCE
WHERE PYG.STATUS='8260000' AND POC.CONSULTANT = @User
OPEN CUR_TREND
FETCH NEXT FROM CUR_TREND INTO @createdby
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@COUNT1=COUNT(*)
FROM PROP_MEETING_GEN PMG
left outer JOIN PROP_X_MEET_PERSN PXMP ON
PXMP.MEETING = PMG.REFERENCE
left outer JOIN ENTITY_TABLE ET ON ET.ENTITY_ID=PMG.REFERENCE
left outer join LK_ENTITY_ROLE LER ON
LER.ENTITY_ID=PXMP.PERSON
WHERE PMG.MEET_STAT=8259042 AND LER.ROLE_ID=111 AND ET.CREATED_BY=@CREATEDBY and PMG.STARTDATE BETWEEN StartDate AND EndDate
INSERT INTO #TEMPTREND VALUES(@STARTDATE, @ENDDATE, @createdby,@COUNT1)
FETCH NEXT FROM CUR_TREND INTO @createdby
END
CLOSE CUR_TREND
DEALLOCATE CUR_TREND
SELECT CREATEDBY,U.USER_NAME AS STARTDATE, ENDDATE, USERID,COUNT1
FROM
#TEMPTREND T,CONFIGUSERS1.DBO.USERS U
WHERE U.USER_ID = T.CREATEDBY
DROP TABLE #TEMPTREND
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply