February 24, 2012 at 7:15 am
Hi
I am hoping for a little guidance with the query below.
It returns the number of units made and a value of the items. due to some strange business logic the unit qty and the value aren't for the same products, life would be easier if it were!!!
I want the data to look like,
hour qty value
7 10 100
8 20 200
etc.
I have a few subqueries that pull the hour from the datetime field, one that does the qty and one that does the value. when I run the query I am getting many enteries and duplication I realise that I don't have a common thread through the subqueries that connect the hour to the qty to the value. So I get all enteries for all hours.
If anyone can point me in the right direction to the solution I will be very very greatful
Thanks
Select HourOfDay,Pieces,myValue,myType
from
(
select substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2) as HourOfDay
FROM PROD_ASSEMBLY_BARCODES
where (
SCAN_ASSEMBLED_DATE between convert(datetime,cast( day(getdate()) as varchar(2))+ '/' + CAST ( month(getdate()) as varchar(2))+ '/' + cast (YEAR(getdate())as varchar(4)),103)
and
convert(datetime,cast( day(getdate()) as varchar(2))+ '/' + CAST ( month(getdate()) as varchar(2))+ '/' + cast (YEAR(getdate())as varchar(4)) + ' 23:59:59',103)
)
AND
(
PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='a' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='b' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='c' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='d' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='e' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='g')
group by substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2)
) as subquery1
,
(
SELECT Count(PROD_ASSEMBLY_BARCODES.MATERIAL_ID) AS Pieces
FROM PROD_ASSEMBLY_BARCODES
where (
SCAN_ASSEMBLED_DATE between convert(datetime,cast( day(getdate()) as varchar(2))+ '/' + CAST ( month(getdate()) as varchar(2))+ '/' + cast (YEAR(getdate())as varchar(4)),103)
and
convert(datetime,cast( day(getdate()) as varchar(2))+ '/' + CAST ( month(getdate()) as varchar(2))+ '/' + cast (YEAR(getdate())as varchar(4)) + ' 23:59:59',103)
)
AND
(
PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='a' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='b' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='c' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='d' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='e' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='g')
group by substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2)
) as subquery2
,
(
SELECT isnull(Sum ( (PROD_ASSEMBLY_BARCODES.ITEM_GROSS) - (([ITEM_DISCOUNT]/100)*[ITEM_GROSS]) - (([ITEM_retro]/100)*[ITEM_GROSS]) + (PROD_ASSEMBLY_BARCODES.ITEM_CARRIAGE) - (([SETTLEMENT_PERCENT]/100)*[ITEM_GROSS])),0) as myValue
FROM PROD_ASSEMBLY_BARCODES INNER JOIN SOP_CUSTOMER ON PROD_ASSEMBLY_BARCODES.ACCOUNT = SOP_CUSTOMER.ACCOUNT INNER JOIN SOP_DISCOUNT ON SOP_CUSTOMER.SETT_PERCENTAGE_CODE = SOP_DISCOUNT.ID
where
(
SCAN_ASSEMBLED_DATE between convert(datetime,cast( day(getdate()) as varchar(2))+ '/' + CAST ( month(getdate()) as varchar(2))+ '/' + cast (YEAR(getdate())as varchar(4)),103)
and
convert(datetime,cast( day(getdate()) as varchar(2))+ '/' + CAST ( month(getdate()) as varchar(2))+ '/' + cast (YEAR(getdate())as varchar(4)) + ' 23:59:59',103))
group by substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2)
) as subquery3
,
(
select
'Today' as myType
) as subquery4
group by HourOfDay,Pieces,myValue,myValue,myType
February 24, 2012 at 7:32 am
Nice job posting your desired output and what you have tried so far. Unfortunately there is little anybody can do without ddl (create table scripts) and sample data (insert statements). Take a look at the first link in my signature for best practices on posting this info to provide you with the best assistance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2012 at 6:07 am
Ok, thanks 🙂
I have attached 4 files the one called tables has the code to create the tables, and the files called examples have test data for the tables.
If anyone could help point in me in the right direction so I can have a join through my subqueries or even a different way to approach the solution I would be very greatful.
many thanks!!
February 27, 2012 at 7:36 am
OK let's try this again. The file with the tables is useful but sample data should be insert statements (preferably ones that you tested).
Last but not least, assuming you provide consumable sample data we still don't know what you want as output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2012 at 8:17 am
Hi
I have created a sql script that creates a test db and 3 tables it imports 2 of the tables ok, but I have tried all day on the 3rd and no joy I have remed it out but left it in the file.
Funnily enough it imports perfect through the GUI, so if someone would be able to import it that way that would be great. I just can't see a reason why it won't import using bulk insert with a format file included in the script.
The file includes the query I have written and you will see that it returns multiple enteries as apposed to
a single entry per hour number of pieces and the value.
the files should be saved in c:\TestData
I am sorry if I didn't explain my requirements very well, I would like the query to return
hour of day pieces value
7 20 15000
8 25 19000
thanks for any help
February 28, 2012 at 8:35 am
The last thing I want to do is to parse all this to strip our your database and schemas so that I can get your data into my test environment. That is why we ask for insert statements. Not tsql to do bulk inserts into hardcoded database and schema names. Yes it is more work for you but you are much more likely to get more of the volunteers around here to help.
Again I point you the first link in my signature about how to post questions in a format that will generate lots of help from lots of people.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 29, 2012 at 4:00 am
I have had a reread!!!
The way explained seems alot better than what I was tying to do.
I think this file ticks all the boxes, creates a db, creates tables and test data and my current query that returns multiple enteries instead of one entry per hour as I would like.
thanks for any assistance, I appreciate th way to create the data that has been a useful exercise in itself.
February 29, 2012 at 4:53 am
I haven't got time at the moment to tidy up the SQL or check the result accuracy for that matter (I'll leave that part up to you!)
However take a look and see if this gives you what you are looking for or maybe points you in the right direction
/* This is the query I came up with but it doesn't return unique records need to join the time field through the subqueries, */
/* or solve the problem another way. */
select subquery1.HourOfDay,Pieces,myValue
from
(
select substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2) as HourOfDay
FROM PROD_ASSEMBLY_BARCODES
where (
SCAN_ASSEMBLED_DATE between convert(datetime, '27/02/2012',103)
and
convert(datetime,'27/02/2012 23:59:59',103)
)
AND
(
PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='a' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='b' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='c' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='d' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='e' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='g')
group by substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2)
) as subquery1
LEFT JOIN
(
SELECT Count(PROD_ASSEMBLY_BARCODES.MATERIAL_ID) AS Pieces
,substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2) as HourOfDay
FROM PROD_ASSEMBLY_BARCODES
where (
SCAN_ASSEMBLED_DATE between convert(datetime, '27/02/2012',103)
and
convert(datetime,'27/02/2012 23:59:59',103)
)
AND
(
PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='a' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='b' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='c' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='d' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='e' Or PROD_ASSEMBLY_BARCODES.STK_PRODUCTION_LINE='g')
group by
substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2)
) as subquery2
ON subquery1.HourOfDay = subquery2.HourOfDay
LEFT JOIN
(
SELECT isnull(Sum ( (PROD_ASSEMBLY_BARCODES.ITEM_GROSS) - (([ITEM_DISCOUNT]/100)*[ITEM_GROSS]) - (([ITEM_retro]/100)*[ITEM_GROSS]) + (PROD_ASSEMBLY_BARCODES.ITEM_CARRIAGE) - (([SETTLEMENT_PERCENT]/100)*[ITEM_GROSS])),0) as myValue
, substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2) as HourOfDay
FROM PROD_ASSEMBLY_BARCODES INNER JOIN SOP_CUSTOMER ON PROD_ASSEMBLY_BARCODES.ACCOUNT = SOP_CUSTOMER.ACCOUNT INNER JOIN SOP_DISCOUNT ON SOP_CUSTOMER.SETT_PERCENTAGE_CODE = SOP_DISCOUNT.ID
where
(
SCAN_ASSEMBLED_DATE between convert(datetime, '27/02/2012',103)
and
convert(datetime,'27/02/2012 23:59:59',103)
)
group by substring(cast(SCAN_ASSEMBLED_DATE as varchar(20)),13,2)
) as subquery3
ON subquery1.HourOfDay = subquery3.HourOfDay
Cheers
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
February 29, 2012 at 5:03 am
Thanks andy, just working through it now but it looks pretty Good!!!!:-)
February 29, 2012 at 5:19 am
Your welcome - hope it works for you 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
February 29, 2012 at 5:27 am
It works!! spent a couple of days reading articles on using the select in the from and join part of the query when I see it with my tables it makes sense!!
I would normally used temp tables and logic to get the results in the past.
no more!!
many thanks!!!!!!
February 29, 2012 at 5:43 am
Temp Tables and Table Variables certainly have their uses especially if you have a large script with using many in-line views like this. I've managed to get scripts that just won't run down to seconds using Temp Tables as in enables you to break the query up into manageable chunks so I would still use them if the situation calls for it! 😀
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply