| Author |
Message |
ajp42
Newbie
Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
|
   Topic: Date Add
Posted: 01 Nov 2015 at 11:46pm |
|
Hi
I am trying to bring back data for current date minus 7 but failing - below is the query from WINSQL - which I am trying to do automatically - any help would be great
SELECT "MITTRA"."MTWHLO", "MITTRA"."MTITNO", "MITBAL"."Safety stock",
right( "MITTRA"."MTTRDT", 2) + '/' + substring(convert(VARCHAR(8), "MITTRA"."MTTRDT"), 5, 2) + '/' + left( "MITTRA"."MTTRDT", 4) AS "Trans Date",
"MITTRA"."MTRIDN", "MITTRA"."MTWHSL", "MITTRA"."MTTTID", "MITTRA"."MTTRTP", "MITTRA"."MTTTYP", "MITTRA"."MTRESP", "MITTRA"."MTRFTX", "MITTRA"."MTTRQT", "MITTRA"."MTMFCO", "MITBAL"."On-hand balance approved", "Items"."Item description", "Staff"."Name (/EAEMNM)"
FROM (("BPW_Live_Datamarts"."dbo"."MITTRA" "MITTRA" INNER JOIN "BPW_Live_Datamarts"."dbo"."Items" "Items" ON ("MITTRA"."MTCONO"="Items"."Company") AND ("MITTRA"."MTITNO"="Items"."Item number")) INNER JOIN "BPW_Live_Datamarts"."dbo"."Staff" "Staff" ON ("MITTRA"."MTCONO"="Staff"."Company") AND ("MITTRA"."MTWHLO"="Staff"."Warehouse")) LEFT OUTER JOIN "BPW_Live_Datamarts"."dbo"."MITBAL" "MITBAL" ON (("MITTRA"."MTCONO"="MITBAL"."Company") AND ("MITTRA"."MTWHLO"="MITBAL"."Warehouse")) AND ("MITTRA"."MTITNO"="MITBAL"."Item number")
WHERE NOT ("Staff"."Name (/EAEMNM)"=N'Jason Whippy' OR "Staff"."Name (/EAEMNM)"=N'Neil Baylis' OR "Staff"."Name (/EAEMNM)"=N'Richard Norris') AND "MITTRA"."MTTTID"=N'SOD' AND ("MITTRA"."MTWHLO" LIKE N'6%' OR "MITTRA"."MTWHLO" LIKE N'7%') and
"Trans Date"<dateadd('d', -7, currentdate)
|
 IP Logged |
|
|
praveeng
Senior Member
Joined: 11 Jul 2011
Online Status: Offline
Posts: 165
|
Posted: 02 Nov 2015 at 1:43am |
|
Hi,
What is the error message you are getting..
and could you check the date format of Trans Date?
use below code
Cdate(Trans Date) < DateAdd('d', -7, currentdate)
--Praveen G
|
|
Praveen Guntuka,
praveen_guntuka@yahoo.com
|
|
IP Logged |
|
ajp42
Newbie
Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
|
Posted: 02 Nov 2015 at 4:18am |
|
Whats the best way to bring back all data for the last 7 days based on a Trans date which is currently in the format of YYYYMMDD
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 02 Nov 2015 at 4:19am |
|
Also you are using the alias in the where clause, I do not think that is allowed and since Trans Date is a string, you will have to convert it to a date.
|
|
IP Logged |
|
ajp42
Newbie
Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
|
Posted: 02 Nov 2015 at 4:21am |
|
I am very new to this
Are you able to put this into the query - so that this will work
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 02 Nov 2015 at 8:15am |
|
I think would just need to change
"Trans Date"<dateadd('d', -7, currentdate)
to
cdate(right( "MITTRA"."MTTRDT", 2) + '/' + substring(convert(VARCHAR(8),
"MITTRA"."MTTRDT"), 5, 2) + '/' + left( "MITTRA"."MTTRDT", 4) < dateadd('d', -7, currentdate)
|
|
IP Logged |
|
ajp42
Newbie
Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
|
Posted: 02 Nov 2015 at 9:17pm |
|
Hi
No this doesn't seem to work
Any ideas ?
I am getting
Could not add the table(("BPW_Live_Datamarts"."dbo"."MITTRA"
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 03 Nov 2015 at 5:29am |
|
That is a very odd message. If this a MS-SQL database, you may need to use convert or cast to change your string to a date.
|
|
IP Logged |
|
ajp42
Newbie
Joined: 26 Mar 2013
Online Status: Offline
Posts: 28
|
Posted: 13 Nov 2015 at 12:44am |
|
Hi
How would I change this to cast - I am using WINSQL currently
Thanks
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 16 Nov 2015 at 4:52am |
|
I do not know anything about WINSQL. You will have to look at their documentation and see if there are any type conversion functions.
|
|
IP Logged |
|
|
|
