How to do Cross Tabbing ?? Please help ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to do Cross Tabbing ?? Please help ...

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2006-07-31 : 06:03:51

Ok..thanks for answering my previous problem, but i still have an another huge problem regarding the existing query. In the below query for each Invoice Number i again have to perform a sum based on the "LinkId" codes and i would have to group say for eg. 10 of the link id's into one resultset for e.g. known as Insurance Revenue and display the sum beneath that... I have gained from some internet search as it could be performed by cross tabbing, but dont have any clue about it. I have attached the query below please have a look and any help would be much appreciated as i am really in big trouble right now..

DECLARE @FromDate BigInt
DECLARE @ToDate BigInt

SET @FromDate = dbo.DateToBigInt('20060110')
SET @ToDate = dbo.DateToBigInt('20060120')

SELECT
DISTINCT
j.ID AS 'ID',
j.inquiryRef AS 'EnquiryRef' ,
CAST( dbo.BigIntToDate(mod.date) AS DateTime) AS 'EnquiryDate',
j.jobRef AS 'JobReference' ,
CAST( dbo.GetJobDate(j.Id) AS DateTime) AS 'JobDate',
si.tranref AS 'InvoiceNumber' ,
j.inquiryLogged ,
0 As 'CostOfSales',
0 AS 'InvoiceSum'

INTO #temp1

FROM
Job AS j
LEFT OUTER JOIN Modification mod ON mod.id = j.inquiryLogged
LEFT OUTER JOIN SalesInvoice si ON si.job = j.id
WHERE mod.[date] BETWEEN @FromDate AND @ToDate

UPDATE t SET CostOfSales = f.CostOfSales
FROM #Temp1 t
INNER JOIN
(
SELECT j.inquiryLogged,
SUM(CASE
WHEN ISNULL(jce.actualCost,0)<>0 THEN jce.actualCost
WHEN ISNULL(jce.actualCost,0)=0 and ISNULL(jce.manualOverRide,0)<>0 THEN jce.manualOverRide
ELSE ISNULL(jce.originalEstimate,0)
END) AS 'CostofSales'
FROM
Job AS j
LEFT OUTER JOIN JobCostElements jce ON j.Id = jce.job
LEFT OUTER JOIN Modification mod ON mod.id = j.inquiryLogged

WHERE mod.[date] BETWEEN @FromDate AND @ToDate

Group by j.inquiryLogged
) AS f

ON f.inquiryLogged = t.inquiryLogged

SELECT * From #temp1
ORDER BY InvoiceNumber

SELECT DISTINCT
si.tranref AS 'InvoiceNumber',
nlc.name AS 'Name',
nlc.Id AS 'LinkId',
SUM(isnull(sil.amount,0)) AS 'MySum'
INTO #linkc

FROM Job As j
LEFT OUTER JOIN SalesInvoice si ON si.job = j.id
LEFT OUTER JOIN SalesInvoiceLines sil ON sil.trannr = si.id
LEFT OUTER JOIN NominalLinkCode nlc ON nlc.id = sil.revenuecode
LEFT OUTER JOIN Modification mod ON mod.id = j.inquiryLogged

WHERE mod.[date] BETWEEN @FromDate AND @ToDate

GROUP BY nlc.name , si.tranref , nlc.id

SELECT * FROM #linkc
ORDER By InvoiceNumber

DROP Table #temp1
GO
DROP Table #linkc
GO

These are the resultset of the #linkc temp table..So as u can see i would need to club 10-15 of the linkid based on their values into one heading saying for e.g. Insurance Revenue and perform a sum on the corresponding heading..

Invoice Number Name LinkId

SSC909473 Sea - Household Effects -7614996620969066557

Sum
2881.0000000000

SSC909480 Relo - School Search 5341925543685311178 750.0000000000

SSC909491 NULL NULL 0.0000000000

SSC909491 Air - Household Effects 7362119764037766978 95.0000000000

SSC909533 Relo - HS - Package Prog -954181502169738295 2295.0000000000

Any help would be much appreciated..

Cheers!!
A..

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-31 : 07:05:52

How do you want to calculat the Sum..??

I mean how did you get this value
Sum
2881.0000000000

???

Chirag