Monthly Report

Question

Post reply

Monthly Report

Roy Rogers

SSCertifiable

Points: 7712
More actions
August 4, 2005 at 9:55 am

#167834

SET NOCOUNT ON

DECLARE @MyTable TABLE
(
Code                        VARCHAR(12),
InstallCounts            INT,
InstallDate                DATETIME
)

DECLARE @Ctr INT
DECLARE @Code VARCHAR(12)
DECLARE @InstallCounts INT
DECLARE @InstallDate DATETIME
DECLARE @DelayFor    CHAR(12)
DECLARE @MonthDate   DATETIME

SET @Code = '1.2.3.4'

SET @Ctr = 0

WHILE CONVERT(DATETIME,DATEADD(DAY, @Ctr, '05/01/2005')) < CONVERT(DATETIME,'08/01/2005')
BEGIN

SET @InstallDate = DATEADD(DAY, @Ctr, '05/01/2005')
SET @InstallCounts = CONVERT(INT, SUBSTRING(REVERSE(CONVERT(VARCHAR,RAND(DATEPART(ms, GETDATE())))), 1, 2))
SET @DelayFor = '000:00:00:' + CONVERT(VARCHAR,@InstallCounts)
WAITFOR DELAY @DelayFor
INSERT @MyTable (Code, InstallCounts, InstallDate) VALUES (@Code, @InstallCounts, @InstallDate)

SET @Ctr = @Ctr + 1
END

SET @Code = '1.3.4.5'

SET @Ctr = 0

WHILE CONVERT(DATETIME,DATEADD(DAY, @Ctr, '05/01/2005')) < CONVERT(DATETIME,'08/01/2005')
BEGIN

SET @InstallDate = DATEADD(DAY, @Ctr, '05/01/2005')
SET @InstallCounts = CONVERT(INT, SUBSTRING(REVERSE(CONVERT(VARCHAR,RAND(DATEPART(ms, GETDATE())))), 1, 2))
SET @DelayFor = '000:00:00:' + CONVERT(VARCHAR,@InstallCounts)
WAITFOR DELAY @DelayFor
INSERT @MyTable (Code, InstallCounts, InstallDate) VALUES (@Code, @InstallCounts, @InstallDate)

SET @Ctr = @Ctr + 1
END

SET @Code = '2.1.2.3'

SET @Ctr = 0

WHILE CONVERT(DATETIME,DATEADD(DAY, @Ctr, '05/01/2005')) < CONVERT(DATETIME,'08/01/2005')
BEGIN

SET @InstallDate = DATEADD(DAY, @Ctr, '05/01/2005')
SET @InstallCounts = CONVERT(INT, SUBSTRING(REVERSE(CONVERT(VARCHAR,RAND(DATEPART(ms, GETDATE())))), 1, 2))
SET @DelayFor = '000:00:00:' + CONVERT(VARCHAR,@InstallCounts)
WAITFOR DELAY @DelayFor
INSERT @MyTable (Code, InstallCounts, InstallDate) VALUES (@Code, @InstallCounts, @InstallDate)

SET @Ctr = @Ctr + 1
END

SELECT * FROM @MyTable

/* I need a result set like when a month is passed */
/* Example July 2005 */

Code
Jul 1, 2005
Jul 2, 2005
…
Jul 31, 2005
1.2.3.4
xxx
xxx
…
xxx
1.3.4.5
xxx
xxx
…
xxx
2.1.2.3
xxx
xxx
…
xxx

Regards,
gova

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Why the waitfors?

The code appears to wait, you just need to take a parameter for the month, or two, like

declare @mn char(2), @yr char(4)

declare @start datetime, @end datetime

select @start = cast( @mn + '/01/' + @yr ) as datetime

select @end = dateadd( m, 1, @start)

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

The script is only for inserting the data into @MyTable.

SELECT * FROM @MyTable

Will give you the table with data to test the queries. I wanted to give Random numbers in the data. If I dont make wait Randon function gives same numbers. Don't worry about the script to generate data. It will Insert data and give you a table with data for testing the queries.

I have table like @MyTable and I need an out put like

Code

Jul 1, 2005

Jul 2, 2005

…

Jul 31, 2005

1.2.3.4

xxx

…

xxx

1.3.4.5

xxx

…

xxx

2.1.2.3

xxx

…

xxx

I can't think a set based approch for that. I am making a procedural loop which has performance problems.

Regards,
gova

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

Noel gave me an answer to one of my queries once. I tweeked it a liitle got the answer. Thanks Noel.

SELECT Code,

DAY1 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 1 THEN InstallCounts ELSE 0 END),

DAY2 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 2 THEN InstallCounts ELSE 0 END),

DAY3 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 3 THEN InstallCounts ELSE 0 END),

DAY4 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 4 THEN InstallCounts ELSE 0 END),

DAY5 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 5 THEN InstallCounts ELSE 0 END),

DAY6 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 6 THEN InstallCounts ELSE 0 END),

DAY7 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 7 THEN InstallCounts ELSE 0 END),

DAY8 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 8 THEN InstallCounts ELSE 0 END),

DAY9 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 9 THEN InstallCounts ELSE 0 END),

DAY10 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 10 THEN InstallCounts ELSE 0 END),

DAY11 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 11 THEN InstallCounts ELSE 0 END),

DAY12 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 12 THEN InstallCounts ELSE 0 END),

DAY13 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 13 THEN InstallCounts ELSE 0 END),

DAY14 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 14 THEN InstallCounts ELSE 0 END),

DAY15 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 15 THEN InstallCounts ELSE 0 END),

DAY16 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 16 THEN InstallCounts ELSE 0 END),

DAY17 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 17 THEN InstallCounts ELSE 0 END),

DAY18 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 18 THEN InstallCounts ELSE 0 END),

DAY19 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 19 THEN InstallCounts ELSE 0 END),

DAY20 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 20 THEN InstallCounts ELSE 0 END),

DAY21 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 21 THEN InstallCounts ELSE 0 END),

DAY22 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 22 THEN InstallCounts ELSE 0 END),

DAY23 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 23 THEN InstallCounts ELSE 0 END),

DAY24 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 24 THEN InstallCounts ELSE 0 END),

DAY25 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 25 THEN InstallCounts ELSE 0 END),

DAY26 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 26 THEN InstallCounts ELSE 0 END),

DAY27 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 27 THEN InstallCounts ELSE 0 END),

DAY28 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 28 THEN InstallCounts ELSE 0 END),

DAY29 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 29 THEN InstallCounts ELSE 0 END),

DAY30 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 30 THEN InstallCounts ELSE 0 END),

DAY31 = SUM(CASE WHEN DATEPART(DAY, InstallDate) = 31 THEN InstallCounts ELSE 0 END)

FROM

@MyTable

WHERE

DATEDIFF(MONTH, InstallDate, '07/01/2005') = 0

GROUP BY

Code

Regards,
gova

noeld SSC Guru Points: 96590 More actions · Answer 4

I don't even remember but if you said so I believe you and ... you're welcome!?

[EDITED] I fixed the spelling... not a good spelling day for me

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

August 4, 2005 at 1:27 pm

#579804

That should be : you're welcomed again .

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=194493

This is where I got the logic.

Regards,
gova