Holidays in SQL Server

Hi!
I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
Suggestions are welcome!
Sincerely,
Nils Magnus EnglundCreating a holidays/calendar table is a good thing. This will surely simplif
y
your search. Remember, your holidays might not be the same as mine so having
the
table will eliminate such.
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wan
t
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where no
t
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||I think it is a good idea to have a calendar table.
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> glsD
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>

Holidays in SQL Server

Hi!

I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.

Suggestions are welcome!

Sincerely,
Nils Magnus Englund"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:2rT%b.103$72.176991232@.news.telia.no...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.

That's probably your best idea.

Your holidays may not be mine.

> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund|||Nils Magnus Englund (nils.magnus.englund@.orkfin.no) writes:
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
> want to select all rows from that table, excluding days which fall on
> holidays or weekends. What is the best way to accomplish this? I
> considered creating a new table called "holidays" and then selecting all
> rows (sort of "where not in (select * from holidays)") , but I was
> looking for a better solution since that implies that I have to populate
> the "holidays" table.

And how would you expect SQL Server to know about syttende maj or when
Midsummer is?

You can of course make the holidays table more or less sophisticated.
You can just put in all Mondays to Fridays that are not dates from now
to 2020 or whatever.

You can also write a stored procedure that fills in the table given the
rules about currently known holidays. You would need to find data on
where Easter falls, to determine days for Easter, Whitsun and Ascenion Day.

Yet an alternative is to put all days in that table, and then a flag
whether the day is a working day or not, no matter whether it's Friday
or Sunday.

And finally, for the SELECT it self I prefer:

SELECT *
FROM tbl t
WHERE NOT EXISTS (SELECT *
FROM holidays h
WHERE t.date = h.date)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

hii

Write SQL Queries for the following:

Cust

Cust_ID

Name

1

AA

2

BB

3

CC

4

DD

5

EE

Ord

Ord_ID

Cust_ID

Amt

Tran_Type

From_Ord_ID

1

1

10

D

NULL

2

1

20

D

NULL

3

2

30

D

NULL

4

2

40

D

NULL

5

2

NULL

D

NULL

6

3

NULL

D

NULL

7

4

40

D

NULL

8

4

50

D

NULL

9

4

-10

C

7

10

4

-20

C

8

11

4

-30

C

8

<!--[endif]-->Write a query that shows the balance for customer DD by trans_type. Consider a Tran_type of “C” to be a transactional adjustment to the associated original “D” record. The result should be as follows.

Name

D

C

Balance

DD

40

-10

30

(It looks like that time of year again, when we start getting requests to do class assignments.)

Subhash,

Please post the efforts you have made up to now, and we can help guide you to a correct solution. I just don't think that you will find folks here willing to do your classwork for you. Also post the table DDL, and sample data in the form of INSERT statements. If you need help with that concept, check here or here.

|||

hahaha.

well its actually easy. you need to use join

and sum function and group by

sql

hii

Hiiiiii
I want a qry which will give me the list of views which are not used in
any SPs in that database. like suppose i have a view view1 and I have
used that view in one of my SPs and i have a view called . View2 its
just created but never called in any SPs. Somebody ll help me ?(reneeshprabha@.gmail.com) writes:

Quote:

Originally Posted by

I want a qry which will give me the list of views which are not used in
any SPs in that database. like suppose i have a view view1 and I have
used that view in one of my SPs and i have a view called . View2 its
just created but never called in any SPs. Somebody ll help me ?

That would be:

SELECT o.name
FROM sysobjects o
WHERE NOT EXISTS (SELECT *
FROM sysdepends d ON o.id = d.depid)
AND o.type = 'V'

However, be very very careful. If you recreate a view, all dependency
information is lost, and thus that view will appear as unused in this
query.

It may be better to script the stored procedures to a text file, and
then search the files for occurrences of the view name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95
SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>
|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>
|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;[vbcol=seagreen]
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500
>
|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did[vbcol=seagreen]
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do
>

Highest balance

Hi
I have table with all customers transactions
I am trying to create a query that can show me the balance of our customers.
I am trying to see when customer had the highest balance. How can I do this?
My customers transactions
Record number
Date.
Customer
Amount
21850
1.1.2004
1111
-1.699,85
21851
1.1.2004
1111
-638,71
21852
1.1.2004
1111
-2.795,87
21853
1.1.2004
1111
144,21
21854
1.1.2004
1111
25.472,30
21855
1.1.2004
2222
2.501,91
21856
1.1.2004
2222
19.942,04
21857
1.1.2004
2222
1.518,95SELECT Customer, Date, Amount
FROM YourTable
WHERE Amount =
(SELECT MAX(Amount)
FROM YourTable Y1
WHERE Y1.Customer = YourTable.Customer)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Thank you for your answer Adam, but this did not work for me. Maybe I didn't
explain this right. I am not trying to get the highest amount from the
Column "Amount". I am trying to get the highest balance. Maybe I need to
create Column balance and calculate from Amount. Is that possible?
Example:
In this example i am trying to get the amount of 7000 that is the highest
balance for this customer
Record Date Customer Amount
1 01.01.04 3344 5000
2 01.01.04 3344 2000
3 01.01.04 3344 -1000
4 01.01.04 3344 -500
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> Hi
>
> I have table with all customers transactions
> I am trying to create a query that can show me the balance of our
customers.
> I am trying to see when customer had the highest balance. How can I do
this?
>
> My customers transactions
>
> Record number
> Date.
> Customer
> Amount
> 21850
> 1.1.2004
> 1111
> -1.699,85
> 21851
> 1.1.2004
> 1111
> -638,71
> 21852
> 1.1.2004
> 1111
> -2.795,87
> 21853
> 1.1.2004
> 1111
> 144,21
> 21854
> 1.1.2004
> 1111
> 25.472,30
> 21855
> 1.1.2004
> 2222
> 2.501,91
> 21856
> 1.1.2004
> 2222
> 19.942,04
> 21857
> 1.1.2004
> 2222
> 1.518,95
>
>
>|||Ahh, now I understand...
The first step is to calculate a running balance:
SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
Then we can use this as a derived table in an outer query to get the max per
customer... I've also added the date to the outer part of the query in case
you want that:
SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
FROM YourTable
JOIN
(SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
GROUP BY Tbl1.Customer, Tbl1.Record
) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
GROUP BY YourTable.Customer, YourTable.Date
Note, I've used Tbl2.Record in order to determine the order of transactions;
if possible, you should use the date instead. I didn't, as the dates you
provided were non-unique.
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Thank you for your answer Adam, but this did not work for me. Maybe I
didn't
> explain this right. I am not trying to get the highest amount from the
> Column "Amount". I am trying to get the highest balance. Maybe I need to
> create Column balance and calculate from Amount. Is that possible?
> Example:
> In this example i am trying to get the amount of 7000 that is the highest
> balance for this customer
> Record Date Customer Amount
> 1 01.01.04 3344 5000
> 2 01.01.04 3344 2000
> 3 01.01.04 3344 -1000
> 4 01.01.04 3344 -500
>
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23MmNgchUEHA.3016@.tk2msftngp13.phx.gbl...
> customers.
> this?
>|||Thank you Adam this works great you saved my day. Your first query "Running
balance" can I save this balance into my Customers transaction table? I did
create column called "running balance" in my Customers transaction table.
regards
Benedikt Fridbjornsson
Computer department
SIF Iceland
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> Ahh, now I understand...
> The first step is to calculate a running balance:
> SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
>
> Then we can use this as a derived table in an outer query to get the max
per
> customer... I've also added the date to the outer part of the query in
case
> you want that:
> SELECT YourTable.Customer, YourTable.Date, MAX(Tbl0.Balance) As Balance
> FROM YourTable
> JOIN
> (SELECT SUM(Tbl2.Amount) AS Balance, Tbl1.Customer, Tbl1.Record
> FROM YourTable Tbl1
> JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
> AND Tbl2.Record <= Tbl1.Record
> GROUP BY Tbl1.Customer, Tbl1.Record
> ) Tbl0(Balance, Customer, Record) ON Tbl0.Record = YourTable.Record
> GROUP BY YourTable.Customer, YourTable.Date
>
> Note, I've used Tbl2.Record in order to determine the order of
transactions;
> if possible, you should use the date instead. I didn't, as the dates you
> provided were non-unique.
>
> "Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
> news:%23bG$jZiUEHA.2580@.TK2MSFTNGP12.phx.gbl...
> didn't
highest[vbcol=seagreen]
5000[vbcol=seagreen]
2000[vbcol=seagreen]
-1000[vbcol=seagreen]
-500[vbcol=seagreen]
>|||Of course...
UPDATE YourTable
SET RunningBalance =
(SELECT SUM(Tbl2.Amount)
FROM YourTable Tbl1
JOIN YourTable Tbl2 ON Tbl1.Customer = Tbl2.Customer
AND Tbl2.Record <= Tbl1.Record
AND Tbl1.Record = YourTable.Record
GROUP BY Tbl1.Customer, Tbl1.Record)
"Benedikt Fridbjornsson" <benni@.sif.is> wrote in message
news:uBVKsurUEHA.2668@.TK2MSFTNGP10.phx.gbl...
> Thank you Adam this works great you saved my day. Your first query
"Running
> balance" can I save this balance into my Customers transaction table? I
did
> create column called "running balance" in my Customers transaction table.
> regards
> Benedikt Fridbjornsson
> Computer department
> SIF Iceland
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OsGd0piUEHA.1172@.TK2MSFTNGP10.phx.gbl...
> per
> case
> transactions;
you[vbcol=seagreen]
to[vbcol=seagreen]
> highest
> 5000
> 2000
> -1000
> -500
do[vbcol=seagreen]
>sql

High usage of RAM on SQL2k

Hi

I have an sql server with very high usage. The web which is connected to the sql server has approx. 1000 unique visitors every time.
The database is optimized and the site goes really fast now.

But a problem we've had since the beginning is that SQL Server constantly uses 1.8 GB of RAM. Never less, never more.

Do you have any idea on how to decrease the usage of RAM. I am sure that this high usage is necessary.check your logs,if they are more truncate them,
may be it helps you.|||But arent the log files truncated during the backup?|||Logs?

run sp_configure and tell me what the results are for min server memory.|||min server memory (MB) 0 2147483647 0 0

Hierchical data for xml

Hi
I am trying to build a query that will give me hierarchical data but am
having little success.
How can I change the query below to give me xml data in a hierchical format,
nesting multiple customer addresses under one customer id?
Thanks for your asistance
Regards
Habib
---
use adventureworks
select c.CustomerID, co.FirstName, co.LastName,
a.AddressID, pa.AddressLine1, pa.City, pa.PostalCode
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
join Sales.CustomerAddress a
on c.CustomerID = a.CustomerID
join Person.Address pa
on a.AddressID = pa.AddressID
where c.CustomerID in( 254, 11532)
for xml pathUse a nested query
select c.CustomerID,
(select co.FirstName,
co.LastName,
a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Person.Contact co
inner join Sales.CustomerAddress a on c.CustomerID =
a.CustomerID
inner join Person.Address pa on a.AddressID = pa.AddressID
where c.CustomerID = co.ContactID
for xml path,type)
from Sales.Customer c
where c.CustomerID in( 254, 11532)
for xml path|||Mark,
That was very helpful. Thanks.
The key was to use the Type directive in the nested query. I missed that
when reading BOL.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||Again thanks for your help.
I have a nice little query as below:
How can I wrap this with two additional tags
<request>
<addOrUpdate>
<Customers>
..
</Customers>
</addOrUpdate>
</request>
--
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers')
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 21
---
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||This should work
select (
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers') ,root('addOrUpdate'),type)
for xml path ('request')|||
Mark,
I discovered something similar when I was tinkering with the query.
select ( select ...
for XML PATH ('Customer'),type) as [addOrUpdate]
for xml path ('request'),type
But try as i might, I cant seem to get end tag to appear after each
</Customers>
I expect the difference is that the record is committed after each
</addOrUpdate>, so without it wrapping each record, the entire XML file is
either committed or entirely rolled back.
One might argue the relative merits of either approach but the specification
requires the end tags after each record.
How can I get that in?
Thanks for the assistance so far. It has been very helpful.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148715862.851001.69320@.j33g2000cwa.googlegroups.com...
> This should work
> select (
> select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('Customers') ,root('addOrUpdate'),type)
> for xml path ('request')
>|||Is this what you're after?
select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('addOrUpdate') ,root('request'),type|||Mark,
No, I should have been clearer with what I meant
I have pasted what I want below.
I have tried tinkering with the SQL statement but it seems impossible to me.
Thanks again and regards
Habib
<request>
<addOrUpdate>
<Customers>
<CustomerID>254</CustomerID>
<FirstName>Helen</FirstName>
<LastName>Dennis</LastName>
<address>
<AddressID>185</AddressID>
<AddressLine1>2681 Eagle Peak</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
<address>
<AddressID>861</AddressID>
<AddressLine1>25915 140th Ave Ne</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<request>
<addOrUpdate>
<Customers>
<CustomerID>11532</CustomerID>
<FirstName>Madison</FirstName>
<LastName>White</LastName>
<address>
<AddressID>201</AddressID>
<AddressLine1>6202 Seeno St.</AddressLine1>
<City>Sammamish</City>
<PostalCode>98074</PostalCode>
</address>
<address>
<AddressID>20692</AddressID>
<AddressLine1>6437 Brookview Dr.</AddressLine1>
<City>Redmond</City>
<PostalCode>98052</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<markc600@.hotmail.com> wrote in message
news:1148762909.570472.310590@.38g2000cwa.googlegroups.com...
> Is this what you're after?
>
> select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('addOrUpdate') ,root('request'),type
>|||I believe this will work
select
(select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
for xml path ('addOrUpdate'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('request')|||YeeHaw! That worked.
Thank you very much for your assistance. You are a genius.
I tried various other combinations such as adOrUpdate/Request and
@.addorUpdate but did not know think of using the FOR XML PATH before the
final from clause. Of course now it makes sense.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148799893.889247.42490@.38g2000cwa.googlegroups.com...
>I believe this will work
> select
> (select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> for xml path ('addOrUpdate'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('request')
>

Hierarchial Queries - Order of the data

Hi

I am using SQL Server 2005 Developer edition(Sep 05). I had an oracle hierarchial query the equivalent of which I had written in SQL Server. The problem is the order of the data is different in SQL Server.

To put in proper context :

I need

1. The root node
2. The root’s children and the children of roolt's children and so on and so forth

I get

1. The root node
2. The root’s immediate children
3. The children of the root’s immediate children
4. And so forth.

Find below the DDL, Insert script, the Hierarchial Query, the data expected to return and the actual data returned.

--
-- - DDL Script -
--

CREATE TABLE [sfmfg].[SFPL_MFG_BOM_TEST](

[ITEM_ID] [varchar](40) NOT NULL,

[MFG_BOM_CHG] [varchar](4) NOT NULL,

[PARENT_ITEM_ID] [varchar](40) NOT NULL,

[PARENT_MFG_BOM_CHG] [varchar](4) NOT NULL,

CONSTRAINT [SFPL_MFG_BOM_TEST_PK] PRIMARY KEY CLUSTERED

(

[ITEM_ID] ASC,

[MFG_BOM_CHG] ASC,

[PARENT_ITEM_ID] ASC,

[PARENT_MFG_BOM_CHG] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--
-- - Insert Script --
--

INSERT INTO SFPL_MFG_BOM_TEST (ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'SE1','A', 'N/A', 'N/A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('MF1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'CYL1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('P1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('TB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BT1','A', 'MF1', 'A')

--
-- - Hierarchial Query -
--

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

--
-- - Expected Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3

P1 A SE1 A 2

--
-- - Returned Data -
--

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

- -- - --SE1 A N/A N/A 1

CYL1 A SE1 A 2

MF1 A SE1 A 2

P1 A SE1 A 2

BB1 A MF1 A 3

BT1 A MF1 A 3

TB1 A MF1 A 3
Any help in this matter would be greatly appreciated.

Thanks & Regards
Imtiaz

Found solution...Here's how the query needs to look like....

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level,SortKey)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level,

CAST(item_id as varchar(8000))+ CAST(mfg_bom_chg as varchar(8000)) as SortKey

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1,

CAST(p.SortKey as varchar(8000)) + CAST(c.item_id as varchar(8000))+ CAST(c.mfg_bom_chg as varchar(8000) )

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

order by SortKey