August 8, 2013 at 4:23 am
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
August 8, 2013 at 4:36 am
You'll need something like:
SUM(CASE WHEN C.Contact_Date = MT.[Date of Expense] THEN 1 ELSE 0 END)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2013 at 4:38 am
james.ingamells (8/8/2013)
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
The count you require is just a single number, isn't it, whereas what you've got at the moment is a result set. You're probably best doing a separate query for the count, something like this:
SELECT
COUNT(*)
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] AND c.Contact_Date = mt.[Date of Expense ]
John
August 8, 2013 at 5:08 am
I need a count on each row rather than a total for the data set.
I have tried the SUM(CASE WHEN C.Contact_Date = MT.[Date of Expense] THEN 1 ELSE 0 END) suggestion but its just pulling through 0 raher than a value??
any other suggestions?
August 8, 2013 at 5:19 am
How can you have a count on each row? It'll either be 1 or 0 (equal or not equal). Please will you provide some sample data and expected results so that we can see what you mean?
John
August 8, 2013 at 5:27 am
This is a real stab in the dark;
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]
, x.ContactCount
--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
INNER join lchs__esr_dwh.dbo.dwh_201_staff_list S
on S.[Assignment Number] = MT.[Assignment Number ]
INNER join lchs_data_warehouse.dbo.dwh_091_s1_smartcard_list SC
on SC.[Smartcard ID] = S.[CRS Identifier]
OUTER APPLY (
SELECT ContactCount = COUNT(*)
FROM nhs_costing.dbo.cost_contacts c
WHERE C.[DoneBy1] = SC.[Name]
AND C.Contact_Date = MT.[Date of Expense ]
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 8, 2013 at 6:27 am
Hi,
I have attached a file with the relevant columns on. What i am looking to do is lookup the 'name' column in the nhs_costing.dbo.cost_contacts view (called doneby1) and count the number of time it appears per date (column c as attached)
Thanks for taking the time to look
August 8, 2013 at 6:40 am
Hi Chris,
I think what the script you have written appears about right to what i am looking for but keep getting this message: Any ideas why?
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "C.Contact_Date" could not be bound.
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
, x.ContactCount
--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
INNER join lchs__esr_dwh.dbo.dwh_201_staff_list S
on S.[Assignment Number] = MT.[Assignment Number ]
INNER join lchs_data_warehouse.dbo.dwh_091_s1_smartcard_list SC
on SC.[Smartcard ID] = S.[CRS Identifier]
OUTER APPLY (
SELECT ContactCount = COUNT(*)
FROM nhs_costing.dbo.cost_contacts C
WHERE C.[DoneBy1] = SC.[Name]
AND C.Contact_Date = MT.[Date of Expense ]
) x
August 8, 2013 at 6:44 am
Hi James
The table alias 'c' is now used in the APPLY subquery and is not visible to the outer SELECT list. If it were in the SELECT list of the APPLY subquery, you would reference it with the alias 'x'. However, since C.Contact_Date = MT.[Date of Expense ], simply substitute C.Contact_Date with MT.[Date of Expense ] in the outer SELECT list
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply