compare child records - t-sql

I have four records, each containing a date.
LineNo ShipDate RefLine
--
1 1/30/05 <null>
2 1/1/05 1
3 1/15/05 1
I want to compare the date in line 1 to 2, then 2 to 3
Basically, the lines belong to the same order. What is going on is
when we want to change a ship date for an order line, we make a copy of
the original line (line 1 in this case), close the copied line out
(line 2 or 3) and reference the original line in it using a
user-defined field (labeled RefLine). The user then goes back to the
original line and changes the ship date. We are trying to track how
many times an orderline is pushed out, and calculate the days between.
The reason we cant just make a new line and set the date there has to
do with links in the original that can't be easily changed. basically
I want my query to look like:
LineNo ShipDate RefLine DaysMoved
---
1 1/30/05 <null> 15
2 1/1/05 1 <null> (this was org ship date)
3 1/15/05 1 15
---
Total 30
I can get the group total just by getting the difference between the
current and min dates, but in my report if I sum the over all using
this method I get 60 days because there are two detail lines.
Anyone help is appreciated...I figured it out. Here is the query I used:
SELECT
co.CUSTOMER_ID
, co.ORDER_DATE
, c.NAME AS 'CUSTOMER_NAME'
, cl.CUST_ORDER_ID AS 'ORDER_ID'
, cl.LINE_NO
, cl.ORDER_QTY
, cl.PART_ID
, m.LINE_NO AS 'MOVE_LINE_NO'
, m.DESIRED_SHIP_DATE
, m.REASON_CODE
, m.REASON_CODE_DESCRIPTION
, (SELECT TOP 1 DESIRED_SHIP_DATE FROM RV_MOVED_SHIP_DATES WHERE
order_id = cl.CUST_ORDER_ID AND
line_no > m.line_no AND
COPIED_FROM_LN = cl.LINE_NO AND
m.DESIRED_SHIP_DATE <
DESIRED_SHIP_DATE) AS 'NEXT_SHIP_DATE'
, cl.DESIRED_SHIP_DATE AS 'CURRENT_DATE'
, m.COPIED_FROM_LN
FROM
dbo.CUSTOMER_ORDER co
INNER JOIN
dbo.CUST_ORDER_LINE cl ON co.ID = cl.CUST_ORDER_ID
INNER JOIN
dbo.CUSTOMER c ON co.CUSTOMER_ID = c.ID
INNER JOIN
dbo.RV_MOVED_SHIP_DATES m ON cl.LINE_NO =
m.COPIED_FROM_LN AND
cl.CUST_ORDER_ID = m.ORDER_ID
WHERE
co.ORDER_DATE BETWEEN @.START_DATE AND @.END_DATE
ORDER BY
co.CUSTOMER_ID, cl.CUST_ORDER_ID, cl.LINE_NO, m.LINE_NO
Stephen wrote:
> I have four records, each containing a date.
> LineNo ShipDate RefLine
> --
> 1 1/30/05 <null>
> 2 1/1/05 1
> 3 1/15/05 1
> I want to compare the date in line 1 to 2, then 2 to 3
> Basically, the lines belong to the same order. What is going on is
> when we want to change a ship date for an order line, we make a copy of
> the original line (line 1 in this case), close the copied line out
> (line 2 or 3) and reference the original line in it using a
> user-defined field (labeled RefLine). The user then goes back to the
> original line and changes the ship date. We are trying to track how
> many times an orderline is pushed out, and calculate the days between.
> The reason we cant just make a new line and set the date there has to
> do with links in the original that can't be easily changed. basically
> I want my query to look like:
> LineNo ShipDate RefLine DaysMoved
> ---
> 1 1/30/05 <null> 15
> 2 1/1/05 1 <null> (this was org ship date)
> 3 1/15/05 1 15
> ---
> Total 30
> I can get the group total just by getting the difference between the
> current and min dates, but in my report if I sum the over all using
> this method I get 60 days because there are two detail lines.
> Anyone help is appreciated...

Compacting a db via T-SQL

Hello dbForumers,

I was wondering if there is a way (MUST be) to instruct SQL server to compact a chosen database's files. I have a batch that runs every night who generate a huge amount to log lines that I get rid of with a backup log xxx with truncate_only, but still the logfile is several GB big afterwards with a lotsa empty space... I wanna get a clean small file everyday =)

Thank you!Its not ideal to shrink the Transaction log every day, rather define a set of size by testing the activity on the database.

During that batch overnight take before and after sizes for Tlog and set the higher level.

Also maintain regular backups of Tlogs which will reduce the size of logical file and helps to fillup the Tlog quickly. If RECOVERY MODEL Is set to SIMPLE then ensure full database backups are carried in regular intervals.|||try DBCC SHRINKFILE

USE UserDB
GO
DBCC SHRINKFILE (logical file name,Target_size)
GO

Target_size is how much free size is left over after you shrink
if you leave this off, you will get the default.

also
you may have to switch the VLogs internally before you can shrink to a size that you desire...
all of his should be done after a tlog backup.
look up "Shrinking the Transaction Log" AND DBCC Shrinkfile in [BOL]

comment/uncomment keyboard shortcuts changed .. grrrrr!!!!

Call me old-school, but I happened to like the one-key Shift-Ctrl-C and Shift-Ctrl-R methods of commenting and uncommenting T-SQL code. I've tried to see how I can set up my own macro's to give me those key combos back instead of the double-the-work ctrl-K + ctrl-C and ctrl-K + ctrl-U keyboard short cuts.

The k-c one is the worst because it takes two hands to type it, but the two fingered k-u isn't much better.

Can someone tell me how I can override or augment the keyboard shortcuts so that I can get these two familiar short-cuts back. I don't care if I have to override something .. whatever it is, it's not nearly as imporant as this to me.In Management Studio goto Tools>Options and select Environment>Keyboard.

Change the Keyboard scheme to SQL Server 2000

--

HTH

Jasper Smith (SQL Server MVP)

http://www.sqldbatips.com

I support PASS - the definitive, global

community for SQL Server professionals -

http://www.sqlpass.org

wrote in message

news:c3b85d6a-9303-480e-877d-7bb4caf9cab6@.discussions.microsoft.com...

> Call me old-school, but I happened to like the one-key Shift-Ctrl-C and

> Shift-Ctrl-R methods of commenting and uncommenting T-SQL code. I've

> tried to see how I can set up my own macro's to give me those key combos

> back instead of the double-the-work ctrl-K + ctrl-C and ctrl-K + ctrl-U

> keyboard short cuts.

>

> The k-c one is the worst because it takes two hands to type it, but the

> two fingered k-u isn't much better.

>

> Can someone tell me how I can override or augment the keyboard shortcuts

> so that I can get these two familiar short-cuts back. I don't care if I

> have to override something .. whatever it is, it's not nearly as

> imporant as this to me.

>|||In Management Studio goto Tools>Options and select Environment>Keyboard. Change the Keyboard scheme to SQL Server 2000

Comment/Uncomment keyboard shortcut changed .. grrrrr!!!!

Call me old-school, but I happened to like the one-key Shift-Ctrl-C and Shift-Ctrl-R methods of commenting and uncommenting T-SQL code. I've tried to see how I can set up my own macro's to give me those key combos back instead of the double-the-work ctrl-K + ctrl-C and ctrl-K + ctrl-U keyboard short cuts.

The k-c one is the worst because it takes two hands to type it, but the two fingered k-u isn't much better.

Can someone tell me how I can override or augment these so that I can get these two familiar short-cuts back. I don't care if I have to override something .. whatever it is, it's not nearly as imporant as this to me.I'm getting used to just using the toolbar comment/un-comment.

Have to agree, there are a few 'little' things that are quite irritating...

I miss the line/column count in the status bar at the bottom. Made finding your errors fairly easy most of the time.

I miss the spinning globe on the dropdown Window list to see if a long running process is still running.

And the ctrl-b to move the resultpane was nice too. It's pretty hit or miss to get the mouse just right to move it now...

Nor can you scroll with a mouse wheel in the Execution Plan Results anymore.

Progress eh? :)|||Actually for Visual Studio developers the change is welcome/necessary because that is consistent with the VS environment. I have to say you have a point about the k-c combination though |||In Management Studio you should be able to go to

Tools -> Customize -> Commands -> Keyboard, then pick Keyboard in Environment and change Keyboard Scheme to SQL Server 2000. You should get your shortcuts back.

HTH,
Boris.

p.s. I should add that you will NOT get ALL of your shortcuts back. Sorry!|||That worked.

You get a Big kiss(tm) for that little tid-bit!

X X
X
X X

:-)|||Yeap, ctrl-b still doesn't work, bummer. Thanks for the tidbit though.

It's nice to have the comment/uncomment back. :)|||

As in the VS2005 IDEs you can use Ctrl-K,C Ctrl-K,U to comment/uncomment source, that's an option, or not ?

Sascha

Combining XML Files using SSIS or T-SQL etc.

How can I combine all my xml files so it can be processed by SSIS in order
that I may create a table containing the imported xml files?Loop?
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
> How can I combine all my xml files so it can be processed by SSIS in order
> that I may create a table containing the imported xml files?
>|||What is Loop?
Could you please explain more in detail.
Thank you in advance for your assistance.
"Peter W. DeBetta" wrote:

> Loop?
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:187DB17A-FA31-4236-844A-7068BE9C072E@.microsoft.com...
>
>|||SSIS now has a Control Flow Item called Foreach Loop Container. You use this
to loop through all the xml files in a specified directory. You will also
need to create a Data Flow Task in the Foreach Loop Container. The samples
that come with SQL Server 2005 have an example of using this container
control, and you can find more info in BOL.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5EAFCB63-AC58-4592-A7DF-786AF5CABBBF@.microsoft.com...
> What is Loop?
> Could you please explain more in detail.
> Thank you in advance for your assistance.
> "Peter W. DeBetta" wrote:
>