February 17, 2010 at 12:05 am
Hello everyone!
For each row in the table, we have a creation date. Based on only this date column, I need to provide a report in such way that for this time slot on this day what are the record count created.
For example:-
Time Slots:
1. 00:00:00 - 04:00:00
2. 04:00:01 - 08:00:00
3. 08:00:01 - 12:00:00
4. 12:00:01 - 16:00:00
5. 16:00:01 - 20:00:00
6. 20:00:00 - 23:59:59
The creation date is stored along with time. So
Time Slot------------------02/01/2010------03/01/2010 ....
00:00:00 - 04:00:00-------------1-------------9-------
04:00:01 - 08:00:00-------------3-------------8-------
08:00:01 - 12:00:00-------------4-------------1-------
12:00:01 - 16:00:00-------------9-------------6-------
16:00:01 - 20:00:00-------------7-------------8-------
20:00:00 - 23:59:59-------------3-------------2-------
The above is the requirement.
Anyways... I kinda did in some low level manner, steps as below
- Created temp table which stores the above time slots.
- declared a top cursor for looping the dates for the month
- declared a 2nd cursor for looping the time from the temp table
- inside the 2nd cursor, i wrote the statement which fetches the count for that date on that particular time slot
- inserting this row in another temp table with the first cursor's Date, then second cursor's time slot an d then the count
- so whole thing loops.
I am getting the data accordingly for each day and each time and even the count is correct. From the final temp table which contains the completed data, i copied them to Excel and created a pivot table based on this data. So it looks clean and it is how needed, finally.
Above is done completely in SQL2K version
Now; Using this in the 2008, is there a better way to approach this? like getting the data directly in the pivot manner in the query output (not even going to Excel). Is this possible? How?
-r WW -n RMudugal.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 17, 2010 at 12:22 am
Hi, thanks for the reply
I have seen that.... not much help from that. I am not sure how you take time out of the date and create a pivot and use the same date column for displaying the date and the count of each respective.
Thanks, anyways.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 17, 2010 at 2:01 am
February 17, 2010 at 2:48 am
Hi, the attached excel file is the format, hope that can be achieved by using pivot in sql2k8 and below sql script fills the table variable with dates, which only data needed. Hope this helps in providing the solution.
thank you.
DECLARE @DATES TABLE (D1 DATETIME)
INSERT @DATES
SELECT '2010-01-01 00:00:00.000'
WHILE 1=1
BEGIN
INSERT @DATES
SELECT DATEADD(HH, 1, (SELECT MAX(D1)+ 1 FROM @DATES))
IF (SELECT COUNT(*) FROM @DATES) = 100
BREAK
END
SELECT D1 AS [CREATION DATE] FROM @DATES
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 17, 2010 at 3:01 am
The 'problem' imo with pivoting data internally in SQL is that , by definition, a table and therefore a resultset has to have a finite and known number of columns. When you pivot data you quite often wish to violate this rule.
You can work around it by using dynamic sql as demonstrated here. This ,again imo, is a last resort if you cant for whatever reason pivot in the presentation layer.
February 17, 2010 at 3:17 am
Sorry Dave, just to make it clear to myself....
from the link you mentioned, (currently I am focusing on the first example), there he has shown the month in vertical manner and then using pivot changing the look to horizontal.
In that example we have the Jan Feb and March as fixed, so it is easy to mention them as a column and column name just using the pivot placing the value under each, respectively.
In my case, the column header is the date itself. Say in one report i need to show Jan's dates 1 to 31,
so you mean, if want to use pivot, i have to hard-write the date '01/01/2010', '01/02/2010', '01/03/2010'... and so on in the main sql statement of the pivot?, so I can use the pivot just to place the value under respective column?
As you said... violating the rule.... how you suggest to go column name dynamic? For sure, I can't guarantee the amount of columns until the user selects the month.
Let me know, and thank you for your reply.
-r WW -n RMudugal
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 17, 2010 at 3:31 am
This may be a better example. Inside SQL there is no good way of doing this.
What FrontEnd software are you using ?
If nothing else , it is quite easy to create reports with SSRS to do this.
February 17, 2010 at 4:01 am
The front-end is a 3rd party app basically used for logging incidents and a lot more, and it has its own database in 2k5. I don't think SSRS is installed on that server, as it is EE with minimal req. We have Crystal reports tool which comes with this. I sometimes create complex report in them.The pivot what i have mentioned can be achieved by Crystal Reports using Cross Tab.
But I am very much interested in the database level. May be not the best way and optimized, but at least a better way to mine what i have showed in the initial post.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 17, 2010 at 5:19 am
Hi
Can u please provide the same query and function that u provide for the above requirment ,
I am also facing problem to get the report according to the created date .
Waiting for ur response .
REgards
sat
February 17, 2010 at 6:38 am
Hi
Actually the script is a SQL batch, i execute it and copy the output to the Excel and create pivot table.
But if you convert this batch to SP, then you can refer this SP is the crystal report and create a cross tab report from this data. the only problem is the horizontal data on 31 columns for Jan month. So i prefer to Excel this report.
-r WW -n RMudugal
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply