quote:

---

Originally posted by khtan

Select
     convert(varchar(10),JMPSH.CreateDate,111) + '('
     Datename(Weekday, JMPSH.CreateDate)) + ')' as PackagingslipDate 
From Cet.dbo.JMPackSlipHeader as JMPSH

---

KH
[spoiler]Time is always against us[/spoiler]


thanks khtan

it worked

i tried it like this....just added the convert statement on the weekday part....
SELECT
convert(varchar(10),JMPSH.CreateDate,111) + ' ' + '(' + Datename
(Weekday,convert(varchar(10),JMPSH.CreateDate,111))+ ')' as PackingSlipDate
FROM Cetus.dbo.JMPackSlipHeader as JMPSH


results

2009/06/03 (Wednesday)
2009/06/05 (Friday)
2009/06/01 (Monday)
2009/06/12 (Friday)
2009/06/02 (Tuesday)
2009/06/12 (Friday)

---

quote:

---

Originally posted by khtan

you don't need to convert to varchar for the datename() function. It accepts datetime

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

you don't need to convert to varchar for the datename() function. It accepts datetime

---

KH
[spoiler]Time is always against us[/spoiler]


Hi KH

Any idea how to add column totals in a Pivot query table....

here is the query
I need to add column total to my pivot query and appear as last row in the table results.....


Select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other]

From

(

SELECT HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120) as PackingSlipDate,Count(JMPSH.pkPackingSlipID) AS Total

FROM Cetus.dbo.JMPackSlipHeader as JMPSH

INNER JOIN Cetus.dbo.HHContainerType as HHCT ON JMPSH.fkContainerType = HHCT.pkContainerTypeID

INNER JOIN usCustomers.dbo.Customers As C ON JMPSH.CustomerID = C.CustomerID

Where JMPSH.CreateDate between '2009-05-01' and '2009-05-31'

and C.MerchPreference = 'Y'

GROUP BY HHCT.ContainerType,convert(varchar(10),JMPSH.CreateDate,120)


) AS S

PIVOT

(
Sum(Total)
FOR ContainerType IN ([Bag],[BoxAA],EmptyStand],[FullStand],[Other]
) AS P
ORDER BY PackingSlipDate


I need results to look like this:

Packaging Date Bag BoxAA EmptyStand FullStand Other
2009/05/04 4 38 8 4 NULL
2009/05/05 17 175 10 NULL NULL
2009/05/06 15 442 4 16 NULL
2009/05/07 6 561 10 5 NULL
2009/05/08 8 372 12 NULL NULL
2009/05/11 4 142 3 1 NULL
2009/05/12 16 151 21 11 NULL
Total 70 1881 68 37 0

---

;with cte (PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other])
as
(
    < your existing query here >
)
select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other]
from   cte
union all
select 'Total', sum([Bag]), sum([BoxAA]), sum([EmptyStand]), sum([FullStand]), sum([Other])
from   cte

quote:

---

Originally posted by khtan

use CTE

;with cte (PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other])
as
(
    < your existing query here >
)
select PackingSlipDate, [Bag],[BoxAA],[EmptyStand],[FullStand],[Other]
from   cte
union all
select 'Total', sum([Bag]), sum([BoxAA]), sum([EmptyStand]), sum([FullStand]), sum([Other])
from   cte

---

KH
[spoiler]Time is always against us[/spoiler]

Hi KH

I am still working on getting the column totals...not really good with CTE....

---

quote:

---

Originally posted by khtan

do you have problem with your query ? What's the problem ?

---

KH
[spoiler]Time is always against us[/spoiler]

I am not sure how to use CTE with Pivot query table...where to add the CTE query to get column totals.

---