August 13, 2010 at 2:12 pm
Hi
Here is My Query:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2010-06-20'
SET @EndDate = '2010-07-25'
DECLARE @SwapDate DATETIME
SELECT @SwapDate = @EndDate,
@EndDate = @StartDate,
@StartDate = @SwapDate
WHERE @EndDate < @StartDate
SELECT @StartDate = DATEADD(ww,DATEDIFF(ww,0,@StartDate),0),
@EndDate = DATEADD(ww,DATEDIFF(ww,0,@EndDate)+1,0)
DECLARE @SQL1 NVARCHAR(4000),
@SQL2 NVARCHAR(4000),
@SQL3 NVARCHAR(4000)
SELECT @SQL1 = 'SELECT CASE WHEN GROUPING([CP Code]) = 1 THEN ''TotalPageviews'' ELSE [CP Code] END AS [CP Code],'+CHAR(10)
SELECT @SQL3 =
' SUM(TotalPageviews) AS TotalPageviews
FROM
(
SELECT DATEADD(WW,DATEDIFF(WW,0,Date),0)-8 AS WeekEndDate,
[CP Code],
SUM(TotalPageviews) AS TotalPageviews
FROM View_DSA
WHERE Date >= ' + QUOTENAME(@StartDate,'''') + '
AND Date < ' + QUOTENAME(@EndDate,'''') + '
GROUP BY DATEADD(WW,DATEDIFF(WW,0,Date),0), [CP Code]
) d
GROUP BY [CP Code] WITH ROLLUP
'
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' SUM(CASE WHEN WeekEndDate = ' + QUOTENAME(d.WeekEndDate,'''')
+ ' THEN TotalPageviews ELSE 0 END) AS [' + d.WeekEndDate + '],' + CHAR(10)
FROM (
SELECT N,
STUFF(CONVERT(CHAR(11),DATEADD(WW, N,@StartDate)-8,101),1,0,'') AS WeekEndDate
FROM Tally
WHERE N <= DATEDIFF(WW,@StartDate,@EndDate)
) d
ORDER BY d.N
EXEC (@SQL1 + @SQL2 + @SQL3)
GO
This query recogonises the first date of the week, aggregates the daily data into weekly data and then pivot the weeks into Columns.
But the problem is, it displays all the weeks between the declared startdate and enddate even when some of the weeks are not present in the database table. Lets say I have data for June and August but no data for July. So I have about 12 weeks in 3 month but there is no data for the 5,6,7 and 8th week. Now when I run this query, It display all 12 weeks with no data in those weeks.
I want that this query should recogonise the dates from the table and not from the date range so that it only displays weeks that are present in the database.
I hope I was clear enough. I have huge amount so data so couldn't post it here. I'll post the DDL, if that helps:
CREATE VIEW View_DSA
AS
SELECT
CAST([# CP Code] AS CHAR(5)) AS [CP Code],
CAST([Time] AS DATETIME) AS [Date],
CAST([Total Pageviews] AS INT) AS [TotalPageviews]
FROM DSA
Thanks.
August 14, 2010 at 2:42 am
You need to change your subquery to use View_DSA instead of the tally table to use only the weeks that show up in the table. You probably need to use a group by...
If the view is based on a rather large table it might help performance to preaggregate the data in a intermediate table (not temp table! It won't be recognized by the dynamic SQL statement) and use that in your dynamic query. You could test it with a direct join on the view and if performance is at the edge of being acceptable try the intermed table approach.
August 16, 2010 at 11:52 am
Hi
Can you give me the query to do that .. I'm already preaggregating the data in this query.
Thanks
August 16, 2010 at 12:17 pm
Please post table def and some sample data in a ready to use format so I have something to test against. (see the first link in my signature for details on how to post data it the most efficient way).
August 16, 2010 at 1:05 pm
Hi
My Table is:
CREATE TABLE DSA (
[# CP Code] varchar(50),
[Time] varchar(50),
[Total Pageviews] varchar(50)
)
INSERT INTO DSA
([# CP Code], Time, [Total Pageviews])
SELECT '51014','8/1/2010','2827962', UNION ALL
SELECT '51014','8/2/2010','2405999', UNION ALL
SELECT '51014','8/3/2010','2485448', UNION ALL
SELECT '51014','8/4/2010','2610767', UNION ALL
SELECT '51014','8/5/2010','2945959', UNION ALL
SELECT '51014','8/6/2010','2296758', UNION ALL
SELECT '51014','8/7/2010','2980303', UNION ALL
SELECT '51014','7/18/2010','2517437', UNION ALL
SELECT '51014','7/19/2010','2019041', UNION ALL
SELECT '51014','7/20/2010','2097835', UNION ALL
SELECT '51014','7/21/2010','2471075', UNION ALL
SELECT '51014','7/22/2010','3644609', UNION ALL
SELECT '51014','7/23/2010','2773555', UNION ALL
SELECT '51014','7/24/2010','3717121', UNION ALL
SELECT '51014','6/27/2010','2623221', UNION ALL
SELECT '51014','6/28/2010','1985718', UNION ALL
SELECT '51014','6/29/2010','1815712', UNION ALL
SELECT '51014','6/30/2010','2126104', UNION ALL
SELECT '51014','7/1/2010','1702672', UNION ALL
SELECT '51014','7/2/2010','1666468', UNION ALL
SELECT '51014','7/3/2010','2019123', UNION ALL
SELECT '51014','7/4/2010','1783373', UNION ALL
SELECT '51014','7/5/2010','1890672', UNION ALL
SELECT '51014','7/6/2010','2424314', UNION ALL
SELECT '51014','7/7/2010','2197743', UNION ALL
SELECT '51014','7/8/2010','2097884', UNION ALL
SELECT '51014','7/9/2010','2029226', UNION ALL
SELECT '51014','7/10/2010','1989296', UNION ALL
SELECT '51014','7/11/2010','1833109', UNION ALL
SELECT '51014','7/12/2010','2398424', UNION ALL
SELECT '51014','7/13/2010','2365550', UNION ALL
SELECT '51014','7/14/2010','1810926', UNION ALL
SELECT '51014','7/15/2010','2483211', UNION ALL
SELECT '51014','7/16/2010','2327007', UNION ALL
SELECT '51014','7/17/2010','1758154', UNION ALL
SELECT '53037','7/11/2010','3576', UNION ALL
SELECT '53037','7/12/2010','3860', UNION ALL
SELECT '53037','7/13/2010','3123', UNION ALL
SELECT '53037','7/14/2010','3458', UNION ALL
SELECT '53037','7/15/2010','3516', UNION ALL
SELECT '53037','7/16/2010','3233', UNION ALL
SELECT '53037','7/17/2010','3176', UNION ALL
SELECT '53037','7/4/2010','2995', UNION ALL
SELECT '53037','7/5/2010','3122', UNION ALL
SELECT '53037','7/7/2010','3040', UNION ALL
SELECT '53037','7/8/2010','3638', UNION ALL
SELECT '53037','7/9/2010','3767', UNION ALL
SELECT '53037','7/10/2010','3745', UNION ALL
SELECT '53037','7/18/2010','3250', UNION ALL
SELECT '53037','7/19/2010','3342', UNION ALL
SELECT '53037','7/20/2010','3157', UNION ALL
SELECT '53037','7/21/2010','3133', UNION ALL
SELECT '53037','7/22/2010','3970', UNION ALL
SELECT '53037','7/23/2010','3611', UNION ALL
SELECT '53037','7/24/2010','3258', UNION ALL
SELECT '53037','6/27/2010','6059', UNION ALL
SELECT '53037','6/29/2010','6394', UNION ALL
SELECT '53037','8/6/2010','2981', UNION ALL
SELECT '53037','8/7/2010','2931', UNION ALL
SELECT '53037','6/30/2010','4627', UNION ALL
SELECT '53037','7/1/2010','3402', UNION ALL
SELECT '53037','7/2/2010','3115', UNION ALL
SELECT '53037','7/3/2010','3032', UNION ALL
SELECT '53037','8/1/2010','3027', UNION ALL
SELECT '53037','8/2/2010','3125', UNION ALL
SELECT '53037','8/3/2010','3299', UNION ALL
SELECT '53037','8/4/2010','3130', UNION ALL
SELECT '53037','8/5/2010','3129', UNION ALL
SELECT '60199','6/27/2010','1320009', UNION ALL
SELECT '60199','6/28/2010','780962', UNION ALL
SELECT '60199','6/29/2010','949653', UNION ALL
SELECT '60199','6/30/2010','1086996', UNION ALL
SELECT '60199','7/2/2010','1123436', UNION ALL
SELECT '60199','7/3/2010','937690', UNION ALL
SELECT '60199','7/18/2010','1395861', UNION ALL
SELECT '60199','7/19/2010','786528', UNION ALL
SELECT '60199','7/20/2010','836900', UNION ALL
SELECT '60199','7/21/2010','641297', UNION ALL
SELECT '60199','7/22/2010','628898', UNION ALL
SELECT '60199','7/23/2010','572505', UNION ALL
SELECT '60199','7/24/2010','1389859', UNION ALL
SELECT '60199','8/1/2010','1233352', UNION ALL
SELECT '60199','8/2/2010','1006052', UNION ALL
SELECT '60199','8/3/2010','965510', UNION ALL
SELECT '60199','8/4/2010','1042195', UNION ALL
SELECT '60199','8/5/2010','930091', UNION ALL
SELECT '60199','8/6/2010','905171', UNION ALL
SELECT '60199','8/7/2010','1322906', UNION ALL
SELECT '60199','7/1/2010','1534519', UNION ALL
SELECT '60199','7/4/2010','693642', UNION ALL
SELECT '60199','7/5/2010','1025405', UNION ALL
SELECT '60199','7/6/2010','396889', UNION ALL
SELECT '60199','7/7/2010','449148', UNION ALL
SELECT '60199','7/8/2010','389064', UNION ALL
SELECT '60199','7/9/2010','573831', UNION ALL
SELECT '60199','7/10/2010','1155328', UNION ALL
SELECT '60199','7/11/2010','1104293', UNION ALL
SELECT '60199','7/12/2010','780170', UNION ALL
SELECT '60199','7/13/2010','771110', UNION ALL
SELECT '60199','7/14/2010','719243', UNION ALL
SELECT '60199','7/15/2010','767354', UNION ALL
SELECT '60199','7/16/2010','837526', UNION ALL
SELECT '60199','7/17/2010','1419438', UNION ALL
My VIEW is:
CREATE VIEW View_DSA
AS
SELECT
CAST([# CP Code] AS CHAR(5)) AS [CP Code],
CAST([Time] AS DATETIME) AS [Date],
CAST([Total Pageviews] AS INT) AS [TotalPageviews]
FROM DSA
GO
Thanks
August 16, 2010 at 1:08 pm
Novicejatt (8/13/2010)
I want that this query should recogonise the dates from the table and not from the date range so that it only displays weeks that are present in the database.I hope I was clear enough. I have huge amount so data so couldn't post it here. I'll post the DDL, if that helps
.
Hi ...just a couple of comments:
is there a business reason to not display weeks where there is no data?
My experience is that most businesses wish to see all weeks...even if no data...???
You say you have a "huge amount of data"....have you looked at Analysis Services?
This can consume vast amounts fo data and "spit out" summary reports very easily...just a suggestion.
regards gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 16, 2010 at 1:22 pm
Hi
Actually there is no missing data for any week, but the reason I want such a query is as the # of weeks increase, so does the # of columns in my report. So I just want to show lets say 5 weeks of data at one time. So to do that Ill just insert 5 weeks of data into a table and use that table to generate the report instead of altering the date range in the query eveytime. I know this is not such a good Idea, but its just temporary. Also right now I'm not using Analysis Services.
Thanks
August 16, 2010 at 2:09 pm
Hi,
here's the solution using the view instead of the Tally solution (I only changed @SQL2):
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' SUM(CASE WHEN WeekEndDate = ' + QUOTENAME(d.WeekEndDate,'''')
+ ' THEN TotalPageviews ELSE 0 END) AS [' + d.WeekEndDate + '],' + CHAR(10)
FROM (
SELECT CONVERT (CHAR(10),DATEADD(WW,DATEDIFF(WW,0,DATE),0),120) WeekEndDate
FROM View_DSA
GROUP BY CONVERT (CHAR(10),DATEADD(WW,DATEDIFF(WW,0,DATE),0),120)
) d
ORDER BY d.WeekEndDate
Just when I posted my reply I've seen your new requirement...
To limit the number of results you could use the TOP clause with an ORDER BY (again, just @SQL2):
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' SUM(CASE WHEN WeekEndDate = ' + QUOTENAME(d.WeekEndDate,'''')
+ ' THEN TotalPageviews ELSE 0 END) AS [' + d.WeekEndDate + '],' + CHAR(10)
FROM (
SELECT TOP 3 N,
STUFF(CONVERT(CHAR(11),DATEADD(WW, N,@StartDate)-8,101),1,0,'') AS WeekEndDate
FROM Tally
WHERE N <= DATEDIFF(WW,@StartDate,@EndDate)
ORDER BY N DESC
) d
Edit:
Or, if you want to avoid the sorting operation required for the TOP clause:
SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' SUM(CASE WHEN WeekEndDate = ' + QUOTENAME(d.WeekEndDate,'''')
+ ' THEN TotalPageviews ELSE 0 END) AS [' + d.WeekEndDate + '],' + CHAR(10)
FROM (
SELECT N,
STUFF(CONVERT(CHAR(11),DATEADD(WW, N,@StartDate)-8,101),1,0,'') AS WeekEndDate
FROM Tally
WHERE N <= DATEDIFF(WW,@StartDate,@EndDate)
AND N > DATEDIFF(WW,@StartDate,@EndDate) - 3 -- place the number of weeks to be displayed here
) d
ORDER BY d.N
I have to ask one more question though: Why do you create a view to cover the lack of proper database design, meaning to use the correct data type???? (Actually, it looks like someone used SSMS to type a few column names and saved that as a table ignoring the fact that SQL Server has a number of column types that should be used...). :pinch:
I'd strongly recommend to get the data type for the source table fixed and get rid of the view. It's just wasting resources.
August 16, 2010 at 2:50 pm
Hi
When I run the first query it gives me:
CP Code2010-06-282010-07-052010-07-122010-07-192010-08-02TotalPageviews
510140000065396542
530370000097626
601990000026300906
602000000014379058
602010000013992453
602020000028035017
602030000011645770
602040000014109229
60205000008133982
602060000011900110
60207000004638197
60208000006492873
60209000001765906
60210000002471653
60211000001160578
60213000001315068
60214000004232959
6021500000928973
60216000001467733
60217000003086290
6021900000751370
60220000002378639
6022100000162232
60222000002947181
6022300000239815
6022400000586003
602250000044706
700860000012848
7072500000119279334
79999000001763965
There is not data in Week Columns.
For the other two queries, I can get same output if I change the Start and End Date but as I told you i don't want to alter the query everytime.
Thanks
August 16, 2010 at 3:02 pm
You might want to be a little more specific what you're really looking for...
If you don't want to provide a start and end date, what will be the logic to calculate those dates internally? "The last X weeks" won't help here unless you include the definition of the "end point". This can either be today or the max(Time) value for a specific [CP Code] or your birthday or a random generated day or whatever. Also we'd need to know how many weeks you want to display and if this is a fixed or variable value. If variable, you'd need to provide the rules as well (examples see above).
I'd recommend to sit back for a moment and rethink the business case and your real requirement. Based on that, provide sample data (if different), rules and expected output based on the sample data.
August 16, 2010 at 3:14 pm
Hi
Thanks, your first query is working. I just had to modify some section of my query.
Also the reason I use VARCHAR in my table and use views is that I use BULK INSERT to import data into my table form CSV file and I'm only able to BULK INSERT data if I have all datatypes as VARCHAR.
Thank you for the help.
August 16, 2010 at 4:37 pm
That's weird...
Usually the usage of a format file takes care of conversion errors. If not, a staging table could be used to load the data. However, the final table should have the correct data types not requring an additional view just to convert the data.
If you'd like us to have a look at it please open a new thread, attach a sample and post your target table structure together with your current structure and your current approach.
August 16, 2010 at 5:00 pm
Hi
I tried with the format file but was not working. Maybe i was doing something wrong. But right now I only wanted to get this thing working. I'm just trying to experiment different methods to get the results I want. So I'm not sure which method I'll chose in the end. Thats Why I"m not really concerned about the Table. I guess I'll start new thread regarding that soon.
Thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply