July 28, 2009 at 10:31 am
I wrote following code to find averages for two months but I dont know how to combine both blocks without joining it.
I tried 1=1 but its not working.
SELECT 'Order Entry Time' AS Classification,
AVG(OrderEntryTime1.FirstEntryMonth1) AS FirstMonth,
AVG(OrderEntryTime2.SecondEntryMonth1) AS SecondMonth
FROM
(
SELECT [Sales Order Number],CONVERT(NUMERIC(19,2),DATEDIFF(MINUTE,MIN(CONVERT(DATETIME,STR(YEAR([Date Sent]))+
'-'+STR(MONTH([Date Sent]))+'-'+STR(DAY([Date Sent]))+' '+STR(DATEPART(HOUR,[Time Sent]))+
':'+STR(DATEPART(MINUTE,[Time Sent]))+':'+STR(DATEPART(SECOND,[Time Sent])))),
MAX(CONVERT(DATETIME,STR(YEAR(CreateDate))+'-'+STR(MONTH(CreateDate))+'-'+STR(DAY(CreateDate))
+' '+STR(DATEPART(HOUR,CreateDate))+':'+STR(DATEPART(MINUTE,CreateDate))+
':'+STR(DATEPART(SECOND,CreateDate))))))/60 AS FirstEntryMonth1
FROM dbo.tblEDI856SentInfo EDI1 (NOLOCK) INNER JOIN dbo.tblMASSOSalesOrder MAS1 (NOLOCK)
ON EDI1.[Sales Order Number]=MAS1.[TranNo]
WHERE MONTH(EDI1.[Date Sent])=1 AND YEAR(EDI1.[Date Sent])=2009
GROUP BY [Sales Order Number]
) AS OrderEntryTime1
(
SELECT [Sales Order Number],COUNT([Sales Order Number]) AS TCount,CONVERT(NUMERIC(19,2),DATEDIFF(MINUTE,MIN(CONVERT(DATETIME,STR(YEAR([Date Sent]))+
'-'+STR(MONTH([Date Sent]))+'-'+STR(DAY([Date Sent]))+' '+STR(DATEPART(HOUR,[Time Sent]))+
':'+STR(DATEPART(MINUTE,[Time Sent]))+':'+STR(DATEPART(SECOND,[Time Sent])))),
MAX(CONVERT(DATETIME,STR(YEAR(CreateDate))+'-'+STR(MONTH(CreateDate))+'-'+STR(DAY(CreateDate))
+' '+STR(DATEPART(HOUR,CreateDate))+':'+STR(DATEPART(MINUTE,CreateDate))+
':'+STR(DATEPART(SECOND,CreateDate))))))/60 AS SecondEntryMonth1
FROM dbo.tblEDI856SentInfo EDI1 (NOLOCK) INNER JOIN dbo.tblMASSOSalesOrder MAS1 (NOLOCK)
ON EDI1.[Sales Order Number]=MAS1.[TranNo]
WHERE MONTH(EDI1.[Date Sent])=2 AND YEAR(EDI1.[Date Sent])=2009
GROUP BY [Sales Order Number]
) AS OrderEntryTime2
July 28, 2009 at 10:32 am
Have you tried a cross-join?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2009 at 10:40 am
No, I dont want to use cross join because by using that i guess it will give me wrong average.
July 28, 2009 at 11:14 am
So - what result are you trying to get to? Are you looking to do averages over BOTH data sets (meaning - go for a UNION ALL set) or some other combination?
Data examples of what you want, would REALLY help to steer you in the right direction.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 28, 2009 at 12:09 pm
First, you really should simplify your code. I struggled through what you were trying to accomplish with your statement. I figured out that this section CONVERT(DATETIME,STR(YEAR(CreateDate))+'-'+STR(MONTH(CreateDate))+'-'+STR(DAY(CreateDate))
+' '+STR(DATEPART(HOUR,CreateDate))+':'+STR(DATEPART(MINUTE,CreateDate))+
':'+STR(DATEPART(SECOND,CreateDate))) serves the sole purpose of truncating the milliseconds. That code can be replaced with this very simple code CreateDate. That's right. All of those gyrations you're going through serve no purpose, because this code is used as an argument for a DateDiff() on minutes and DateDiff() ignores datetime parts more granular than the datepart being used. You can run the following code to verify this. The times are only 3 ms apart.SELECT DateDiff(mi, '2008-07-28 11:59:59.997', '2008-07-28 12:00:00.000')
In the first part, it looks like you have two fields on for the date portion and one for the time portion of the date/time sent. You can simplify these conversions by the following Convert(datetime, Convert(char(11), [Date Sent], 120) + Convert(char(12), [Time Sent], 114))
Finally, when you want to do the exact same calculations for two separate months and show them on the same row, you should think about using PIVOT or something similar.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 28, 2009 at 12:15 pm
I want the output in following format:
---------------------------------------------------
Classification | Jan AVG | Feb AVG |
-------------------------------------------------- |
Order Entry Time | FirstMonth | SecondMonth |
---------------------------------------------------
I have two blocks of code, first block calculates FirstMonth Average and second block calculates SecondMonth Average. I am not able to combine both blocks in order to produce above output.
I want to join both blocks but not on any condition but just for combining.
July 28, 2009 at 1:07 pm
shahm10 (7/28/2009)
I want the output in following format:---------------------------------------------------
Classification | Jan AVG | Feb AVG |
-------------------------------------------------- |
Order Entry Time | FirstMonth | SecondMonth |
---------------------------------------------------
I have two blocks of code, first block calculates FirstMonth Average and second block calculates SecondMonth Average. I am not able to combine both blocks in order to produce above output.
I want to join both blocks but not on any condition but just for combining.
I'm confused now - that IS a cross join like has been suggested to you.
Of course - if you have more than one row in either of the two "halves", you're going to match up every record on the "left" with every record on the "right".
If that's not what you want then there is ing to be some form of a join, just not one that's obvious right now (like - link row A with next month's row)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply