Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wembleybear
Yak Posting Veteran
93 Posts |
 Posted - 2015-04-29 : 11:13:20
|
I have a query where I need to see in 2 seperate columns, the Actual date of deliveries and collections, and the Invoice Week number of the same. The problem is that in our ERP system delivery date (di.ddate) is a date field, and the collection date is nvarchar. This is my query and sample results below, as you can see I'm getting nulls for the collection lines. How can I do this efficientlyselect di.dticket, di.item, case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) end ActDate,Case when di.item = 'DEL' then(select YearWeek from MCSReports.RptRevenueGuideWeekLookupwhere di.ddate between FromDate and ToDate) end InvWeekfrom deltickitem diwhere item in ('DEL','COL') Sample results below:dticket item ActDate InvWeek-------------------- -------------------------------------------------- ----------------------- --------01-000002 DEL 2011-07-05 15:53:00.000 2011-2701-000002 COL NULL NULL01-000004 DEL 2011-07-05 16:04:00.000 2011-2701-000004 COL NULL NULL01-000005 DEL 2011-07-05 16:08:00.000 2011-2701-000005 COL NULL NULL01-000006 DEL 2011-07-05 16:19:00.000 2011-2701-000006 COL NULL NULL01-000007 DEL 2011-07-05 16:24:00.000 2011-2701-000007 COL NULL NULL01-000008 DEL 2011-07-05 16:29:00.000 2011-2701-000021 DEL 2011-07-05 08:48:00.000 2011-27 Many thanksMartyn |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-29 : 11:36:35
|
could you post a few rows of your input data for the same ticket numbers?I'm also worried that this:select YearWeek from MCSReports.RptRevenueGuideWeekLookupwhere di.ddate between FromDate and ToDate may not always return just one value. If so, you'll get an error |
 |
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2015-04-29 : 12:40:28
|
Some of the base data is below, the text2 field is what we use as the collection date, it's not ideal but there are no other date fields in the ERP system we could use.The date lookup table is used by the internal revenue guide in the ERP when producing the weekly accounts, so we need to use that to get the YearWeekNo.dticket descr item ddate text2-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ----------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------01-013465 DELIVERY DEL 2013-03-28 09:11:00 08/04/201501-018564 COLLECTION COL 2013-11-06 11:33:00 01/04/201501-018595 DELIVERY DEL 2013-11-07 11:33:00 24/03/201501-018595 COLLECTION COL 2013-11-07 11:33:00 24/03/201501-021442 DELIVERY DEL 2014-03-20 09:41:00 27/03/201501-021442 COLLECTION COL 2014-03-20 09:41:00 27/03/201501-024916 DELIVERY DEL 2014-08-21 13:30:00 30/03/201501-024916 COLLECTION COL 2014-08-21 13:30:00 30/03/201501-025540 DELIVERY DEL 2014-09-19 08:46:00 02/04/201501-025540 COLLECTION COL 2014-09-19 08:46:00 02/04/201501-026936 COLLECTION COL 2014-11-19 09:41:00 30/03/201501-027380 DELIVERY DEL 2014-12-10 10:05:00 02/04/201501-027380 COLLECTION COL 2014-12-10 10:05:00 02/04/201501-027545 DELIVERY DEL 2015-01-07 00:00:00 27/03/201501-027687 DELIVERY DEL 2015-01-14 00:00:00 01/04/201501-027687 COLLECTION ThanksMartyn |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-29 : 13:39:53
|
I simulated your setup like this:create table deltickitem (dticket varchar(20), item char(3), ddate datetime, text2 nvarchar(50))insert into deltickitem(dticket,item,ddate,text2) values('01-013465','DEL','2013-03-28 09:11:00','08/04/2015'),('01-018564','COL','2013-11-06 11:33:00','01/04/2015')select di.dticket, di.item, case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) end ActDate,Case when di.item = 'DEL' then 2--(select YearWeek from MCSReports.RptRevenueGuideWeekLookup--where di.ddate between FromDate and ToDate)when di.item = 'COL' then 3 end InvWeekfrom deltickitem diwhere item in ('DEL','COL')Using this the query produced:dticket item ActDate InvWeek01-013465 DEL 2013-03-28 09:11:00.000 201-018564 COL 2015-04-01 00:00:00.000 3 so no nulls. That must mean that your data is different somehow |
|
|
|
jleitao
Posting Yak Master
100 Posts |
Posted - 2015-04-29 : 13:47:05
|
| Not sure where is your problem.i suggest you make a test to see if the problem is on CONVERT(datetime,di.text2,103) command.replace this:case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) end ActDateto this:case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,getdate(),103) end ActDatethen tell us the result.------------------------PS - Sorry my bad english |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-30 : 04:15:35
|
I can't see how CONVERT is returning NULL. If the date is not valid then it will generate an error, rather than NULL.Putting an example of your data in your CONVERT works fine, e.g:SELECT CONVERT(datetime, '01/04/2015', 103) I think more likely that the actual query you are using is more complex than the sample you have posted?? and that is causing NULL propagation.Or di.item does NOT match either value (perhaps trailing spaces or "tab" or somesuch invisible character). This will give you a bogus date of 1900 if di.item does not match DEL or COL. case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) ELSE CONVERT(datetime, '19000101') end ActDate, |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2015-04-30 : 07:50:39
|
| Thanks all for your help, I have re-written the query taking your advice and the only nulls I now see on collection dates are correct.Many thanksMartyn |
|
|
|
|
|
|
|
|
Help with converting date in Case statement