September 15, 2009 at 11:13 am
Hi there!
I was hoping someone could please help me with my syntax. I'm trying to put a nested where statement with my date conversion.
Here is my cast stuff clause:
where
and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-10,getdate())
[/code]
And this is what I'm trying to insert:
[code]and CRM_Orders.PROCESS_TYPE IN ('CLO','OPEN')[/code]
The reason why I need to do this is because I need to find back logged orders with two different sets of criteria -5 days and -10 days in the same dataset.
I tried googling everything I could think of for help, but I got nowhere fast.
Thank you in advance for any advice you can provide!!
Michelle 🙂
September 15, 2009 at 11:32 am
I didn't get what you need. Maybe I can try and help yuo solve your problem instead of solving your solution... 🙂
Could you please post some sample data and what result are you trying to achieve?
September 15, 2009 at 11:43 am
Sure no problem, Benyos.
Here is my query:
SELECT DISTINCT CRM_Orders.TRANSACTION_ID, CRM_StatusCodes.USER_STATUS, CRM_OrganizationalUnits.STEXT
FROM CRM_Orders INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
CRM_OrganizationalUnits ON CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
where
CRM_StatusCodes.USER_STATUS 'Complete'
and CRM_StatusCodes.USER_STATUS ' '
and CRM_Orders.PROCESS_TYPE IN ('ZINT','ZSVO')
and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-10,getdate())
and CRM_OrganizationalUnits.STEXT = 'Region 9 - Field'
For line: and CRM_Orders.PROCESS_TYPE IN ('CLO','OPEN') I need to find back logged orders 10 days out which I'm accomplishing by this: and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-10,getdate())
But then I also need to find: and CRM_Orders.PROCESS_TYPE IN ('PM') which are back logged orders 5 days out. Which I can accomplish very easily by doing this:
and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-10,getdate())
So I would like to write something like this:
SELECT DISTINCT CRM_Orders.TRANSACTION_ID, CRM_StatusCodes.USER_STATUS, CRM_OrganizationalUnits.STEXT
FROM CRM_Orders INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
CRM_OrganizationalUnits ON CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
where
CRM_StatusCodes.USER_STATUS 'Complete'
and CRM_StatusCodes.USER_STATUS ' '
and (cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-10,getdate()),select CRM_Orders.PROCESS_TYPE from CRM_Orders where CRM_Orders.PROCESS_TYPE IN ('CLO','OPEN'))
and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-5,getdate()),select CRM_Orders.PROCESS_TYPE from CRM_Orders where CRM_Orders.PROCESS_TYPE IN ('PM'))
But I'm obviously getting something wrong with my syntax.
Thank you for your help Benyos!! I hope this explains it a little better....
-Michelle 🙂
September 15, 2009 at 2:33 pm
Hhmmm... still kinda' stuck here.
I broke it down and I have two queries:
SELECT DISTINCT
count(CRM_Orders.TRANSACTION_ID)
FROM CRM_Orders INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
CRM_OrganizationalUnits ON CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
where
CRM_StatusCodes.USER_STATUS 'Complete'
and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-5,getdate())
and CRM_StatusCodes.USER_STATUS ' '
and CRM_Orders.PROCESS_TYPE IN ('ZINT','ZSVO')
and CRM_OrganizationalUnits.STEXT = 'Region 9 - Field'
Count result: 29
SELECT DISTINCT
count(CRM_Orders.TRANSACTION_ID)
FROM CRM_Orders INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
CRM_OrganizationalUnits ON CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
where
CRM_StatusCodes.USER_STATUS 'Complete'
and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-10,getdate())
and CRM_StatusCodes.USER_STATUS ' '
and CRM_Orders.PROCESS_TYPE IN ('ZRPM')
and CRM_OrganizationalUnits.STEXT = 'Region 9 - Field'
Count result: 3
What I need to do is shove these two queries together so I get a final count of 32 based on the criteria in bold above. I've been working on it most of the day, but this one just isn't coming to me.
Anyone have andy ideas???
-Michelle 🙂
September 15, 2009 at 2:55 pm
Hi Michelle
I really am happy to help but it's quite frustrating (for you, because you are the one who needs the code) when you don't post any DDL to help us help you.
I'm still trying to figure what you need...If you posted some sample data and at least one row of desired result - it would have been much easier.
However, I'll try to suggest a solution based on what I thought you might have meant:
;WITH MY_ORDERS
AS (
SELECT DISTINCT
CRM_Orders.TRANSACTION_ID,
CRM_StatusCodes.USER_STATUS,
CRM_OrganizationalUnits.STEXT
FROM
CRM_Orders
INNER JOIN CRM_StatusCodes ON
CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_OrganizationalUnits ON
CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
WHERE
CRM_StatusCodes.USER_STATUS NOT IN ('Complete', ' ')
)
SELECT
*
FROM
MY_ORDERS
WHERE
(DATEDIFF(Day, CRM_Orders.CREATED_AT, getdate()) >=10 AND CRM_Orders.PROCESS_TYPE IN ('CLO','OPEN'))
OR
(DATEDIFF(Day, CRM_Orders.CREATED_AT, getdate()) >=5 AND CRM_Orders.PROCESS_TYPE IN ('PM'))
I changed your query to CTE so it's easier to reference it in nested joins if you need. I also got rid of all the CAST AND STUFF trying to go back to good old adtediff.
I guess I missed something, but that might take you a bit further towards what you need.
If yuo need both records (-5 and -10 days) in same row, just perform a join over the same CTE again (MY_ORDERS).
Again, if you post some sample data and result - I'll be more than happy (& able) to help you.
🙂
September 15, 2009 at 3:08 pm
Since you haven't posted DDL, sample data, and expected results, I can't test this. Try the following:
SELECT DISTINCT CRM_Orders.TRANSACTION_ID, CRM_StatusCodes.USER_STATUS, CRM_OrganizationalUnits.STEXT
FROM CRM_Orders INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
CRM_OrganizationalUnits ON CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
where
CRM_StatusCodes.USER_STATUS 'Complete'
and CRM_StatusCodes.USER_STATUS ' '
and (cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day, CASE WHEN CRM_Orders.PROCESS_TYPE IN ('CLO','OPEN') THEN -10 WHEN CRM_Orders.PROCESS_TYPE = 'PM' THEN -5 END
Basically what I've done is replaced your hard-coded day difference with a case statement.
I think that you can also simplify your Cast statement as follows. Again, you haven't provided sample data, so I can't be sure what the input looks like, but I'm guessing you have 'yyyymmddhhmmss' without any separators. If that is the case try the following formula instead:
Convert(Datetime, Left(<your date field>, 8), 112)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 15, 2009 at 3:33 pm
Many apologies, thank you very much for your assistance.....
This my code:
SELECT DISTINCT CRM_Orders.TRANSACTION_ID, CRM_StatusCodes.USER_STATUS, CRM_OrganizationalUnits.STEXT
FROM CRM_Orders INNER JOIN
CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID INNER JOIN
CRM_OrganizationalUnits ON CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
where
CRM_StatusCodes.USER_STATUS 'Complete'
and CRM_StatusCodes.USER_STATUS ' '
and CRM_Orders.PROCESS_TYPE IN ('ZINT','ZSVO')
and cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) <=dateadd(day,-5,getdate())
and CRM_OrganizationalUnits.STEXT = 'Region 9 - Field'
The result set looks like this:
TRANSACTION_ID | USER_STATUS | STEXT
6000000123 | Open | 'Region 9 - Field
6000000124 | Confirmed | 'Region 9 - Field
6000000125 | Released | 'Region 9 - Field
6000000127 | Wait | 'Region 9 - Field
6000000128 | Confirmed | 'Region 9 - Field
And my CRM_Orders.CREATED_AT looks like this: '20090315060047', which is why I'm doing the cast stuff statement so I can perform the calculation against it like in the above statement where I have the '-5'. But it can only display orders that fit the criteria 'CRM_Orders.PROCESS_TYPE IN ('ZINT','ZSVO')' which is a certain type of order.
But I also need my code to use the that calculation 'cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, ....' BUT with a '-10' and it needs to fit this criteria 'CRM_Orders.PROCESS_TYPE IN ('ZRPM')' all in the same query.
I was thinking if I could put the 'CRM_Orders.PROCESS_TYPE IN ('ZINT','ZSVO')' after the 'cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0....''...-5' and
'CRM_Orders.PROCESS_TYPE IN ('ZRPM')' after the 'cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0....''...-10' that would solve it, but I must be missing something very easy.
Thank you again for taking the time....
Michelle 🙂
September 15, 2009 at 3:36 pm
Ok - it sounds to me like you want to know about how to use AND and OR.
This example is entirely silly - but there you go:
If I want to get pizza then I would want a large pizza that had pepperoni or jalapenos
So I might write
SELECT * FROM pizzas inner join toppings on toppings.pizzaid = pizzas.id
where size = large and toppings.name = pepperoni or toppings.name = jalapenos.
The trouble with that is that it's not really specific enough for SQL Server - that would return me pizzas which were large and had pepperoni, or any size pizza with jalapenos. To make it specific enough you need to use brackets
I.e. where size = large and (toppings = pepperoni or toppings = jalapenos)
so - the above examples pretty much cover what you need, but I thought you might find a bit of background handy.
One other thing - you are only looking for specific items from the tables you join onto - so sometimes it can help to specify the things you are looking for in the join clause. So - I might use
select * from pizzas inner join toppings on toppings.pizzaid = pizzas.id and (toppings.name = pepperoni or toppings.name = jalapenos)
where size = large
That does depend though on whether you have indexes which suit that style of query...
HTH 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 3:38 pm
I tried your suggestion Drew and it's what I'm looking for!
Except... I'm getting a 'Incorrect syntax near 'END'.' I'm sure it's something dumb I'm overlooking.
-Michelle 🙂
September 15, 2009 at 3:43 pm
If I want to get pizza then I would want a large pizza that had pepperoni or jalapenos
select * from pizzas inner join toppings on toppings.pizzaid = pizzas.id and (toppings.name = pepperoni or toppings.name = jalapenos)
where size = large
HTH 🙂
Thanks for the advice Matt, but I think I just got hungry reading your post...
-Michelle 😉
September 15, 2009 at 3:43 pm
SELECT DISTINCT CRM_Orders.TRANSACTION_ID,
CRM_StatusCodes.USER_STATUS,
CRM_OrganizationalUnits.STEXT
FROM CRM_Orders
INNER JOIN
CRM_StatusCodes
ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
AND CRM_StatusCodes.USER_STATUS != 'Complete'
AND CRM_StatusCodes.USER_STATUS != ' '
INNER JOIN
CRM_OrganizationalUnits
ON CRM_Orders.ORGANIZATIONAL_ID = CRM_OrganizationalUnits.ORGANIZATIONAL_ID
AND CRM_OrganizationalUnits.STEXT = 'Region 9 - Field'
WHERE (CRM_Orders.PROCESS_TYPE IN ('ZINT', 'ZSVO', 'ZRPM')
AND CAST (STUFF(STUFF(STUFF(CAST (CRM_Orders.CREATED_AT AS Varchar (17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS Datetime) <=
DATEADD(day, CASE WHEN CRM_Orders.PROCESS_TYPE IN ('ZINT', 'ZSVO') THEN -5 ELSE -10 END, GETDATE()));
Just a different take. 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 3:44 pm
mm (9/15/2009)
Thanks for the advice Matt, but I think I just got hungry reading your post...
-Michelle 😉
Heh - snap...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 3:51 pm
mm (9/15/2009)
I tried your suggestion Drew and it's what I'm looking for!Except... I'm getting a 'Incorrect syntax near 'END'.' I'm sure it's something dumb I'm overlooking.
-Michelle 🙂
As I said, I couldn't test it, because you hadn't provided the DDL, sample data, and expected results. It may be a missing/extra paren or something like that.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 15, 2009 at 3:51 pm
Oh Cool!! That worked Matt! You know I never thought about doing it that way!?
I learn something new and cool everytime I come here... thanks to all you experts.
I can see a lot of uses fopr this snippet already 😀
-Michelle
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply