July 27, 2010 at 12:08 pm
I'm using an Excel file as a data source for a SSRS report. Most of the data in the report comes from the SQL tables in our main information system. There's some kind of mismatch with the DateTime field between the Excel file and the SQL tables. The data from the Excel shows the annual total for the field for every day, rather than the daily amount.
Here, the "Budget" field is in my Excel file, where the others come from a table in the SQL tables. The annual total of the budget field is 4,386.
Date Admits MTDBudHospAdmits
07/01/2010 13 134386
07/02/2010 15 284386
07/03/2010 7 354386
My query for the Excel file is:
[font="Courier New"]SELECT DateTime, sum(AdmitsNoSNUNoNSY) AS 'BudHospAdmits', sum(DaysNoSNUNoNSY) AS 'BudHospDays', sum(ALOSNoSNUNoNSY) AS 'BudHospALOS', sum(NSYAdmits) AS 'Bud NsyAdmits', sum(NSYDays) AS 'BudNsyDays', sum(NSYALOS) AS 'BudNsyALOS', sum(SNUAdmits) AS 'BudSnuAdmits', sum(SNUDays) AS 'BudSnuDays', sum(SNUALOS) AS 'BudSnuALOS', sum(SurgCases) AS 'BudSurgCases'
FROM [BUDGET10$]
GROUP BY DateTime[/font]
Any ideas how can I fix this (keeping in mind I'm a complete noob)?
July 27, 2010 at 12:15 pm
You will increase your chances for someone posting a tested solution if you posted the table definition(s) and some sample data (NOT actual data, but something reasonably close) as described in the article in the first link in my signature block
July 27, 2010 at 12:16 pm
I'm not sure I understand you're problem. The query you have doesn't seem to relate to the actual problem. Are you trying to join this data against data from a SQL table and having trouble?
Based on your limited information, my guess is that the SQL tables hold datetime fields that contain the time element. '7/27/2010' is not equal to '7/27/2010 1:04:15 PM' because SQL Server takes '7/27/2010' to mean '7/27/2010 12:00:00 AM'. You may need to strip the time part from the datetime field in the SQL tables.
July 27, 2010 at 12:23 pm
jvanderberg (7/27/2010)
I'm not sure I understand you're problem. The query you have doesn't seem to relate to the actual problem. Are you trying to join this data against data from a SQL table and having trouble?Based on your limited information, my guess is that the SQL tables hold datetime fields that contain the time element. '7/27/2010' is not equal to '7/27/2010 1:04:15 PM' because SQL Server takes '7/27/2010' to mean '7/27/2010 12:00:00 AM'. You may need to strip the time part from the datetime field in the SQL tables.
Correct, I am trying to join, using DateTime as the common field. (Sorry, but as a noob, I sometimes don't know enough to ask the question correctly!)
I've tried to format the Excel file as '7/27/2010 12:00:00 AM'. It may be that SSRS is reading the DateValue field in the Excel file as that goofy Excel datecode rather than what's actually displayed. I'll try changing it to straight text and see what happens.
If that doesn't work, how does one "strip the time part from the datetime field in the SQL tables"?
Thanks!
July 27, 2010 at 12:33 pm
The problem isn't your Excel source. The problem is that you are trying to join unequal values. You have Excel, which has only dates. Those get converted automatically to datetime fields by SQL Server, with the time part being midnight. You SQL Server tables most likely have a date and time. When you join between the two data fields, you will only get records from the SQL table which occurred at midnight exactly (probably none). So you need to convert the dates in the SQL Server to all be the same date, but at midnight:
dateadd(dd,0, datediff(dd,0,[datefield]))
I don't know what your join looks like, but it should look something like this:
SELECT
<field list>
FROM
[ExcelTable]
{LEFT | RIGHT} JOIN [SQLTable] ON [ExcelTable].[DateField] =
dateadd(dd,0, datediff(dd,0,[SQLTable].[DateField]))
Also, if you are grouping your SQL Table by date, you'll want to preform the same function in your GROUP BY expression:
SELECT
dateadd(dd,0, datediff(dd,0,[DateField])) AS [OccuranceDate],
SUM([AggregatedField]) AS [SummaryField]
FROM
[SQLTable]
GROUP BY
dateadd(dd,0, datediff(dd,0,[DateField]))
July 27, 2010 at 12:40 pm
Thanks very much. I'll start digesting that and see if I can get it to work, in spite of my ignorance!
July 27, 2010 at 12:44 pm
No worries. It may help you to look at the two tables. The one created from the Excel file should show dates, all of which have midnight as the time part. The SQL Server table should have dates with a full time part. Then, realize that the equal operator requires that they be exactly the same. Once you understand the problem, you may understand the solution a little better.
July 28, 2010 at 7:38 am
stchambers
This may not directly address your problem however read this blog posting by LynnPettis - which contains code for all sorts of date time manipulations. You may want to add these to your "bag of tricks".
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply