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.
| Author |
Topic |
|
jamesingamells
Starting Member
11 Posts |
 Posted - 2013-08-08 : 06:14:55
|
| Hi,My SQL knowledge is basic to average and need some help in counting rows in a join where 2 dates match:SELECT MT.[Organisation ] ,MT.[Assignment Number ] ,MT.[First Name ] ,MT.[Last Name ] ,MT.[Position Title ] ,MT.[Site Name ] ,MT.[Date of Expense ] ,MT.[Reason ] ,MT.[Expense Item ] ,MT.[From ] ,MT.[FromPostcode ] ,MT.[To ] ,MT.[ToPostcode ] ,MT.[Step Number ] ,MT.[Step Mileage ] ,MT.[Total Journey Mileage ] ,MT.[Total Journey Value ] ,S.[Assignment Number] as [Assignment No.] ,S.[CRS Identifier] ,S.[Org Cost Centre] ,SC.[Name],C.[Contact Date] --need to add in a new line something like below --,count (C.Contact_Date) when C.Contact_Date = MT.[Date of Expense ]FROM [lchs_ref].[dbo].[Mileage_Table2] MT join lchs__esr_dwh.dbo.dwh_201_staff_list S on S.[Assignment Number] = MT.[Assignment Number ] join lchs_data_warehouse.dbo.dwh_091_s1_smartcard_list SC on SC.[Smartcard ID] = S.[CRS Identifier]join nhs_costing.dbo.cost_contacts C on SC.[Name] = C.[DoneBy1]Dont know if what i am trying is poosible but any help would really be appreciated.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-08 : 06:19:46
|
it should be...sum (case when C.Contact_Date = MT.[Date of Expense] then 1 else 0 end) over().. ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Select and Count and Join