Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[spGetFinancialMovementsBetweenDates] @dtStart datetime, @dtEnd datetime AS DECLARE @dOpeningBalance as DECIMAL(10,2) DECLARE @dClosingBalance as DECIMAL(10,2) DECLARE @dCreatedInPeriod as DECIMAL(10,2) SELECT c.dt, SUM(c.InvTotal) as bf, SUM(c.CreatedInDT) as created_dt INTO #tt FROM (SELECT a.Uploaded_DT, b.dt, MAX(a.[Invoice Total]) as InvTotal, CASE WHEN CONVERT(varchar(10),MAX(a.[Date Created]),103) = b.dt THEN MAX(a.[Invoice Total]) ELSE 0 END as CreatedInDT FROM TableA a INNER JOIN QueryB b ON b.uploaded_dt = a.Uploaded_DT AND a.Uploaded_DT >= @dtStart AND a.Uploaded_DT <= DATEADD(day,1,@dtEnd) WHERE a.[Date Created] IS NOT NULL GROUP BY a.uploaded_dt, b.dt, a.[Payee Number], a.[Invoice Number] ) c GROUP BY c.dt SET @dOpeningBalance = (SELECT bf FROM #tt WHERE dt = CONVERT(varchar(10),@dtStart,103)) SET @dClosingBalance = (SELECT bf FROM #tt WHERE dt = CONVERT(varchar(10),@dtEnd,103)) SET @dCreatedInPeriod = (SELECT SUM(created_dt), CASE WHEN dt > CONVERT(varchar(10),@dtStart,103) and dt <= CONVERT(varchar(10),@dtEnd,103) THEN 1 ELSE 0 END as grp FROM #tt GROUP BY CASE WHEN dt > CONVERT(varchar(10),@dtStart,103) and dt <= CONVERT(varchar(10),@dtEnd,103) THEN 1 ELSE 0 END ) SELECT @dOpeningBalance as bf, @dCreatedInPeriod as [in], (@dopeningbalance - @dClosingBalance) + @dCreatedInPeriod as [out], @dClosingBalance as cf DROP TABLE #tt GO
|Linking a DMV to a database id/sql text in SQL server 2008||8||46|
|Query / Window function ?||3||18|
|t-sql split string into multiple rows||7||37|
|How to get max value of non numeric field in MS SQL table when field contains alpha characters||2||9|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!