T-SQL (SS2K8) :: Item Sales Batch Allocation
Oct 7, 2014
I found next script for batch item allocation based on FEFO/FIFO method (according to DateCol interpretation) :
DECLARE @Table TABLE
(
RowID int identity Primary Key,
DOCTYPE varchar(40),
DateCol datetime,
[Code] ....
The result looks like :
RowIDDOCTYPEDateCol QTYPRDLOT
1 Purchase2007-01-01 00:00:00.0000AA2007FW
2 Sale 2007-01-03 00:00:00.000-30AA2007FW!2007SS
3 Purchase2007-01-04 00:00:00.00020AA2007SS
4 Sale 2007-01-04 00:00:00.000-20AA2007SS
5 Purchase2007-01-09 00:00:00.00010AA2007FW
The issue is on the second line. What I need is to split that record in 2 or more lines, every line containing one single lot record.
View 5 Replies
ADVERTISEMENT
Dec 2, 2014
I ran the following but only received the top 3 sales.
with cte as (
select SALESMAN_ID,
ORDER_NO,
ORDER_TOTAL,
rank() over (partition by order_no
[Code] ....
results:
salesman_idorder_noorder_total
BF 9389037333.86
BF 8630094288.59
BF 8630813491.90
How can I get top 3 sales per salesmanid:
View 7 Replies
View Related
Mar 10, 2014
My Table struct
create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float
[code]....
like i wanna display each pincodes how to make a code?
View 6 Replies
View Related
May 13, 2015
I have data similar to the below
CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
View 3 Replies
View Related
Jun 16, 2015
I have a table with 370 million rows and 50+ columns. I need to change the data type of a column from character to numeric. Here's what it contains:
40 million with numbers I want to keep, the rest I just want to set to null:
4k with alpha characters
55 million other numbers
275 million empty strings
An alter column statement fails not just on the alpha characters but on the empty strings. So I tried a couple things on a test database to get an idea of the time it would take:
An update statement to clear out the non-numeric data is too slow (~1.5 days, batched 10000 at a time). I think I probably should create a new column anyway though, so I'm going to copy the data to a new table since it would be faster than adding a new column to the original table.
An insert ... select ... takes about 12 hours; adding WITH (TABLOCK) didn't seem to have any effect, and I'm not sure how to batch it. Recovery model is simple.
A select ... into ... only takes about 1 hour, but can't be batched.
Using a 3rd party ETL tool takes about 5 hours, batched.
I wanted to batch it to minimize impact on other queries but primarily the logs. Is there any way to do a fast batched bulk transfer within SSMS?
View 9 Replies
View Related
Apr 24, 2014
I have about 1200 sql files in one of my folders. Almost all of these files do data inserts and updates, so they should be run only once. As and when required I have manually ran around 150 of them already. Whenever I ran any of these scripts, I log that file name into a log table in my sql server including the execution time. Since running 1000+ more files takes a lot of time, I want to automate running of these files through a batch file. But I also want to filter the files that are already run.My file list looks like follows.
InsertToOrderTypes.sql
UpdateClientAddress.sql
DeleteDuplicateOrders.sql
InsertToEmailAddress.sql
ConsolidateBrokerData.sql
UpdateInventory.sql
EliminateInvalidOfficeLocations.sql
My log table in the database looks like this.
select * from sqlfileexecutionlog
FileNameRunTimeResult
---------------------
DeleteDuplicateOrders.sql03/12/2014 14:23:45:091Success
UpdateInventory.sql04/06/2014 08:44:17:176Success
Now I want to create a batch file to run the remaining files from my directory to my sql server. I also want to wrap each of these sql file executions in a transaction and log success/failure along with the runtime and filename into sqlfileexecutionlog table. As I add new sql files into this directory, I should be able to run the same batch file and execute only the sql files that have not bee run.
View 9 Replies
View Related
May 19, 2015
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children: Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Dec 6, 2013
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 Replies
View Related
Mar 6, 2007
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
View 3 Replies
View Related
Jul 12, 2007
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies
View Related
Jan 18, 2007
hi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
View 9 Replies
View Related
Nov 26, 2007
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
View 3 Replies
View Related
Oct 16, 2014
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
View 6 Replies
View Related
Aug 7, 2007
HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
View 4 Replies
View Related
Feb 11, 2002
Hi,
Can someone give me some help on find out the memory limit for SQL Server 7.0 and how to limit the memory being used by the SQL Server?
Thank you very much in advance!
Denise
View 1 Replies
View Related
Sep 14, 2000
Hi,
I need to create a 5GB database with 4GB for data and 1GB for log in v7.0. I know that in v6.5 I would have created five 1GB devices - to go easy on the backups.
Could someone please advise on how I should distibute allocation of space.
Should I allocate 1GB to the primary files and 1GB each to 3 secondary files?
Should I just allocate 4GB to primary?
I would really really appreciate any reponse? If there are articles I would appreciate links.
Thanks
kira
View 1 Replies
View Related
Jan 16, 2001
I have a small data warehouse which periodically has old data deleted.
However after a delete the free space within the database is not released. If I copy the tables, drop them, recreate, and copy back, the space is there.
Any ideas what is causing this anyone ?
Thanks
View 1 Replies
View Related
Nov 4, 2002
I was wondering, is there a way to allocate processors to SQL2000. I have a server that has 4 processors, I would like to leave one just for the operating system and have SQL2000 use the other 3. Is this possible and do you think it would be recomended to do this? Or should i just leave the 4 processors for everything?
thanks in advance
View 1 Replies
View Related
Aug 17, 2006
Dear Friends
I have one database with multipe MDF files.Normally when I am creating a new table it's going to primary MDF file.How I can allocate a new table to the MDF file which I am specifying when table is creating
Thanks in Advance
Filson Paul
View 1 Replies
View Related
May 21, 2007
hai,
once again pavan. i need some clarification on this issues
how sql server allocates memeory for the instances.
means
procedure cache
buffer cache
log cache
system data structure.
could you please provide information regarding this issue
regards,
pavan
pavan
Associate Oracle DBA
View 1 Replies
View Related
Mar 27, 2008
I have windows 2003 with ms sql 2000. The machine has 3.6 gigs of ram and only runs ms sql, nothing else.
Whenever the first query is made sql will allocate as much as 1.5 gigs of ram which is just killing the system. This system reboots nightly so this first query happens every morning.
We have tried setting the min and max memory of sql, as well as the reserver memory setting to see if it will preallocate all that memory but we've had no luck.
Is there a way to make SQL allocate all that memory ahead of time? maybe make it cache some tables or something?
I know very little about MS SQL so please be very descriptive about possible solutions or troubleshooting steps.
Thanks,
--Carlos
View 2 Replies
View Related
Dec 15, 2007
Hi,
can some one throw some light on how the DBAs calucaulate the space allocations?
For example I have 30000 records which has 30 columns each defined as varchar(100) and if the db is full and wants to increase the space. Then how much extrac space should be allocated??
View 5 Replies
View Related
Jan 18, 2008
How to find how much memory allocated to SQLCLR by sqlserver. Also is there any way to determine how much memory my code needs to run? Thanks in advance.
Regards,
Hari Prakash.
View 7 Replies
View Related
Jun 29, 2007
Any comments, please. I have a new server 5x160GB drives. It needs to run IIS for a web app and SQL Server 2005. One user database. I was thinking of dividing space as follows and wanted to get some thoughts from others.
2 drives = RAID 1 set, 160GB space, OS, IIS, SQL exe, tempDB, user database log file
3 drives = RAID 5 set, 320GB space, SQL data files, user database files
Thanks for looking...
View 5 Replies
View Related
Mar 10, 2004
Here is what I am trying to do...
I want to goup "members" togethers in a "group."
A table for members and a table for groups.
each containing coluns...
but inside Groups I would like a column that contains ID numbers for the members that be long to that group.
Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.
Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.
Hope this makes sense
View 1 Replies
View Related
Oct 5, 2000
On two different unrelated servers this week, I got these errors from a DBCC CHECKDB(tempdb):
Msg 8905, Level 16, State 1, Server NTSONYX, Procedure P_DATABASE_BACKUPS, Line 121
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:136752) in database
ID 2 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
A re-boot solved the problem, but what is causing it?
View 2 Replies
View Related
Oct 25, 1999
During daily scheduled maintenance the following error occurs, which causes the maintenance job to fail. How can we fix?
Thanks!
Allocation Discrepancy: Page is allocated but not linked; check the following pages and ids: allocation pg#=1085440 extent id=1085464 logical pg#=1085464 object id on extent=8 (object name = syslogs) indid on extent=0
View 2 Replies
View Related
Sep 28, 1999
I am getting the following error on a version 6.5 database
when I run the weekly database backup.
"Allocation Discrepancy: Page is allocated but not linked; check the following pages and ids: allocation pg#=491520 extent id=491720 logical pg#=491720 object id on extent=8 (object name = syslogs) indid on extent=0"
The backup script I run is as follows..
SQLMAINT.EXE -D ECAP -CkDB -CkAl -UpdSts -BkUpDB F:MSSQLBACKUP -BkUpMedia DISK -DelBkUps 8 -Rpt F:MSSQLLOGECAP_DbBkUp.rpt
This database is a 7 x 24 database. What is the least intrusive and/or best way to correct this problem?
Jim
View 1 Replies
View Related
Jun 21, 2005
EXECUTE master.dbo.xp_sqlmaint N' -D TempDB -CkDB -CkCat '
I run this job on say all DB's and recieve a memory allocation failure.
I run checkdb in QA and it works fine any ideas?
Thanks.
View 5 Replies
View Related
Aug 20, 2015
I'm log shipping to AWS. Currently, we are shipping from a server with 64 GB RAM to one at AWS with 30. It's not a massive server but does have periods of high usage when certain jobs/tasks run. It's been running fine for a year but the server size is large and is barely used. My understanding is that for simply log shipping, we don't need a lot of memory and I was hoping to drop the instance size down and use an instance with 16 GB Ram instead. It would save thousands per year.
View 2 Replies
View Related
Sep 13, 2007
I would like to ask regarding the memory allocation fo SQL Server 2000. For example if my Data Server have 8GB physical memory installed how much memory can SQL Server 2000 utilize? Based on my research and understing SQL 2000 Server can only utilize 3GB memory? But using the AWE you can set the memory to a maximum server memory?
View 8 Replies
View Related