Debit v Credit
Hi, Can someone assist me? I am trying to produce a total by month and year for all months for this year and last year even if there are no records present. I also need my second query to be taken from the first query to give me a total of debits v credits. How do i join the two queries together to get my results? Query 1 SELECT th .addressIdForPrimary ,MONTH(th.TransactionDate) AS 'Month' ,YEAR(th.TransactionDate) AS 'Year' ,CONVERT(nvarchar(4),YEAR(th.TransactionDate)) + RIGHT(N'0' + CONVERT(nvarchar(2), MONTH(th.TransactionDate)), 2) AS 'MonthYear' ,Quantity = (Case when sum(th.TotalQuantity) is null then 0 else sum(th.TotalQuantity) End) , Total = (Case when SUM(tf.TotalNettRounded) is null then 0 else SUM(tf.TotalNettRounded) End) FROM transactionheader as th INNER JOIN transactionfigures as tf ON tf.transactionheaderId = th.Id AND th.CompanyDivisionId = 1 AND tf.CategoryIdForCurrency = 62 INNER JOIN WorkflowStage ON WorkflowStage.Id = th.WorkflowStageId AND WorkflowStage.Id IN ('48') WHERE th .addressIdForPrimary = 7319 GROUP BY th .addressIdForPrimary ,YEAR(th.TransactionDate) ,MONTH(th.TransactionDate) ORDER BY th.addressIdForPrimary Query 2 SELECT th .addressIdForPrimary ,MONTH(th.TransactionDate) AS 'Month' ,YEAR(th.TransactionDate) AS 'Year' ,CONVERT(nvarchar(4),YEAR(th.TransactionDate)) + RIGHT(N'0' + CONVERT(nvarchar(2), MONTH(th.TransactionDate)), 2) AS 'MonthYear' ,Quantity = (Case when sum(th.TotalQuantity) is null then 0 else sum(th.TotalQuantity) End) , Total = (Case when SUM(tf.TotalNettRounded) is null then 0 else SUM(tf.TotalNettRounded) End) FROM as th INNER JOIN tf ON tf.transactionheaderId = th.Id AND th.CompanyDivisionId = 1 AND tf.CategoryIdForCurrency = 62 INNER JOIN WorkflowGroupStage ON WorkflowGroupStage.WorkflowStageId = th.WorkflowStageId AND WorkflowGroupStage.WorkflowGroupId IN ('14') WHERE th .addressIdForPrimary = 7319 GROUP BY th .addressIdForPrimary ,YEAR(th.TransactionDate) ,MONTH(th.TransactionDate) ORDER BY th.addressIdForPrimary ,YEAR(th.TransactionDate) ,MONTH(th.TransactionDate)
November 25th, 2010 7:34pm

Hi Sergei Can you recommend a download from google, preferably a safe one! Thanks LordLucanM Stoker
Free Windows Admin Tool Kit Click here and download it now
November 26th, 2010 11:44am

Use this script to create your own calendar table - Why should I consider using an auxiliary calendar table? It will be the safe download of T-sql script.Sergei
November 26th, 2010 5:33pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics