combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO

12/22/2006

2

12:48:45 PM

9

12/22/2006

16

5:40:55 AM

1

12/22/2006

16

12:03:59 PM

2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.I was suggested to try this out:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

I'll run it after I use DTwizard to export the data into my table. I should also mention I have no PKs defined, just a FK that references Id from a table called Employees. I'm thinking it's best to define ID and Date and Time as PKs.|||

As far as SQL Server is concerned, it will be far easier over time to work with and deal with date/time data if it is stored as one column. I recommend combining the two.

One primary reason is that the combined column will allow easier datetime comparisions and searches.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Would it be better to combine the Date & Time fields into one
column? If so, how?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.drurjen (jfontecha@.gmail.com) writes:

Quote:

Originally Posted by

Good morning.
>
I am importing an XLS file into one of my tables. The fields are:
>
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
>
When I do the import, I get the following:
>
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
Here are my doubts:
>
1. Would it be better to combine the Date & Time fields into one
column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

Quote:

Originally Posted by

2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.

Combine 3 huge queryies to produce another SUM

I have to combine 3 huge queries to get a GT for ProjGross (see calcs.xls on formulas). Please check out ProjGross here
http://www.photopizzaz.biz/projgross.txt
and see this for the logic for projgross
http://www.photopizzaz.biz/calcs.xls
I am trying to combine (Daily Run Rate * Days Remaining )+ InHouse1 as (stated in the xls) using the previous select statements respectively to get my final ProjGross

All queries so far in my projgross.txt work great...now I just need to combine what's needed to move forward in my calcs.xls to create ProjGross...at the Company level.

You'll see that I attempted to start the ProjGross Query in my .txt file

I have rewritten INHOUSE1 (START) as follows:

SELECT sub.CustomerNumber, sub.PostedAmount + sub.CCsOld + sub.CCsNew + sub.PDCsNew + sub.PDCsOld
FROM
( Select d0.CustomerNumber AS CustomerNumber,
CASE WHEN d0.Type = 'In-House'
THEN SUM(ISNULL(d0.PostedAmount,0))
ELSE 0
END AS PostedAmount,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsOld,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsOld
FROM DCR d0 With(NOLOCK)
JOIN CustomerNumber cno
ON cno.customernumber = d0.customernumber
AND (d0.branch = '00002' AND cno.CheckBranch = 1)
OR (cno.CheckBranch = 0)
WHERE d0.Type = 'In-House'
GROUP BY d0.CustomerNumber
) AS sub

You should be able to get the rest using the same principle. You are using the same table each time, so all this can be done in one query, albeit a *** of a query, and you can do away with the ...customernumber in... by joining to that table, which should boost peformance a lot. Let me know if you have any more problems. That should work, but you may get a compilation errors, as I do not have the db here in front of me to test.

|||yea but is it going to go through each Case? each case statement will evaluate to true because there are records for each instance that you're checking so I don't think CASE is good here..only one can evaluate true in a CASE|||

I've actually already solved it on my own just earlier today. Thanks for your input though!

Select @.ProjFee = SUM(ProjFee)

FROM

(

Select d1.CustomerNumber, ((((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0)) / (select CurrentPostingDay from v_CurrentPostingDay))) + ((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsOldTable.OldCCs,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0) + ISNULL(PDCsOldTable.OldPDCs,0)))) * d1.FeeSchedule / 100 as ProjFee

FROM (Select distinct CustomerNumber, FeeSchedule From DCR) d1

- $Posted

LEFT JOIN

(Select d0.CustomerNumber, SUM(ISNULL(d0.PostedAmount,0)) AS PostedAmount

FROM DCR d0 (NOLOCK)

WHERE d0.Type = 'In-House'

AND ((d0.branch = '00002' and d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

GROUP BY d0.CustomerNumber) as PostedTable ON PostedTable.CustomerNumber = d1.CustomerNumber

- OldCCs

LEFTJOIN

(Select d3.CustomerNumber, SUM(ISNULL(d3.CC,0)) AS OldCCs

FROM DCR d3 (NOLOCK)

WHERE d3.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d3.Type = 'CC'

AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d3.dc_OnHoldDate IS NULL

GROUP BY d3.CustomerNumber) as CCsOldTable ON CCsOldTable.CustomerNumber = d1.CustomerNumber

- NewCCs

LEFTJOIN

(Select d4.CustomerNumber, SUM(ISNULL(d4.CC,0)) AS NewCCs

FROM DCR d4 (NOLOCK)

WHERE d4.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d4.Type = 'CC'

AND ((d4.branch = '00002' and d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d4.dc_OnHoldDate IS NULL

GROUP BY d4.CustomerNumber) as CCsNewTable ON CCsNewTable.CustomerNumber = d1.CustomerNumber

- NewPDCs

LEFTJOIN

(Select d5.CustomerNumber, SUM(ISNULL(d5.PDC,0)) AS NewPDCs

FROM DCR d5 (NOLOCK)

WHERE d5.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d5.Type = 'PDC'

AND ((d5.branch = '00002' and d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d5.pdc_OnHold IS NULL

GROUP BY d5.CustomerNumber) as PDCsNewTable ON PDCsNewTable.CustomerNumber = d1.CustomerNumber

- OldPDCs

LEFTJOIN

(Select d6.CustomerNumber, SUM(ISNULL(d6.PDC,0)) AS OldPDCs

FROM DCR d6 (NOLOCK)

WHERE d6.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d6.Type = 'PDC'

AND ((d6.branch = '00002' and d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d6.pdc_OnHold IS NULL

GROUP BY d6.CustomerNumber) as PDCsOldTable ON PDCsOldTable.CustomerNumber = d1.CustomerNumber

GROUP BY d1.CustomerNumber,

PostedTable.PostedAmount,

CCsOldTable.OldCCs,

CCsNewTable.NewCCs,

PDCsNewTable.NewPDCs,

PDCsOldTable.OldPDCs,

d1.FeeSchedule

) as z

SELECT ProjFee = @.ProjFee

|||Ok, but my query is much simpler, and there are only two cases for each case statement, true, so add value, or false so add 0. Try it and see. It will only add the to the approriate totals for the relevant records, the rest it will ignore by adding 0. The query you have written is way too complicated and thus probably very slow. I would try and simplify it, hopefully my example will help.